Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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.


Leave a comment on the original post [, opens in a new window]

Loading comments...