cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

mceclip0.png 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
mceclip1.png
 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:  
mceclip3.png
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

mceclip4.png 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.
mceclip5.png
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.
mceclip6.png

mceclip7.png 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)
mceclip8.png
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.
mceclip9.png
Create a Level Flag to identify which level each Class ID belongs to. The logic to create the flags is:
  1. Parent ID null and GrandParent ID null: Level 1/LOB
  2. Parent ID not null and GrandParent ID null: Level 2/sub LOB
  3. Parent ID not null and GrandParent ID not null: Level 3/Platform
mceclip16.png
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.
mceclip12.png
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
mceclip13.png
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.
mceclip14.png
Creating Filters
Create three dependent filters – LOB, sub-LOB and Platforms using the dedicated columns created for each of them.
mceclip15.png
Rate this article:
Version history
Last update:
‎03-02-2023 08:28 AM
Updated by:
Contributors