Most of the scenarios, there will be relation between different attributes of the same dimension and in this case we have to explictly define the relationship using dimenison designer. It improves the performance as well as affects calculations that are aggregated across these attributes. There is a chance of performance degrade if the relationship is not set properly. In this post we can see the steps to set attributes relationship and also important properties of the same.
We can use the same DSV(AdventureworkdDW2008) that I used to demonstrate named query where I have replaced the below query with the actual DimProduct table.
select a.ProductKey
,a.EnglishProductName
,b.ProductSubcategoryKey
,EnglishProductSubcategoryName
,c.ProductCategoryKey
,EnglishProductCategoryName
from dimProduct a
inner join DimProductSubcategory b on a.ProductSubcategoryKey = b.ProductSubcategoryKey
inner join DimProductCategory c on b.ProductCategoryKey = c.ProductCategoryKey
For each product sub category in the result set of this query is having multiple product and each product category is having multiple product sub category so there is one to many relationship exists between product sub category and product also produt category and product sub category.
Step 1 : Add DimProduct dimension from DSV to the dimension designer
 |
| Fig 1 |
Step 2 : Drag and drop English product category name from attributes window to heirarchy window and also English product sub category name and English product name in the same sequence as shown in the figure 2.
 |
| Fig 2 |
Step 3 : Click on the attribute relationships tab of the dimension designer and you can see the diagram looks like Fig 3
 |
| Fig 3 |
Step 4 : We can set the relationship between these attributes by two method. first method is just drag and drop the many relation field to one relation field ie. drag and drop English product name to English product subcategory name. Second method is right click on the relationship arrow and select edit which will open the windown as shown in Fig 4 and we can choose the many field from the name drop down of soure attribute.
 |
| Fig 4 |
Once you set the right relationship the diagram looks like figure 5
 |
| Fig 5 |
Two different types of relationship types are available in dimension design ie. flexible and rigid. if the relationship between the attributes are non changeable over time, we should set its type as rigid and vice versa. For e.g birthdate of the advertiser is fixed which is rigid type. You can set the relationship type by right click on the relationship arrow and select relationship type as shown in the fig 6.
 |
| Fig 6 |
Setp 5 : Once you set the attribute relationship and the hierarchy, it is important to give proper key columns and name for each attribute. For identifying product uniquely we need to add product sub category key and product category key into the key columns of product along with product key as shown in figure 6. Same way we need to add productkey along with product subcategory key to identify it uniquely.
 |
| Fig 7 |
Step 6 : We need to define column name since the key column is composite key. give the desription field into name column.
 |
| Fig 8 |
Now you can see in dimension browser that all fields along with newly created heirarchy is there for browsing which may lead the user in confused state.
 |
| Fig 9 |
To avoid this situation we need to set attributehierarchyvisible property of attribute to false to make sure that only hierarchy is available for browsing to avoid any kind of confusion for the user.
 |
| Fig 10 |
Once you set this property in dimension structure, browser window looks like the figure 10.
 |
| Fig 11 |
