Parent-Child Hierarchy

Consists of values that define the hierarchy in a parent-child relationship and does not contain named levels. For example, an Employee hierarchy might have no levels, but instead have names of employees who are managed by other employees.
A parent-child hierarchy is a hierarchy of members that all have the same type. This contrasts with level-based hierarchies, where members of the same type occur only at a single level of 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.
In relational tables, the relationships between different members in a parent-child hierarchy are implicitly defined by the identifier key values in the associated base table. However, for each Oracle BI Server parent-child hierarchy defined on a relational table, you must also explicitly define the inter-member relationships in a separate parent-child relationship table.
Let us start with Parent-Child Hierarchy and see how to implement it .
Am taking Employee table for work around .
Employee table DDL and Data(For Oracle DB) :
CREATE TABLE “EMP”
(    “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10 BYTE),
“JOB” VARCHAR2(9 BYTE),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0)
);
REM INSERTING into EMP
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,’SMITH’,’CLERK’,7902,to_date(’17-DEC-80′,’DD-MON-RR’),2450,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,’CLARK’,’MANAGER’,7839,to_date(’09-JUN-81′,’DD-MON-RR’),2450,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,’FORD’,’ANALYST’,7566,to_date(’03-DEC-81′,’DD-MON-RR’),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,’JAMES’,’CLERK’,7698,to_date(’03-DEC-81′,’DD-MON-RR’),950,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,’KING’,’PRESIDENT’,null,to_date(’17-NOV-81′,’DD-MON-RR’),5000,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,’JONES’,’MANAGER’,7839,to_date(’02-APR-81′,’DD-MON-RR’),2975,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,’BLAKE’,’MANAGER’,7839,to_date(’01-MAY-81′,’DD-MON-RR’),2850,null,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,’MILLER’,’CLERK’,7782,to_date(’23-JAN-82′,’DD-MON-RR’),1300,null,10);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,’SCOTT’,’ANALYST’,7566,to_date(’19-APR-87′,’DD-MON-RR’),3000,null,20);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,’MARTIN’,’SALESMAN’,7698,to_date(’28-SEP-81′,’DD-MON-RR’),1250,1400,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,’WARD’,’SALESMAN’,7698,to_date(’22-FEB-81′,’DD-MON-RR’),1250,500,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,’ALLEN’,’SALESMAN’,7698,to_date(’20-FEB-81′,’DD-MON-RR’),1600,300,30);
Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,’TURNER’,’SALESMAN’,7698,to_date(’08-SEP-81′,’DD-MON-RR’),1500,0,30);
commit;
Step1 : Create a blank repository and import the Employee table in to Physical Layer .

Step2: Create Alias on EMPLOYEE table 1.’EmployeeDim’ and 2.’SalaryFact’ and give physical join between EmployeeDim to SalaryFact .


Step3: Drag the tables in to BMM layer and give aggregations for the fact columns.

Step4: Now right click on Employees logical table and choose for new parent child hierarchy .

Step5: Choose the member key (by default it will take the primary key . Here Employee Number) and parent column as shown in the below screenshot.

Step6: Click on ‘parent- child settings’ .This is the place where we are going to generate the DDL & DML scripts which we can use to create and populate the hierarchy table which will be used by BI server to report parent child hierarchies. Here click on ‘Create Parent-Child Relationship Table’ .

Step7: Enter the DDL&DML script names and click Next .


Step7: Give name for the Parent Child hierarchy table and Click Next .

Step8: You can see both DDL and Script to populate data here .

Click Finish .
You can see the screen as follows .
Click Ok again Ok .
After finishing the wizard you can see the HierarchyTable got imported automatically.
Right click on the EMPLOYEE_HIERARCHY table –> click on update rowcount and observe that you will get ‘Table does not exist error’
Step9:Go to the path <beahome>\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository. Run the scripts ‘EMPLOYEE_PARENT_CHILD_DDL.sql’ and ‘EMPLOYEE_PARENT_CHILD_DATA.sql’ .(My case I used SQL Developer to Run this scripts)
DDL :
CREATE TABLE EMPLOYEE_HIERARCHY ( MEMBER_KEY DOUBLE PRECISION, ANCESTOR_KEY DOUBLE PRECISION, DISTANCE NUMBER(10,0), IS_LEAF NUMBER(10,0) );
Script  For polulate Data :
declare
v_max_depth integer;
v_stmt varchar2(32000);
i integer;
begin
select max(level) into v_max_depth
from EMPLOYEE
connect by prior EMP_NO=MANAGER_ID
start with MANAGER_ID is null;
v_stmt := ‘insert into LINEAGE.EMPLOYEE_HIERARCHY (MEMBER_KEY, ANCESTOR_KEY, DISTANCE, IS_LEAF)’ || chr(10)
|| ‘select EMP_NO as member_key, null, null, 0 from EMPLOYEE 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 EMP_NO as member_key, MANAGER_ID as ancestor_key, sys_connect_by_path(replace(replace(EMP_NO, ”\”, ”\”), ”|”, ”\p”), ”|”) as hier_path,’ || chr(10)
|| ‘        case when EMP_NO in (select MANAGER_ID from EMPLOYEE ) then 0 else 1 end as IS_LEAF’ || chr(10)
|| ‘      from EMPLOYEE ‘ || chr(10)
|| ‘      connect by prior EMP_NO = 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;
/
Click on COMMIT to commit changes .
Right click on the EMPLOYEE_HIERARCHY table –> click on update rowcount and observe that you will not  get ‘Table does not exist error’ .
Step10: Pull the ‘ParentChildHierarchy’  into Presentation Layer ,Check Consistency and save the repository .Now we are ready to Build reports .


Step11: Go to answers create analysis including Employee Hierarchy