Oracle Business Intelligence

Products Banner

OBIEE; Parent-child level 0 members in analysis

Received Response


We are suing OBIEE and we are sourcing data from Essbase.

I want to query all level 0 members in an analysis and bring back the parents in one column and the level 0 member in another column.

I can filter on the level 0 members using selection steps but splitting it into parent in one column and level 0 member name in another column is my challenge.

I presume I need to do this with MDX code. We prefer not include extra columns in the repository as it is only a requirement for one analysis.

Thank you




  • Goeie more Johan,

    Could you say a bit more about the issue with "splitting it into parent"?

    Level 0 are leaf members and hence the parent should be level 1 (logically speaking). Is your hierarchy and unbalanced and / or ragged one and your leafs are in different generations of the cube's hierarchy?

    If so, then, yes, MDX is the way to go since the RPD abstracts by generations, not levels.


  • Hi Christian

    Here is an example:

    Essbase outline:

    XYZ (Parent)

    • Child 1 (Child of XYZ)
    • Child 2 (Child of XYZ)
    • Child 3 (Child of XYZ)

    OBIEE output required:

    Parent Child

    XYZ Child1

    XYZ Child2

    XYZ Child3

  • Ok so we're back to the "level" vs "generation" topic with Essbase as hinted to with my initial question of ragged/unbalanced hierarchies.

    If your level 0 members are all in generation 5 of a given hierarchy, then for the parent you just use gen 4 and you're done.

    So the question I asked remains: Are your hierarchies unbalanced or ragged? Is that why you think you need MDX? Isn't it possible to exactly match your "parent" to any of the columns that are created automatically when you drag a cube into the RPD?

  • The hierarchies are unbalanced. I therefore cannot use a particular generation column to determine on what generation the lowest member (level 0) is. I used a case statemen as follows to determine the lowest level:


    When Gen6 is null and Gen5 is not null then Gen5

    When Gen7 is null and Gen6 is not null then Gen6

    When Gen8 is null and Gen7 is not null then Gen7


  • Implementing that logic is a bit touchy as it costs performance for each and every query.

    In the end you're most likely best off with MDX.