Creating Hierarchies

Hierarchies represent the relationships between different levels of detail within a dimension. They allow users to navigate through data from a high-level summary down to granular details, providing a structured way to explore and analyze business information.

Understanding Hierarchy Types

There are two primary types of hierarchies in SQL Server Analysis Services (SSAS) multidimensional modeling:

Parent-Child Hierarchies: These are formed when a single attribute contains both the parent and child members, often used for organizational structures (e.g., employees reporting to managers) or geographical breakdowns where a parent can have multiple children, and a child can only have one parent. The relationship is defined by a parent column referencing a unique key column within the same attribute.
Unbalanced Hierarchies: In an unbalanced hierarchy, the levels do not necessarily have the same depth. For example, a product category hierarchy might have 'Electronics' as a top level, followed by 'Computers' and 'Televisions', but then 'Computers' might have levels like 'Laptops' and 'Desktops', while 'Televisions' might only have 'LED' and 'OLED'.
Ragged Hierarchies: Similar to unbalanced hierarchies, ragged hierarchies have levels that do not align perfectly across all branches. A member at a particular level might have children that exist at different levels of depth compared to other members at the same level.

Creating a User-Defined Hierarchy

User-defined hierarchies are the most common type and are created by combining existing attributes within a dimension. This allows you to define navigation paths that make sense for your business analysis.

Steps to Create a User-Defined Hierarchy:

In SQL Server Data Tools (SSDT) or Visual Studio with Analysis Services projects, open your Analysis Services project.
In the Solution Explorer, double-click the dimension you want to add a hierarchy to.
In the Dimension Designer, switch to the 'Browser' tab.
In the 'Attributes' pane, drag and drop the attributes you want to include in the hierarchy into the 'Hierarchy Levels' pane. Arrange them in the desired order from top level to bottom level.
Right-click on the newly created hierarchy in the 'Hierarchy Levels' pane and select 'Rename'. Give it a meaningful name (e.g., "Geography", "Product Hierarchy").
To create a parent-child hierarchy, you need to designate a parent attribute and a key attribute within the same dimension. This is typically done in the Attribute properties.

Configuring Hierarchy Properties

Once a hierarchy is created, you can configure its properties to control its behavior and appearance in client applications.

Name: The display name of the hierarchy.
Dimension: The dimension to which the hierarchy belongs.
Levels: The individual levels within the hierarchy, each corresponding to an attribute.
IsAggregatable: Determines if the top-level members of the hierarchy can be aggregated. For most hierarchies, this should be True.
Members With Data: Specifies how to handle members that do not have data at a specific level. Options include 'HideMembers' (default), 'ShowAllMembers', and 'ShowAbbreviatedMembers'.

Example: Creating a 'Date' Hierarchy

Let's consider a 'Date' dimension with attributes like Year, Quarter, Month, and Day. We can create a hierarchy to allow users to analyze data by Year, then Quarter, then Month, and finally Day.


-- Example T-SQL for dimension creation (conceptual)
CREATE DIMENSION [Date]
AS
(
    [DateKey],
    [Year],
    [Quarter],
    [Month],
    [Day],
    [MonthName]
);

-- In SSAS Dimension Designer for 'Date' dimension:
-- Drag 'Year', 'Quarter', 'Month', 'Day' into Hierarchy Levels pane.
-- Rename the hierarchy to "Date Hierarchy".

Benefits of Using Hierarchies

Implementing well-defined hierarchies significantly enhances the usability and analytical power of your SSAS cubes:

Intuitive Navigation: Users can easily drill down and roll up through data, mimicking real-world business structures.
Improved Performance: Pre-defined hierarchies can be leveraged by Analysis Services for faster query processing and aggregation.
Enhanced User Experience: They simplify complex data models, making them more accessible to business users.
Consistent Reporting: Hierarchies ensure that aggregations and reporting are consistent across different levels of detail.

Key Concept: A dimension can contain multiple hierarchies. For example, a 'Product' dimension might have a 'Product Category Hierarchy' and a 'Product Model Hierarchy'.

Best Practices

Keep hierarchy names descriptive and intuitive.
Ensure that attribute relationships are correctly defined before creating hierarchies.
Align hierarchies with common business reporting needs.
Avoid excessively deep hierarchies, as they can sometimes hinder usability.
Consider creating both user-defined and parent-child hierarchies where appropriate.