Complex reporting off a SSAS cube
While using a cube in SSAS is usually a great source for reporting, it is not always the best choice. Sometimes it is better to report off of the data warehouse that the cube is built from.
For example, say you want to create a P&L statement using a financial cube. You want it to look like a normal P&L, meaning not look hierarchical. Also, it should have subtotals for groups of data. This really can’t be done using the SSAS cube as a source. Any front end to it such as PerformancePoint or SSRS would need to use either a hierarchy or a bunch of calculations using an Analytic Grid (and you can’t arrange the calculations in any order you want). The better option is to use SSRS and go against the data warehouse, not the cube. As per Finance Blog you will get here the brief explanation about finance.
The downside is that with SSRS, the ability to drill down is not built-in like with a cube hierarchy. So if you want that support you will need to code for it. Plus, you won’t have the benefit of using the aggregations in the cube, so going against the data warehouse will be slower than using a cube.
Another option is instead of reporting against the data warehouse, you could write MDX statements to pull out the data you need into relational tables, then use SSRS against those relational tables to give you the flexibility you need to create the P&L report the way you want. It would require more work to build the relational tables, but frees you from the constraints of trying to create a report against the hierarchical nature of a SSAS cube.
The bottom line is sometimes reports don’t work with OLAP as they might not fit into a rigid hierarchy, so you need the flexibility to go against the data warehouse
A similar situation is if you have existing SSRS reports that are going off a production system, and from the production system you build a data warehouse and off that data warehouse you build a cube. Do you convert those SSRS reports to use the cube or the data warehouse? It is usually better to have those reports go directly against the data warehouse: It is easier because you can use SQL instead of MDX, you can minimize what is in the cube as you can leave the data for these SSRS reports in the data warehouse, you won’t need to convert the existing SQL to MDX, and you have the option to use many filters in the SQL WHERE clause. Think of a cube as a source for creating new types of reports, not for replacing existing reports.
More info:
James,
Could explain beter your perspective that argues that is not possible using “Finantial Cube as Source”?
Almost my cubes are for finantial departments and always used CUBE as source without any problem. Let me know into deep detail what was the issues you faced. It’s possible? Thank you!
Amazing blog you have here!
Pedro
Hi Pedro. Glad you like my blog. The cube should always be used over the data warehouse for reports if possible, but my point is going against the cube forces you into a hierarchical structure for the reports, and when trying to do a report that does not fit into a hierarchical structure, like a P & L, you just can’t make it work in SSRS. So, you are forced to go against the data warehouse instead or use Excel. Or say you want to show on the report a total of account 100 and account 200 and display that total as the last line in the report. You don’t have that kind of freedom when using the hierarchical structure of the cube.
I have written a blog for P&L Statements.
Thanks Mehul!
Another solution would be to use cube value fomulas in Excel to point at the cube. Then you can build the report structure that is required as well as include addtional calculations that might not be in the cube using Excel formulas. This has the added benefit of allowing an end user to create and update the report without much help from IT. Pivot table filters and slicers can also be leveraged to provide a rich selection and filtering experience.
Great point Drew, thanks. Of course this means that you are using Excel as the front-end instead of SSRS, but with so many users familiar with Excel and with Excel Services, it’s an excellent option.
I still dont understand what DW can do and a CUBE not! 🙂 Sorry James for boring you! 🙂
If the P&L report is something like this one:
http://www.liveware.com.au/jet-express-for-microsoft-dynamics%C2%AE-nav/screenshot-report-consolidated-p-and-l-by-company-full-jet-express/
I made several before…. but probably I’m not seing correctly your point of view!
Congratulations for your blog and keep the amazing work!
Pedro
Hi Pedro. Using your example, say I wanted to take “Selling Expenses” and “Computer Expenses” and add them together and put them in a line at the bottom of the report called “One-time Expenses”. How would you do that? Or say I wanted “Cost of Raw Materials” to be indented more and bolded, and I wanted to take PROFIT/LOSS and multiply it by 2 and add a line with that total as “EXPECTED PROFIT/LOSS 2012”?
Hi James, you’re right – it’s ugly & very manual process to make SSRS return rows different than the natural hierarchies that are set up. I needed to do this once, for financial reports, and it wasn’t pretty. (Filtered groups to control presentation on each row; calculations to detect which level & do indentation & bolding based on level #; etc. Very tedious.) However, the hierarchies worked as desired within an Excel environment – so we put the “burden” on the fully formatted reports that wouldn’t change very often.
I agree with you that the relational DW was more well-suited for the format desired. That would have been my solution. However, my client drew a line in the sand: all reporting is from the cube only – they were concerned about the possibility of getting two different answers if we created some reports relationally & some multidimensionally (i.e., the cube had a lot of time intelligence & business calculations defined that wouldn’t have been accessible to the relational DW queries).
Great input Melissa. And I have found that what is even harder than using hierarchies in SSRS is using them in PerformancePoint. Maybe one day Microsoft will make it easier. Thanks so much!