559 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 three 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.
This blog will show how to take a single table containing parent-child relationships, and turn it into a pivot table.
To follow this example, you'll need to import the tblStaff table from the MAM database into your model:
Give the table a friendly name Staff.
Also, you'll need to create a calculated column to give the full name of each member of staff:
Concatenate the first name of each staff member with a space, and then the last name.
Consider Richard Madeley (there are some odd people's names in the table ...):
We'll track back and see who this person reports to in the tree.
Richard Madeley has staff id number 7, and reports to the manager with staff id number 4 (Joseph Conrad). He in turn reports to the manager with staff id number 1 (Gladstone Small), who reports ... to no one.
An organogram for the MAM top-level managers.
What this blog will do is show how to represent this staff hierarchy in a pivot table.
The first thing we'll do is derive who reports to whom:
The PATH function conveniently extracts a pipe-delimited list of id numbers.
The function used is:
=PATH( [StaffId], [ManagerId] )
You can use the PATHLENGTH function to find how long each id string is:
We'll use this to fine-tune the pivot table towards the end of this blog.
I'm going to arbitrarily create the following roles within the MAM company:
You can pick out each role by using a function like this:
where RoleNumber can equal any of the numbers 1 through to 6. For example, to get someone's director, use this formula:
=PATHITEM([IdPath], 2, INTEGER)
This would give the following (only the first 3 roles are shown here):
Richard Madeley has staff member 1 as his CEO, staff member 4 as his director and is a manager.
Now we know the id number at each level of hierarchy, we can use the LOOKUPVALUE function to return the relevant person's name. For example, for the director this would be:
That is, look up the staff name column from the table where the staff id number is the one returned by the PATHITEM function. This gives:
I've altered each of the 6 calculated columns so that they look up the name corresponding to the staff id number at the respective managerial level in the staff table.
You can now use the 6 columns to create a hierarchy:
I've created a hierarchy called Organogram (see the previous part of this blog for how to do this), which includes the 6 managerial levels.
You will probably also need to create relationships between the Transaction, Pos and Staff tables:
I've imported the StaffId column for the Pos table, so I can use this to link to the Staff table, to show who served which person.
You can now incorporate your Organogram hierarchy into a pivot table:
I've also hidden the CEO, Director, etc columns from client view, to make the pivot table field list look as clean as possible.
Here's what this would show:
Almost perfect! Just a shame about Alex Seddon and his ilk.
To get round the awkward fact that for some sales (such as those made by Alex Seddon above) there are no minions or even team leaders, you could amend each level formula to test for blanks. For example, here's what the formula would now be at level 6 (the Minion formula):
ISBLANK ( PATHITEM ( [IdPath], 6, INTEGER ) ),
[StaffId], PATHITEM ( [IdPath], 6, INTEGER )
That is, if there is nothing at level 6 in the path, show the next level up in the hierarchy instead. If you change the formula for all 6 levels, this will give data for every single column of the table:
Every level now has a value, even though some are repeats.
Things have improved, but perhaps not much:
Alex Seddon now appears to report to himself!
To stop multiple Alex Seddons appearing, you can create two new measures as follows:
|Measure||What it will show|
|CurrentDepth||For any pivot table cell, the current level of hierarchy (1 to 6).|
|ValueToShow||Blank if we've drilled down too far, otherwise the total sales.|
Here's an expression you could use for the CurrentDepth measure:
ISFILTERED ( Staff[Minion] ),
ISFILTERED ( Staff[Team Leader] ),
ISFILTERED ( Staff[Junior Manager] ),
ISFILTERED ( Staff[Manager] ),
IF ( ISFILTERED ( Staff[Director] ), 2, 1 )
Here's what this measure could look like when you create it:
The measure in the staff table (it uses the NumLevels calculated column that we referred to earlier).
Here's what the ValueToShow measure would look like:
If we're trying to show data below the maximum number of levels for this member of staff, show a blank instead.
Here's the formula for this measure:
staff[CurrentDepth] > max(Staff[NumLevels]),
Finally, you can choose to display the ValueToShow measure in your pivot table:
Show our squeaky new measure.
Finally, the perfect pivot table!
Alex Seddon only appears at the appropriate managerial level.
A final question: was it worth it? At the very least, you'll certainly have learnt a lot about DAX functions if you've followed me all the way to the end of this blog!
|Parts of this blog|
25 Aytoun Street