Define Dimensions in Multidimensional Modeling

Tip: Dimensions are fundamental to multidimensional analysis. They provide the context for your data, allowing users to slice and dice measures from various perspectives.

What are Dimensions?

In SQL Server Analysis Services (SSAS) multidimensional models, a dimension is a table that describes the business data. Dimensions contain attributes that categorize, filter, and group measures. Think of them as the "who," "what," "where," "when," and "how" of your business data.

For example, a Date dimension might contain attributes like Year, Quarter, Month, and Day. A Product dimension could have attributes like Category, Subcategory, and Product Name.

Key Components of a Dimension

Attributes: These are columns from your dimension tables. Each attribute represents a distinct characteristic of the dimension (e.g., Year, City, Customer Name).
Attribute Hierarchies: Attributes can be organized into hierarchies, allowing users to navigate through different levels of detail. For instance, a Geography dimension might have a hierarchy of Country > State > City.
Dimension Key: A unique identifier for each row in the dimension table.
Natural Key: The primary key in the source data that uniquely identifies a dimension member.
Name Column: The column used to display the name of a dimension member.

Creating Dimensions

You typically create dimensions in SQL Server Data Tools (SSDT) by:

Adding a new Dimension object to your Analysis Services project.
Selecting the source table(s) from your data source view.
Choosing the attributes that will form the dimension and its hierarchies.
Configuring properties for attributes and hierarchies.

Example: Creating a 'Product' Dimension

Suppose you have a DimProduct table in your data warehouse with columns like ProductID, ProductName, ProductCategory, and ProductSubcategory.

To create a 'Product' dimension:

Select DimProduct as the source table.
The ProductID will typically be the key.
ProductName will be a descriptive attribute and often the name column.
ProductCategory and ProductSubcategory can be attributes.
You can create a hierarchy like: ProductCategory > ProductSubcategory > ProductName.

Dimension Properties

When defining a dimension, you'll configure various properties, including:

Property	Description
`Name`	The name of the dimension (e.g., 'Product').
`Source Table`	The table in the data source view that provides the data for the dimension.
`Key Columns`	The column(s) that uniquely identify members in the dimension.
`Name Column`	The column used for displaying the member name.
`Attribute Hierarchy Enabled`	Determines if hierarchies can be built from this dimension.
`Members With Data Aggregation`	Specifies how to aggregate members with data.

Dimension Types

Analysis Services supports several dimension types, each with specific uses:

Standard Dimension: The most common type, based on a single table.
Parent-Child Dimension: Represents hierarchical data where members can have a parent and children (e.g., employee-manager relationships).
Role-Playing Dimension: A single physical dimension that can be used in multiple roles within a cube (e.g., a single Date dimension used for Order Date, Ship Date, and Delivery Date).
Unbalanced Hierarchy Dimension: Hierarchies where levels do not have the same number of related attributes.

Important: Proper dimension design is crucial for query performance and usability. Avoid overly wide dimensions or excessively deep hierarchies.

Best Practices

Denormalize your dimension tables in the data warehouse to improve performance.
Use descriptive names for dimensions and attributes.
Create meaningful hierarchies that reflect how users will analyze data.
Consider using natural keys when appropriate but ensure they are stable.
Handle unknown or null values gracefully, often by creating dedicated "unknown" members.

By carefully defining your dimensions, you lay the groundwork for a powerful and intuitive multidimensional analysis solution in SQL Server Analysis Services.