Parent Child Hierarchies– OBIEE Wizard Script

 

As per the OBIEE documentation, the parent-child table requires the following columns -

• member_key
• ancestor_key
• distance
• is_leaf

We will work with the following very simple employee hierarchy

• bob
• jill
• pete
• phil

This is represented in the table EMPS as follows

The parent child map table EMPS_H, created via the OBIEE wizard, has the following rows in it

Ten rows have been created. (ignore hierarchy col)

What has the script done?

In effect, it has created a link between each member and each of its ancestor, while calculating the distance between member and ancestor.

For our example, then:

• bob has two rows, one identifying him as a root node (ancestor_key is null) and another self-referential row
• jill has two rows, one link to only ancestor bob, and self-referential
• pete has three rows, two links to ancestors bob and jill, and self-referntial
• phill has three rows, two links to ancestors bob and jill, and self-referential

So in effect, for each member, there will N rows created, where N = 1 + number of ancestors. The exception is top level members, these will always have two rows, one for self-referential, and one link to a null ancestor (this is how OBIEE identifies top-level nodes).

So we can calculate the number of rows as follows

• Bob =  top-level member = 2 rows
• Jill = 1 + 1 ancestor = 2 rows
• Bob = 1 + 2 ancestors = 3 rows
• Pete = 1 + 2 ancestors = 3 rows

Total = 10 rows

The script actually performs the following steps

• Issue a hierarchical subquery (CONNECT BY PRIOR) => returns 4 rows
• Issue a levels subquery => returns 3 rows (levels: null, 1, and 2)
• Cross join hierarchial results with levels results and set ancestors => 3*4 = 12 rows
• Set distance, and prune null ancestors => remove 3 rows = 9 rows
• Add row for each top-level member => add 1 row => 10 rows

 

 

Can a single simple CONNECT BY PRIOR SQL be used instead?

• No because a link and the distance between each member and ancestor is needed
• CONNECT BY PRIOR  can start at the bottom “mgr = prior emp” or start at the top “emp = prior mgr” but in both cases do not get the ancestor and distance cols as needed for OBIEE. (and number of rows is different for top-down)