This is the final installment on the series of blog entires regarding how to leverage PowerPivot data using Excel cube functions. The main purpose of the series has been to explain how to use Excel in a way that allows the report author to create appealing reports and dashboards using PowerPivot in-memory data but beyond the constraints and limitations imposed by Pivot tables.
As you can see, the final report (screenshot above) uses a combination Pivot table references, MDX filtering functionality, DAX measures and Excel Cube functions. There is a reason why each of these was used, which is what we discuss next.
To recap, is worth mentioning a few things that you can do when working with PowerPivot data using Excel cube functions:
- Cube functions access the in-memory tables using the workbook connection named “PowerPivot Data”, which was automatically created when importing data to PowerPivot.
- Even though the storage is SSAS in-memory columnar mode (Vertipaq), Cube functions can access this data as if it was SSAS in OLAP mode.
- Cube functions allow using a limited amount of MDX functionality which is very useful when constructing custom sets to be used across the report
- DAX calculated columns and measures can be called via Excel Cube functions. Even though they are essentially relational, when using these DAX calculations over Excel Cube functions you can think about them in terms or traditional UDM/OLAP measures and dimensions.
Cube functions: not a vehicle for free form MDX
Even though MDX is allowed inside Cube functions, do not think they will just simply pass the MDX expression to the storage engine (SSAS in OLAP or Vertipaq mode), as if you were in a free form query window like SSMS. There are limitations that are important to keep in mind:
- CUBESET function only allows one expression when building the set. What this means is that you cannot combine two filters in the functions to create a set across 2 or more dimensions (in othere words, a single-tuple set). You can construct this kind of set however, if you hardcode the set definition manually.
Only one set expression is allowed. Two filters cannot create a tuple set, unless hardcoded manually, as in the expression below:
=CUBESET("PowerPivot Data","({[DimDate].[CalendarYear].[All].[2005],[DimDate].[CalendarYear].[All].[2006]},[DimGeography].[ContinentName].[All].[Asia])","MySet")
Remember: [DimGeography] and [DimDate] are not real OLAP dimensions when using PowerPivot data in this report. They are in-memory tables. PowerPivot converts them to ‘dimension equivalents’ after loading the data. But, you may ask, how does PowerPivot know what is supposed to be a dimension and what a measure? It doesn’t. In Vertipaq, any field can be either a dimension or a measure. Try dragging [DimGeography].[RegionCountryName] to the values section of the Pivot table. Vertipaq automatically turns it into a measure, which is by default a count of values.
- Even though you can define tuples that include more than one dimension following the technique above, the result will be equivalent to a resultset over a column or row axis with crossjoined dimensions. In other words, there is no way to generate the equivalent to a ‘slicer’ axis using the CUBESET function.
There is no equivalent slicer axis in the CUBESET function. Displaying members of a tuple set will yield duplicate values. In this case the first Asia value is associated with Year 2005, and the second one with Year 2006.
- CUBEVALUE function only allows calling a measure which is then filtered using member expressions. What this means is that you cannot create MDX calculated members on the fly using the CUBEVALUE function.
In order to compensate for the first two limitations above, if there is a need to apply a slicer to a set it is best to reference existing pivot tables, as the default behavior on them is to filter the dimension members on the column or rows axis in a way that is equivalent to the ‘WHERE’ clause in T-SQL or MDX (slicer axis).
In a pivot table, members are filtered following the behavior of a WHERE slicer clause. When building custom reports, there may be a need to reference pivot tables in other spreadsheets if slicing the data in this particular way is necessary.
Regarding the CUBEVALUE limitation (not being able to construct on the fly calculated members), the way to circumvent it is by way of DAX calculations. These can be called as if they were MDX members, even to the point of being able to change the context of a calculation in the exact same way once you would when invoking a regular SSAS measure. This is due to the fact that both languages assign context as a function of the coordinate pointers over stored data’s memory space. MDX does it over a cube space, wheras DAX does it over a relational system of tables and relationships. However, they are in essence very similar, and to Cube functions they look exactly the same. An example will explain this better:
In order to calculate the Accumulated Sales Growth Since Inception of the product, we use the following DAX measures:
Measure [Sales (RunningTotal)]:
IF([Sales] <> "",
CALCULATE([Sales],DATESBETWEEN(DimDate[Datekey],CALCULATE(FIRSTDATE(DimDate[Datekey]), ALL(DimDate)),LASTDATE(DimDate[Datekey]))),
BLANK())
Measure [Product Sales Growth (Since Inception)]:
IF([Sales (Running Total)] <> "",
(
CALCULATE(
[Sales (Running Total)],
LASTNONBLANK(
ALL(DimDate[CalendarMonth]), [Sales (Running Total)])) -
CALCULATE(
[Sales (Running Total)],
FIRSTNONBLANK(
ALL(DimDate[CalendarMonth]), [Sales (Running Total)]))
) /
CALCULATE(
[Sales (Running Total)],
FIRSTNONBLANK(
ALL(DimDate[CalendarMonth]), [Sales (Running Total)]))
,BLANK())
This function removes any current filter context on [CalendarMonth] when calculating growth. It won’t affect, however, the [CalendarYear] attribute. When applying this DAX measure to a specific calendar Year, it will reflect the accumulated sales growth over the year by using a simple growth formula:
(Final Accumulated Sales – Initial Sales) / (Initial Sales)
If what we want is to display the growth for ALL years, we must remove any existing filter context for the [CalendarYear] attribute when evaluating the DAX measure. In the sample report, we also want to display this total rate in one cell, outside of a pivot table. We can accomplish this using the following Cube formula:
We can call the DAX measure within the Cube formula, and by specifying the [All] member of the [CalendarYear] attribute we change the filter context of evaluation, which gives us the total accumulated sales growth across all years.
Finally, despite the limitations exposed above regarding MDX functionality in Cube formulas, it can be used when filtering set values or constructing custom sets. In the sample report the X axis values applied to the chart where filtered to only the years that have associated fact data, which was accomplished using the MDX ‘Exists’ function. In that way, the chart won't show empty labels for those years that have no data.
The MDX ‘Exists’ function keeps only those ones that have [Sales] data. MDX can be used even though Vertipaq is fundamentally relational, not multidimensional.
In summary, Excel cube functions using PowerPivot data is a great combination that gives I.T. or data analysts a way to build custom Excel reports and dashboards that break out the constraints of pivot tables. DAX calculation and MDX set manipulation functions can be used in a single Excel report in order to generate highly customized views of data that a user can still interact with.