SSAS : Parent-Child dimension
Introduction
Parent-Child dimension is a dimension with an auto-referencing relationship in the main table of the dimension that can be used in many data structures such as accounts or employees.
Implementation
In order to better explain how to create this type of dimension and the options, you need to understand the context of the example which is part of this article. We have a dimension table called DimFamily in the Data warehouse, a table that contains the list of families (categories) of a product organized in a descending hierarchy and is defined in this way :
Indeed, creating our Parent-Child dimension is based on two columns FamilyId and FamilyParentId, FamilyId is the key that identifies each member, and the FamilyParentId column called parent key column identifies the parent of each member, together define the parent-child relationship between the members of the dimension.
Turning now to the Cube part (SSAS), we first create the table in the DSV :
It should be noted that the relationship between the two DataColumns is source=FamilyParentId and Destination=FamilyId.
Then, creating the dimension from the dsv, BIDS automatically detects that the usage property of FamilyParentId is in Parent.
After the dimension process, here is the result :
Properties
1. MembersWithData
By default, the value of this property is NonLeafDataVisible, which means that if a member is also its parent, make it visible :
However, if we set the property to NonLeafDataHidden, members who are equal to their parents will be invisible :
2. MembersWithDataCaption
This property is effective when the MembersWithData property is set to NonLeafDataVisible, because it removes the ambiguity between parent and child if a member is also its parent by naming the parent member.
For example, setting the value *(Parent) gives the following result :
3. RootMemberIf
This property tells us when to consider a member as a relative parent of its hierarchy, according to the following choices :
- ParentIsBlank: Only members with an null or empty parent key are at the highest level of the hierarchy.
- ParentIsSelf: Only members with a parent key equal to the member key are at the highest level of the hierarchy.
- ParentIsMissing: Only members with no parent keys are at the highest level of the hierarchy.
- ParentIsBlankSelfOdMissing: Group all three choices.
4. NamingTemplate
By default, the different levels of a parent/child hierarchy are named "Level1, Level2 ...".
The purpose of the NamingTemplate property is to give the possibility to create your own naming model.
However, you can rename each level in the hierarchy to call what you want.
Result on an Excel file :
Conclusion
Now that the dimension is created, you can link it to the fact tables at the lowest level.
Other Languages
This article is also available in the following languages: