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

    Parent Child hierarchy Scenario

    Saro
      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