Lesson 10: Create Hierarchies
In this lesson, you will create hierarchies. Hierarchies are groups of columns arranged in levels; for example, a Geography hierarchy might have sub-levels for Country, State, County, and City. Hierarchies can appear separate from other columns in a reporting client application field list, making them easier for client users to navigate and include in a report. To learn more, see Hierarchies (SSAS Tabular).
To create hierarchies, you will use the model designer in Diagram View. Creating and managing hierarchies is not supported in the model designer in Data View.
Estimated time to complete this lesson: 20 minutes
This topic is part of a tabular modeling tutorial, which should be completed in order. Before performing the tasks in this lesson, you should have completed the previous lesson: Lesson 9: Create Perspectives.
To create a Category hierarchy in the Product table
In the model designer, click on the
Modelmenu, then point to Model View, and then click Diagram View.
Use the Minimap controls at the top-right of the model designer to change how you can view objects in Diagram View. If you reposition objects in Diagram View, that view will be retained when you save the project.
In the model designer, right-click the
Producttable, and then click Create Hierarchy. A new hierarchy appears at the bottom of the table window.
In the hierarchy name, rename the hierarchy by typing
Category, and then press ENTER.
Producttable, click the Product Category Name column, then drag it to the
Categoryhierarchy, and then release on top of the
Categoryhierarchy, right-click the Product Category Name column, then click Rename, and then type
Renaming a column in a hierarchy does not rename that column in the table. A column in a hierarchy is just a representation of the column in the table.
Producttable, right-click the Product Subcategory Name column, then in the context menu, point to Add to Hierarchy, and then click
Rename Product Subcategory Name to
By using click and drag, or by using the Add to Hierarchy command in the context menu, add the Model Name and Product Name columns (in order) and place them beneath the Product Subcategory Name column. Rename these columns
To create hierarchies in the Date table
In the model designer, right-click the Date table, and then click Create Hierarchy.
Rename the hierarchy to Calendar.
Add the following columns, in-order, and then rename them:
Column Rename to: Calendar Year Year Calendar Semester Semester Calendar Quarter Quarter Month Calendar Month Day Of Month Day
In the Date table, repeat the above steps, creating a Fiscal hierarchy, including the following columns:
Column Rename to: Fiscal Year Year Fiscal Semester Semester Fiscal Quarter Quarter Month Calendar Month Day Of Month Day
Finally, in the Date table, repeat the above steps, creating a Production Calendar hierarchy, including the following columns:
Column Rename to: Calendar Year Year Week Number Of Year Week Day Of Week Day
To continue this tutorial, go to the next lesson: Lesson 11: Create Partitions.