NW Business Intelligence

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

Archive for the ‘Business Intelligence’ Category

Business intelligence related

Testing Reports, How Well Should We Do It?

Posted by Brad Greene on October 14, 2012

I think everyone would agree that one of the long held goals in Business Intelligence, the delivery of a known (hopefully high) quality deliverables, is a very critical success factor. If your BI team does not deliver this your customers (end users) quickly become disillusioned and suspicious of your work. Even small mistakes, made once too often, can do long term damage and you will spend months, maybe years, proving your work can be trusted. As the importance of the data warehouse has risen, the need for rigorous testing of deliverables has risen. However, my observation is in many cases the resources applied to testing has not kept pace; especially when compared to other areas of software development.

This posting is specifically about testing reports. This is an area where I think we can do much better. As developers we understand that every link in the BI delivery chain is critical to delivering quality. However, time and again, I see the a dramatic drop in the level of sophistication for testing reports from that applied to the other steps in BI delivery. Development teams work so hard to make sure their data is loaded on time, cleansed, transformed into business value and made available to their end users only to be presented in reports that may have defects. Those defects are slipping in because testing reports is not easy. It is deceptively complex.

I’m most familiar with the Cognos product suite but I believe that many of the challenges testing reports apply to other BI suites. Here are some I see:

Reports are typically not written in a procedure language (commonly XML, CSS, HTML, etc.)
Reports are database dependent so output is infinitely variable
Reports often contain graphic output like charts and images
Reports can be output in more than one format (PDF, CSV, Excel, XML, etc.)
Reports support variables or parameters, sometimes large numbers
Reports may require support for more than one language
Reports may be dependent on logic in metadata repositories than can be branched independently from the reports
Reports often involve user security, can be complex
Reports can support multiple data sources and types
Reports are often supported in multiple environments (browser, browser version, OS, bitness)
Reports can have strict formatting specifications (pixel perfect reports for printing)

Before I was doing dedicated BI work I was captive at a firm that was using Mercury as a testing tool. We developed a set of reports as part of a SaaS product suite. The testing tool was using basic “screen scraping” methods to compare report outputs between versions to find changes. We were able to set up automated testing of the reports to accommodate our 6 week release cycle. This was in the early days of very large databases and ours was constantly undergoing changes to tables and data. Sound familiar? We didn’t think of ourselves as data warehouse developers but that is what we did and fortunately we were able to invest in quality testing tools that included features for automating our report testing. Tools like Mercury were relatively expensive even then but the cost of delivering a bad report was part of the justification.

There are now tools available to do this job that are far more integrated and easier to use. I’m aware of one solid player that offers real testing features to the Cognos market. There may be other companies but I’ve not seen them. The one I have actually seen work is Motio. It doesn’t solve all the issues I listed above but nothing does yet. Testing reports takes a lot of process and effort to get it right. Is it worth the investment? That’s the question. A colleague of mine sent me an interesting article that might help find that answer. Here Douglas Hubbard discusses how we might better measure the right things when doing a cost-benefit analysis: The IT Measurement Inversion

Making things even more difficult is Agile. With Agile development methods making their way into most companies it is interesting to see how BI teams adapt to make the most of this methodology. You can find lots of others writing about their experiences with this. As the pressure mounts to quicken the pace of release cycles in BI the ability to hold the line on quality is even more important. The challenges for the QA team (often the very same BI developers) vary widely depending on the size of the organization. However, there are a few common themes;

Staffing testing roles to meet demand
Underestimating the difficulties testing BI reports and related BI analytic output
Getting overwhelmed by the challenges of managing multiple releases (branching is starting to become more common in BI)
Lack of tools to automate report testing (as discussed above)

So what to do?

Here is what I see most organizations do. Bluntly put, close their eyes and hope. Relying on unit testing by developers and spot checks by a few part time QA staff.

OK, that may be slightly dramatized but it is essentially what ends up happening. The costs of BI are typically under constant scrutiny (still). No one seems to ever budget $200K (just an example) or more for testing reports when they start a BI project. No one understands why you should spend that kind of money on testing. It’s not trivial for sure. I’m not saying that’s what these tools cost. Enterprise software pricing is complicated and you have people costs as well. However, that’s not the point. The software does something complex and saves companies a lot of money when it is used appropriately. BI teams have to start asking themselves about the cost of these actions?

Delivering reports with inaccurate values in them
Delivering reports that run slowly or unpredictably
Delivering reports to the wrong people
Delivering reports that allow access to sensitive data (regulatory compliance)
Delivering reports that are badly formatted (missing logos, misaligned columns, etc.)
Reports with misspelled words
Reports that fail under some conditions (one environment and not another)

Whether big or small, BI teams have to take testing of their BI reporting deliverables seriously. Not doing so is a sure sign of a lack of experience. It is only a matter of time before this approach leads to problems. Unless the project or company is quite small it can and should afford to invest in a dedicated tool. The pay off is huge in the long run. No team can afford to deliver bad reports, period. Smaller companies and projects may not be able to afford a dedicated tool. This doesn’t mean you can’t adopt good practices for testing it just means they will be more work and take more of people’s time. Taking the time to set up dedicated testing environments with known data sets, SQL scripts to run to validate report output, screen shots from prior reports to compare results to and so on, is how you mimic an automated tool. You just can’t do all that by clicking buttons. But the concepts are the same. You can learn more about this by just watching vendor demos to see what features they offer and building your own manual processes. Then hopefully, some day, your project will be worthy of the investment in automated report testing tools.

Posted in Business Intelligence | Tagged: , , , | 2 Comments »

SSAS vs Transformer

Posted by Brad Greene on February 16, 2012

I’ve been all over the place the past couple of months. One interesting project I started during this time is helping prove the viability of using Microsoft’s SSAS cube tech to replace Cognos Transformer. The application is retail and there is quite a lot of data in some of the fact tables (one is nearing a billion rows). Some of the dimensions are quite large but not in the millions fortunately. The challenge for this customer has been that Transformer, being what it is, at these volumes of data, has started to become overwhelmed. Build times are getting ridiculously long and the failure rate, while not high, is enough to be troubling and the recovery from any failure is painful given the long build times. I know there are ways to work around some of the limitations and people have been very resourceful. Go there if you have to I guess. But there are options.

Seeing this I felt compelled to recommend SSAS as the next step. We’re starting to see more Cognos clients in this situation making this decision. The results are just too compelling to ignore. The SSAS tool has become robust, feature rich and is very scalable. Our initial proof of concept confirmed everything we expected. We were able to design and build cubes at the lowest levels of detail, providing a more seamless user experience, and do it with build times that were far, far shorter than those of any comparable Transformer cube.

The combination of SSAS to design and build big, detailed cubes, combined with the BI management and presentation capabilities provided in Cognos is an awesome combination. If you simply do this to deliver cubes to Excel users you are missing the point here. Transformer is just no longer able to handle the increasing volumes of data some companies are collecting. So, fix that by plugging in SSAS, but don’t abandon all the other great things Cognos has to offer. It’s a great marriage.

There is a BUT here. No question that SSAS requires more technical skills than Transformer. Knowledge of the MDX language is mandatory. Technical staff will have a non-trivial learning curve to climb but the alternative is not pretty either. In an environment where SQL Enterprise is already present it may well be an easy decision to make the move to SSAS. Get some help, get some training and do it. This stuff works.

Posted in Business Intelligence, Technology | Tagged: , , , | Comments Off on SSAS vs Transformer

Cognos Component List

Posted by Brad Greene on September 23, 2011

Want the list of installed components in a Cognos installation? Substitute your server name into the URL below and paste it into your browser. Assumes your are using CGI of course. Change it to cognosisapi.dll if you are using the IIS DLL. You should get a long list that starts out something like the text below.


; Licensed Materials - Property of IBM
; BI and PM: is
; (C) Copyright IBM Corp. 2004, 2010
; US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp
[Product Information]
LICENSE_BI_SERVER_name=IBM Cognos License
C8BISRVR_name=IBM Cognos Business Intelligence Server
LICENSE_BI_SAMPLES_name=IBM Cognos License


Posted in Business Intelligence, Tech Tips | Tagged: | 2 Comments »

Cognos Report Studio, IIS 7 and HTTP 405 errors

Posted by Brad Greene on September 19, 2011

A lot of businesses are finally upgrading or installing IBM Cognos on Windows Server 2008 with IIS 7. The documentation for configuring IIS 7 with Cognos 10 is hard to find and often inconsistent leading to some frustration. I’m not going to document all the steps needed here because there are lots of them and they vary based on whether you happen to have IIS 7 or 7.5 for example. One thing is clear, Microsoft’s effort to tighten up security has resulted in a lot more work being required to get things working correctly. This follows an all too familiar pattern in software development, the more features you add the more complicated to use it gets. Tightening security is one of those “damned if you do and damned if you don’t” issues as well. Almost no one is going to be happy. You might be more secure but you also might not be up and running for a few days!

Just a point of clarification on one item. The WebDav feature/service/role is required and is not automatically included with the IIS 7 install. If you have IIS 7.0 you will probably have to download the module from Microsoft’s web site and install it using the Administrator account. If you have IIS 7.5 then all you need to do is enable the WebDav role from Server Manager. If you do not install or enable WebDav you will most likely get a “405 – Method Not Allowed” error in Cognos Report Studio when trying to browse the images directory to select an image to place on your report. This despite being able to browse the folder from Internet Explorer. Directory Browsing allows your images to be accessed by Report Studio still requires WebDav. Here are a few useful links provided by colleagues or found in my efforts to get through the process.

Configure WebDAV on IIS 7.5

Configure IIS 7 and Cognos 8

Cognos Admin errors and IIS 7

Posted in Business Intelligence, Tech Tips | Tagged: , , , , | Comments Off on Cognos Report Studio, IIS 7 and HTTP 405 errors

Cognos Reports and Microsoft Office

Posted by Brad Greene on August 20, 2011

IBM Cognos has provided an ancillary piece of software that dynamically connects Cognos Reports to Microsoft Office tools like Word, PowerPoint and Excel for several releases. It was called Go Office under the 8.x series but has been renamed Cognos for Microsoft Office. I have not used this tool before Cognos 10 but a new client was looking for a way to produce reports in PowerPoint without needing to manually keep the data in them up to date. New to report development they were expecting to seamlessly connect some Cognos Report Studio reports to Microsoft Office. Fine idea in theory. It looked great in the demo I’m sure.

As with all things in the world of software the devil is the details. This tool works well for certain things. Simple things. If you are producing reports with small list reports, crosstabs of a half dozen cells or charts, it’s fine. The integration of Cognos into Office is fairly seamless. Once you configure the Options with your Cognos gateway server’s URI you will be presented with a tree prompt widget that lets you navigate your Cognos Connection folders. Find the report you want in the tree, select Import and set a few options in the dialog boxes you’re presented with and the report is imported. Pretty simple really.

The surprise comes when you don’t understand that the reports you import are converted to Office objects native to the tool you are using. So if you are importing into PowerPoint and import a list report the result will be a PowerPoint table. The data in the table will be “live”. It is refreshed with a click of a button. That’s the “beauty” of the integration. The unfortunate side is you just lost all your formatting. If you spent any time formatting your report in Report Studio all the work is tossed away as it is converted to the PowerPoint table. I suppose this is not unreasonable because of the vast differences between the Cognos Studio product capabilities and PowerPoint.

The same is true for Word and Excel of course. If you have complex reports that are large and heavily formatted then you are going to have to carefully plan how you use Cognos for Microsoft Office. PowerPoint tables are limited to 25 lines for example and header, footer, label and other text seems to be left behind during import. It works, but don’t expect your nicely formatted lists and crosstabs to pop up in Word just like they did in Cognos Connection. There is some work left to do on formatting but your data will be live.


Posted in Business Intelligence | Tagged: , | 2 Comments »

Statistics are cool

Posted by Brad Greene on June 19, 2011

I just added a link (actually put it back in) to last year’s coolest documentary for data geeks. Hans Rosling’s “Joy of Stats“. It is an entertaining 1 hour on a topic that most people would never dream could be even remotely entertaining. The guy is just plain interesting to listen to. Business Intelligence has a lot to do with stats and I really like the way Rosling makes his point with visually effective presentations. Imagine getting the opportunity to build a dashboard with some of the techniques he uses!

As always my source for this kind of cool stuff is www.flowingdata.com. Check it out if you have not done so. Always something fun to see popping up there weekly.

Posted in Business Intelligence, General News | Comments Off on Statistics are cool

SSRS Report Builder 3.0 and IE9 needs .NET 3.5

Posted by Brad Greene on June 12, 2011

If you are using Microsoft IE 9 browser and the new Report Builder with SQL Server 2008 or later you may have run into the error below when trying to start RB 3.0. It seems odd at first because Windows 7 comes with .NET 3.5. I learned after a little searching that the issue lies with IE9. The problem is easily resolved by setting the Compatibility mode for this URL (the one you use to access your SSRS Report Server). You may have to enable the setting in IE9 under the Tools menu. Do a web search for how to enable Compatibility on IE9 and you will find an MS Support site article showing you how to do it. With that turned on the browser behaves like prior releases and, I assume, allows it to find the .NET Framework that RB 3.0 requires to run.

Report Manager error opening Report Builder 3.0

Report Manager error opening Report Builder 3.0

With the Compatibility mode set to “on” as indicated by the icon below Report Builder now runs as expected.

Report Manager opening Report Builder 3.0

Report Manager opening Report Builder 3.0

Posted in Business Intelligence, Tech Tips | Tagged: , , | 1 Comment »

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

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