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:
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:
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.
Include the above relationship table in your catalog.
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.
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.
Hierarchy look up table alias2
Create an alias lookup table to provide descriptions for Child2 attributes.
This table provides fact data.
Attributes will be created as defined below:
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.