Tuesday, May 19, 2009

Grand Totals in Essbase sourced requests after

I noticed something very disturbing after applying to our development environment: report grand totals for tables and row totals for pivots don't yield any results anymore unless you manually set all fact columns to "Sum" or "Avg" in the report. The "Aggr_External" which has normally had its effect (by importing it from the cube through the physical to the BMM layer to presentation layer) no longer takes care of this.

Let's look at the issue in detail:

Upgrading to, this is what we find as a default behaviour in a simple table report:

And in a pivot:

Now think about the concept behind building an rpd on top of an Essbase source for a second. Ponder what Venkat wrote about aggregations in for 4-article series here, here, here and here. No longer utilizing Aggr_External and just no longer aggregating at all is just plain stupid. (Yes, I'm on kind of a rant here...)

Now to correct that quick & dirty in the report, we assign an aggregation rule in the column formula:

And...oh miracle...we have data again:

Now as I said, I find this nothing but retarded.
Why has this been changed? Is this actually expected behaviour? If so...what's the rationale behind that? Normally, aggregation rules set on the BMM layer always persist unless they are explicitly overwritten either in the column formula or for pivots in the measures. Why drop this all of a sudden?

My manual intervention is a workaround, but I'm facing several cubes and almost a hundred reports. Furthermore, one of the Account dimensions has more than 400 members. So manually changing 400 potentially mapped account members isn't fun...

So the solution (if I will actually keep and not just drop if for a downgrade) would be to modify the BMM layer and change all normal account members to "Sum" and all percentages to "Avg" as aggregation rule.

Stay tuned for my upcoming rant on dimension members and filters. Another extremely intelligent change with

Venkat reacted to my post here. I tested it and it seems that despite having label only top level members like he mentioned, some analyses seem to work with totals and grand totals. This will need more investigating.


No comments:

Post a Comment