Federating Essbase & Relational with Parent Child Hierarchy in OBIEE

Question

Does anybody have experience of federating an Essbase and Relational source in OBIEE (currently on 12C but hopefully it's not a version specific thing)?

We have an enterprise Essbase cube with a Management Unit dimension 10 generations deep which is ragged and skipped level; in the relational database that we need to federate with, we have resolved this with a parent-child dimension and associated closure table. I have not seen anywhere how we can federate without flattening out the relational source (we have a tight refresh deadline on the relational database and it changes quite frequently, especially at month end) so avoiding the added ETL complexity of flattening the dimension would be ideal if it can be avoided.

Any assistance would be greatly appreciated (I'm new to Essbase and OBIEE so I'll even appreciate being told I'm an idiot...)

OBIEE version 12C

Essbase version 11g

Relational source Amazon Redshift

[Deleted User] · Answer

It's one of the Community Ideas that's being pushed around for a while now but there are many detractors to the whole "more than one valid answer" concept.

3259977 · Answer

Also wanted to mark this as a correct answer (they should really allow that, there can be more than one answer to a question)!

Thank you both for your responses - took a couple of days for me to get things moving and validate the approach but it's looking like we have the answer now.

Gianni Ceresa · Answer

So the idea of having both as level based hierarchy as Christian said, using an opaque view if you can't do it with a view / materialized view / ETL in the DB, is probably easier as it will also answer your other point about filters: Report w/Parent Child Hierarchy not filtering

But if you want to stay on parent-child you can switch a dimension of your Essbase cube in OBIEE to be parent-child too and that's what you see in the above link.

Parent-child are both good and bad: positive thing is when you don't know / can't know the number of levels/generations. But you lose the concept of levels and the physical representation of columns and filters (vs selection steps) which can be a bit confusing ...