Creating Dimensions

Dimensions are fundamental objects in SQL Server Analysis Services (SSAS) multidimensional models. They represent the business perspectives (e.g., customers, products, dates, locations) by which users analyze data. Properly designed dimensions are crucial for intuitive and efficient querying of your data cubes.

Understanding Dimension Types

SSAS supports several dimension types, each suited for different scenarios:

  • Standard Dimension: The most common type, typically based on a single table in the data source view.
  • Degenerate Dimension: A dimension that is derived directly from a fact table's column (e.g., a transaction ID that doesn't have its own dimension table).
  • Junk Dimension: A dimension that consolidates multiple low-cardinality dimension attributes from different dimension tables into a single dimension. This helps reduce the number of dimension tables needed for fact tables with many small dimensions.
  • Role-Playing Dimension: A single physical dimension that can be referenced multiple times in a cube, with each reference playing a different role (e.g., a Date dimension used for Order Date, Ship Date, and Due Date).

Steps to Create a Dimension

You can create dimensions using SQL Server Data Tools (SSDT) or programmatically. Here's a general workflow using SSDT:

  1. Open your SSAS Project: In Visual Studio with the Analysis Services projects extension, open your existing SSAS multidimensional project or create a new one.
  2. Add a New Dimension: Right-click on the "Dimensions" folder in Solution Explorer and select "Add Dimension".
  3. Choose Dimension Type: The Dimension Wizard will appear. Select the type of dimension you want to create (Standard, Degenerate, Junk, or create from existing cube dimension). For most cases, you'll select "Standard dimension".
  4. Select Source Table: Choose the table from your Data Source View that will serve as the source for your dimension. This table should contain the attributes you want to expose to your users.
  5. Select Source Columns: Identify the columns from the source table that will become attributes in your dimension. Select a unique identifier column (key attribute) and other descriptive attributes.
  6. Name the Dimension: Provide a meaningful name for your dimension (e.g., "DimCustomer", "DimProduct", "DimDate").
  7. Define Hierarchies (Optional but Recommended): You can create attribute hierarchies (e.g., Year -> Quarter -> Month for a Date dimension) to enable drill-down analysis.
  8. Review and Finish: Review the configuration and click "Finish".

Important Note:

Ensure your source table has a primary key or a unique combination of columns that can uniquely identify each dimension member. This is crucial for data integrity and performance.

Key Dimension Concepts

  • Attributes: Individual columns from your source table that describe the dimension members. Examples include "Customer Name", "City", "Product Category", "Month Name".
  • Attribute Hierarchy: A hierarchical structure of attributes within a dimension, allowing users to navigate through different levels of granularity.
  • Key Attribute: The attribute that uniquely identifies each member of the dimension. It's usually a primary key from the source table.
  • Natural Hierarchy: A hierarchy that naturally exists in your data, like date or geography.
  • Snowflake Dimensions: Dimensions where attributes are normalized into multiple related tables. This can lead to more complex modeling but can save space if attributes are highly redundant.
  • Star Schema vs. Snowflake Schema: Understanding these schema designs helps in deciding how to structure your dimensions. Star schemas (dimension tables directly linked to a fact table) are generally preferred for performance.

Performance Tip:

For large dimensions, consider enabling "Attribute All" for the key attribute to improve query performance when users select the entire dimension.

Example: Creating a Customer Dimension

Scenario:

You have a `Customers` table in your data warehouse with columns like `CustomerID`, `FirstName`, `LastName`, `City`, `State`, and `Country`. You want to create a Customer dimension for your cube.

Steps:

  1. In SSDT, add a new dimension, select "Standard dimension", and choose the `Customers` table.
  2. Select `CustomerID` as the key attribute.
  3. Include `FirstName`, `LastName`, `City`, `State`, and `Country` as descriptive attributes.
  4. Optionally, create a geography hierarchy: `Country` -> `State` -> `City`.
  5. Name the dimension `DimCustomer`.

After deployment, users can analyze sales by customer, city, state, or country.

Next Steps

Once dimensions are created, they are typically linked to fact tables within a cube. Learn more about creating measures and designing cubes.