SSAS Dimension Design

Introduction to SSAS Dimension Design

Designing effective dimensions is crucial for building high-performance and user-friendly Analysis Services (SSAS) solutions. Dimensions provide the business context for your data, allowing users to analyze measures from different perspectives.

A well-designed dimension model enables users to easily slice and dice data, aggregate results, and gain valuable insights. This article will guide you through the fundamental concepts and best practices for designing dimensions in SSAS.

Key Principles for Designing Dimensions

When designing dimensions, consider the following principles:

• Business Relevance: Dimensions should represent real-world business concepts that users want to analyze.
• Granularity: Define the lowest level of detail for each dimension.
• Attribute Relationships: Establish logical relationships between attributes within a dimension.
• Surrogate Keys: Use surrogate keys for dimension tables to ensure stability and performance.
• Attribute Usage: Carefully consider how each attribute will be used for filtering, grouping, and slicing.

Understanding Dimension Attributes

Dimension attributes are the columns within a dimension table that describe the characteristics of that dimension. For example, in a 'Product' dimension, attributes could include 'Product Name', 'Category', 'Subcategory', 'Color', and 'Size'.

Attribute Properties

Each attribute has several properties that influence its behavior and how it's used in the cube:

• Name: The display name of the attribute.
• Key Column: The column that uniquely identifies each member of the attribute.
• Attribute Hierarchy: Determines if the attribute can be used to build hierarchies.
• Attribute Type: Specifies the type of data the attribute holds (e.g., Text, Integer).
• Attribute Order: Controls the order in which attributes appear in the browser.

For example, consider a DimProduct table with the following columns:

ProductID (PK, INT)
ProductName (VARCHAR)
Category (VARCHAR)
Subcategory (VARCHAR)
ListPrice (DECIMAL)
                    

In SSAS, ProductID would typically be the key column, while ProductName, Category, and Subcategory would be descriptive attributes.

Creating and Using Hierarchies

Hierarchies are one of the most powerful features of SSAS, allowing users to navigate data at different levels of aggregation. They are typically built from the attributes of a dimension.

Types of Hierarchies

• User-Defined Hierarchies: Created by combining multiple attributes in a parent-child or natural order. For example, a 'Geography' hierarchy could be Country > State > City.
• Natural Hierarchies: Represent pre-defined relationships in the data, such as a Date hierarchy (Year > Quarter > Month > Day).

When designing hierarchies, ensure that the attribute relationships are logical and reflect how users will naturally explore the data.