1. Building Semantic Models in Oracle Analytics Cloud
  2. Reference
  3. Miscellaneous Reference Information
  4. Manually Create Relationship Tables for Parent-Child Hierarchies Using Scripts

Manually Create Relationship Tables for Parent-Child Hierarchies Using Scripts

Use the script examples provided in this topic to manually create relationship tables for parent-child hierarchies. When run, the scripts generate and load the relationship tables required for your parent-child hierarchies.

Note:

Oracle recommends that you use the Generate Relationship Table functionality to create the scripts. See Generate Scripts to Create a Parent-Child Relationship Table.
The key elements that you must define for a parent-child hierarchy are the identifier columns for the member and the parent of the member. The following needs to be replaced with Member Key column, Parent Key column and the desired relationship table name before executing the script.
  • ${SOURCE_TABLE_NAME} — Source table with Parent Child data. For example, an Employee or Employee dimension table.
  • ${SOURCE_MEMBER_KEY_COL_NAME} — Key column in the source table. For example, Employee_Key in Employee table.
  • ${SOURCE_PARENT_KEY_COL_NAME} — Parent Key column in the source table. For example, Manager_Key in Employee table.
  • ${RELATIONSHIP_TABLE_NAME} — Parent-child relationship table for parent-child hierarchies based on relational tables.
  • ${MEMBER_COLUMN_DATA_TYPE} — Data type of member key column
  • ${PARENT_COLUMN_DATA_TYPE} — Data type of parent key column. The same data type as member key column.

Oracle Database

Example 20-1 Create Relationship Table Script

CREATE TABLE ${RELATIONSHIP_TABLE_NAME} (MEMBER_KEY ${MEMBER_COLUMN_DATA_TYPE} , ANCESTOR_KEY ${PARENT_COLUMN_DATA_TYPE}, DISTANCE NUMBER(10,0), IS_LEAF NUMBER(10,0) );

Example 20-2 Load Relationship Table Script


For details declare 

v_max_depth integer;

v_stmt varchar2(32000);

i integer;

begin

select max(level) into v_max_depth

from ${SOURCE_TABLE_NAME}

connect by prior ${SOURCE_MEMBER_KEY_COL_NAME}= ${SOURCE_PARENT_KEY_COL_NAME}

start with ${SOURCE_PARENT_KEY_COL_NAME} is null;

v_stmt := 'insert into ${PARENT_CHILD_TABLE_NAME} (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)' || chr(10) 

|| 'select ${SOURCE_MEMBER_KEY_COL_NAME}as member_key, null, null, 0 from ${SOURCE_TABLE_NAME} where ${SOURCE_PARENT_KEY_COL_NAME} 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 ${SOURCE_MEMBER_KEY_COL_NAME} as member_key, ${SOURCE_PARENT_KEY_COL_NAME} as ancestor_key, sys_connect_by_path(replace(replace(${SOURCE_MEMBER_KEY_COL_NAME}, ''\'', ''\''), ''|'', ''\p''), ''|'') as hier_path,' || chr(10) 

|| '        case when ${SOURCE_MEMBER_KEY_COL_NAME} in (select ${SOURCE_PARENT_KEY_COL_NAME} from ${SOURCE_TABLE_NAME} ) then 0 else 1 end as IS_LEAF' || chr(10) 

|| '      from ${SOURCE_TABLE_NAME} ' || chr(10) 

|| '      connect by prior ${SOURCE_MEMBER_KEY_COL_NAME} = ${SOURCE_PARENT_KEY_COL_NAME} ' || chr(10) 

|| '      start with ${SOURCE_PARENT_KEY_COL_NAME} 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; 

/

Example 20-3 Employee Table Example

declare 

v_max_depth integer;

 v_stmt varchar2(32000);

 i integer;

 begin

 select max(level) into v_max_depth

 from SAMP_EMPL_D_VH

 connect by prior Employee_Key=Mgr_Id

 start with Mgr_Id is null;

 v_stmt := 'insert into 07 - ORE Sample.<<SQLSERVER_TABLE>> (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)' || chr(10) 

 || 'select Employee_Key as member_key, null, null, 0 from SAMP_EMPL_D_VH where Mgr_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 Employee_Key as member_key, Mgr_Id as ancestor_key, sys_connect_by_path(replace(replace(Employee_Key, ''\'', ''\''), ''|'', ''\p''), ''|'') as hier_path,' || chr(10) 

 || '        case when Employee_Key in (select Mgr_Id from SAMP_EMPL_D_VH ) then 0 else 1 end as IS_LEAF' || chr(10) 

 || '      from SAMP_EMPL_D_VH ' || chr(10) 

 || '      connect by prior Employee_Key = Mgr_Id ' || chr(10) 

 || '      start with Mgr_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; 

 /

SQL Server

Example 20-4 Create Table

CREATE TABLE ${PARENT_CHILD_TABLE_NAME} ( MEMBER_KEY ${MEMBER_TYPE_DEF} , ANCESTOR_KEY ${ANCESTOR_TYPE_DEF}, DISTANCE INTEGER, IS_LEAF INTEGER )

Example 20-5 Insert Content

WITH Closure(${SOURCE_PARENT_KEY_COL_NAME}, ${SOURCE_MEMBER_KEY_COL_NAME}, Distance) as (

select directs.${SOURCE_PARENT_KEY_COL_NAME}, directs.${SOURCE_MEMBER_KEY_COL_NAME}, NULL from ${SOURCE_TABLE_NAME} directs where directs.${SOURCE_PARENT_KEY_COL_NAME} is NULL union all 

select directs.${SOURCE_MEMBER_KEY_COL_NAME} ,directs.${SOURCE_MEMBER_KEY_COL_NAME}, 0 from ${SOURCE_TABLE_NAME} directs union all 

select super.${SOURCE_PARENT_KEY_COL_NAME}, sub.${SOURCE_MEMBER_KEY_COL_NAME}, sub.Distance+1 from ${SOURCE_TABLE_NAME} super, Closure sub 

where super.${SOURCE_MEMBER_KEY_COL_NAME} = sub.${SOURCE_PARENT_KEY_COL_NAME} and super.${SOURCE_PARENT_KEY_COL_NAME} is not NULL)

Insert into ${PARENT_CHILD_TABLE_NAME} (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

select Closure.${SOURCE_MEMBER_KEY_COL_NAME} as MEMBER_KEY, Closure.${SOURCE_PARENT_KEY_COL_NAME} as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

Case WHEN temp.${SOURCE_PARENT_KEY_COL_NAME} is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct ${SOURCE_PARENT_KEY_COL_NAME} from ${SOURCE_TABLE_NAME} ) temp on Closure.${SOURCE_MEMBER_KEY_COL_NAME}= temp.${SOURCE_PARENT_KEY_COL_NAME} where Closure.${SOURCE_PARENT_KEY_COL_NAME} is NOT NULL or (Closure.${SOURCE_PARENT_KEY_COL_NAME} is NULL and Closure.Distance is NULL)

Example 20-6 Original File

WITH Closure(Mgr_Id, Employee_Key, Distance) as (

select directs.Mgr_Id, directs.Employee_Key, NULL from SAMP_EMPL_D_VH directs where directs.Mgr_Id is NULL union all 

select directs.Employee_Key ,directs.Employee_Key, 0 from SAMP_EMPL_D_VH directs union all 

 select super.Mgr_Id, sub.Employee_Key, sub.Distance+1 from SAMP_EMPL_D_VH super, Closure sub 

 where super.Employee_Key = sub.Mgr_Id and super.Mgr_Id is not NULL)

 Insert into 07 - ORE Sample.<<TABLE_NAME>> (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

  select Closure.Employee_Key as MEMBER_KEY, Closure.Mgr_Id as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

 Case WHEN temp.Mgr_Id is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct Mgr_Id from SAMP_EMPL_D_VH ) temp on Closure.Employee_Key = temp.Mgr_Id where Closure.Mgr_Id is NOT NULL or (Closure.Mgr_Id is NULL and Closure.Distance is NULL)

IBM DB2

Example 20-7 Create Table

CREATE TABLE "${PARENT_CHILD_TABLE_NAME} " ( "MEMBER_KEY" ${MEMBER_TYPE_DEF} , "ANCESTOR_KEY" ${ANCESTOR_TYPE_DEF}, "DISTANCE" INTEGER, "IS_LEAF" INTEGER )

Example 20-8 Insert Content

Insert into "${PARENT_CHILD_TABLE_NAME} " (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

WITH Closure(${SOURCE_PARENT_KEY_COL_NAME}, ${SOURCE_MEMBER_KEY_COL_NAME}, Distance) as (

select directs.${SOURCE_PARENT_KEY_COL_NAME}, directs.${SOURCE_MEMBER_KEY_COL_NAME}, NULL from ${SOURCE_TABLE_NAME} directs where directs.${SOURCE_PARENT_KEY_COL_NAME} is NULL union all 

select directs.${SOURCE_MEMBER_KEY_COL_NAME} ,directs.${SOURCE_MEMBER_KEY_COL_NAME}, 0 from ${SOURCE_TABLE_NAME} directs union all 

select super.${SOURCE_PARENT_KEY_COL_NAME}, sub.${SOURCE_MEMBER_KEY_COL_NAME}, sub.Distance+1 from ${SOURCE_TABLE_NAME} super, Closure sub 

where super.${SOURCE_MEMBER_KEY_COL_NAME} = sub.${SOURCE_PARENT_KEY_COL_NAME} and super.${SOURCE_PARENT_KEY_COL_NAME} is not NULL)

select Closure.${SOURCE_MEMBER_KEY_COL_NAME} as MEMBER_KEY, Closure.${SOURCE_PARENT_KEY_COL_NAME} as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

Case WHEN temp.${SOURCE_PARENT_KEY_COL_NAME} is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct ${SOURCE_PARENT_KEY_COL_NAME} from ${SOURCE_TABLE_NAME} ) temp on Closure.${SOURCE_MEMBER_KEY_COL_NAME} = temp.${SOURCE_PARENT_KEY_COL_NAME} where Closure.${SOURCE_PARENT_KEY_COL_NAME} is NOT NULL or (Closure.${SOURCE_PARENT_KEY_COL_NAME} is NULL and Closure.Distance is NULL)

Example 20-9 Original File

 Insert into "07 - ORE Sample"."<<CreateTable>>" (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

 WITH Closure(Mgr_Id, Employee_Key, Distance) as (

select directs.Mgr_Id, directs.Employee_Key, NULL from SAMP_EMPL_D_VH directs where directs.Mgr_Id is NULL union all 

select directs.Employee_Key ,directs.Employee_Key, 0 from SAMP_EMPL_D_VH directs union all 

 select super.Mgr_Id, sub.Employee_Key, sub.Distance+1 from SAMP_EMPL_D_VH super, Closure sub 

 where super.Employee_Key = sub.Mgr_Id and super.Mgr_Id is not NULL)

 select Closure.Employee_Key as MEMBER_KEY, Closure.Mgr_Id as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

 Case WHEN temp.Mgr_Id is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct Mgr_Id from SAMP_EMPL_D_VH ) temp on Closure.Employee_Key = temp.Mgr_Id where Closure.Mgr_Id is NOT NULL or (Closure.Mgr_Id is NULL and Closure.Distance is NULL)

Teradata

Example 20-10 Create Table

CREATE MULTISET TABLE "${PARENT_CHILD_TABLE_NAME} " ( "MEMBER_KEY" ${MEMBER_TYPE_DEF} , "ANCESTOR_KEY" ${ANCESTOR_TYPE_DEF}, "DISTANCE" INTEGER, "IS_LEAF" INTEGER )

Example 20-11 Insert Content

Insert into "${PARENT_CHILD_TABLE_NAME} " (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

WITH RECURSIVE Closure(${SOURCE_PARENT_KEY_COL_NAME}, ${SOURCE_MEMBER_KEY_COL_NAME}, Distance) as (

select directs.${SOURCE_PARENT_KEY_COL_NAME}, directs.${SOURCE_MEMBER_KEY_COL_NAME}, NULL from ${SOURCE_TABLE_NAME} directs where directs.${SOURCE_PARENT_KEY_COL_NAME} is NULL union all 

select directs.${SOURCE_MEMBER_KEY_COL_NAME} ,directs.${SOURCE_MEMBER_KEY_COL_NAME}, 0 from ${SOURCE_TABLE_NAME} directs union all 

select super.${SOURCE_PARENT_KEY_COL_NAME}, sub.${SOURCE_MEMBER_KEY_COL_NAME}, sub.Distance+1 from ${SOURCE_TABLE_NAME} super, Closure sub 

where super.${SOURCE_MEMBER_KEY_COL_NAME} = sub.${SOURCE_PARENT_KEY_COL_NAME} and super.${SOURCE_PARENT_KEY_COL_NAME} is not NULL)

select Closure.${SOURCE_MEMBER_KEY_COL_NAME} as MEMBER_KEY, Closure.${SOURCE_PARENT_KEY_COL_NAME}  as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

Case WHEN temp.${SOURCE_PARENT_KEY_COL_NAME} is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct ${SOURCE_PARENT_KEY_COL_NAME} from ${SOURCE_TABLE_NAME} ) temp on Closure.${SOURCE_MEMBER_KEY_COL_NAME} = temp.${SOURCE_PARENT_KEY_COL_NAME} where Closure.${SOURCE_PARENT_KEY_COL_NAME} is NOT NULL or (Closure.${SOURCE_PARENT_KEY_COL_NAME} is NULL and Closure.Distance is NULL)

Example 20-12 Original File

 Insert into "07 - ORE Sample"."<<CREATE_TABLE>>" (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)

 WITH RECURSIVE Closure(Mgr_Id, Employee_Key, Distance) as (

select directs.Mgr_Id, directs.Employee_Key, NULL from SAMP_EMPL_D_VH directs where directs.Mgr_Id is NULL union all 

select directs.Employee_Key ,directs.Employee_Key, 0 from SAMP_EMPL_D_VH directs union all 

 select super.Mgr_Id, sub.Employee_Key, sub.Distance+1 from SAMP_EMPL_D_VH super, Closure sub 

 where super.Employee_Key = sub.Mgr_Id and super.Mgr_Id is not NULL)

 select Closure.Employee_Key as MEMBER_KEY, Closure.Mgr_Id as ANCESTOR_KEY, Closure.Distance as DISTANCE, 

 Case WHEN temp.Mgr_Id is null then 1 else 0 end as IS_LEAF

from Closure Left Outer Join (select distinct Mgr_Id from SAMP_EMPL_D_VH ) temp on Closure.Employee_Key = temp.Mgr_Id where Closure.Mgr_Id is NOT NULL or (Closure.Mgr_Id is NULL and Closure.Distance is NULL)