[OBIEE 11g] HIERARCHY

 

HIERARCHY

There 2 types of Hierarchy in OBIEE 11g namely:

 1.    Level-Based Hierarchy 

 2.    Parent-Child Hierarchy (or) Value Based Hierarchy

LEVEL – BASED HIERARCHY

In Level-Based Hierarchy,Again they are divided into 2 Types namely: 

1.    Ragged Hierarchy 

2.    Skip Level Hierarchy

RAGGED HIERARCHY

A hierarchy in which all the lowest-level members do not have the same depth

(or)

A Ragged hierarchy is a hierarchy which has unbalanced child attributes

SKIP LEVEL HIERARCHY

A hierarchy in which certain members do not have values for certain higher levels

(or)

A Skipped hierarchy is a hierarchy in which some child doesn't have a direct parent attribute i.e., some attributes missing at some level.

Create a Alias for Countries1 and name it as Countries1_fact

Now Apply Physical joins in the Physical layer

  

Now RightClick and apply ChildLevel

 

Double Click the countries1_Dim,it opens a DialogBox in that

Now click Ragged and Skipped levels

 

 

Parent-Child Hierarchy 

It consists of values that define the hierarchy in a parent-child relationship and does not contain named levels.

Now I am taking Employee Table. The table will be present in hr Schema (Oracle Database)

Click Next Button to proceed.

Now select the Employee table 

Now

Create an Alias and name it as  Employee_Dim and Employee_Fact

Apply  Physical Joins between 2 tables  in the Physical Layer 

Now Browse the Parent Column

Click on the Parent-Child Setting 

Click the Create Parent-Child Relation Table.

Herecreate the Name in DDl Script 

Now Write  Name in the textfield.This is nothing but Table Name in the Database

Script for Creating the Parent_Child_ Relation  Table : 

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

Script For Inserting the Data to Parent_Child_Relation Table :

declare

v_max_depth integer;

 v_stmt varchar2(32000);

 i integer;

 begin

 select max(level) into v_max_depth

 from EMPLOYEES

 connect by prior EMPLOYEE_ID=MANAGER_ID

 start with MANAGER_ID is null;

 v_stmt := 'insert into HR.Parent_Child_Relation (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)' || chr(10)

 || 'select EMPLOYEE_ID as member_key, null, null, 0 from EMPLOYEES 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 EMPLOYEE_ID as member_key, MANAGER_ID as ancestor_key, sys_connect_by_path(replace(replace(EMPLOYEE_ID, ''\'', ''\''), ''|'', ''\p''), ''|'') as hier_path,' || chr(10)

 || '        case when EMPLOYEE_ID in (select MANAGER_ID from EMPLOYEES ) then 0 else 1 end as IS_LEAF' || chr(10)

 || '      from EMPLOYEES ' || chr(10)

 || '      connect by prior EMPLOYEE_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;

 /

   Description: 

   Member Key Means ---- Primary Key in the Table

   Parent Key -----Top Most Column Id for Manager

  

   

    Now RightClick and apply UpdateRowCount, it won’t display for that you have to  run the script in the database

 

Copy both the Scripts and run in the Databse.

 

 Now Deploy in the Weblogic