MSDN Community Articles

Creating Hierarchies in SSAS

By John Doe | Published: October 26, 2023

Hierarchies are a fundamental concept in multidimensional databases, allowing users to navigate data at different levels of granularity. In SQL Server Analysis Services (SSAS), creating effective hierarchies is crucial for enabling intuitive data exploration and reporting. This article will guide you through the process of creating different types of hierarchies.

What is a Hierarchy?

A hierarchy is a set of levels that represent a parent-child relationship between members. For example, a common geographic hierarchy might include levels for Country, State/Province, and City. Users can then drill down from Country to State/Province and then to City to see increasingly detailed data.

Types of Hierarchies

SSAS supports two primary types of hierarchies:

• Regular Hierarchies: These are the most common type, typically derived from a single dimension. They represent a natural, parent-child relationship within that dimension.
• Parent-Child Hierarchies: These are a special type of hierarchy where the relationship between members is defined by a parent-key column within the same table. This is useful for representing data with an arbitrary depth, such as organizational structures or account charts.

Creating a Regular Hierarchy

Let's assume you have a 'Geography' dimension with attributes like 'Country', 'StateProvince', and 'City'. To create a regular hierarchy:

1. Open your SSAS project in SQL Server Data Tools (SSDT).
2. In the Solution Explorer, right-click on the dimension you want to create a hierarchy for (e.g., 'Geography').
3. Select 'New Hierarchy'.
4. In the Dimension Designer, drag and drop the desired attributes from the 'Attributes' pane to the 'Hierarchies' pane. The order in which you drop them defines the level order. For our example, drag 'Country' first, then 'StateProvince', and finally 'City'.
5. Rename the hierarchy to something descriptive, like 'Geography Hierarchy'.
6. Save your changes and deploy the SSAS project.

You can also create hierarchies directly from attribute relationships. If you have defined a natural attribute relationship (e.g., City belongs to StateProvince, StateProvince belongs to Country), SSAS can often automatically suggest or create hierarchies.

Creating a Parent-Child Hierarchy

For a parent-child hierarchy, you'll need a table with at least two columns:

• A unique identifier for each member (e.g., 'EmployeeID').
• A column that references the unique identifier of the member's parent (e.g., 'ManagerID').

To create a parent-child hierarchy:

1. Ensure your dimension is based on a data source view that includes the table with parent-child relationships.
2. In the Dimension Designer, right-click in the 'Attributes' pane and select 'New Parent-Child Hierarchy'.
3. Select the attribute that serves as the unique identifier (e.g., 'EmployeeID').
4. Select the attribute that serves as the parent identifier (e.g., 'ManagerID').
5. SSAS will automatically create a hierarchy representing the parent-child structure.
6. Configure the 'NameColumn' for the hierarchy to display meaningful member names.
7. Save and deploy.

Parent-child hierarchies offer flexibility but require careful consideration of performance and user experience, especially with very deep or unbalanced structures.

Best Practices

• Descriptive Names: Use clear and concise names for your hierarchies and levels.
• Logical Order: Ensure levels are ordered logically from most general to most specific.
• Attribute Relationships: Define attribute relationships correctly, as they often drive hierarchy creation and performance.
• User Experience: Consider how users will interact with the hierarchy and design it to facilitate intuitive navigation.

By mastering hierarchy creation in SSAS, you can significantly enhance the analytical capabilities and user-friendliness of your multidimensional solutions.