Business Intelligence – Oracle

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.

About these ads

10 Responses to “Oracle BI EE 10.1.3.3./2 – Understanding Todate and AGO – Achieving MAGO, QAGO and YAGO”

  1. Raman said

    I have been struggling with Time dimension. I have been following Creating Repository OBE. From your two blogs on time dimension I have following questions:

    (1) THe OBE instructions asks for moving all like attributes under each level – e.g., move all Calendar month attributes under Calendar Month Level etc. From prev blog on this topic it is clear that this is not correct – e.g., Calendar Month Name is not unique and therefore should not be moved under month. Am I correct?

    (2) In the second screen shot looks like you have not created logical level keys for CAL_YEAR_NUMBER, CALENDAR_YEAR_NAME, Year Quarter and Year Month. Isn’t defining logical level keys for them necessary before proceeding with the queries?

    (3) Under Year level, is it necessary to create logical level key for both CAL_YEAR_NUMBER and CALENDAR_YEAR_NAME?

    Thanks

  2. Rob Fowler said

    I have been using the AGO function at my lowest level which is at the month level. I have

    Month sales
    Ago(month sales, 12)
    Ago(month sales, 24)

    This works when comparing month level values to month, but I want to use the sum of these at a plant level.

    If I show plant and Month Sales for 2007, I get one row.

    Year Plant Month Sales
    2007 One 1000

    But if I out a ago function column, it shows 12 records

    Year Plant Month Sales Month Ago12
    2007 One 10 12
    2007 One 12 13
    and so on for 12 records.

    Can the ago function measures sum?

    Thanks
    Rob

  3. Dila Cil said

    Is there a way to use the AGO function to calculate Year Beginning Amount?? I have not been able to figure it out. I tried to use Level-based measures on my date dimension(with First), it worked partially. If there were no values for January it would show February, March, but while totalling it would only total January…
    All I am trying to do is a report with Beginning Month, YTD, YTD Change, YTD %Change. Any input is appreciated. Thanks!

  4. Alberto said

    How do you actually achieve this part here?

    “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.”
    I tried to do it but I can’t make the years to not show up.

  5. Marko said

    Hi,

    I have managed to get YearAGO values without any problem but later in answers I had problems with getting totals per any dimension level (it is throwing error message). Then I have realized that If I set Agregation rule for that field (within Answers) to “Sum” totals are working fine. Of course that leads to another problem that I am not able to solve…

    Now my filters(prompts) are not working properly and it happens only when that calculated field is included in the request. What I managed to catch is that error is occuring when filter is to resctricive and report not able to return any data. So instead message “No results” I am getting this:

    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 3113, message: ORA-03113: end-of-file on communication channel Process ID: 0 Session ID: 150 Serial number: 4500 at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

    What could be the reason for this?

    Thanks,
    Marko

  6. Marko said

    I have solved this problem by upgrading OracleDB from 10g to 11g. Seems that generated SQL was not recognized by version 10g.

    Regards,
    Marko

  7. JoeyMac said

    Hi All,

    I have gotten the AGO function to work in all scenarios except for the February MAGO. For this one case it always seems to not count the last day or two in January of the prior month. Is this a bug? I noticed that the screenshot for the MAGO sales in this blog also do not tie out with February MAGO numbers. Any thoughts?

    Thanks!

  8. [...] relational database’s sql queries. I have covered this before for a relational source here and here. Now with the support for Essbase data sources in BI EE, Ago and ToDate functions are [...]

  9. annarr said

    Hi,

    Thanks a lot for your useful post!

    When I implemented the Ago function I got the following error:

    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22036] Level in AGO function (Date) must be a rollup of (or equivalent to) the measure level (Year). (HY000)

    I have the year, qtr, month, week & day and defined all the levels as chronolgical keys. Also tried defining only the lowest date leve as the chronological key. But the error remained the same.

    Can you pls. throw some light on this / help?

    Thanks

    Annarr

  10. sadik kamal said

    hi
    i have a big problem with ago function, in the business model i created a measure with ago function when using answers to make a request that give the amount sold and last year amount sold which use ago function i have wrong result.

    can you help me please

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

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: