SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...