Business Intelligence – Oracle

Oracle BI EE 10.1.3.3./2 – Understanding Todate and AGO – Achieving YTD, QTD and MTD

Posted by Venkatakrishnan J on November 5, 2007

Though i had known about the existence of repository functions Ago and TODate, i had never used them before especially in the context of achieving YTD, QTD and MTD. So, i thought i would give it a try and see how it works and of course find out the possible use cases of using these functions. In this article we would see how to go about using ToDate function. Just remember that we do not have these functions in Answers. To use these one would have to create custom logical columns in the repository. Before we start lets look at the syntax of both of these functions.

TODATE:

      TODATE(<Measure Column>, <Level Column in the Time Dimension>)

      Eg.   TODATE(BISE1_TUTORIALWH.SALES.AMOUNT, BISE1_TUTORIALWH.TIMESDim.”Year”)

AGO:

      AGO(<Measure Column>, <Level Column in the Time Dimension>, <Shift Required>)

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

Now lets look at TODATE function in particular. According to the docs TODATE function helps in achieving Year to Date(YTD), Month to Date(MTD) and Quarter to Date(QTD) kind of functionality. So what does YTD, QTD and MTD mean in the context of say Sales(a measure). Lets understand these first.

YTD Sale   –   It means the summation of sales from 1st Jan of the current year to the Current Date. The current Date is kind of a misnomer since its value can vary(can have values like day, month, quarter etc) depending on the granularity. We will understand this while going over these functions in detail.

QTD Sale   –   It means the summation of sales from first of first month of the current quarter of the current year to the Current Date.

MTD Sale   –   It means the summation of sales from first of the current month to the Current Date.

The base schema that i use to demonstrate for this will be the BISE1_TUTORIALWH that gets bundled with bise1. The first step that i did was to create a time Dimension. TODATE and AGO can be used only along with Time Dimensions. Lets create one with the following hierarchy.

      Year   ->   Quarter   ->   Month of Year   -> Dimension Key

      

Remeber while creating the Dimension mark it as a Time Dimension.

      

Also, the lowest unique level has to be identified as the chronological key for the time dimension. In our case Dimension Key would be the chronological key.

      

Now lets create a new Logical column YTD Sales. As you see below, TODATE will take sales as one parameter and a level in the Time Dimension(not the logical table) as another parameter. Similarly, lets create QTD Sale and MTD Sale.

            

      

Remember that these custom logical columns are very specific regarding their granularity. Varying the granularity will give wrong results in the answes. Lets understand this by creating a simple report. Our report will have the following columns

   Year   Quarter   Month   Sales   YTD sales

      

Lets look at the results and try to understand what this function does.

      

As you see above YTD Sales basically achieves a rolling sum. For example, If you take Year 2003, Quarter 1 and Month 2, the sales is 20,095. But the YTD Sale is a summation of Month 1 + Month 2 = 20095 + 3683 = 23778. We do not have a day level granularity. We only have a month level grain in the time dimension hence, it rolls it up to the month level. Now lets remove the Month of year column and just have Year, Quarter, Sales and YTD Sales.

      

      

Oops. What has happened to the YTD? According to the definition of YTD, for Year 2003 and Quarter 1, we must have a summation of sales for all the months in Quarter 1. i.e. 3683 + 20095 + 4157 = 27936. But what we have in the actual result is 55397. How is that possible? Thats one of the reasons why understanding grain in TODATE and AGO is very important. As we removed the month from the report, what YTD has done is it has done a summation of the YTD values of all the months in the previous report instead of the Sales ie. 3683 + 23778 + 27936 = 55397. Same would be the case for MTD and QTD.

      

      

Here we have seen only TODATE. Lets looks at AGO in a later article.

Advertisements

28 Responses to “Oracle BI EE 10.1.3.3./2 – Understanding Todate and AGO – Achieving YTD, QTD and MTD”

  1. […] 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 […]

  2. Vikram Takkar said

    Excelent article…. Keep it up man..

  3. vikram takkar said

    Hi Boss,

    i want to implement this but i am not getting what is the Dimension_Key you are taking and why are you taking Dimension_key as chronological key.. where is the Dimension_key is defined in the tables… can you explain a bit…

    Secondly you are telling that YTD means “From 1st of CURRENT Year to till Date” then how can you show report for YTD having other years.. Refer your reports… (If it calculate for the current years then if we include other years in our report it should show different result)..

    can you please explain me at vikramtakkar@gmail.com or call me at +91988115427.I shal be thankful to you

  4. Venkatakrishnan J said

    Dimension Key is my unique key for the dimension. For the lowest level it is the chronological key. I have just taken an example here. Always, define chronological keys for all your levels(unique keys identifying a level). Also, when i say for the current year it means for the year for which you are choosing in the report. For example, If you have Year, Quarter and YTD sales in your report, then for year 2003 and Quarter 2, then YTD sales will be a summation of sales for the 1st and 2nd quarter. But if you have it for say Year 2007 and Quarter 4, then YTD will be summation of sales for months of Oct, Nov alone since we dont have data for december yet.

  5. vikram takkar said

    Thanks for replying…

    I have following herierchy..

    year
    Quarter
    Month

    So you want to say that chronological key contains a keys from all the levels ie year , Quater & month.
    So chronological key contains ( yearKey,quarterKey,monthKey)

    Please confirm is this what you want to say?

    I am taking chronological key as yearKey & monthKey & i am not getting the desired results from it…Please suggest

  6. vikram takkar said

    Hey please read the following lines in double quotes carefully written by you..

    “if you have it for say Year 2007 and Quarter 4, then YTD will be summation of sales for months of Oct, Nov alone since we dont have data for december yet.”

    If you are saying this then what is the difference between QTD & YTD.. both will populate the same result..

  7. Venkatakrishnan J said

    Oops. Typo on my part. It is QTD. And for the keys if you have 3 levels, then you need chronological keys in all the 3 levels. So if you have Year->Quarter->Month then you should have Year Key as the chrono key in the level 1, Quarter Key as the Chrono Key in level 2 and Month Key as the chrono key in level 3.

  8. sreenu said

    Hi ,
    I have a problem with todate function. And more over on which leve i have specify the chronology key in the hierarchy.

    If i have a composite primary key in the dim table how i can specify the lowest level. In the least level you have specify the primary key of the dim. is it? Now in my case it is composite primary key? How can i define and can i give this key as chronologhy key or not ?

    Pls advise me
    thank you

  9. Venkatakrishnan J said

    When you have composite keys you need to create a new logical column which would be a concatenation all the key attributes. Its better to define chronological keys in each level.

  10. sreenu said

    I will try that option. Thank you venkat , for your immediately reply. Keep it up. I really appriciate you boss.

  11. sreenu said

    Hi Krishna,
    When i am taking in the report year , qtd these two columns. It is showing for all the quarters are same data. Is it correct. Shall we take another colimn in the report.

    Pls advise me

  12. Vikram Takkar said

    Hi Venkatakrishnan,

    I have one doubt.. In your above reports i think YTD seems to be correct but if u take a case of QTD , Data for 2006 is fine but for year 2007 for month of January Sales must be equal to QTD Sales instead it is showing ( This year Sales in january + Last years sales in january)

    Please clarify..

  13. Vikram Takkar said

    Hi Venkatakrishnan,

    Can you please clarify the above ..

    i shall be thankful to you..

  14. Venkatakrishnan J said

    Yes you are right. I should have made that clear. The last QTD Pic show how your calculations can get awry by having the wrong chronological key. QTD should have summation of sales starting from the start of that quarter and not from the previous years.

  15. Vikram Takkar said

    Hi Venkatakrishnan,

    But i am talking about the second last Pic where you have shown

    Year Quarter Month Sales QTD sales

    In your Second last pic Data for year 2003 is fine but for year 2004 for month of January “Sales” must be equal to “QTD Sales” instead it is showing ( This year Sales in january + Last years sales in january)

    Please help me as i am also getting the same results..

  16. Venkatakrishnan J said

    What is your chronological key? Also, are your level keys unique? For example, if you have Quarter (1 to 4), then you need to make that unique across all the years. You can append Year and Quarter to make that unique. THe problem in your case and the above pic is that the levels are not unique.

  17. Vikram Takkar said

    Thanks for replying..

    I think i understood problem.. I will tell you what i will understood later..

    Please look at the following Herierchy i have.

    Here you want to tell that quater level is not Unique across the years so i need to create one more logical key(year) in the quater level so that quater can be unique accross the years. and sae in the case of Month.

    I have year key as chronological key..

    Conclusion: quater level is not Unique across the years so i need to create one more logical key(year) in the quater level so that quater can be unique accross the years. and sae in the case of Month.

    Am i on right path..?

    I shall be thank ful to you if u will help me.

  18. Vikram Takkar said

    I entered HTML TAGS in the above post and its not reflected..

    i am sending the links for the pics

  19. Vikram Takkar said

    sorry

  20. Vikram Takkar said

    Hi Venkatakrishnan,

    Thanks for all your support.. Now i am abloe to get required Data..
    I appended year key to the quarter key and set the new key as primary key for quater level..

    I am able to see the correct data.. Thanks once again..

    I have one suggestion for you.. If you can include the right screen shots for the above article it will be good.\

    Regards
    Vikram Takkar

  21. Soumya said

    Name (required)

    Mail (will not be published) (required)

    Website

    XHTML: You can use these tags:

    Hi ,

    Just thought of rephrasing my question.
    I have a Date dimension configured on a W_DAY_D and use my measures in the fact against this dimension level ( basically date)

    I tried to set up this Day dimension as a TIme dimension for configuring the WTD( Week to date)

    I marked it as Time dimension. configured the composite Chronological key at the Week level to make the unique chrono key using Year.

    After doing these steps, the rpd throws my an error saying ' The Physical Table W_DAY_D which is a part of the Time dimension is also used in the logical table 'Measures'(Logical Fact Table in my Rpd), which is not a part of any time dimension.

    I am little confused with this eeror message.

    Shouldn't i use the same dimension as atime dimension and a normal day dimension?
    I think i am missing something here.Could you please help me figure this out.

  22. sridev said

    hi,

    please tell about chronological key.

    what is the use of chronological key in hierarchy?

  23. Saj said

    Is it possible to user these Measures in this fashion:

    For current Year:
    Customer MTD Sales YTD Sales
    ———- ———- ———-
    Customer 1
    Customer 2
    Customer 3

    Thanks,

  24. […] 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 function […]

  25. The style of writing is quite familiar to me. Have you written guest posts for other bloggers?

  26. Mani said

    I am trying to implemented a 3 month rolling average on a report filtered on current month. Please help me with your inputs on how to achieve this.

    Thanks

  27. Himanshu said

    Hi..I am implementing YTD report however i have to generate it without a specific olap database..The OLTP table i have to generate the report against looks like this..
    SNo Dealer SaleDate #CarsSold
    1 hyundaiA 1/1/2009 10
    2 hyundaiB 20/5/2009 25
    3 hyundaiA 15/2/2010 35

    I modelled the above physical table as the following 3 tables in my business layer. All the 3 tables are based on the same OLTP table in my physical layer

    Dealer Dim Table with the following logical columns
    SNo
    DealerName

    Time Dim Table
    SNo
    SaleDate
    SaleYear-Derived by applying Year function to SaleDate
    SaleQuarter-Again derived from SaleDate
    SaleMonth-Again Derived from SaleDate
    SaleQuarterDesc & SaleMonthDesc as suggested by you above by appending year and quarter etc

    The chronological keys while creating timeDimension is defined appropriately at all levels and at lowest detail level it is defined as SNo.

    Sale Fact Table contains follwing columns
    SNo
    #CarsSold

    All the 3 tables are being derived from the one and only physical OLTP table i have. the facts and dimensions are realted by column SNo.

    I followed all the steps defined by you above but not getting appropriate results. I am not getting error but getting wrong results. Kindly let me know if what am trying to do is indeed possible and if you can determine where i am going wrong. I am new to OBIEE and chances are I migth be going fundamentally wrong somewhere.

  28. Himanshu said

    Email Update

    Hi..I am implementing YTD report however i have to generate it without a specific olap database..The OLTP table i have to generate the report against looks like this..
    SNo Dealer SaleDate #CarsSold
    1 hyundaiA 1/1/2009 10
    2 hyundaiB 20/5/2009 25
    3 hyundaiA 15/2/2010 35

    I modelled the above physical table as the following 3 tables in my business layer. All the 3 tables are based on the same OLTP table in my physical layer

    Dealer Dim Table with the following logical columns
    SNo
    DealerName

    Time Dim Table
    SNo
    SaleDate
    SaleYear-Derived by applying Year function to SaleDate
    SaleQuarter-Again derived from SaleDate
    SaleMonth-Again Derived from SaleDate
    SaleQuarterDesc & SaleMonthDesc as suggested by you above by appending year and quarter etc

    The chronological keys while creating timeDimension is defined appropriately at all levels and at lowest detail level it is defined as SNo.

    Sale Fact Table contains follwing columns
    SNo
    #CarsSold

    All the 3 tables are being derived from the one and only physical OLTP table i have. the facts and dimensions are realted by column SNo.

    I followed all the steps defined by you above but not getting appropriate results. I am not getting error but getting wrong results. Kindly let me know if what am trying to do is indeed possible and if you can determine where i am going wrong. I am new to OBIEE and chances are I migth be going fundamentally wrong somewhere.

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: