Excel Pivot tables are a great way to view and analyze cube data in an ad-hoc nature and also satisfy simple reporting requirements. Once your reporting requirements become somewhat sophisticated however, you may not be able to do all the things you want to do using pivot tables. There are definitely other reporting options, such as SSRS, but if you like the ease and familiarity of excel, you should consider using Excel’s cube functions to satisfy some of these requirements.
When you create reports using pivot tables, you are locked in to the capabilities that pivot tables offer, however, when you use cube functions, you have much more freedom. This is because each cell in excel can contain its own formula, and you can lay these cells out any way you’d like. The formulas can be self-contained, or reference other cells. Since cube functions are used just like any other Excel function, we get all the same flexibility with cube functions as we do with standard Excel functions.
OK, enough of the background. Now we’ll walk through an example.
Let’s say it’s June 2008 (due to the dataset I’m working with) and you want to view 2008 profit at a glance for each country with actuals through June, and a projection for the remainder of the year. To base your projection, you decide to add 10% over 2007 actuals. Of course you could alter the cube to add calculations to do this, but for this scenario we’ll say that’s not an option. We first start by creating a pivot table, so we have something that looks like this:
This gets us close, but we still need to add the 2007 actuals for July through December and add a 10% increase. We could create a named set in Excel to include the required 2007 members, but this doesn’t get us the 10% increase. Instead, we will convert the pivot table to formulas using the OLAP Tools ribbon option. This is a great way to get started on a report with cube functions since it gets you well on your way.
Here is the result. I’ve highlighted some cells for us to take a look at the formulas that they contain.
- A1: =CUBEMEMBER(“Adventure Works DW”,”[Measures].[Internet Gross Profit]”). This gets us the measure for our report.
- A8: =CUBEMEMBER(“Adventure Works DW”,”[Customer].[Customer Geography].[Country].&[United States]”). This gets us the “United Sates” dimension member.
- G2: =CUBEMEMBER(“Adventure Works DW”,”[Date].[Calendar].[Month].&&”). This gets us the June 2008 dimension member.
- G8: =CUBEVALUE(“Adventure Works DW”,$A$1,$A8,G$2). Finally this gets us the value for Internet Gross Profit for United States in June 2008. Notice how it references the other cells defined above. This is how the values are filtered by the rows and columns headers of the report.
Now we need to add the 2007 actuals to use for our projections. We just add columns for July through December. In row 2, we need to get the cube members for the months in 2007 instead of 2008 as follows: =CUBEMEMBER(“Adventure Works DW”, “[Date].[Calendar].[Month].&&”). Then copy the formulas for the value cells from another column, and you’ve got your prior year actuals in the report as a basis for your projections.
To increase the 2007 values by 10%, you could just multiply each 2007 value by 1.1 as follows =CUBEVALUE(“Adventure Works DW”,$A$1,$A8,H$2) * 1.1. However, I’d recommend referencing another cell with this factor so it can be easily changed. You could even name the cell to make the formula more readable. =CUBEVALUE(“Adventure Works DW”,$A$1,$A8,H$2) * (1 + Projected_Increase)
Now that you’ve got a good start, add some additional features such as filters, formatting, totals, static text, etc. to give the user a richer experience. You may also want to hide some cells to reduce some of the clutter in the report. You can end up with something like the following:
This was a very simple example. There is some obvious room for improvement, such as:
- Consider making the split from current year to prior year based on the current system date, or a filter selection. This should be fairly straightforward by incorporating simple excel functions such as IF(logical_test,[value_if_true], [value_if_false]) into the month/year cube member cells.
- Make your projected increase more dynamic using seasonality or prior increases.
- If your cube has budget or forecast numbers, use this other measure for the projections. Since you’re using cube functions, referencing a different measure in your projection cell formulas is trivial.
- The sky is the limit. If you take a closer look at the cube function samples above, you’ll notice they contain MDX expressions. The more knowledge and experience you have with MDX, the more sophisticated your reports can become.
Cube functions won’t solve all your reporting needs. Here are some nice pivot table features that you lose when using cube functions instead.
- Dynamic number of rows/columns. Pivot tables automatically adjust the number of rows and columns displayed based on the data that is returned. Since each cell either contains a formula, or does not, you lose this dynamic nature with cube functions.
- Ability to drill down. Pivot tables allow the user to very easily drill down through a dimension hierarchy to see values at different levels. You could mimic this by grouping rows or columns, but they need to be manually set up and again you lose the dynamic nature of pivot tables.
- At least a basic knowledge of MDX (Multi-dimensional Expression) is required to really gain value from using these cube functions.
Though Excel cube functions may not solve all your reporting needs, they are definitely a powerful tool at your disposal. If you are already reporting off of an SSAS cube using excel pivot tables, I strongly encourage you to experiment with these functions to provide your users with a more robust set of reports.
I Want To Learn More
Excel Functions (by category)