Business Intelligence – Oracle

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
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.

Advertisements

4 Responses to “Oracle BI EE 10.1.3.3/2 – Varying Aggregation based on Levels – Analytic Functions Equivalence”

  1. Limor said

    Hi Venkat,
    Can you do these functions from the RPD?
    I tried it and there is a syntax error with the “BY”.
    Thanks,
    Limor

  2. Venkatakrishnan J said

    Limor,

    Its not possible to use these within the RPD. One can use them only from within Answers.

    Venkat

  3. […] Again, what this does it generates a ROW_NUMBER() OVER (PARTITION BY GEOGRAPHY.REGION_NAME,….) clause internally to generate the desired output. This is an extension of what we saw in my previous blog entry here […]

  4. […] Oracle BI EE 10.1.3.3/2 – Varying Aggregation based on Levels – Analytic Functions Equivalence page_revision: 1, last_edited: 1241302517|%e %b %Y, %H:%M %Z (%O ago) edittags history files print site tools+ options edit sections append backlinks view source parent block rename delete help | terms of service | privacy | report a bug | flag as objectionable Hosted by Wikidot.com — get your free wiki now! Unless stated otherwise Content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License Click here to edit contents of this page. Click here to toggle editing of individual sections of the page (if possible). Watch headings for an “edit” link when available. Append content without editing the whole page source. Check out how this page has evolved in the past. If you want to discuss contents of this page – this is the easiest way to do it. View and manage file attachments for this page. A few useful tools to manage this Site. See pages that link to and include this page. Change the name (also URL address, possibly the category) of the page. View wiki source for this page without editing. View/set parent page (used for creating breadcrumbs and structured layout). Notify administrators if there is objectionable content in this page. Something does not work as expected? Find out what you can do. General Wikidot.com documentation and help section. Wikidot.com Terms of Service – what you can, what you should not etc. Wikidot.com Privacy Policy. _uff = false; _uacct = “UA-68540-5″; _udn=”wikidot.com”; urchinTracker(); _qoptions={ qacct:”p-edL3gsnUjJzw-” }; […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: