Attribute Hierarchies in Multidimensional Modeling

This document describes attribute hierarchies in SQL Server Analysis Services (SSAS) multidimensional models. Attribute hierarchies are fundamental to providing meaningful analytical views of your data.

What are Attribute Hierarchies?

In multidimensional modeling, an attribute hierarchy represents a parent-child relationship or a set of related attributes within a dimension. For example, in a Geography dimension, you might have attributes like Country, State/Province, and City. These can be arranged into an attribute hierarchy where Country is the parent of State/Province, and State/Province is the parent of City.

These hierarchies allow users to:

• Drill Down and Roll Up: Navigate through different levels of detail in the data. For instance, from viewing sales by country to viewing sales by state within a country, and then to sales by city within a state.
• Slice and Dice: Focus on specific segments of the data.
• Analyze Trends: Understand performance at various aggregation levels.

Types of Attribute Hierarchies

Analysis Services supports two primary types of attribute hierarchies:

1. Natural Hierarchies (Parent-Child Hierarchies)

These hierarchies are derived from a single table that contains a self-referencing relationship. Each row in the table has a key and a reference to its parent's key. This is common for organizational structures, product categories, or geographical data where relationships are inherently hierarchical.

Example: An Employee dimension table where each employee record has an EmployeeID and a ManagerID (which references another EmployeeID).

2. User-Defined Hierarchies

These hierarchies are created by arranging existing attributes from a dimension into a specific structure. You define the levels and the relationships between them, irrespective of the underlying physical table structure. This is the most common type of attribute hierarchy.

Example: In a Date dimension, you can create a hierarchy with levels for Year, Quarter, Month, and Day. In a Product dimension, you might create a hierarchy with Category, Subcategory, and Product Name.

Creating Attribute Hierarchies

Attribute hierarchies are primarily defined within the Dimension Designer in SQL Server Data Tools (SSDT) or Visual Studio with Analysis Services projects.

Steps to Create a User-Defined Hierarchy:

1. Open your Analysis Services project in SSDT.
2. In the Solution Explorer, right-click on the Dimensions folder and select New Dimension.
3. Choose Use an existing table and select your dimension table.
4. In the Dimension Designer, switch to the Hierarchy tab.
5. Click the New Hierarchy button on the toolbar.
6. Select User Hierarchy.
7. Drag attributes from the Data Source View pane to the Hierarchy Levels pane in the desired order. The topmost attribute will be the highest level.
8. Name your hierarchy appropriately.
9. Configure properties for each level, such as level name, key columns, and aggregation functions.

Hierarchy Properties and Configuration

When defining hierarchies, you can configure various properties:

• Name: A descriptive name for the hierarchy.
• Levels: Define the constituent attributes and their order.
• Level Properties:
• Name: Name of the level (e.g., 'Year', 'State').
• Key Columns: The attribute(s) that uniquely identify members at this level.
• Name Column: The attribute column used for displaying member names.
• Members With Data: How to handle members that might not have data at a particular level.
• All Member: Define the name and behavior of the 'All' member for the hierarchy.

Benefits of Using Attribute Hierarchies

• Improved User Experience: Provides intuitive navigation for business users.
• Enhanced Performance: Pre-defined aggregations can significantly speed up queries.
• Meaningful Analysis: Enables users to derive insights by analyzing data at different granularities.
• Data Consistency: Ensures a standardized way of viewing and reporting on data across the organization.

Example: Product Dimension Hierarchy

Consider a Product dimension with attributes:

• ProductID
• ProductName
• SubcategoryName
• CategoryName
• BrandName

A user-defined hierarchy could be structured as follows:

Hierarchy: Product Hierarchy
  Level 0: Category (from CategoryName)
  Level 1: Subcategory (from SubcategoryName)
  Level 2: Brand (from BrandName)
  Level 3: Product (from ProductName)
        

This hierarchy allows users to see total sales by Category, then drill down to see sales by Subcategory within each category, and so on, down to individual Products.

Further Reading

• Dimensions in Multidimensional Models (Analysis Services)
• Parent-Child Hierarchies in Analysis Services
• Creating Dimensions in Analysis Services