SQL Server Analysis Services

Creating Dimensions

This section provides comprehensive guidance on creating and configuring dimensions in SQL Server Analysis Services (SSAS) multidimensional models. Dimensions are fundamental components of a data warehouse, providing the business context for analytical queries.

Understanding Dimensions

Dimensions are tables that contain descriptive attributes about the data in your fact tables. They enable users to slice, dice, and filter data in meaningful ways. Common examples of dimensions include Time, Geography, Product, and Customer.

Types of Dimensions

SSAS supports several types of dimensions, each with specific use cases:

• Standard Dimensions: The most common type, directly linked to a fact table.
• Degenerate Dimensions: Attributes that are stored directly in the fact table, typically transaction identifiers.
• Role-Playing Dimensions: A single dimension (e.g., Date) used multiple times in a cube with different roles (e.g., Order Date, Ship Date).
• Junk Dimensions: Used to consolidate low-cardinality flags and indicators that would otherwise clutter fact tables.
• Parent-Child Dimensions: Used for hierarchical data where the relationship between parent and child members is variable or unknown in advance (e.g., organizational hierarchy).

Steps to Create a Dimension

You can create dimensions using SQL Server Data Tools (SSDT) or by scripting using XMLA.

Using SQL Server Data Tools (SSDT)

1. Open your Analysis Services project in SSDT.
2. In Solution Explorer, right-click the Dimensions folder and select New Dimension.
3. The Dimension Wizard will guide you through the process.
4. Choose the source table(s): Select the tables from your data source view that will form the dimension.
5. Define dimension attributes: Select the columns from the source table(s) that will become attributes.
6. Identify the primary key: Specify the key column(s) for the dimension.
7. Create hierarchies: Optionally, define user-defined hierarchies to represent drill-down paths.
8. Name the dimension: Provide a descriptive name for your dimension.

Key Dimension Configuration Options

• Attribute Hierarchy Display Folders: Organize attributes within folders in the browser pane.
• Attribute Key Columns: Define the columns used as keys for attribute uniqueness.
• Attribute Name Columns: Specify the columns that display the names of attribute members.
• Attribute Relationships: Define relationships between attributes to enable query performance optimizations and enable natural hierarchies.
• Dimension Properties: Configure properties like IsAggregatable, UnknownMember, and KeyErrorAction.

Example: Creating a Product Dimension

Let's assume you have a DimProduct table in your data source with columns like ProductKey, ProductName, Category, and Subcategory.

To create a Product dimension:

-- Conceptual steps in SSDT
1. Right-click Dimensions -> New Dimension.
2. Choose 'Use existing table'.
3. Select 'DimProduct' from your Data Source View.
4. Select 'ProductKey' as the Key Attribute.
5. Select 'ProductName', 'Category', 'Subcategory' as other attributes.
6. Define 'Category' as the parent of 'Subcategory', and 'Subcategory' as the parent of 'ProductName' to create a natural hierarchy.
7. Name the dimension 'Product'.
            

Best Practices

• Granularity: Ensure the dimension's granularity matches or is finer than the fact table's granularity.
• Naming Conventions: Use clear and consistent naming for dimensions and their attributes.
• Attribute Relationships: Properly define attribute relationships to leverage dimension processing and query optimizations.
• Unknown Members: Configure handling for unknown members to prevent query errors.
• Aggregations: Understand how dimension attributes affect aggregation design.

Further Reading

• Dimension Attributes
• Dimension Relationships
• User-Defined Hierarchies