Remodeling Recursive Hierarchy Tables for Business Intelligence

If a hierarchy has too many levels, uneven branches, or an unlimited number of levels, then these kind of hierarchies are hard to represent using fixed dimension modeling or a fixed width table.

In scenarios like these, dynamic data modeling structures like recursive hierarchies will come into the picture. In a recursive hierarchy, relationships are usually represented by using parent and child relationships. A child of a parent node can be a parent itself, which creates the hierarchy.

You can represent a hierarchical structure like this…

recursive hierarchy

…in a recursive fashion using the table below:

data hierarchy table

As you can see, nodes like 2 and 3 appear in both the parent and child columns.

MicroStrategy does not support recursive models out of the box, so a certain level of remodeling is necessary to use the data in a recursive table. There are several ways to remodel a recursive table to be used in Business Intelligence applications. In this blog post, I will show you some of the models I have used.

Flattening Recursive Table Model

This is probably the most popular model and the suggested approach by MicroStrategy. In this model, you flatten the recursive table and create dimension tables at each level, and will have foreign key relationship with the next higher level. In this model, the recursive table will be broken down as follows:

ragged hierarchy microstrategy

Or we can represent it similarly in just one table as follows:

flattening hierarchies data warehouse

MicroStrategy is a member of the Smartbridge Partner Alliance.

Learn More

In MicroStrategy, we can create attributes for each of these levels and provide drill paths, so we are able to drill from any parent node to anywhere in the hierarchy. This will let users visualize the hierarchy structure and provide very nice navigation.

This model is perfect if your hierarchy is fixed and not going to change much. On the other hand, if your hierarchy changes, using this model will result in making changes to both the database and reporting tool to consider newly added hierarchy levels. This will result in much less flexibility.

Higher Level Node to Leaf Node Relationship Model

In this model you will simplify the recursive structure into just one level of relationships. You will create relationships from all the nodes in the higher levels in the hierarchy tree to the leaf nodes and represent just these relationships.

So the above hierarchy will be represented as follows in this model:

Tableau parent-child hierarchy

In MicroStrategy, we can create Parent and Child attributes using Parent and Child fields, respectively. The Parent attribute can be used for rolling up values, and the child attribute will return values at the node level (only for leaf node values).

This model provides a dynamic way of representing the recursive hierarchy and at the same time providing one level of drill. However, this model does not work if you have fact values for nodes in higher levels (e.g., nodes 2 and 3) in your hierarchy tree.

Recursive Relationship Model Using Distance to Child

Another way you can remodel a recursive table is by adding a “distance” column from the parent to the child node, and nodes have relationships to themselves. The table for this type of a model would look like below:

Tableau recursive hierarchy

You can see that we will have node relationships with themselves in this model ((1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7)). We would implement this model in MicroStrategy by creating aliases for the above mentioned relationship table and the hierarchy look up tables.

Tables are represented below for implementing this model.

Relationship table

Include the above relationship table in your catalog.

power BI parent-child table

Relationship alias table

Create an alias table to the relationship table to create additional level of depth.

Hierarchy lookup table

Include a lookup table for your hierarchy descriptions. We use a single hierarchy ID to represent both parent and child. This table is used to provide descriptions for Parent1.

hierarchichal data model

Hierarchy lookup table alias1

Create an alias lookup table to separate parent and child attribute descriptions. This table is used to provide description for Child attributes.

ragged hierarchy in microstrategy

Hierarchy look up table alias2

Create an alias lookup table to provide descriptions for Child2 attributes.

Fact table

This table provides fact data.

Attributes will be created as defined below:

Tableau ragged hierarchy

Parent Attribute: Parent attribute is used for display purposes, to provide drill path and to display higher level nodes in the tree if used with distance metric (by applying a filter condition on distance).

Child Attribute: Child attribute will give values rolled up to the child element selected in the child attribute. The child attribute will have the same elements as the parent attribute (as each node has fact values); if you do not want to display the same element after drilling you can filter the elements out using distance column.

Child 2 Attribute: Child2 attribute will give data just for that one particular node.

Using this approach, you can have the dynamic nature of the recursive hierarchy, you can have drilling, and you can get rolled up values at each node level as well.

If you are wondering why I have an alias table and two child attributes, the client for which I implemented this model wanted to have a parent attribute prompt and also wanted to give the user the option to group by child (rolled up values). Without aliasing, we can get rolled up values only by using the parent attribute. And as it’s been filtered in the prompt, using the same attribute in the grouping will not help.

To manage this requirement I created an alias table and created attribute relationships as mentioned above. If you don’t have a similar requirement similar to this, you are good with just one table and the child2 attribute will replace the child attribute.

Feel free to comment or drop me an email if you have questions on any of these models. And I would also love to hear about how you have implemented recursive hierarchies in your business intelligence tool.

There’s more to explore at Smartbridge.com!

Sign up to be notified when we publish articles, news, videos and more!