Dynamic OLAP Reports

The specification from a customer's BI solution, that I just completed, included 3 reports. (The project was really about designing a high performing cube with a hundreds of millions of facts, over 20 real dimensions and updates every 10 minutes.) I love those sort of challenges. Anyway, the 3 reports were essentially the same Profit and Loss Statement at different levels of the business hierarchy. The customer expected 3 reports, and the early BI developer created 3 reports in the PoC, however I created one report that could navigate up and down the business hierarchy with hyperlinks. Needless to say, the customer was delighted to have one report and the flexibility to click up and down the business. This is similar to a blog I made some time ago. I encourage BI developers to use SQL Server Reporting Services Reports (SSRS) on OLAP cubes to create fast and linked reports so that the reports appear like an application to the end user.

Adding hyperlinks to another report in SSRS is really simple, you just right click on the text box, asking for text box properties and clicking on Action Go to Report. The parameters need to be configured. In my case, the parameter for the business unit is created using the current member's unique name and its parent's unique name. So a tiny bit of MDX is required. That brings me onto another of my soapbox themes. BI developers should know MDX. If your BI developer doesn't know MDX, he/she is unlikely to be able to design high performing cubes and will not know how to exploit all the available functionality. it's a bit like asking someone to design a relational data warehouse for you, who doesn't know SQL very well. Good MDX skills are a predicate for good cube designs.

I can't provide the example of my customer's report, but you can see examples of dynamic and linked reports on http://RichardLees.com.au/Sites/Demonstrations