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:
http://martinmason.wordpress.com/2012/02/26/the-ssas-financial-cubepart-1ragged-hierarchies/

TopDown and hide middle levels:
http://asmdx.blogspot.nl/2008/04/parent-child-hierarchy-to-level-base.html

BottomUp approach by Chris Webb (Ragged Hierarchies, HideMemberIf and MDX Compatibility):
http://cwebbbi.wordpress.com/2009/11/11/ragged-hierarchies-hidememberif-and-mdx-compatibility/

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

SSAS-Ragged-Hierarchy-TopDown-Approach

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

SSAS-Ragged-Hierarchy-BottomUp-Approach

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

SSAS-Ragged-Hierarchy-Cube

Define the relationships (nothing fancy here):
SSAS-Ragged-Hierarchy-Cube-Structure-Relationships

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.

SSAS-Ragged-Hierarchy-Cube-Structure

And here’s the result of that hard work:

Dimension-Browse-BottomUp

Excel-Browse-BottomUp

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.








Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s