OBIEE 11g New features : Working with Hierarchical Table

Hierarchical table feature is a new addition to OBIEE 11g.
A hierarchy can be created when a parent child table exists. Consider an Employee and manager table with the following hierarchy.
 

To create an hierarchy out of this parent child table we follow the steps as detailed below.

Step-1

We create a table in the warehouse schema with the following script:

CREATE TABLE XW_EMPL_D(EMPL_ID NUMBER,EMPL_NAME VARCHAR2(100),MANAGER_ID NUMBER,SALARY NUMBER);

INSERT INTO XW_EMPL_D VALUES(1,'Andy',NULL,25000);

INSERT INTO XW_EMPL_D VALUES(2,'Raj',1,18550);

INSERT INTO XW_EMPL_D VALUES(3,'Kumar',1,19000);

INSERT INTO XW_EMPL_D VALUES(4,'Gomez',3,16000);

INSERT INTO XW_EMPL_D VALUES(5,'Puyol',3,15550);

INSERT INTO XW_EMPL_D VALUES(6,'Ronald',5,15875);

INSERT INTO XW_EMPL_D VALUES(8,'Borges',6,8000);

INSERT INTO XW_EMPL_D VALUES(9,'Mark',6,9500);

Step-2

 Import the table XW_EMPL_D and create an alias in physical layer of OBIEE  with the name Hier_Employee as shown in the figure above.

Step-3

Create a new Business model called ValueHierarchy. 

Step-4

 Drag the physical table Hier_Employees table twice and rename one as Hier_Employees_Salary

Step-5

Define 1:N relationship between Hier_Employees and Hier_Employees_Salary as shown above in BMM only.
 

Step-6

Now create a Logical Parent-Child Dimension from Hier_Employees by right clicking on it.

Step-7

Verify that Member Key and Parent Key are EMPL_ID and Manager_ID respectively.

Step-8

Step-9

Step-10

Step-11

Provide name of the hierarchy table and data source/schema details and click next.
 

Step-12

The DML and DDL script for the hierarchy table can be viewed by clicking the ViewScript Button. Encircled.  Say Finish.

 These scripts need to be executed back in the Datawarehouse schema so that the hierarchical stable is created and populated.

DDL:
CREATE TABLE HIERARCHY_TABLE ( MEMBER_KEY DOUBLE PRECISION, ANCESTOR_KEY DOUBLE PRECISION, DISTANCE NUMBER(10,0), IS_LEAF NUMBER(10,0) )
 

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 HIERARCHY_TABLE (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;

 /

Step-13

This is the final review window where the Member Key, Parent Key, Relationship Distance and Leaf Node Identifier are shown and it is pre-populated. Say OK.
 

Step-14

Verify that the dimension is created in the BMM layer

Step-15

Verify that a Hierarchy table is also created in the physical layer. Note the icon is different for this hierarchical table.
 

Step-16

Drag the 2 tables to the presentation Layer.

Step-17

Create a report using thehierarchy column along with Salary and Employee name. The report looks like the one above. One can browse the hierarchy tree using this feature.