Tuesday, October 22, 2013

[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












1 comment:

  1. Hi,The company must first be authorised by the Ministry of Economy and Commerce with Business setup in Qatar, and this is done when the Ministry deems the company to be participating in a project which facilitates the development of a public service or utility.Thanks....

    ReplyDelete