I have posted a few times about Excel’s CUBE functions which are very useful for creating reports in a format and layout that pivot tables often just cannot achieve. They were useful when SSAS were the only cubes to work with, but with the introduction of PowerPivot and in-memory cubes, their use has grown for me.
As well as basic CUBE function capability, they are extensible by customising the MDX expressions. In a recent post I showed how to use the MDX EXISTS function to get each product sub-category within each product category in the AdventureWorks database. This worked, and whilst it was primarily intended to show that a bit of MDX can go a long way, this solution was a bit klunky.
Last week I was giving a PowerPivot course with Chris Webb, and in a session on CUBE functions I gave this MDX example, using separate product, product sub-category, and product category tables. In this session we discussed the possibility of adding a product hierarchy and using that hierarchy to get the breakdown of categories. Trying it there and then, we were getting close (we could see all of the product sub-categories within a given category in intellisense), but we couldn’t get it quite right. I put this down to my lack ability in MDX, so I asked Chris to help me; Chris is a top MDX guy.
With Chris’s help and guidance, good progress has been made. I think this is a useful way to get descendants/children of an item at a certain level, so I will share it here. To begin with, a few details of my model:
- I am using Excel 2013 here
- my product table was renamed from DimProduct to Product within the model
- the product hierarchy, named ProductHierarchy, consisted of a product category name, ProductCategory, product sub-category name, ProductSubcategory, and product, Product
To get the set of category names from this hierarchy, I used
To get the set of sub-categories within the Accessories category, I used
[F5]:=CUBESET(“ThisWorkbookDataModel”,”[Product].[ProductHierarchy].[All].[Accessories].Children”,”‘Accessory’ sub-category set”)
Assuming that the category set is listed in cells E7 down, we could use a cell reference to E8 (which returns the Accessories member), it reduces tp
=CUBESET(“ThisWorkbookDataModel”,”[Product].[ProductHierarchy].[All].[“&E8″].Children”,”‘Accessory’ sub-category set”)
And finally, to get the set of products within the Bike Racks sub-category, I used
=CUBESET(“ThisWorkbookDataModel”,”[Product].[ProductHierarchy].[Accessories].[Bike Racks].Children”,”‘Bike rack’ product set”)
Note that we have to include all parent levels of the hierarchy, both Accessories and Bike Racks.
And again, assuming that the category set is listed in cells E7 down, we can use a cell reference to E8 (which returns the Accessories member), and that the product set for Accesories is listed in F7 down, we can use a cell reference to F7 (which returns the Bike Racks member), it reduces to
=CUBESET(“ThisWorkbookDataModel”,”[Product].[ProductHierarchy].[“&E8&”].[“&F7&”].Children”,”‘Bike rack’ product set”)
There we have it, a relatively simple way to drill-down to any level within a hierarchy.
Please not that if you try this and copy and paste the formulas above, it seems to have turned some of the double-quotes to the wrong type, so they will need editing.