SSAS 2008 dimension designer has introduced one more tab called “AttributeRelationships” to simplify the process of setting relationships between the attributes. This tab is used to add, modify or delete attribute relationships of a dimension.
What is Attribute Relationship and why it is required?
As we know, a dimension comprises of attributes and hierarchies. The attributes are managed and configured using various attribute properties in the BIDS dimension designer. The attributes in a dimension are organized based on the relationship defined between the attributes in the dimension table. It is the attribute relationship that relates the key attribute within a dimension table to all the other attributes in the same table.
A dimensional model is based on either a star schema or a snow-flake schema. In star schema, all the dimension attributes belongs to the same relational table and hence an attribute relationship is automatically defined between the key attribute and each non-key attribute of the dimension. In snow-flake schema, the dimension attributes are derived from more than one tables related with each other. Let’s have a look at the product dimension which has the attributes derived from three dimension tables, DimProductCategory, DimProductSubCategory and DimProduct
In this case an attribute relationship is defined based on the following logic.
- Between the key attribute and each non-key attribute bound to columns in the main dimension table.
- Between the key attribute and the attribute bound to the foreign key in the secondary table that links the underlying dimension tables.
- Between the attribute bound to foreign key in the secondary table and each non-key attribute bound to columns from the secondary table.
Attribute Relationship Designer
To define unique attribute relationships, the Attribute Relationships tab in the Dimension Designer is used. But beware that incorrectly defined attribute relationship can cause illogical query results. Let’s understand the concept with the help of the Product dimension. The following figure exhibits the Attribute Relationship tab for the Product dimension.
The Attribute relationship designer has three panes,
Design pane – shows the graphical representation of the attribute relationship that is defined between the selected attributes.
Attributes pane – shows the available attributes that are selected from the Product dimension tables.
Attribute Relationships pane – shows the relationships set between the attributes.
In the above example,
- One-to-one relationship is set between the Product à Color and Product à Description.
- Many-to-one relationship is set between Product à Product Subcategory and Product à Size
- Product Subcategory attribute has a many-to-one relationship with the Category attribute.
- Size attribute has a many-to-one relationship with the Size Range attribute.
The importance of Attribute Relationship
Attribute relationship is important in the dimension design as it provides the following benefits,
- It takes less time for processing dimension, partition and query by reducing the amount of memory required for processing.
- Increased query performance by faster storage access and optimized query execution plans.
- Enables the aggregation design algorithms to select more effective aggregates, provided that user-defined hierarchies have been defined along the relationship paths.
SSAS 2008 has introduced a graphical designer for configuring relationships between attributes. Though the attribute relationship is defined automatically, sometimes we need to customize the default design to define a natural hierarchy, customize the sort order etc. using Attribute Relationship Designer.