Passing parameters with Hierarchical Col in Report

OBIEE notes :

• The create prompted link menu item provides a good example of how to pass parameters to a dashboard page
• This URL can be embedded within an analysis to pass parameters between hierarchical summary report and a detail report without hierarchy. Action links fail with Javascript error on reports with hierarchical columns. 

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)

Parent Child Hierarchies–Multiple Roll Ups

 

Is it possible to roll up a member to multiple ancestors in a parent child hierarchy?

I have the following two hierarchies in the same table:

Management hierarchy:

• bob
  • jill
    • phil
    • pete

Sales hierarchy:

• barbara
  • jenny
    • pete
    • pat

Modelled in the RPD

And shown in Answers

As can be seen, pete rolls up to two different ancestors: bob and barbara.

Using a standard OBIEE parent child hierarchy, however, the dipslay control only shows pete once when both hierarchies are shown.

If each individual hierarchy is shown, then pete appears under both.

Interestingly, the underlying SQL is rolling up pete twice correctly.

 

In conclusion, it appears that the front-end control does not like multiple roll ups.

The problem occurs even if you using a session variable to choose one of the underlying hierarchies.

Notes on parent child tables:

No join needed for map