Dimension Attributes
Dimension attributes are the fundamental building blocks of dimensions in SQL Server Analysis Services (SSAS) multidimensional models. They represent the descriptive characteristics of the data that users will analyze. For example, in a 'Product' dimension, attributes could include 'Product Name', 'Product Category', 'Brand', and 'Color'.
Key Concepts and Types
Dimension attributes define the levels of granularity and the ways users can slice and dice their data. Understanding the different types of attributes is crucial for effective cube design.
Key Attributes
The key attribute is the primary identifier for a dimension. It uniquely identifies each member of the dimension. Typically, this is a surrogate key from the source data warehouse or a business key that is guaranteed to be unique.
- Each dimension must have at least one key attribute.
- It is used to link the dimension to fact tables in the data warehouse.
Attribute Types
Attributes can be categorized based on their role and behavior within the dimension:
- Regular Attribute: The most common type. Represents descriptive properties.
- Attribute Relationships: Defines how attributes within a dimension are related, enabling the creation of natural hierarchies.
- User-Defined Hierarchy: A grouping of related attributes that allows users to navigate data at different levels of granularity (e.g., Year -> Quarter -> Month).
- Semi-additive Measures: While measures are typically associated with fact tables, certain attributes can influence how semi-additive measures behave.
Configuring Dimension Attributes in Visual Studio
When designing your SSAS solution using SQL Server Data Tools (SSDT) in Visual Studio, you configure dimension attributes within the Dimension Designer.
Steps to Create and Configure Attributes:
- Add Attribute: Right-click on the dimension and select "New Attribute".
- Attribute Properties:
- Name: A user-friendly name for the attribute.
- Key Columns: Specifies the source column(s) from the dimension table that define the attribute's unique values.
- Attribute Type: Sets the role of the attribute (e.g., 'Attribute Name', 'Key', 'Parent'). This is vital for hierarchy formation.
- Attribute Hierarchy Enabled: Determines if the attribute can be used in user-defined hierarchies.
- IsAggregatable: Controls whether the attribute's values can be aggregated (usually 'Yes' for descriptive attributes).
- Attribute Relationships: Defines the parent-child relationships between attributes.
- Dimension Structure Tab: Drag and drop attributes into the desired hierarchy structure.
Attribute Relationships
Attribute relationships are critical for defining the structure and navigation capabilities of a dimension. They establish a parent-child relationship between attributes, allowing Analysis Services to create implicit hierarchies and improve query performance.
Types of Attribute Relationships:
- One-to-Many: A parent attribute has many child attributes, but each child attribute belongs to only one parent. This is the most common type and forms natural hierarchies. For example, 'Product Category' (parent) to 'Product Subcategory' (child).
- Many-to-Many: Not directly supported between attributes within a single dimension. This scenario is handled through fact table design or specific dimensional modeling techniques.
Example: Product Dimension
Consider a 'Products' dimension with the following source columns:
ProductID(integer, primary key)ProductName(varchar)ProductSubcategoryName(varchar)ProductCategoryName(varchar)BrandName(varchar)
In SSAS, you would configure these as attributes:
| Attribute Name | Key Columns | Attribute Type | Is Aggregatable | Hierarchy Role |
|---|---|---|---|---|
| Product | ProductID |
Attribute Name | Yes | Key Attribute |
| Product Name | ProductName |
Attribute Name | Yes | Regular |
| Product Subcategory | ProductSubcategoryName |
Attribute Name | Yes | Regular |
| Product Category | ProductCategoryName |
Attribute Name | Yes | Regular |
| Brand | BrandName |
Attribute Name | Yes | Regular |
Creating a Hierarchy:
You could then establish attribute relationships to create a natural hierarchy:
Product Category->Product Subcategory->Product
This allows users to analyze sales by category, then drill down to subcategory, and finally to individual products.
Advanced Attribute Configurations
Attribute Type: Parent-Child
For hierarchical data where the depth is not fixed (e.g., organizational structures, employee reporting lines), you can use the 'Parent-Child' attribute type. This requires two key columns: one for the member's key and one for its parent's key.
Attribute Type: Ranged Date
When creating date dimensions, Analysis Services can automatically generate date-related attributes (Year, Month, Day, etc.) and set their types appropriately. This is often more efficient than manually creating each attribute.
Attribute Properties for Performance
- AttributeCacheSize: Controls how many attribute members are cached.
- Store: Defines how attribute data is stored.