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
