Oracle BIEE 11g 11.1.1.7.1 – Sub Totals and Pivot Calculations

February 8, 2015 Manish Chaturvedi

    This post is a reappraisal of another a while back by Venkat from RittmanMead, which can be read here. The original post had laid out behind the scene handling of Summary and Grand Totals in Pivot tables.

The main take away is that as of OBI 10.1.3 Sub Totals and Pivot Table calculations are frequently handled by the BI Server – with only the basic Select pushed to the database. Intermediate results used to derive the calculations and totals are found in tmp files on the BI Server, raising questions about both performance and scalability.

So has anything changed how OBIEE generates SQL (both Logical and Physical) as far as Summary Reports and Sub Totals are concerned?



    I decided to take a look. The following Pivot table report is sub totaled by Product Category, has a Grand Total and, further calculates % contribution of each Sub Category in the Total.

 

 
The Logical SQL generated does not seem to be doing anything new.

 

 

REPORT_SUM is used for BI Server based aggregation for the Report Totals. The two different calls to this function are attributed to a. Sub Totals By Product Category and b. Grand Total.

 
Time to check the Temp Directory – nothing there.

 
Maybe the Physical SQL offers a clue. And there it is – Query Sub factoring and Analytic Sql to the rescue!


 

 

 
So there is plenty of improvements under the hood when it comes to handling Report Totals.
The % calculation is nowhere to be seen in the Physical query. So the components are handled by the database before passing the results for final ratio calculation back to the BI Server.
Next step would be locate the mechanics of this last stage of the calculation.