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.
Conclusion
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.
Regards
Amit Karkhanis
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
Nice one.
I have a question, why the attribute “size range” is shown under “size” ? I mean why size range is in the same box as size AND not two boxes with an arrow between them ? I understand that there is a relationship, but how do you define that ?
Thks
Just play around with this tab and you will get the answer. (I assume that you have created a sample project for testing based on the AdventureWorksDW2008 database). Follow these steps,
1. Create a product dimension similar to the one shown in the above screenshot containing the attributes Color, Description, Product Category, Product Name, Product SubCategory, Size, Size Range.
2. Go to the Attribute Relationship tab and ensure that no relationship exists between any attributes. (You will define that step by step)
3. Right click in the Attribute Relationship pane and select “New Attribute Relationship…” from the shortcut menu. Set the relationship between Product and Color. Once you do this Color attribute will appear inside the Product box. Do similar step for Description, Product Subcategory and Size. You can now see all these four attributes inside Product box. Fine till now, the change will start from the next step.
4. Now define a relationship between Size and Size Range in the Attribute Relationship pane and you will notice that a separate box is created for Size, and Size Range attribute is placed below size. I guess a single box shows a relationship up to one level. So the product box is accommodating only one level i.e Product –> Size. Once you create one more level i.e. Product –> Size –> Size Range, the designer represents that by creating a separate box. Similar is the case with Product Subcategory –> Produuct Category.
Now coming to your question “Why Size Range is shown under Size and not two boxes with an arrow between them” (like Product and Size), you can try the following and see the magic.
Go to the Dimension Structure tab and create a new hierarchy containing Size Range at level one and Size as level two and see the change in the Attribute Relationship tab. Now two separate boxes with an arrow between them are created to show the relationship.
I guess this is how the designer works Smile