Oracle BI EE 10.1.3.3/2 – Varying Aggregation based on Levels – Analytic Functions Equivalence
Posted by Venkatakrishnan J on October 2, 2007
I just learned about a new method of aggregation that is available in BI EE. Thanks to Kurt Wolf. It is pretty basic and this requirement comes up pretty often. Let us try to look into the requirement first. The requirement is to create a report that has the following columns
TOTAL_NAME REGION_NAME SUBREGION_NAME COUNTRY_NAME AMOUNT_BY_REGION
Well, the above columns are actually part of the GEOGRAPHY and SALES tables of the bise1_tutorialwh schema. In our report, AMOUNT_BY_REGION needs to have the summation of amount only till level Region_Name. In SQL terms, this can be achieved easily using analytic functions. The SQL would look like this
SELECT TOTAL_NAME, REGION_NAME, SUBREGION_NAME, COUNTRY_NAME, SUM(AMOUNT) OVER (PARTITION BY TOTAL_NAME, REGION_NAME ORDER BY SUBREGION_NAME, COUNTRY_NAME) AS AMOUNT_BY_REGION
A.DIMENSION_KEY = B.GEOGRAPHY
As you see above, our requirement could be easily achieved by using analytic functions. But question was, how do we do that using OBI EE without using analytic functions. Lets build the report first without the AMOUNT_BY_REGION column.
Now, lets add a new column and wrap the column using the function SUM(SALES.AMOUNT BY GEOGRAPHY.TOTAL_NAME, GEOGRAPHY.REGION_NAME).
And, lets look at the report now.
As you see, it is very simple but very powerful.