SQL Server Analysis Services Documentation

Hierarchies in Multidimensional Models

Hierarchies are a fundamental concept in multidimensional data modeling, allowing users to navigate and analyze data at different levels of granularity. In SQL Server Analysis Services (SSAS), hierarchies are created within dimensions to represent natural business structures, such as time (Year, Quarter, Month, Day) or geography (Country, State, City).

Types of Hierarchies

  • Parent-Child Hierarchies: These are typically used for data that has a recursive relationship, like organizational structures or employee reporting lines. Each member in a parent-child hierarchy can have only one parent.
  • Unbalanced Hierarchies: In unbalanced hierarchies, different branches can have different depths. For example, a product category hierarchy might have some categories with many sub-categories, while others have none.
  • Ragged Hierarchies: Similar to unbalanced hierarchies, ragged hierarchies have branches of different lengths. A common example is a geography hierarchy where some countries have states and cities, while others may only have cities directly under them.
  • Balanced Hierarchies: All branches of a balanced hierarchy have the same depth. A typical example is a date hierarchy (Year, Quarter, Month, Day) where every year has quarters, every quarter has months, and every month has days.

Creating and Managing Hierarchies

Hierarchies are defined within the Dimension Designer in SQL Server Data Tools (SSDT) or Visual Studio. You can create a hierarchy by dragging attributes from the data source view into the Hierarchies pane of a dimension.

Consider a Time Dimension. You might have the following attributes:

Attribute Name Data Type
CalendarYear Integer
CalendarQuarter Integer
MonthNumberOfYear Integer
MonthName String
DayNumberOfMonth Integer
FullDateAlternateKey Date

You can then construct a hierarchy like this:

Time Hierarchy:
  - Year (based on CalendarYear)
    - Quarter (based on CalendarQuarter)
      - Month (based on MonthName)
        - Day (based on DayNumberOfMonth)

Hierarchy Properties

When you create a hierarchy, you can configure various properties, including:

  • Name: A unique identifier for the hierarchy.
  • Caption: The display name for the hierarchy in client applications.
  • Level Breadth: Controls how many levels are displayed at once.
  • Level Depth: Controls the maximum depth of the hierarchy.
  • Member Keys: Specifies the attribute(s) used to uniquely identify members within a level.
  • Related Dimension: Links the hierarchy to its parent dimension.

Using Hierarchies in Analysis Services

Hierarchies are exposed to client applications like Excel, Power BI, and other BI tools. Users can then drill down into levels (e.g., from Year to Quarter) or roll up (e.g., from Day to Month) to aggregate data as needed. This provides an intuitive way to explore large datasets.

Important Note:

The effectiveness of your multidimensional model heavily relies on well-designed and intuitive hierarchies. Spend time planning and creating hierarchies that align with how your business users expect to analyze data.

Key Benefits of Using Hierarchies

  • Improved User Experience: Simplifies data exploration and analysis.
  • Performance Optimization: SSAS can optimize queries based on hierarchical structures.
  • Consistent Reporting: Ensures data is aggregated and presented uniformly.
  • Business Alignment: Reflects natural business reporting structures.

Considerations for Parent-Child Hierarchies:

While powerful, parent-child hierarchies can sometimes impact performance on very large, deep hierarchies. It's advisable to benchmark and consider alternative designs if performance becomes a concern.

Further Reading