Difference in Grand Total Calculation between OBIEE and OAS with COUNT DISTINCT

Question

We recently migrated from OBIEE to OAS using the same RPD, but we noticed a difference — which does not occur every time we run the reports — in the Grand Total values of Pivot Tables when using measures defined as COUNT DISTINCT. The row-level results are identical between OBIEE and OAS, but the Grand Total differs.

By reviewing the Physical SQL Queries, OBIEE generates multiple subqueries with ROW_NUMBER, while OAS uses GROUPING SETS and GROUPING_ID. Could this change in SQL generation be the reason for different Grand Total results?

Is it best to align OAS behavior (in Physical SQL Query Production) with OBIEE, or to use a different aggregation rule at the front end (report) by applying SUM — mimicking Excel Pivot Table behavior, which sums all row-level results — instead of the default?

Gianni Ceresa · Answer

Hi,

My first question would be: does your RPD have any error or warning? Can you fix those?

Over the various releases, from OBIEE to OAS, the system has become a bit more strict in how things should be modelled, trying to be more predictable on how it will behave based on your model.

What concerns me a bit in your description is that the behaviour in OAS isn't consistent as you say it doesn't always happen. This shouldn't be possible, or maybe you should add more details in your RPD to have your analysis always perform in the same way.

For your question about aligning the OAS behaviour to OBIEE, there is a parameter to tell your OAS to behave like an older version. I would not use that, because it kind of make upgrade partially pointless. If needed, you should instead look into your model and analyses to modify them as needed to have what you need, in a consistent way, and without modifying too many parameters in the product configuration itself.