Analytical Need
In many cases a hierarchy appears in the data structured as 2 levels - A parent and a child. The business user would like to see the entire hierarchy and analyze the data according to the different levels, but can't do that since they don't exist in the data yet.
Modeling Challenge
There is no option to perform loops in SQL. Also, we would like to avoid performing too many join operations which take a heavy toll on the machine.
Solution
We'll assume we have a finite number of hierarchies - in our example, we'll use 4 levels (this can be expanded to more levels, we'll discuss this later on).
We'll bring in the same table again to the ElastiCube and use it as a source for our lookups.
The source data looks like this:
Figure 1. Data structure
We'll create the 4 levels as new custom fields like so:
Figure 2. 4 new custom fields and the data_backup table (source for lookups)
This is the definition for the 4 fields:
- Level4 : Campaign field - this the lowest level and just for convenience purposes, we have duplicated it
- Level3 : ParentCampaign field
- Level2 :
(SQL)
lookup(data_backup, ParentCampaign,ParentCampaign,Campaign)
we'll bring in the ParentCampaign from the backup table based on who is the parent for our parent (2 levels up from Campaign)
- Level1 :
(SQL)
lookup(data_backup, ParentCampaign, level2, Campaign)
Same logic as in Level2. We bring in the parent of Level2.
We would also want to define which rows contain the full 4 levels hierarchy - since the lookups will complete all 4 values only for the campaigns that have 4 levels. That is why we'll add a flag that marks this.
The code for this will be :
(SQL)
CASE WHEN IsNullOrEmpty(Level4)=FALSE AND IsNullOrEmpty(Level3)=FALSE AND
IsNullOrEmpty(Level2)=FALSE AND IsNullOrEmpty(Level1)=FALSE THEN 1 ELSE 0 end
If needed, you can always have a custom query to extract just the rows with the flag=1 and use that for the hierarchy.
In case you have wondered why there is an agent field in the data, this is an example where you can do the same for agents. You can have 4 new columns for an agent (1 agent per level).
If you need more levels, you can add more custom fields (more lookups). The first 2 will be the same as now.
- 427 KB - Hierarchy.ecdata
