Saturday, November 29, 2008

OBIEE AGO and TODATE Series

Have a look a these two tables:

image image

Let's create a MONTH_YEAR_AGO Column:

image

Have a look at the result:

image

....some thing is not right here....

Let's investigated the date dimension:

image

Looks alright? Have a look again without year and month:

image

We have a sorting issue here, so let's add a sorting column:

image

image

OK that's fixed, let's go back to our report:

image

Still a problem there, let's sort the chronological key:

image

image

That really fixed the problem!

Till Next Time

6 comments:

Anonymous said...

but as it shown, the data is nit currect,
watch, feb 2005 at your first picture, and feb 2006+2005 at your last picture, it is not the same value

John Minkjan said...

Look oke to me:
First pic feb 2005 = 34.797
Second pic feb 2006 = 32.243

last pic f_fact_val (feb 2006) = 32.243
f_fact_month_year_ago (= feb 2005)= 34.797

What is wrong according to you?

regards
John

san said...

Well people always starts with giving a solution.But let me start by asking a question on OBIEE.
Iam working on a calculation where I have 2 columns one is a running Sum and the other is Moving average.

Month/Year | Number of Employee till date | No of Employee terminated till date
Jan 200 10
Feb 200 15
mar 200 20
Apr 200 25
So in this example I want to calculate my turnover which is nothing but Termination till date / Number of employee till date.Here the employee count is a moving average and termination count is running sum.So to calculate the emploee YTD and Termination YTD i am using RSUM and MAVG function which works fine and are inbuilt function in OBIEE.But when I try to use (Termination till date / Number of employee till date) the tool doesnot allow me to do the calculation.Is it possible at all to do an division (/) in OBIEE answers or RPD.I was unsuccessful.Any help in this regard is greatly appriciated.Till next time and one more problem :)

himanshu hira said...

I have a Question.
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.

Anonymous said...

You explained this more than Oracle did. Great document. Keep it up and all the blessings.

Aaron Mhonda

Anonymous said...

Good Post, once you get it all setup however the monster queries that this creates are slowing down the system. Is there something I am missing or are we to expect these kind of queries when using time series functions