Create Hierarchies

You can define hierarchies, in tabular models, as metadata for defining relationships between two or more columns in a table. Further, hierarchies can appear separate from other columns in a reporting client field list, making them easier for client users to navigate and include in a report.

Moreover, we will learn to create hierarchies representing a group of columns arranged in levels. 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. Therefore, to create hierarchies, use the model designer in Diagram View. Also, creating and managing hierarchies is not supported in Data View.

How to Create hierarchies?

Steps to create a Category hierarchy in the DimProduct table?

• Firstly, in the model designer (diagram view), right-click the DimProduct table > Create Hierarchy. A new hierarchy appears at the bottom of the table window. Rename the hierarchy Category.
• Secondly, click and drag the ProductCategoryName column to the new Category hierarchy.
• Thirdly, in the Category hierarchy, right-click the ProductCategoryName > Rename, and then type Category. 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.
• Next, click and drag the ProductSubcategoryName column to the Category hierarchy. Rename it Subcategory.
• Lastly, right-click the ModelName column > Add to hierarchy, and then select Category. Do the same for EnglishProductName. Rename these columns in the hierarchy Model and Product.

Steps to create hierarchies in the DimDate table

1. In the DimDate table, create a new hierarchy named Calendar.
2. Add the following columns in-order –
   • CalendarYear
   • CalendarSemester
   • CalendarQuarter
   • MonthCalendar
   • DayNumberOfMonth
3. In the DimDate table, create a Fiscal hierarchy. Include the following columns –
   • FiscalYear
   • FiscalSemester
   • FiscalQuarter
   • MonthCalendar
   • DayNumberOfMonth
4. Finally, in the DimDate table, create a ProductionCalendar hierarchy. Include the following columns –
   • CalendarYear
   • WeekNumberOfYear
   • DayNumberOfWeek