Microsoft Docs

Create Hierarchies in Azure Analysis Services

Hierarchies allow users to navigate data in a multi-level, top-down fashion. For example, a date hierarchy might include Year, Quarter, Month, and Day levels. Hierarchies are created in the tabular model designer.

When to Use Hierarchies

• To provide a user-friendly way to explore dimensional data.
• To group related attributes into logical levels.
• To enable drill-down and roll-up analysis.

Steps to Create a Hierarchy

You can create hierarchies using SQL Server Data Tools (SSDT) for Visual Studio or directly within the Azure portal's model designer.

Using SQL Server Data Tools (SSDT)

1. Open your Azure Analysis Services tabular model project in Visual Studio.
2. In the Model Designer, right-click on the table containing the column you want to use as the basis for the hierarchy.
3. Select Create Hierarchy.
4. In the "Create Hierarchy" dialog box, select the columns that will form the levels of your hierarchy, in the desired order.
5. Click OK.

A new hierarchy will appear in the table's metadata pane. You can rename it and adjust its properties.

Using the Azure Portal Model Designer (Limited Functionality)

While the Azure portal provides some model management features, complex hierarchy creation is typically done with SSDT. However, you can view and manage existing hierarchies.

Key Hierarchy Concepts

Levels

Each step in a hierarchy is a level. Levels are defined by columns from your table. Ensure that the data within each level is unique and that there's a clear parent-child relationship between consecutive levels.

Parent-Child Hierarchies

For organizational data like employee reporting structures or product categories, you might use parent-child hierarchies. These are typically created using a single column that references the parent of each member.

Ragged Hierarchies

A ragged hierarchy is one where some branches are deeper than others. For example, a geographical hierarchy might have some countries with detailed regions, while others do not.

Managing Hierarchies

Renaming Hierarchies and Levels

You can rename hierarchies and their individual levels to make them more intuitive for end-users. Select the hierarchy or level in the metadata pane and use the properties window.

Hiding Hierarchies

You can choose to hide hierarchies from end-user reporting tools if they are intended for internal modeling purposes or to avoid clutter. Right-click the hierarchy and select Hide from client tools.

Deleting Hierarchies

To delete a hierarchy, right-click it in the metadata pane and select Delete.

Best Practices for Hierarchies

• Use clear and descriptive names for hierarchies and levels.
• Maintain consistent data quality in the columns used for hierarchies.
• Consider the end-user experience when designing hierarchy structures.
• Avoid overly deep hierarchies, which can become difficult to navigate.
• Use hierarchies to simplify complex data relationships for reporting.