Creating Dimensions
Dimensions are fundamental building blocks in data warehousing and business intelligence solutions. They represent descriptive attributes that users can use to slice and dice data. In Analysis Services, you can create both multidimensional and tabular dimensions.
Understanding Dimensions
A dimension table typically contains descriptive attributes that answer questions like "Who?", "What?", "Where?", "When?", and "How?". For example, a "Product" dimension might include attributes like Product Name, Category, Subcategory, and Brand. A "Date" dimension might include Year, Month, Day, and Day of the Week.
Creating Dimensions in Multidimensional Models
In multidimensional models, dimensions are created from dimension tables in your data source. The process usually involves:
- Identifying Dimension Tables: Locate the tables in your relational database that contain the descriptive attributes you want to use for analysis.
- Creating Dimension Objects: In Visual Studio with Analysis Services projects, you can create new dimension objects.
- Configuring Attributes: For each dimension, you define its attributes. These attributes become the columns that users will interact with (e.g., drilling down by Year, then Month, then Day).
- Defining Hierarchies: You can create user-defined hierarchies to enable drill-down analysis (e.g., Country -> State -> City).
Tip: Ensure your dimension tables are clean and contain unique keys to avoid data inconsistencies.
Creating Dimensions in Tabular Models
In tabular models, dimensions are implicitly created from the columns of your fact tables and related dimension tables. When you import data into a tabular model, Analysis Services automatically infers relationships and creates columns that can be used for filtering and grouping, effectively acting as dimensions.
- Importing Data: The primary step is importing your data from various sources.
- Defining Relationships: Explicitly define relationships between your fact and dimension tables. These relationships are crucial for how filters propagate.
- Creating Calculated Columns (Optional): You can create calculated columns to derive new attributes for analysis.
Conceptual illustration of dimension attributes and hierarchies.
Key Concepts:
Attributes
Attributes are the individual descriptive columns within a dimension. They are the lowest level of detail for a given dimension and are used to group, filter, and label data.
Hierarchies
Hierarchies represent a parent-child relationship between attributes within a dimension. They allow users to navigate through different levels of granularity. For example, a Time dimension might have a hierarchy of Year -> Quarter -> Month -> Day.
Granularity
The granularity of a dimension defines the level of detail at which data is stored or analyzed. For instance, a Product dimension might have a granularity of Individual Product, or it might be aggregated to the Product Category level.
Note: Carefully consider the granularity of your dimensions based on the analytical requirements of your users.
Example: Creating a "Geography" Dimension
Let's consider creating a "Geography" dimension for sales analysis. You would typically have a table like this:
CREATE TABLE DimGeography (
GeographyKey INT PRIMARY KEY,
City VARCHAR(100),
StateProvince VARCHAR(100),
Country VARCHAR(100),
Region VARCHAR(100)
);
In a multidimensional model, you would create a dimension object based on this table, defining City, StateProvince, Country, and Region as attributes. You could then create a hierarchy like Country -> StateProvince -> City.
In a tabular model, you would import this table and create a relationship between the appropriate key in your fact table and the GeographyKey. The columns (City, StateProvince, Country, Region) would then be available for filtering.
This section provides a foundational understanding of creating dimensions. Subsequent tutorials will delve deeper into specific configurations and best practices for both multidimensional and tabular models.