OBIEE; Parent-child level 0 members in analysis

Johan Hammes-92061

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

[Deleted User]

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

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

[Deleted User]

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

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.

[Deleted User]

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.