Bookmark

About Me

I am Venkatakrishnan J, a Business Intelligence enthusiast working with Rittman Mead Consulting who likes blogging about acquisitions in the BI space, technical workings of the BI tools in general and Oracle Business Intelligence tools in particular. All the views expressed here are my own and does not reflect the views of Rittman Mead Consulting or Oracle. Going forward, i would be blogging at http://rittmanmead.com/blog.

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.

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.

Vikram Takkarsaid

vikram takkarsaid

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

Venkatakrishnan Jsaid

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.

Venkatakrishnan Jsaid

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.

sreenusaid

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 ?

Vikram Takkarsaid

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)

Vikram Takkarsaid

Venkatakrishnan Jsaid

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.

Vikram Takkarsaid

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)

Venkatakrishnan Jsaid

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.

Vikram Takkarsaid

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.

Soumyasaid

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.

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.

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.