How to create and use hierachies in Analysis Services tabular model
Part one 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 (this blog)
  2. Creating hierarchies in Analysis Services Tabular
  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 and using hierarchies in SSAS Tabular

A hierarchy allows you to group multiple levels into a single dimension:

Hierarchy diagram

An example of a hierarchy: Wol is a product, which belongs to the Owl animal and ultimately to the Bird species.

 

You can add fields in a hierarchy into a pivot table with a single click:

Adding hierarchy

Rather than ticking each of Species, Animal and Product, you can just tick the Classification hierarchy to include all of its levels in the pivot table.

 

The result looks pretty and is easy to use:

Pivot table using a hierarchy

You can click on the outline symbols to expand or collapse species, animals or products.

 

This blog shows how to create and use hierarchies, then considers the special case of parent-child hierarchies.

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