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