557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to create and use hierachies in Analysis Services tabular model
Part two of a three-part series of blogs
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.
I'll use this page to show how to create and use the hierarchy shown in the previous part of this blog.
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.
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.
You can tick a hierarchy to add it to a pivot table:
|Ticking the hierarchy ...||... displays it in the pivot table.|
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.
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 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.
|Parts of this blog|
25 Aytoun Street