Hierarchical data in transaction databases usually comes with parent-child relationship. For example manager-employee or brand product.
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:
Hierarchical Table - htable.
This is a common transformation. So there should be nice, fast ways to achieve it. There are some solutions:
Links:
Primary Key | Employee ID | Employee Name | Manager ID |
1 | 10 | John Doe | 20 |
2 | 20 | Jane Doe | 30 |
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_wid | Employee | Level 2 | Level 3 | Level 4 | Level 5 | Level 6 |
1 | John Doe | Jane Doe | Jay Smith | Joane Smith | Jack Doe | Jack Doe |
2 | Jane Doe | Jay Smith | Joane Smith | Jack Doe | Jack Doe | Jack 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