Printed 2017/01/23 10:36PM

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.

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.