Im looking forward to implement the same result in my BI with a parent-child hierarchy.
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
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.
per_all_people_f------------Dimension per_all_assignments_f-----Fact per_jobs---------------------Dimension
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.
For Ancestorkey-->Managerid Memberkey------->Employeeid