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

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:

Community_Admin_0-1634724811074.jpeg

Figure 1. Data structure

We'll create the 4 levels as new custom fields like so:

Community_Admin_1-1634724811153.jpeg

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

 

Version history
Last update:
‎10-20-2021 03:14 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request