This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

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. Creating and using hierarchies in SSAS Tabular Creating hierarchies in Analysis Services Tabular (this blog) 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.

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.

Creating and using hierarchies in SSAS Tabular
Creating hierarchies in Analysis Services Tabular (this blog)
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

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.

Parts of this blog
Creating and using hierarchies in SSAS Tabular Creating hierarchies in Analysis Services Tabular (this blog) Modelling parent-child hierarchies in DAX

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR