Business Intelligence – Oracle

Oracle BI EE 10.1.3.4.1 and Essbase Connectivity – Report Based and Essbase based Grand Totals – Answers Based Aggregation

Posted by Venkatakrishnan J on May 20, 2009

This post here by Christian prodded me to write about another interesting feature in the BI EE and Essbase Connectivity. As you would probably know BI EE supports report based grand totals/sub-totals in a table view. There are 2 types of totals. One is BI Server based totals wherein BI Server would do the totalling on a result set. The other is data source specific totalling wherein the query is fired back to the underlying data source to obtain the totals. For example, lets just quickly import the Demo->Basic cube in to the repository and build a very simple report as shown below.

As you see, it is a very simple report containing a report based total and a sub-total at the market level. Before going further, lets look at the outline of the Basic cube first. As you see, every dimension top member is set as a stored only member.

If you look at the MDX of the above report, you would notice that 3 MDX queries would be fired. One for the base report , one for the grand total and the other for the market sub-total.

The aggregation for the Measure Sales is Aggr_External in both the physical and logical layer. In Answers, the aggregation is set as default. Now, lets go to the outline and convert the Year Dimension top member to be a label only member as shown below.

Now, try running the same report above. You would notice that the report level totals and sub-totals are totally wrong as shown below.

The main reason for this is the fact that since we have converted the topmost member of the outline to be label-only. So for the Year dimension, it will always pick the Qtr1 value instead of totalling all the quarters. For report developers, this would turn out to be an absolute nightmare considering the fact a report based total is created under the assumption that, the totalling is done on the report and not at the data source level. Now, from Answers lets change the aggregation os the sales measure to SUM.

And look at the report.

Basically, a report level SUM ensures that all the custom aggregations/totalling occuring in a pivot table/table are done at the report level instead of at the Essbase layer. So by default, ensure that you always have SUM at the report level to ensure that you do not get wrong answers especially for totals and sub-totals.

This should have given you an idea of how the aggregations at 3 layers (Physical, BMM and the Answers) can affect a report. I would cover the usage of Report Aggregations across different BMM and Physical Layer aggregations in the future.

About these ads

5 Responses to “Oracle BI EE 10.1.3.4.1 and Essbase Connectivity – Report Based and Essbase based Grand Totals – Answers Based Aggregation”

  1. Hi Venkat,

    Interesting. However, I have four dimension with label only top members. I just tried this out and it’s coming back nicely with correct sums across Gen2 and Gen3 plus correct grand totals. (10.1.3.4.1 without 7349048!).

    “So by default, ensure that you always have SUM at the report level to ensure that you do not get wrong answers especially for totals and sub-totals.” … that’s a big statement. One of the advantages is (was) that we could rely on Essbase for aggregations and save the bi server from doing the work.

    I guess that’s out of the window then…

    Cheers,
    Christian

  2. Venkatakrishnan J said

    Hi Christian – I am not saying you would always get wrong results with label only members. In fact, in my first example it produced the correct results. There are other Essbase specific properties that one would have to be aware of. Also in many Essbase implementations, they do not aggregate till the topmost level. In such cases, you would always be getting NULL for grant totals. So, to ensure that grand totals are always done at a report level, we need to have the SUM externally applied in answers. You have been spot on in your observations. I believe there is an underlying reason why you are getting the results as NULL in the grand total

    1. Either non-aggregated cube.
    2. Or Essbase security kicks in producing #NoAccess which in turn would be converted as NULL by BI Server.

    -Venkat

  3. Thank you for your great information.

  4. greg said

    Thanks a lot for the info, you can check out http://www.gregkramerassociates.com/ for more help on Business Intelligence

  5. ankit said

    Hi,

    Hope you doing good,
    Can you please look into this, if you get some time:

    http://forums.oracle.com/forums/message.jspa?messageID=4535701#4535701

    thanks,
    Ankit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: