Modeling Hierarchies in Azure Analysis Services

Hierarchies represent business relationships between data in your tabular model. They allow users to navigate through different levels of detail, providing a more intuitive and user-friendly analysis experience. Common examples include date hierarchies (Year > Quarter > Month > Day) or organizational hierarchies (Country > State > City).

Why Use Hierarchies?

Improved User Experience: Simplifies data exploration by providing structured navigation paths.
Enhanced Analysis: Enables drill-down and drill-up capabilities in reporting tools.
Business Relevance: Maps directly to common business reporting structures.

Creating Hierarchies

Hierarchies can be created using either Visual Studio with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS).

Using Visual Studio (SSDT)

Open your Azure Analysis Services tabular model project in Visual Studio.
In the Model Designer, right-click on the table containing the column(s) you want to use for the hierarchy.
Select "Create Hierarchy".
Choose a name for your hierarchy (e.g., "DimDateHierarchy", "ProductHierarchy").
Drag and drop the columns that define the levels of your hierarchy from the "Fields" pane to the new hierarchy pane. Ensure the order of the columns reflects the desired drill-down path.
You can create both standard hierarchies and parent-child hierarchies.

Using SQL Server Management Studio (SSMS)

Connect to your Azure Analysis Services instance using SSMS.
Navigate to your database, then to the "Tables" folder.
Right-click on the table where you want to create a hierarchy.
Select "New Hierarchy".
Enter a name for the hierarchy.
Drag columns from the table's column list to the hierarchy designer.

Types of Hierarchies

Standard Hierarchies

These are the most common type, where each level is based on a distinct column. For example, a "Date" hierarchy might have columns for Year, Quarter, Month, and Day.

Example Structure:


Year
  Quarter
    Month
      Day

Parent-Child Hierarchies

Parent-child hierarchies are used when you have a self-referencing relationship within a single table. A common example is an employee table where each employee has a manager, and managers are also employees. This requires two columns:

ID Column: A unique identifier for each member.
Parent ID Column: A foreign key referencing the ID column of the parent member.

Azure Analysis Services automatically determines the relationships and levels based on these two columns.

Tip: Parent-child hierarchies are powerful for representing organizational structures, skill trees, or any recursive data relationships. Ensure your data has a single root node (a member with no parent) for proper hierarchy formation.

Hierarchy Properties

When creating or editing a hierarchy, you can configure several properties:

Name: The display name of the hierarchy.
Description: A brief explanation of the hierarchy.
Levels: The constituent columns or attributes forming the hierarchy.
Is Hidden: Whether the hierarchy should be visible to end-users in reporting tools.

Working with Hierarchies in Reporting Tools

Once published, hierarchies appear in reporting tools like Power BI, Excel, or Tableau, allowing users to easily navigate through the data:

Drill Down: Move from a higher level (e.g., Year) to a lower level (e.g., Quarter).
Drill Up: Move from a lower level to a higher level.
Expand/Collapse: Show or hide detail for specific items within a level.

Best Practices

Logical Naming: Use clear and descriptive names for hierarchies and their levels.
Consistent Structure: Ensure hierarchies reflect common business structures and user expectations.
Attribute Design: Create hierarchies from well-defined attributes that have a clear parent-child or sequential relationship.
Avoid Overly Deep Hierarchies: While powerful, extremely deep hierarchies can sometimes be cumbersome for users.
Use Parent-Child for Recursive Data: Leverage parent-child hierarchies for organizational charts or similar recursive structures.