Dimensions in SQL Server Analysis Services

Dimensions are fundamental objects in SQL Server Analysis Services (SSAS) that provide descriptive attributes for the data in your cubes. They allow users to slice and dice measures, providing context and enabling analytical queries. Think of dimensions as the "who, what, where, when, and how" of your business data.

What are Dimensions?

In a star schema or snowflake schema, dimensions are typically represented by tables that contain descriptive text and hierarchical data. In SSAS, these tables are modeled into dimension objects that can be related to fact tables (which contain measures) through measure groups.

Key Concepts of Dimensions

• Attributes: These are the individual columns within a dimension that describe the dimension member. For example, in a 'Product' dimension, attributes might include 'Product Name', 'Product Category', 'Brand', and 'SKU'.
• Hierarchies: Dimensions often contain hierarchies, which represent levels of aggregation. A common example is a 'Geography' dimension with a hierarchy like 'Country' -> 'State' -> 'City'. Users can then drill down and roll up through these levels.
• Member Properties: Beyond the attributes used for navigation, dimensions can have member properties that provide additional descriptive information. These properties are not typically used for slicing and dicing but can be displayed in reports.
• Types of Dimensions:
  • Standard Dimensions: The most common type, directly mapping to a table or view.
  • Degenerate Dimensions: Attributes that are part of the fact table but are treated as a dimension (e.g., Order Number).
  • Role-Playing Dimensions: A single dimension that can be used multiple times in a cube with different roles (e.g., a 'Date' dimension used as 'Order Date', 'Ship Date', and 'Delivery Date').
  • Parent-Child Dimensions: Used to model recursive relationships, such as organizational charts or product trees where a member can have a parent that is also a member of the same dimension.

Creating and Configuring Dimensions

Dimensions are typically created using SQL Server Data Tools (SSDT) or Visual Studio with Analysis Services projects. The process involves:

1. Dimension Wizard: Guides you through selecting the source table(s), defining attributes, and creating hierarchies.
2. Attribute Configuration: Setting properties like KeyColumns, NameColumn, and AttributeHierarchyEnabled.
3. Hierarchy Design: Arranging attributes into logical levels to form navigational hierarchies.
4. Member Property Definition: Adding additional descriptive attributes to dimension members.
5. Dimension Type Selection: Choosing the appropriate dimension type based on your data model requirements.

Example: Product Dimension

Consider a 'Product' dimension with the following source table:

CREATE TABLE DimProduct (
    ProductKey INT PRIMARY KEY,
    ProductName VARCHAR(100),
    ProductCategory VARCHAR(50),
    ProductSubcategory VARCHAR(50),
    Brand VARCHAR(50),
    Color VARCHAR(20)
);

In SSAS, you would create a 'Product' dimension with:

• Attributes: ProductKey (Key attribute), ProductName, ProductCategory, ProductSubcategory, Brand, Color.
• Hierarchy: A 'Product Hierarchy' could be defined as: ProductCategory -> ProductSubcategory -> Brand -> ProductName.
• Member Properties: Color could be added as a member property of the ProductName attribute.

Best Practices

• Attribute Granularity: Ensure the lowest level of your dimension attributes represents a unique member.
• Descriptive Names: Use clear and intuitive names for dimensions, attributes, and hierarchies.
• Avoid Over-Normalization: While normalization is good for source systems, for SSAS dimensions, a denormalized or star schema-like structure often performs better.
• Optimize Hierarchies: Design hierarchies that align with business reporting needs.
• Use Member Properties Wisely: Add properties that enhance reporting without cluttering the navigational structure.

Further Reading

• Dimension Design Guidelines
• Advanced Dimension Features