Have a look a these two tables:
Let's create a MONTH_YEAR_AGO Column:
Have a look at the result:
....some thing is not right here....
Let's investigated the date dimension:
Looks alright? Have a look again without year and month:
We have a sorting issue here, so let's add a sorting column:
OK that's fixed, let's go back to our report:
Still a problem there, let's sort the chronological key:
That really fixed the problem!
Till Next Time
6 comments:
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
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
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 :)
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.
You explained this more than Oracle did. Great document. Keep it up and all the blessings.
Aaron Mhonda
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
Post a Comment