Hi friends,

Im just working on the parent child hierarchy using the following link with the sample data.

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi11115/biadmin11g_02/biadmin11g.htm_

I tried to implement the same hierarchy using my local data instead of referring to the sample data.

I have a below query which returns the employee with manager along with the employee position

select distinct papf.person_id,  papf.full_name "Employee Name", supf.person_id "Manager Id", supf.full_name "Manager Name", pj.name "Position Name"
from per_all_people_f papf, per_all_assignments_f paaf, per_all_people_f supf, per_jobs pj
where papf.person_id = paaf.person_id and supf.person_id = paaf.supervisor_id and paaf.job_id = pj.job_id
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date and 
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date

Im looking forward to implement the same result in my BI with a parent-child hierarchy.

Since i have imported three tables to my physical layer

per_all_people_f------------Dimension
per_all_assignments_f-----Fact
per_jobs---------------------Dimension

For creating parent-child in BI, we need to have a separate Parent child table which consist of four columns like ancestorkey, memberkey, distance, leaf.

From the above column i can understand the meaning like

For Ancestorkey-->Managerid
Memberkey------->Employeeid

But i couldnt get with the meaning for distance column as the meaning suggest like a distance b/w the two and leaf column as the meaning suggest like a leaf member. which leaf member does it refer.

I also referred the below link then too couldnt get the meaning for it

http://www.rittmanmead.com/2010/08/oracle-bi-ee-11g-parent-child-hierarchies-differing-aggregations/+

How i can form the parent child table for the BI from my above three tables of HRMS.

Thanks

Regards,
Saro