SQL Server Documentation > Analysis Services > Multidimensional Models > Dimension Attributes

Dimension Attributes

Last updated: October 26, 2023 | Feedback

Dimension attributes are the fundamental building blocks of dimensions in SQL Server Analysis Services (SSAS) multidimensional models. They represent the various characteristics or properties that describe the data within a dimension. For example, in a 'Product' dimension, attributes might include 'Product Name', 'Product Category', 'Color', and 'Size'.

Understanding and effectively configuring dimension attributes is crucial for creating robust and performant OLAP cubes. They determine how users can slice and dice data, how hierarchies are structured, and how the overall data model is interpreted.

Key Concepts of Dimension Attributes

  • Attribute Hierarchy: Attributes can form implicit or explicit hierarchies. An implicit hierarchy is a default structure based on attribute relationships. An explicit hierarchy is a user-defined structure like geographical regions (Country > State > City).
  • Attribute Relationships: These define the dependencies between attributes within a dimension. They are vital for query performance and ensuring data integrity. A common example is the relationship between 'City' and 'State', where multiple cities belong to a single state.
  • Attribute Types: SSAS supports various attribute types, including standard attributes, key attributes, and related attributes. The key attribute is typically the unique identifier for a dimension member.
  • Granularity: The level of detail represented by an attribute. For example, in a 'Date' dimension, 'Day' is more granular than 'Month'.
  • Name Column: The column in the data source that provides the user-friendly name for the attribute member.
  • Attribute Caption: The display name for the attribute itself, often seen in user interfaces.

Creating and Configuring Dimension Attributes

Dimension attributes are typically created and managed using SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS).

Steps to Create an Attribute:

  1. Open your SSAS project in SSDT.
  2. Navigate to the Dimensions folder in Solution Explorer.
  3. Double-click the dimension you want to modify.
  4. In the Dimension Designer, switch to the Attributes pane.
  5. Drag and drop columns from the Data Source View onto the Attributes pane.
  6. Configure the properties of the newly created attribute in the Properties pane.

Common Attribute Properties:

  • Name: The internal name of the attribute.
  • AttributeHierarchyEnabled: Controls whether the attribute can be used in hierarchies.
  • AttributeHierarchyVisible: Determines if the attribute is visible to end-users.
  • KeyColumns: The source columns that uniquely identify members of this attribute.
  • NameColumn: The source column providing the display name.
  • AttributeRelationships: Define parent-child or dependency relationships with other attributes.

Example: Configuring Attribute Relationships

Let's consider a 'Geography' dimension with attributes 'City', 'State', and 'Country'.

You would establish the following relationships:

  • City > State (Many-to-One)
  • State > Country (Many-to-One)

In the Dimension Designer, you can drag the State attribute onto the City attribute to establish this relationship. This allows Analysis Services to efficiently group cities by their respective states.

Note: Proper definition of attribute relationships is critical for performance tuning. Avoid many-to-many relationships if possible, or use appropriate strategies to handle them.

Advanced Dimension Attribute Features

  • Attribute Member Properties: You can expose additional columns from your data source as member properties. These properties are not used for filtering or aggregation directly but provide descriptive information to users when they drill down into dimension members.
  • Ranged Attributes: Useful for attributes like 'Age' or 'Income', where you might want to group members into predefined ranges (e.g., 18-25, 26-35).
  • Adjacency Relationships: Used for defining relationships between adjacent members within an attribute, often seen in time-based dimensions (e.g., previous month, next quarter).

Best Practices

  • Use Descriptive Names: Ensure attribute names are clear and intuitive for end-users.
  • Define Key Attributes Appropriately: The key attribute should be unique and stable.
  • Optimize Attribute Relationships: Design relationships to reflect the natural structure of your data and improve query performance.
  • Enable Hierarchy for Frequently Used Attributes: This enhances user navigation and analysis.
  • Leverage Member Properties: Provide additional context without impacting query performance.
Previous: Dimensions Next: Hierarchies