SSAS Tabular Part 9 : Create Hierarchies

Hierarchies are groups of columns in a table and arranged based on level and its sub levels. For example we can say Year-->Samester-->Quarter etc.

To create hierarchies, we will use the model designer in Diagram View.

Steps to create Hierarchies

For example, I am taking DATE  table and going to create a Hierarchies on it.

1. In the model designer, click on the Model menu, then point to Model View, and then click Diagram View.

2. Right-click the Date table, and then click Create Hierarchy. A new hierarchy appears at the bottom of the table window.
3. In the hierarchy name, rename the hierarchy by typing Datetree, and then press ENTER.
4. In the Date table, click the CalendarYear column, then drag it to the Datetree hierarchy, releasing it on top of it.
5. In the Date table, click and drag the CalendarSemester column to the Datetree hierarchy.
6. In the Date table, click and drag the CalendarQuarter column to the Datetree hierarchy.
7. Datetree hierarchy has created.

Output:

Now we have to see the outcome of Datetree hierarchy.

1        1. To see it’s activity click the Model menu, and then click Analyze in Excel.

2. In Excel, in the PivotTable Field List, notice the Datetree field in  Date Table as well as Geography, Product, Product Category, Product Subcategory, and Internet Sales tables with all of their respective columns appear.

3. Go to Date table and select Datetree field, go to Product Table and select EnglishProductName and finally go to Measure and select TotalOrderQuantity.

4. Now we can see first column appear as Drilldown. By Clicking “+” symbol we can dill the data up to granular level. (Year --> Semester --> Quarter)