Designing Dimensions

Dimensions are fundamental components of a multidimensional data model in SQL Server Analysis Services (SSAS). They provide the context for your data, allowing users to slice, dice, and analyze measures from various perspectives. This document guides you through the process of designing effective dimensions.

What are Dimensions?

In SSAS, dimensions represent the descriptive attributes of your business data. For example, in a sales cube, dimensions could include:

• Time: Year, Quarter, Month, Day
• Geography: Country, State, City
• Product: Category, Subcategory, Product Name
• Customer: Segment, Customer Name

Each dimension consists of attributes that users will use to filter and group their data. These attributes can be organized into hierarchies for more sophisticated analysis.

Key Concepts in Dimension Design

Dimension Attributes

Attributes are the individual columns or fields that make up a dimension. They describe the members of the dimension. Key considerations for attributes include:

• Attribute Keys: The unique identifier for each member within an attribute.
• Attribute Types: SSAS defines several attribute types (e.g., Key, Name, Parent, Granularity) that influence how attributes are used in queries and aggregations.
• Attribute Properties: Including descriptions, display folders, and ordering.

Hierarchies

Hierarchies allow you to define parent-child relationships between attributes, creating drill-down capabilities. Common examples include:

• Time Hierarchy: Year → Quarter → Month → Day
• Product Hierarchy: Category → Subcategory → Product

Designing intuitive and logical hierarchies is crucial for user experience.

Dimension Types

SSAS supports several dimension types, each with specific use cases:

• Standard Dimensions: The most common type, representing descriptive attributes.
• Fact Dimensions: Used to store factual data within a dimension, often for specific reporting needs.
• Role-Playing Dimensions: A single dimension can be used multiple times in a cube, each with a different role (e.g., a 'Date' dimension used as 'Order Date', 'Ship Date', and 'Delivery Date').
• Degenerate Dimensions: Attributes that are part of the fact table but are not part of a dimension.

Best Practices for Dimension Design

Normalize Your Data

Dimensions should be designed based on normalized relational tables. Avoid redundancy to ensure data integrity and efficient processing.

Choose Appropriate Granularity

The granularity of a dimension determines the lowest level of detail for its members. Ensure it aligns with your analytical requirements.

Define Meaningful Hierarchies

Create hierarchies that reflect the natural business structure and allow for intuitive exploration of data.

Use Attribute Types Correctly

Assign the correct attribute type to each attribute to leverage SSAS features like roll-ups and advanced querying.

Optimize for Performance

Consider using dimension perspectives, pre-calculated aggregations, and proper indexing to improve query performance.

Steps to Design a Dimension

1. Identify Business Requirements: Determine the analytical perspectives needed by users.
2. Identify Source Data: Locate the relevant columns in your data source views.
3. Create the Dimension: Use SQL Server Data Tools (SSDT) to create a new dimension.
4. Define Attributes: Add attributes from your source tables.
5. Configure Attribute Properties: Set keys, names, attribute types, and other properties.
6. Design Hierarchies: Create parent-child or natural hierarchies.
7. Set Dimension Properties: Configure options like dimension type and storage mode.
8. Process the Dimension: Load the dimension data into the SSAS database.

Example: Designing a 'Product' Dimension

Let's consider designing a 'Product' dimension from a 'DimProduct' table in your data warehouse.

In this example:

• ProductID would be the key attribute.
• ProductName would be the name attribute.
• ProductCategory and ProductSubcategory would be attributes used to build a product hierarchy.

Further Reading