What is it?
Balanced hierarchy: In balanced (standard) hierarchies, branches of the hierarchy all have the same level (depth), with each member's parent being at the level immediately above the member.
Example: time dimension, where the depth of each level (year, quarter, month, etc.) is consistent.
Unbalanced hierarchy: the hierarchy branches can have inconsistent depths.
Using Parent/child dimension
Using ragged hierarchy with "Hide Member If" property
Another approach is to design a regular dimension on top of a ragged or unbalanced table/view. Ragged hierarchy: In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member.
Once we create the normal hierarchy, we can use HideMemberIf property to hide Nulls or repeated members.
Unbalanced hierarchy: the hierarchy branches can have inconsistent depths.
Example of an unbalanced hierarchy is an organization chart. The levels within the organizational structure are unbalanced, with some branches in the hierarchy having more levels than others.
The following charts illustrate the unbalanced hierarchies:
How to handle it on SSAS?
There are 2 methods to handle ragged hierarchies on SSAS:
Using Parent/child dimension
In this method we need to create parent-child dimension. Parent-child dimension can be created on top of a parent-child table/view where each record has a reference to its parent record (e.g. ParentID column). An example of this type of tables can be found on Adventure Works sample database.
Using ragged hierarchy with "Hide Member If" property
Another approach is to design a regular dimension on top of a ragged or unbalanced table/view. Ragged hierarchy: In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member.
Example: geography dimension which number of levels can be different on different countries. The levels provide a meaningful context to its members, thus, while Washington DC is a child of USA, it is included at the City level with Los Angeles. Therefore there will be nulls on some levels.
Note-1: We can also repeat the parent name instead of null as a placeholder.
Note-2: In the example above, 1st and 2nd record will be required when we need to assign facts to non-leaf members. In this example we may need to assign a fact to CA which is a non-leaf member.
Once we create the normal hierarchy, we can use HideMemberIf property to hide Nulls or repeated members.
HideMemberIf Setting
|
Description
|
Never
|
Level members are never hidden.
|
OnlyChildWithNoName
|
A level member is hidden when it is the only child of its parent and its name is null or an empty string.
|
OnlyChildWithParentName
|
A level member is hidden when it is the only child of its parent and its name is the same as the name of its parent.
|
NoName
|
A level member is hidden when its name is empty.
|
ParentName
|
A level member is hidden when its name is identical to that of its parent.
|
Example of an Account hierachy:
Important notes:
- For large parent-child dimensions, aggregations are created only for the key attribute and the top attribute, therefore queries returning cells at intermediate levels are calculated at query time and can be slow. If you have a large parent-child hierarchy (more than 250,000 members), you may want to consider using a hierarchy with a fixed number of levels.
- Microsoft recommends using P-C whenever you need to set unary operators.
- DataMember property only works in parent-child dimensions
- You can’t cross-join levels on a parent-child hierarchy. It might cause some complications in report development
.png)
Posts
