Beyond Excel pivot tables: Leveraging cube formulas with MDX

PowerPivot and DAX are a powerful technologies, but there is still a good amount of work that can be done with traditional cube functions. In its current version, PowerPivot lacks the concept of dimension hierarchies; however very customized Excel reports that leverage cube hierarchies can still be built using MDX enabled cube functions. This approach goes beyond the limiting nature of traditional pivot tables by enabling power users to transverse dimensions using custom formulas that are a mix of MDX with traditional Excel functionality.

This approach can be used, for example, to determine the top city for each of the displayed countries in a traditional SSAS connected pivot table, as shown below (using Adventure Works 2008 SSAS cube)

 

By converting the pivot table to formulas, we free ourselves from any of the inherent pivot table limitations -

 

Notice that filters are still kept as dropdowns, which is a powerful feature as it allows the construction of very dynamic formula based reports.

 

Selecting the member Germany (cell A8 in screenshot above) shows us how Excel navigates the cube in order to retrieve the member name -

 

 

We can use a similar approach with the CUBESET Excel function in order to create an MDX set of the top cities by reseller sales for each of the countries displayed in the spreadsheet;  In this example, I leave the set name empty which will make it invisible to people viewing the spreadsheet cell values. However, even though there no value is displayed, the cell contains a reference to the set object now, and we can use reference it via Excel formulas in order to complete our report:

 

Using the CUBERANKEDMEMBER function, we retrieve the first member of the set and show the city name (In this example the set only contained one member)

 

Just as easily, a power user with understanding of hierarchies could slightly modify the MDX descendant formula to retrieve a different level of the hierarchy, like the top postal code (using the value of 3 for the descendants formula, in effect changing the level of granularity retrieved from the dimension)

 

Even though is a fairly simple technique, I hope this small article shows the potential of using MDX enabled cube formulas. More complicated calculations like weighted averages or trends can be also derived using this technique, enabling power users to quickly satisfy questions from decision makers and build highly customized reports. In turn, the BI team can leverage the successful and popular calculations and ‘promote’ them to the cube (which will most likely happen with DAX measures as well).