Defining Dimensions

Understand how to define dimensions for your multidimensional models.

Dimensions provide the context for the data in your cube. They are the backbone of your analytical queries, allowing users to slice and dice measures from various perspectives. In SQL Server Analysis Services (SSAS) multidimensional modeling, dimensions are fundamental objects that represent business entities such as customers, products, dates, and employees.

Key Concepts in Dimension Design

Attributes

Attributes are the basic building blocks of a dimension. Each attribute typically represents a column from a dimension table in your data source. Examples include 'Customer Name', 'City', 'Product Category', and 'Month Name'. Attributes can be organized into hierarchies.

Descriptive Attributes: Provide detailed information about the dimension member.
Key Attributes: Used to uniquely identify dimension members.

Hierarchies

Hierarchies represent relationships between attributes, enabling users to navigate through different levels of granularity. Common examples include:

Time Hierarchy: Year > Quarter > Month > Day
Product Hierarchy: Category > Subcategory > Product
Geography Hierarchy: Country > State > City

Users can drill down or roll up through these hierarchies to analyze data at different levels of aggregation.

Dimension Types

Analysis Services supports several types of dimensions, each with its own characteristics and use cases:

Standard Dimensions: The most common type, typically based on a single dimension table.
Fact Dimensions: Used when a dimension member is directly related to a fact and has no intrinsic hierarchy.
Role-Playing Dimensions: A single dimension table can be used multiple times in a cube to represent different roles (e.g., a 'Date' dimension used for 'Order Date', 'Ship Date', and 'Delivery Date').
Degenerate Dimensions: Attributes that are part of the fact table but do not have a corresponding dimension table.
Parent-Child Dimensions: Represent hierarchical data where the relationships between members are stored directly within the dimension table (e.g., an organizational hierarchy).

Creating and Configuring Dimensions

Dimensions can be created using SQL Server Data Tools (SSDT) or programmatically. The process generally involves:

Selecting the Data Source: Choose the table or view from your data source that contains the dimension data.
Defining Attributes: Identify and configure the attributes that will comprise your dimension.
Creating Hierarchies: Define user-defined hierarchies by arranging attributes in a logical order.
Setting Properties: Configure various dimension properties such as the name, key columns, and enabling/disabling features like writeback or aggregation.

Best Practices for Dimension Design

Denormalization: Dimension tables are typically denormalized to improve query performance.
Attribute Naming: Use clear and consistent naming conventions for attributes.
Granularity: Ensure the granularity of your dimension is appropriate for the measures it supports.
Skipping Levels: Configure hierarchies to handle missing levels gracefully.
Natural Keys: Use natural keys where appropriate, but be mindful of their stability.

For more detailed information on specific configuration options and advanced scenarios, please refer to the official Microsoft Learn documentation.

Example: Product Dimension

Consider a product dimension with the following attributes:


- ProductKey (Key Attribute)
- ProductName
- ProductCategory
- ProductSubCategory
- Brand
- Color
- Size

A user-defined hierarchy might be structured as:


Product Category (Level 0)
  Product SubCategory (Level 1)
    Brand (Level 2)
      ProductName (Level 3)