SSAS Ragged Hierarchy (BottomUp approach)

Hi there reader!
There are a couple of methods on how to populate so called ‘ragged hierarchies’ in SSAS, a few tricks:

Naturalizing a Parent-Child Hierarchy:

TopDown and hide middle levels:

BottomUp approach by Chris Webb (Ragged Hierarchies, HideMemberIf and MDX Compatibility):

I really liked the BottomUp approach that Chris Webb proposed. This is how a TopDown-hierarchy looks like:


And here’s the BottomUp-hierarchy we’re going to be using for this demonstration:


First things first, here’s the cube’s structure:


Define the relationships (nothing fancy here):

Here’s where the magic happens! Set the HideMemberIf-property to ‘OnlyChildWithParentName‘, so basically hide the child when it has the same name as its parent.


And here’s the result of that hard work:



Important note
This method works for Excel without setting the “MDX Compatibility = 2” requirement, but in SSRS and your MDX query it will only return those records which have no hidden levels. Strange issue, I suggest you make another seperate hierarchy for reporting that has the HideMemberIf property set to ‘Never’ for all levels.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s