BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Hiearchies allow you to group related columns together, to make for prettier pivot tables and happier users. This blog shows you how to create and use hiearchies in SSAS Tabular, considers whether they're worth it, and also looks at the specific case of how to model parent-child hierarchies.
- Creating and using hierarchies in SSAS Tabular
- Creating hierarchies in Analysis Services Tabular (this blog)
- Modelling parent-child hierarchies in DAX
Posted by Andy Brown on 11 February 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Creating hierarchies in Analysis Services Tabular
I'll use this page to show how to create and use the hierarchy shown in the previous part of this blog.
Combining the required fields
You can only create a hierarchy by combining columns within a single table, so the first step is to use the RELATED function to do this:
We need to bring these 3 columns into a single table (the Product one, since this is at the bottom of the hierarchy).
Here's the formula for the Animal calculated column (the Species one would be similar):
This will display the animal to which each product belongs.
Creating the hierarchy
The next step is to switch to diagram view and create a hierarchy:
It doesn't matter which level of the hierarchy you choose; right-click on one of Animal, Product or Species and choose to create a hierarchy based on it (here I've chosen to start with the field at the top of the hierarchy, the Species).
You can now rename the hierarchy created (I've called mine Classification, although biologically it's anything but sound):
Here we're about to rename the created hierarchy.
Now click on each of the other two columns and drag them into their correct position in the hierarchy:
Dragging the Animal column onto the hierarchy.
Note that you have to be careful where you position the mouse - the following position wouldn't work, because I've dragged the column too far down:
The difference is subtle, but if you released the mouse button now nothing would happen (the horizontal line fills the whole table, not just the hierarchy width).
Eventually you'll end up with the required hierarchy:
You might also like to hide the component columns from client tools, as here, so that the only thing a user can choose is the hierarchy.
Using a hierarchy in your pivot table
You can tick a hierarchy to add it to a pivot table:
|Ticking the hierarchy ...||... displays it in the pivot table.|
Other possible hierarchies
It's common to create a hierarchy for viewing dates:
Dividing time into years, quarters and months.
For our model, the other obvious hierarchy is by quadrant, region, town, centre and store:
A hierarchy with five levels in it.
The advantage of hierarchies
On the plus side, there's no doubt that hierarchies can make for tidy pivot tables:
A user can instantly choose to see multiple levels of detail, just by ticking the hierarchy boxes.
Pivot tables based on hierarchies also look impressive:
The pivot table for the fields shown above.
You can even base a slicer on a hierarchy, although (as I've just discovered) all this does is to create one slicer for each of its levels!
The disadvantage of hierarchies
The reason I'm not a big fan of hierarchies is that they restrict a user's choices. For example, there's no way to hide the Product column in this pivot table:
If you don't want a user to be able to expand down to the product level - tough!
Compare this with a non-hierarchical solution:
A user can now choose exactly which levels of detail to display.
I don't even think the result looks any worse:
A pivot table based on the fields above - it's hard to tell it apart from the one using a Classification hierarchy.
Perhaps I'm missing the point, but it seems to this owl that hierarchies don't bring much to the party!
An exception to the above is when you have a parent-child relationship built into a single table, which is the subject of the final part of this blog.