Javier Guillen posted on October 28, 2010 21:12
PowerPivot Data + Cube Formulas = a powerful combination
One of the interesting things about PowerPivot is that it really is, under the covers, an instance of SQL Server Analysis Service. And because of this we get some features for ‘free’, features that already work nicely and reliably in the traditional SSAS engine. One of those features is the interactive functionality between Excel and the cube, and specifically - Excel Cube functions. These are functions that allow us to build highly customized SSAS connected Excel based reports, well beyond any inherent limitations of pivot tables. You may have seen these formulas automatically generated by Excel, when selecting the option to convert an SSAS connected Pivot Table to formulas (via the OLAP tools menu) -
Two great advantages of using these functions are that we can build highly customized, asymmetric reports and that we can use MDX functionality to query either a traditional SSAS cube or the in-memory Vertipaq engine.
The term "Excel functions" and "Excel formulas" is sometimes used interchangeably. Excel Cube functions are the 7 built-in functions available in the formula bar, under the 'Cube' category; Excel cube formulas are cell formulas that use cube functions in order to achieve dynamic query behavior to the Analysis Service instance.
But how? If PowerPivot is loading data in-memory in what looks like a relational schema (one tab for each table), then how is it that we connect to a ‘cube’?
Well, PowerPivot (or Vertipaq, shall we say) builds an in-memory OLAP cube. You can see this when running DAX queries against the PowerPivot data, Excel displays a ‘Running OLAP query’ message on the status bar. (It really is, when you think about it, an amazing technology… cubes are embedded in the workbook!)
Anyhow, going back to topic – cube formulas. PowerPivot creates an embedded connection to the in-memory data. You can see the connection string under the data tab in the Excel ribbon. Select connection and you will see one called ‘PowerPivot Data’. This was automatically created when importing data via the PowerPivot window.
When building customized Excel reports, you can retrieve data from this source with any of the built-in Excel Cube functions.
PowerPivot applications... without Pivot tables!
Cube formulas are good to build custom reports that are difficult to create with standard pivot tables, yet still leveraging the functionality of being connected to the database (in the case of PowerPivot, the in-memory Vertipaq database). We can also leverage DAX measures, and execute them through Cube functions outside the pivot table.
I the next following posts I will go over a sample report, highly customized to create the type of business reporting that decision makers would want to see. The example uses the new Microsoft business intelligence database, Contoso Retail DW (available for download here), and leverages some of the interesting features of PowerPivot and Cube functions to build Excel reports that require functionality beyond what is normally exposed through pivot tables.
