Ragged hierarchies with InMemory

This article provides a brief overview of how to address the issue of a dimension with a ragged hierarchy or “holes” in the hierarchy.

Creating a ragged hierarchy is one solution to the situation where you have a hierarchy - if you don't use a ragged hierarchy you will be showing empty or duplicate levels in the hierarchy as shown in the example below.

Assumptions for this example:

• Department dimension with the following hierarchy levels: 
  • Division
  • SubDivision
  • Store
  • Department
    
    
• All Departments have a Division and a Store level.
  
  
• Some Departments have SubDivisions (such as under Engine) while others do not (such as under Earthmoving). See the table below
  
  
  
• User should always be able to drill down from top level (Division) to lowest level (Department).

Option 1 - Department dimension with EMPTY Levels

Simply leave the empty levels in place.

As users drill down, they will encounter empty levels.

Option 2 - Department dimension with DUPLICATE Levels

If empty levels are not acceptable, then another option is to fill empty levels by copying lower level values up.

For example, if a record has an empty SubDivision, then copy Store up to SubDivision, and copy Department up to Store. This eliminates the empty levels, but the user will encounter duplicate values in lower levels.

As you can see below, the Store and Department have been copied up to the SubDivision and Store levels to fill the empty levels under the Earthmoving Division.

As users drill down, they will eventually encounter duplicate levels.

Option 3 - Department with RAGGED Levels

To eliminate both empty and duplicate levels, create a dimension in the data model using the “Ragged hierarchy depth column” feature.

This requires first modifying the dimension table as follows:

• Copy the level values up to the empty level as described in option 2 above.
• Add a depth column to indicate the maximum level that will be shown for a given record. This column can be named anything (in this case it is called “Depth”
  

In the data model definition for the dimension, drag the Depth column into the “Ragged hierarchy depth column”.

As users drill down, they will only be able to see the lowest level based on the depth, with no empty or duplicate levels. Note that Earthmoving drills down 3 levels and Engine drills down 4 levels.