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.

  1. Creating and using hierarchies in SSAS Tabular
  2. Creating hierarchies in Analysis Services Tabular (this blog)
  3. Modelling parent-child hierarchies in DAX

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

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:

Product, animal and species

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):

RELATED function for animal

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:

Create hierarchy menu

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):

Renaming a hierarchy

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:

Adding animal to 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:

Dragging column too fafr

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:

The final 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 ... Displaying hierarchy
Ticking the hierarchy ... ... displays it in the pivot table.

Other possible hierarchies

It's common to create a hierarchy for viewing dates:

Hierarchy for calendar

Dividing time into years, quarters and months.

 

For our model, the other obvious hierarchy is by quadrant, region, town, centre and store:

Geography hierarchy

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:

Pivot table fields with hierarchies

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:

Pivot table with 3 hierarchies

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:

Hiding the product column

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:

Not using a hierarchy

A user can now choose exactly which levels of detail to display.

 

I don't even think the result looks any worse:

Pivot table without hierarchy

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.

  1. Creating and using hierarchies in SSAS Tabular
  2. Creating hierarchies in Analysis Services Tabular (this blog)
  3. Modelling parent-child hierarchies in DAX
This blog has 0 threads Add post