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
FROM
GEOGRAPHY A,
SALES B
WHERE
A.DIMENSION_KEY = B.GEOGRAPHY
FROM
GEOGRAPHY A,
SALES B
WHERE
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.
Posts
