Tuesday, January 24, 2012

Create Hierarchies - Database Side

Hierarchical data in transaction databases usually comes with parent-child relationship. For example manager-employee or brand product.




Primary KeyEmployee IDEmployee NameManager ID
110John Doe20
220Jane Doe30

Transactional Table - ttable


The goal is to turn this information into useful level base hierarchy that OBIEE can used for drilling up/down.


 It this case drilling up:


row_widEmployeeLevel 2Level 3Level 4Level 5Level 6
1John DoeJane DoeJay SmithJoane SmithJack DoeJack Doe
2Jane DoeJay SmithJoane SmithJack DoeJack DoeJack Doe

Hierarchical Table - htable.



This is a common transformation. So there should be nice, fast ways to achieve it. There are some solutions:

  • multiple joins - one join between each two levels
    SELECT t1.Employee_NAME as employee,
                   t2.Employee_NAME level 2,
                   t3.Employee_NAME level 3 .........
    FROM ( SELECT Employee_Name, Manager_ID FROM ttable) t1,
               ( SELECT Employee_Name FROM ttable WHERE t1.Manager_ID = ttable.Employee_ID ) t2,
    ( SELECT Employee_Name FROM ttable WHERE t2.Manager_ID = ttable.Employee_ID ) t3,
    ..........................................

  • In Oracle database this can be achieved with connect by and system_connect_path

    The advantage here is that system_connect_path will give the entire path of the hierarchy. In same cases this might be very useful.

  • Order the levels and the pivot the table

    If the levels can be queried ordered(the lowest level first or last), then if the result is pivoted the hierarchical table will be created.
     
  • With loop in pl/sql or java.

    I would try to avoid that method unless it is completely necessary. For example if there are a lot of considerations involved in creating the hierarchy.
  • Parent-Child hierarchies in obiee

Links:



No comments:

Post a Comment