23 September 2009

Handling Ragged/Unbalanced hierarchies in SSAS

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.
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
References and more info:

10 comments:

  1. Hi Iman,
    the only problem with ragged hierarchy is that they are completely unusable with Excel!!!
    (also with Excel 2010)

    Francesco

    ReplyDelete
  2. Hi Francesco,

    Thanks for your comment. Actually I haven't experienced any problem with ragged hierarchy on Excel. What exactly do you mean by that?

    Iman

    ReplyDelete
  3. Hi Iman
    I mean this problem:

    http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/2a36d561-fdea-4b78-8d5f-3d383ed6d6a1

    Francesco

    ReplyDelete
  4. Francesco,

    Yes, I had read about the 'MDXCompatibility' issue you mentioned, but in my recent project I used the ragged hierarchy (second approach with level1, level2, ...) and it worked well with Excel 2007. Not sure if that was fixed in new SP or that's because of something else.

    Cheers,
    Iman

    ReplyDelete
  5. Francesco De Chirico02 October, 2009

    Hi Iman,
    thanks for your clarification. In your solution Washington DC is correctly shown in Level 2. But if you want to maintain it to level 3 (and therefore you have an intermediate Level 2 empty like in the Country-State-City hierarchy) with Excel (2007 SP2 and/or actual 2010 beta) you can't visualize it correctly. At least I'm not able! :-)
    Francesco

    ReplyDelete
  6. Great tip Iman
    Found this post very useful recently.

    ReplyDelete
  7. Hi,

    I have been fighting against the same Francesco's problem.

    Everything works fine at MSAS, but Excel cannot be able to represent the same hierarchy structure.
    Any sugestion?

    Thanks.
    Carlos

    ReplyDelete
  8. I found this on another site but to get ragged hierarchies to appear in Excel 2007 you need to add this to the connection string:

    Extended Properties="MDX Compatibility=2";MDX Compatibility=2;

    Once I did that, Excel worked fine.

    ReplyDelete
  9. Also hit by the impossibility to work with Excel (2007) and ragged hierarchies as others above. Setting MDX Compatibility as suggested by Michael completely messes up the layout: the All level moves somewhere to the middle, level1 items appear on both level0 and level1, ... My suggestion: do not force MDX Compatibility = 2, unless you tested the implications thoroughly!
    Cheers
    Tom

    ReplyDelete