Lesson 10: Create Hierarchies

 

Applies To: SQL Server 2016

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

  1. In the model designer, click on the Model menu, then point to Model View, and then click Diagram View.
  1. Right-click the Product table, and then click Create Hierarchy. A new hierarchy appears at the bottom of the table window.

  2. In the hierarchy name, rename the hierarchy by typing Category, and then press ENTER.

  3. In the Product table, click the Product Category Name column, then drag it to the Category hierarchy, releasing it on top of Category.

  4. In the Category hierarchy, right-click the Product Category Name column, then click Rename, and then type Category.

    System_CAPS_ICON_note.jpg Note


    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.

  5. In the Product table, click and drag the Product Subcategory Name column to the Category hierarchy.

  6. Rename Product Subcategory Name to Subcategory.

  7. Click and drag the Model Name and Product Name columns (in order) and place them beneath the Product Subcategory Name column. Rename these columns Model and Product, respectively.

To create hierarchies in the Date table

  1. In the model designer, right-click the Date table, and then click Create Hierarchy.

  2. Rename the hierarchy to Calendar.

  3. Add the following columns, in-order, and then rename them:

    ColumnRename to:
    Calendar YearYear
    Calendar SemesterSemester
    Calendar QuarterQuarter
    Month CalendarMonth
    Day Of MonthDay
  4. In the Date table, repeat the above steps, creating a Fiscal hierarchy, including the following columns:

    ColumnRename to:
    Fiscal YearYear
    Fiscal SemesterSemester
    Fiscal QuarterQuarter
    Month CalendarMonth
    Day Of MonthDay
  5. Finally, in the Date table, repeat the above steps, creating a Production Calendar hierarchy, including the following columns:

    ColumnRename to:
    Calendar YearYear
    Week Number Of YearWeek
    Day Of WeekDay

To continue this tutorial, go to the next lesson: Lesson 11: Create Partitions.

Community Additions

ADD
Show: