Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Federating Essbase & Relational with Parent Child Hierarchy in OBIEE
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
Answers
-
Ok, I'll bite - you are an...no not really; it's a very valid question :-)
If you want to federate then you will need something that's comparable between the two sources or - and I guess that isn't really an option - you just don't use that dimension in your federated model.
So the Management Unit dimension has to fit. Otherwise OBIEE can't do the in-memory stitch join between the two data sets. And since I guess the Essbase cube is even more set in stone than the relational one I'd say the "easiest" but also "hackiest" option is to write an opaque view in the RPD which flattens your parent-child table.
The more proper way of course would be having your Management Units dimension exist in both incarnations physically: parent-child and level-based. For purely relational analyses you'd use the former and for federation the latter.
Not a solution per se but about the best you can expect for a quick win.
0 -
So we just discussed this with @Gianni Ceresa in our OBI/Essbase chat channel and he remembered this:
So depending on what you have in the cube this may actually be an idea worth looking at.
0 -
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 ...
0 -
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.
0 -
