Hierarchies (Parent-Child Hierarchy) in OBIEE 11G

A Parent-Child hierarchy is a hierarchy of members that all have the same type. It is a value-based hierarchy — Consists of values that define the hierarchy in a parent-child relationship and does not contain named levels. This contrasts with level-based hierarchies, where members of the same type occur only at a single level of the hierarchy.

For example, an Employee hierarchy might have no levels, but instead have names of employees who are managed by other employees. Employees can have titles, such as Vice President. Vice Presidents might report to other Vice Presidents and different Vice Presidents can be at different depths in the hierarchy.

The most common real-life occurrence of a parent-child hierarchy is an organizational reporting hierarchy chart, where the following all apply:

• Each individual in the organization is an employee.
• Each employee, apart from the top-level managers, reports to a single manager.
• The reporting hierarchy has many levels.

 These conditions illustrate the basic features that define a parent-child hierarchy, namely:

•  A Parent-Child hierarchy is based on a single logical table (for e.g., the "Employees" table)

• Each row in the table contains two identifying keys, one to identify the member itself, the other to identify the "parent" of the member (for example, Emp_ID and Mgr_ID)

 
Steps to build Parent-Child Hierarchy:

Step 1: Create a table (Ex: XW_EMPL_D) in warehouse schema as shown in above example.

Step 2: Make it available in Physical layer of repository and create aliases to construct Fact-Dimension relationship.

Step 3: Construct necessary joins in physical layer between these source aliases and drag them in to Business Model and Mapping Layer.

Step 4: Construct necessary joins in BMM layer and define 1: N relationship between Dimension and Fact Tables as shown below,

Step 5: Now create a Logical Parent-Child Dimension from PC_XW_EMPL_D by right clicking on it as shown below,

Step 6:  Specify Member Key and Parent Column as EMPL_ID & MANAGER_ID respectively as shown below and click on button Parent-Child Settings.

Step 7:  Click on button Create Parent-Child Relationship Table as shown below,

Step 8: Specify the names for DDL and DML scripts to be generated for Parent-Child relationship Table as shown below, then click on Next->

Step 9: Mention the Table name (Ex: PARENT_CHILD_TBL) for Parent-Child relationship, will be available in Physical layer and need to denote database object and Catalog information for the same as shown below, then click on Next->

Step 10: It will generate scripts for Parent-Child Relationship table and click on Finish.

Generated Scripts are:

DDL Script:

CREATE TABLE PARENT_CHILD_TBL ( MEMBER_KEY NUMBER(10,0), ANCESTOR_KEY NUMBER(10,0), DISTANCE NUMBER(10,0), IS_LEAF NUMBER(10,0) )

DML Script:

declare
v_max_depth integer;
 v_stmt varchar2(32000);
 i integer;
 begin
 select max(level) into v_max_depth
 from XW_EMPL_D
 connect by prior EMPL_ID=MANAGER_ID
 start with MANAGER_ID is null;
 v_stmt := 'insert into VMDash_Admin.PARENT_CHILD_TBL (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)' || chr(10)
 || 'select EMPL_ID as member_key, null, null, 0 from XW_EMPL_D where MANAGER_ID is null' || chr(10)
 || 'union all' || chr(10)
 || 'select' || chr(10)
 || '  member_key,' || chr(10)
 || '  replace(replace(ancestor_key, ''\p'', ''|''), ''\'', ''\'') as ancestor_key,' || chr(10)
 || '  case when depth is null then 0' || chr(10)
 || '  else max(depth) over (partition by member_key) - depth + 1' || chr(10)
 || '  end as distance,' || chr(10)
 || '  is_leaf' || chr(10)
 || 'from' || chr(10)
 || '(' || chr(10)
 || '  select' || chr(10)
 || '    member_key,' || chr(10)
 || '    depth,' || chr(10)
 || '    case' || chr(10)
 || '      when depth is null then '''' || member_key' || chr(10)
 || '      when instr(hier_path, ''|'', 1, depth + 1) = 0 then null' || chr(10)
 || '      else substr(hier_path, instr(hier_path, ''|'', 1, depth) + 1, instr(hier_path, ''|'', 1, depth + 1) - instr(hier_path, ''|'', 1, depth) - 1)' || chr(10)
 || '    end ancestor_key,' || chr(10)
 || '    is_leaf' || chr(10)
 || '  from' || chr(10)
 || '    (' || chr(10)
 || '      select EMPL_ID as member_key, MANAGER_ID as ancestor_key, sys_connect_by_path(replace(replace(EMPL_ID, ''\'', ''\''), ''|'', ''\p''), ''|'') as hier_path,' || chr(10)
 || '        case when EMPL_ID in (select MANAGER_ID from XW_EMPL_D ) then 0 else 1 end as IS_LEAF' || chr(10)
 || '      from XW_EMPL_D ' || chr(10)
 || '      connect by prior EMPL_ID = MANAGER_ID ' || chr(10)
 || '      start with MANAGER_ID is null' || chr(10)
 || '    ),' || chr(10)
 || '    (' || chr(10)
 || '      select null as depth from dual' || chr(10);
 for i in 1..v_max_depth - 1 loop
 v_stmt := v_stmt || '      union all select ' || i || ' from dual' || chr(10);
 end loop;
 v_stmt := v_stmt || '    )' || chr(10)
 || ')' || chr(10)
 || 'where ancestor_key is not null' || chr(10);
 execute immediate v_stmt;
 end;
 /

Note: Execute these generated scripts in warehouse schema.

Step 11: Drag these Fact and Dimension Tables to Presentation Layer to make them available for Queries.

Step 12: Build a sample Analysis with the Hierarchical Column and it gives results as shown below,

Changes appear in repository are:

     1. In Physical Layer, New table PARENT_CHILD_TBL is available in specified path.

     2. Dimensional Hierarchy is available in Business Model and Mapping Layer.

  Below is the screen shot, which shows the Settings of Parent-Child Hierarchy,

 

Here it considers Member Key as MEMBER_KEY, Parent Key as ANCESTOR_KEY, Relationship Distance as Distance and Leaf Node Identifier as IS_LEAF (to identify leaf nodes).  Above generated Scripts will be identifying these keys and order the hierarchy.

x---------------------x------------------------x