Forum Stats

  • 3,853,796 Users
  • 2,264,272 Discussions
  • 7,905,448 Comments

Discussions

OBIEE; Parent-child level 0 members in analysis

Hi

We are suing OBIEE 12.2.1.4 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

Johan

Tagged:

Answers

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,675 Gold Crown

    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.

    Totsiens!

  • Johan Hammes-92061
    Johan Hammes-92061 Member Posts: 14 Green Ribbon

    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

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,675 Gold Crown

    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?

  • Johan Hammes-92061
    Johan Hammes-92061 Member Posts: 14 Green Ribbon

    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:


    Case

    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

    Etc.

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,675 Gold Crown

    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.