Analytical Need
With a Parent – Child hierarchy that has 3 levels, there must be a series of dependent filters for all levels individually but, affects the other levels below it too.
Business defined hierarchy
Level I - Line of Business (LOB) { CL11, CL3, CL9 }
Level II - Sub LOB { CL1, CL8, CL10, CL12, CL6, CL7 }
Level III – Platform { CL2, CL5 }
We need to have 1 dashboard filter for each level, so a total of 3 filters (dependent).
Example:
If you select “CL12” from Level II (sub LOB) -> it filters transactions of classes CL12, CL2 and CL5
If you select “CL9” from Level I (LOB) -> it filters transactions of classes CL9, CL10, CL12, CL2, CL5, CL6 and CL7
Modeling Challenge
Flattening the Parent Child Hierarchy in the Elasticube, keeping in mind to create & effect filters on the three levels, hierarchically.
In the Dimension table, the Class ID is the column that contains all the Levels of the hierarchy in the same column and each Class ID has a Parent ID next associated to it. (If there is no Parent ID, then it means that is Level I – topmost level). This Class ID is the key for this dimension table.
In the Fact table, there is only one column for the Class ID which has members from all 3 levels and which will connect with the Fact Table.
Solution
Step I – Create a copy of the Dim table – Backup Table
We need to create a copy of the Dim table with only Class ID and Parent ID pair for all classes – For Look-Up purposes (Please note that, we could use the same Dim table for looking up. But, for better understanding of this use case, I have decided to duplicate the Dim table for look-up purposes)
Step II – Identifying Hierarchy levels in Dim Class
For each Class ID in the DIM table, we need to identify which levels it belongs to – LOB, sub LOB or Platform. So, to do that, using Class ID and Parent ID, let’s do a quick background check to find the Grand Parent ID! Using Look-Up function, look what is the Parent ID in the Backup table, of the Parent ID in the Dim table.
Create a Level Flag to identify which level each Class ID belongs to. The logic to create the flags is:
- Parent ID null and GrandParent ID null: Level 1/LOB
- Parent ID not null and GrandParent ID null: Level 2/sub LOB
- Parent ID not null and GrandParent ID not null: Level 3/Platform
Step III – Create custom columns for filtering
We need to assign each Class ID with LOB, Sub LOB and Platform it is associated with.
Example:
let us consider CL 10 & CL 5.
Since CL 10 is a Sub LOB, LOB : CL 9 ; Sub LOB : CL 10 ; Platform : null
Since CL 5 is a Platform, LOB : CL 9 ; Sub LOB : CL 12 ; Platform : CL 5
This assignment is based on the Level Flag of the Class ID:
If the flag ID is:
LOB – LOB = Class ID | Sub LOB = null | Platform = null
Sub LOB – LOB = Parent ID | Sub LOB = Class ID | Platform = null
Platform – LOB = GrandParent ID | Sub LOB = Parent ID | Platform = Class ID
As a part of the last step, join the Class Dimension table and the Fact table using the Class ID as the key and perform a build.
Creating Filters
Create three dependent filters – LOB, sub-LOB and Platforms using the dedicated columns created for each of them.
