Business Intelligence – Oracle

Archive for November 15th, 2007

Oracle BI EE 10.1.3.3./2 – Understanding Todate and AGO – Achieving MAGO, QAGO and YAGO

Posted by Venkatakrishnan J on November 15, 2007

If you had gone through my previous blog entry here, i had blogged about the usage of TODate function in the repository. In this article we would be seeing how to go about using the AGO function. AGO function provides a shift backwards in Time. So basically, this would help in analyzing monthly sales with last month sales, Quarterly sales with Last Quarter sales etc. Also, Todate and AGO can be used nested within each other. We will be using the following hierarchy in the time dimension.

      Year
            Quarter
                  Month

One of the most important things that one should take care while creating a time dimension is that, the lowest chronological key should be a unique key. And all the above levels should have unique identifiers. For example, if you have columns Year, Quarter of Year and Month, you need to make Quarter and Month unique by appending Year to both Quarter and month. So, basically i have created 2 new custom columns Quarter Year and Month Year.

      

So, my Time Dimension would like the one below

      

Now lets look at the syntax of AGO function.

AGO(<measure>, <level>, <shift needed>)

Example: AGO(BISE1_TUTORIALWH.SALES.AMOUNT, BISE1_TUTORIALWH.TIMESDim.”Month”, 1)

What this would basically do is it would calculate the value of the measure for the previous Month(level of the time dimension shifted by one month).

      

Now lets look at the final results after using MAGO and QAGO in our report and compare it with the actual sales.

      

      

As you see above, MAGO has shifted the values by one month and QAGO has shifted the values by 1 quarter.

Posted in All Posts, OBI EE Plus | 10 Comments »