NW Business Intelligence

thoughts on technology, B.I., and more…

SSRS Custom Assemblies require .NET 3.5?

Posted by Brad Greene on June 10, 2011

I’ve been working on prepping for my Microsoft 70-448 certification and learning a ton of things. Yesterday I was adding some custom assemblies to a report using VB as my code base. I decided to try using Visual Studio Express 2010 to build the DLL as an added exercise. That worked as expected. Then I copied the DLL to the required folders and defined the references in the report. So far no problems. However, when I went to preview the report I got an error which indicated that my assembly was of a version later than that supported. That left me scratching my head for a few minutes.

It occurred to me to check the Advanced Compiler properties in VSE for the assembly I had just built. There I noticed  the target .NET Framework was set to 4.0. I suspected that was the problem and set it to 3.5, recompiled the DLL and tried it all again. Sure enough, that was the issue. I then went to Google and confirmed it.

So, if you build custom assemblies for SSRS 2008 R2, as of June 2011, you probably have to stick with compiling for the 3.5 .NET Framework. I did not see any posts or articles for work-arounds but that doesn’t mean one isn’t possible. Never say never.

Posted in Business Intelligence, Tech Tips | Comments Off on SSRS Custom Assemblies require .NET 3.5?

Delivering on the promise

Posted by Brad Greene on June 1, 2011

For the past half dozen years I have spent the majority of my time helping companies get down that last mile or two of a BI project. While not always, it was often the case that I was there because IT had found the burden of delivering a BI solution was more than they bargained for. It may be they underestimated the skills required; maybe they were oversold by the vendor on the ease of implementation; often they were just slammed with other vital work. One advantage I always had in these situations was being allowed to work mostly outside the boundaries of the typical IT processes. Ah yes, the so called “cowboys” had arrived is what I’m sure the IT people thought. However, they had no choice because their customers demanded a solution be delivered now! And there in lies my point. We all exist to serve those business users, our real customers. In BI that means delivering solutions in a way that is often very different than other applications IT typically delivers.

I just read a Forrester BI trend report that confirms what I have been seeing and thinking for a while now. This trend towards centralizing BI into IT is not paying off. I understand why it has happened. We needed the buy in from upper management, the sponsors, data governance, master data management and all that. No doubt about it. That is all vital but it is a Fuastian deal. At least as far as our real customers are concerned because along with becoming a part of the centralized IT structure inevitably comes bureaucracy, layers of process ill suited to BI and we end up lost and bogged down unable to deliver on the promise. I’m sure somewhere a company has managed to make it work but overall it appears to be a growing problem. The Forrester report suggests we let IT hang on to data preparation and turn the rest back over to the business. Interesting. We need to do something different, that is for certain.

I don’t want to return to the “cowboy” days and throw all the hard earned lessons and best practices out the window but we need to realize that our customers move at the speed of their business. They are mad at us because we can’t keep pace. We need to be brutally honest with ourselves and answer the question “Is everything I’m doing adding value for my customer and am I doing it in a way that meets ALL their needs?”. That means getting them solutions quickly and standing side by side with them so we know what it feels like when we fail to deliver.

Posted in Business Intelligence | Comments Off on Delivering on the promise

Analytics as a Service, could be

Posted by Brad Greene on May 9, 2011

The idea of offering analytics as a specialization in the world of SaaS, or software as a service, has been around for a while now. In healthcare you have companies like Verisk and in the web space there is Google of course. Whether the offerings are really a form of SaaS or just spin, like putting the word “cloud” in front of your favorite technology platform, depends on the company in question. However, a friend posted a link to an article by Derrick Harris about Opera Solutions on his Facebook page that caught my attention. The article is titled Check Out the Big Data Expert You’ve Never Heard Of. Nope never have, and it was interesting to read up on what they are doing. Besides the article you can go to the company web site here, Opera Solutions. Sounds like a smart group of people doing something well. I also noted they are making use of Hadoop tech here. This company is worth keeping an eye on.

Posted in General News, Technology | 1 Comment »

SSRS security setup on Windows 7

Posted by Brad Greene on April 26, 2011

Here are a collection of tips for getting SSRS 2008 R2 working under Windows 7 on a simple setup like a single user laptop. I have had to go through this a couple times and were a few things that tripped me up so I thought I would try to pull all the solutions into one post in case it helps someone save some time. If you are getting the error message…

User ‘someDomain\someUser’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

…when trying to open Report manager there is a good chance the following may help.

This approach is the easiest and is only appropriate for a single user laptop or developer kind of set up. If you need UAC then go figure out how UAC is supposed to be configured and make that work for you. This way just makes it work by putting your user account into the security role SSRS needs to avoid the UAC conflict.

First you need to run your browser as Administrator. This the first place you can get tripped up. The “Run as…” option is used (accessed with a right of the mouse if you didn’t know) but don’t try to do this on the IE icon in the Taskbar. Open Explorer and navigate to the the folder that has iexplore.exe executable and do it there. Now you will actually be running IE as the Admin account you select from the dialog that pops up.

If you have done the above correctly you should be able to successfully navigate to your new SSRS Reports folder. The default URL would typically be http://localhost:80/Reports. If you used a name instead of “localhost” then it might be http://lenovo-t60p:80/Reports for example. The 80 port is the default and obviously you need to change that if you used a different port. The main point is that if you installed 2008 R2 and SSRS correctly, then configured SSRS correctly and are running IE as Admin you should get to a page that looks something like the following. If you are still getting the original error that brought you here it is time to think about starting the installation over and checking for any missed steps. Is the Report service is running? Can you see the databases created by the SSRS configuration tool in SSMS?

Once you have gotten the screen above the rest is easy in my opinion. All that remains is to add your user account to the Admin roles in two places under SSRS and then you will be able to run IE from your normal user account.

So, while still running IE as Admin, navigate to the Site Settings, click on Security, then New Role Assignment and add your account to the list. After adding myself to the role list my setup looks like the screen shot below.

Now, we need to do this in one more place. We need to add our account to the Home folder security role list. Navigate back to the default start screen which is the Home folder and click on Folder Settings. Then using New Role Assignment add your account to the list.

If there is nothing else wrong with your SSRS configuration you should be good to go. Exit IE and start IE again using your normal account to see if you can access the SSRS service URL now.

The only other issue I had was not having http://localhost/ (or in my case http://lenovo-t60p/) in the list of Trusted sites in IE’s security settings. To do this you will have to uncheck the check box that restricts entries to https only.

 

Good luck with your new SSRS 2008 R2 installation. The tool is looking better than ever!

[Edit] – One more thing I ran into on an early attempt. I was using AVG Free 9.x anti virus software. It was interfering with SSRS so I removed it. I probably could have tweaked it to exclude some folders or whatever but I didn’t have the time for that so I tried Lavasoft’s Ad-Aware and have had no problems with it right out of the box.

Posted in Tech Tips, Technology | Comments Off on SSRS security setup on Windows 7

Using server side functions in Cognos

Posted by Brad Greene on April 24, 2011

When creating Cognos reports using Report Studio you have the option to embed functions in queries and query items. There are two basic types of functions available. There are Cognos provided functions that are built into the product. Using these functions insures that there is no dependency on a third party database. This is important for reasons of portability. The other type are database specific functions which are vendor specific.

Cognos does not provide functions for every situation and it is sometimes necessary to use a database vendor provided function. It is important to realize two things when you do this. 1) Your report is very likely no longer portable between database environments and 2) You may have to change the Processing settings for the query subject in order for the Report to function. The reason you may have to change the Processing settings is because you may have used both Cognos and database functions in the same query. This confuses Cognos in a way. It tries to process every function as local and fails. You will have to set the processing to Database only to force Cognos to convert its own functions to database versions of the same and thus all processing will be done in SQL before the data is returned to Cognos. Below is an example of using the MS SQL Server Datepart function. Note, the use of curly braces in place of quotes around the parameters.

  datepart({year},[time_stamp]) * 100 + datepart({month},[time_stamp])

Posted in Tech Tips | Comments Off on Using server side functions in Cognos

Building a new blog site

Posted by Brad Greene on April 22, 2011

Just put this site up tonight. Going to move all the content from the old site over here and start a new one using WordPress. It’s been a few years since I created the old one and while Joomla is a good CMS it is overkill for my needs. WP is just so much easier to manage when all you need is a blog and a few pages of static content. Keeping WP up to date is also a snap and Dreamhost makes the installs a no brainer as well.

Getting this far took less than an hour of total effort. How cool is that? Now the real work begins…

Posted in General News | Comments Off on Building a new blog site

Microsoft’s BI Stack is Back

Posted by Brad Greene on April 15, 2011

A couple of years ago I had figured Microsoft was killing their BI product efforts. Word on the street was that key people and products were being slayed left and right. Well that may have been true but it seems to have been part of a plan to attack the market from a different angle. I’m not a sales guy or even a marketing whiz but I am good with technology and from what I have seen in 2008 R2, Microsoft BI is back! OK, maybe I’m late to the party but I finally arrived and it looks pretty good. I’ve been reading a bunch of “what’s new in R2” articles and blogs and finding them interesting so I ordered up my copy of 2008 R2 a few weeks ago.

The upgrade was pretty painless. I had some issues with my laptop installation and setup of SSRS but thanks to some help from a very smart friend and the removal of AVG Free antivirus software things are humming along nicely. Report Builder is looking really good and they have done a lot in the area of ease of use based on my recollection of 2005. I know Cognos Report Studio very well and I have to say Cognos, get cracking on some new updates. This stuff looks quite good at a price point that a lot of companies are going to find hard to pass up. I’ve never been really thrilled with the charting features in Cognos so I want to see what I can do with the latest in Report Builder.

Next stop is PowerPivot then SSAS. SSAS being the bigger nut to crack but I want to see how it stacks up too. I’ve heard there are even more cool things on the horizon so it may be time to fully spool up on Microsoft BI. After all, they are at the top of the Gartner BI Quadrant this year. Amazing.

Posted in Business Intelligence | Comments Off on Microsoft’s BI Stack is Back

BI is not just reports!

Posted by Brad Greene on September 9, 2009

Does it feel like sometimes the BI team does nothing more than deliver reports, updates to reports and more variations on the same reports? I have a hunch that is the case more often than a lot of us would like to admit. The promise of BI is always big when making the initial investment in software and hardware. That juicy suite of tools and studios are demonstrated as a tightly integrated whole capable of pumping out solutions that will turn your business into a well oiled machine. Executive management is sold if even half of it turns out to be true. But, initially at least, efforts almost always focus on reports, operational in most cases.

It’s understandable. These are the low hanging fruit. The data is easily captured and delivered to a data mart and the reports themselves are well defined and heavily used. A nice quick win to leverage a big investment. Everyone wins. However, what happens next is key I think because this is where organizations have to fight to hang on to their original vision. That quick initial win feed on itself but not always in a good way. You are now going to get more and more requests to build operational reports. Your demand is going to grow exponentially. Along with it comes production support burdens that are not trivial. You will be supporting a growing user base that is dependent on a system that must be operational during peak business periods. Unless you have planned for this burden you will find your team now consumed and converted to an operational reporting development and support team! And that brings me back to my opening sentence. It’s now going to be very hard to go back to management and ask for more resources to deliver on the original big picture solution they were so excited about.

It’s the proverbial slippery slope. I think what has to happen is more BI leaders have to resist that urge to shine by delivering on operational reporting and push harder towards engaging on projects that deliver complete package solutions. That means finding projects that can use a wider range of components. One that has an analytical piece, a simple dashboard, some basic event notification based on business process and maybe some scheduled report delivery element. Obviously this will vary based on the tools you have to work with. The idea is to look for projects that will exercise the full suite and deliver a solution to a real business problem from end to end. That is where you will be contributing the most to the company’s success. Letting your BI team get stuck in the rut of operational reporting is a failure to deliver on the promise of BI.

Posted in Business Intelligence | Comments Off on BI is not just reports!

More on data visualization

Posted by Brad Greene on May 27, 2009

A few months back I wrote about the aesthetics of information, or information visualization, and how it is a vital part of getting your reports (charts and presentations, etc.) to be more effective. I’m always on the look out for tools and articles on the topic so this posting is just a nod to a couple things I’ve seen and liked lately in the area of data visualization.

First, to Stephen Few at Perceptual Edge. I recieved an older white paper he had written for Cognos on the principals of viusal communication that led me to his web site, which of course is his business storefront. Of interest is a nice list of articles and other white papers here. He includes both articles of his own as well as from other authors on the topic. I like his style of writing and he seems to keep an open mind and avoid stating absolutes. I have not had a chance to read many of his articles but the few I have read lead me to think he is a real champion of data visualization.

My experience has been that most BI developers don’t understand the importance of visual design in the their work. The tendency is to brush it off as “eye wash” or leave it for the end users to bring up in user acceptance testing. I’m biased perhaps because my educational background in architecture included the visual as a key element in every “deliverable”. Communicating with the eye in that field is essential. I don’t see it any other way in BI when part of a deliverable includes something more than lists and cross tabs.

Lastly, a nod to a company getting some buzz lately is Lyzasoft. People are blogging about them and there are some lively discussions. I have not had a chance to check them out beyond getting a feel for where they sit in the broad spectrum of BI. Sounds like they sit somewhere between Tableau and QlikView. I’ll have to add them to my list of tools to try after my very positive experience with Tableau a few months back. From what I’ve read I don’t expect them to be that advanced in visualization though.

Posted in Business Intelligence | Comments Off on More on data visualization

Compare SQL Server tables

Posted by Brad Greene on January 5, 2009

Once in a while it’s nice to compare the list of tables in one database with another to see what might be missing. Here is a simple select statement to do that. In this case the databases are on different servers as well. The server names are in brackets. Any table missing in database B will show as ‘NULL’ in the list next to the table name found in database A.

You need the ability to connect to both of servers in SQL Server Management Studio. I’m sure there are other ways to do this depending on what level of access you have or tool you are using. This works in SQL Server 2005 at least.

select A.TABLE_NAME, B.TABLE_NAME from [DB_A].database_A.INFORMATION_SCHEMA.TABLES A
left join [DB_B].database_B.INFORMATION_SCHEMA.TABLES B
on A.TABLE_NAME = B.TABLE_NAME
order by 1

You can refine it by limiting it to only those that are missing in B this way:

select A.TABLE_NAME, B.TABLE_NAME from [DB_A].database_A.INFORMATION_SCHEMA.TABLES A
left join [DB_B].database_B.INFORMATION_SCHEMA.TABLES B
on A.TABLE_NAME = B.TABLE_NAME
where
B.TABLE_NAME is null
order by 1

Posted in Tech Tips | Comments Off on Compare SQL Server tables