Oracle Communities

Oracle Analytics and AI

Child Item

Essbase descendantas reporting using OBIEE similar to HFR

I have question about OBIEE reporting with Essbase Data source connection. We are aware of that using HFR, we can select a member and apply a function like Children, or descendants to it, so when the report open, it gets all the members underlying under the selected member.

We can even allow the users to choose member at run time and let the report display data to that member at lower level (level 0) .

Is this similar functionality possible in OBIEE? i see we have prompt where user can choose a member from dimension, however it only fetches the data what is present in result set.

How can I create a report in obiee, where users chooses a member from a dimension, and then report displays the data for all the level 0 members of it, instead just showing the data for the member selected.

Find more posts tagged with

Accepted answers

All comments

3227688

Thanks, Let me try these which option better works for me. I will have a good learning curve here.

Ideally what i want to achieve is let users choose base members for each functional area (dimension), then report has to extract data at level 0 across all those dimensions from Essbase

Robert Angel

The hierarchy is flattened as you say but that would not prevent your users from selecting parents and they would work in the normal way with descendants functionality.

Others may have had a different experience, but the thing that I found most onerous with Essbase modelling in OBIEE was the need to filter on all dimensions but one, though this was against an extremely large HPCM cube which carries its own challenges.

As to end user experience the flattened hierarchies are extremely poor particularly as there is no means to identify level0 / parent members, and the comparatively poor performance; when compared to Smart View; was again very poor.

These issues lead to the decision to export metadata and balance data to rdbms tables and with some judicious tuning the performance and end user experience were both massively improved. Here we flagged level0 members on the dimension members. Cube use the parent member in drill functionality. And was also able to provide OBIEE reporting on the metadata which was a big aid to administrators.

Note this was not a small project.

ArijitC

@Robert Angel, I agree. Flat prompts are not good when you have lots of member in it.

I have used another option with flat hierarchy earlier. A "special OBIEE alias" in essbase . Where the alias name is manipulated to add "__" for each generation.

So It will look like ...

root

__A

____B

Then you can choose what part of the hierarchy you want to display by selecting only those members. IMO, It is better option than hierarchical prompt when selecting only a narrow part of the hierarchy.

Robert Angel

Thanks for the workaround, as you say better than nothing.

Bet those are a pain to maintain!!!

Btw - compared to smart view how do you find performance?

ArijitC

I haven't face any performance issue with OBIEE/ASO cube yet. I think OBIEE works better with aggregated ASO than BSO. Smartview also came long way since we were forced to ues it( loved excel add in). It's quirkiness is lot under control now.

All said this is my assumption while working with the tool, I haven't done any base line comparison.

Robert Angel

Thanks, I was on a project where the reports from OBIEE took hours to run, but I left it before we got to the bottom of where the pain point was, and on the same data sets Smart View was bringing data back in seconds, ASO or BSO.

3227688

Thanks ArjitC. It worked as expected for us, to start with.

Only challenge we have is, we have a limitation of 1 million of fetching rows on OBIEE side, so even if i have more than one essbase dimension set at top level (then OBIEE has to fetch all level 0 of these dimension), the combinations going more than a million and report fails.

We were planning to create a dashboard for users, where they can choose a member from each dimension of essbase except couple of dimensions, and let the report pull all level 0 across all the dimensions ( with suppress on).

User may not like to select a member from each and every dimension.