4 Replies Latest reply on Nov 25, 2012 10:33 AM by Saro

    Parent Child hierarchy Scenario

      Hi friends,

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


      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
      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
      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


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