MSDN Community Articles

Working with Hierarchies in Analysis Services

Hierarchies are fundamental to dimensional modeling in SQL Server Analysis Services (SSAS). They allow users to explore data at different levels of granularity, providing powerful analytical capabilities. This article guides you through the process of creating and working with hierarchies in SSAS.

What are Hierarchies?

A hierarchy represents a set of related attributes that can be navigated from a high level to a low level. Common examples include:

• Geography: Country > State/Province > City > Postal Code
• Time: Year > Quarter > Month > Day
• Organization: Department > Team > Employee

In SSAS, hierarchies are built on top of dimension attributes. They define a parent-child relationship or a natural grouping that enables drill-down and roll-up operations in BI applications.

Creating a Hierarchy

You can create hierarchies using SQL Server Data Tools (SSDT) or directly within the SSAS multidimensional model.

Steps in SSDT:

1. Open your SSAS project in SSDT.
2. Navigate to the Dimensions designer.
3. Select the dimension where you want to create a hierarchy.
4. In the Attributes pane, right-click on an attribute and select New Hierarchy.
5. Give your hierarchy a meaningful name (e.g., "Geography Hierarchy").
6. Drag and drop attributes from the available attributes list into the hierarchy designer to define the levels. The order matters for drill-down behavior.
7. Ensure the AttributeHierarchyEnabled property for each attribute in the hierarchy is set to True.
8. The top-level attribute in the hierarchy is automatically made the "root" of the hierarchy.

Types of Hierarchies

SSAS supports two primary types of hierarchies:

User-Defined Hierarchies (UDH)

These are the most common type, created by arranging attributes in a logical order, as described above. They represent natural groupings and relationships.

Parent-Child Hierarchies

These hierarchies represent relationships where a member can have multiple children, and each child can have a different parent. This is common for organizational structures or account structures where the depth can vary.

To create a parent-child hierarchy:

1. Ensure your dimension table has a column that identifies the parent of each row (e.g., ParentID) and a column that uniquely identifies the member (e.g., MemberID).
2. In the Dimension designer, right-click and select New Parent-Child Hierarchy.
3. Select the attribute that serves as the unique member identifier and the attribute that serves as the parent identifier.
4. SSAS will automatically create a hierarchy with special attributes like Level and Member_Key, along with navigation attributes like Parent_Level.

Working with Hierarchies in MDX and DAX

Hierarchies are exposed in query languages like MDX and DAX, allowing you to leverage them for complex analysis.

MDX Examples:

To select members from a hierarchy:

SELECT
    {[Geography].[Geography Hierarchy].[Country].Members *
     [Geography].[Geography Hierarchy].[State Province].Members} ON COLUMNS,
    {[Measures].[Sales Amount]} ON ROWS
FROM [YourCube]

To drill down using a hierarchy:

SELECT
    [Geography].[Geography Hierarchy].[Country].CurrentMember.Children ON COLUMNS,
    {[Measures].[Sales Amount]} ON ROWS
FROM [YourCube]
WHERE ([Geography].[Geography Hierarchy].[Country].&[USA])

DAX Examples:

In DAX, hierarchies are often represented as calculated columns or relationships. You can use functions like `PATH` and `PATHITEM` for parent-child hierarchies.

To visualize a hierarchy in Power BI or other DAX-enabled tools, you typically place the hierarchy levels in the visual's axis or legend fields.

Best Practices

• Meaningful Names: Use descriptive names for hierarchies and their levels.
• Consistent Granularity: Ensure all attributes within a single hierarchy have consistent granularity.
• Attribute Hierarchy Properties: Set AttributeHierarchyEnabled to True for attributes that will be part of a hierarchy. Control AttributeHierarchyOptimizedState and AttributeHierarchyVisible as needed.
• Natural Navigation: Order attributes logically to support intuitive drill-down and roll-up.
• Performance: For large dimensions, consider the impact of hierarchies on cube processing and query performance.

Understanding and effectively utilizing hierarchies is crucial for building powerful and user-friendly dimensional models in Analysis Services. By following these guidelines, you can enhance the analytical capabilities of your BI solutions.