Have a look at these tables of February 2008 and 2009: (remember 2008 was a "leap" year)
If in February 2009 we use the ago function of OBIEE:
AGO(BM_AGO_TEST.F_FACTS.F_FACT_VAL, BM_AGO_TEST.DIM_DATE_TESTDim."YEAR", 1)
On a month level we get:
Which is not quite correct since it is missing the "AGO" data for FEB 29 2008 (1232). Now you could argue that this is correct since there is no FEB 29 2009. Most accounting systems agree that in such a case the data for FEB 29 should be added to the data of FEB 28. One way of solving this is adding a Year Ago DATE_ID to your date dimensions with a double entry for FEB 28 2009:
If connect an alias of our fact table to the DT_ID_YAGO we get:
We see that the data for FEB 28 and FEB 29 2008 is now summarized for FEB 28 2009.
An other advance for working with fixed ago column is your time dimension is that the query costs are about 80 % percent lower then using the OBIEE AGO function. Is this example 187 versus 853 on as Oracle 10Gr2 database.
Till Next Time
7 comments:
Hi:
I tried your steps and fill every days' amount by 1. Then I got some results like this:
Date Amount YAG_Amount
20090228 1 1
20090228 1 1
Which was supposed to have results like:
Date Amount YAG_Amount
20090228 1 2
What's wrong?
well, I fixed this problem by restart all the BI Server. Don't know why.
This solution doubles transaction amounts that are dated 2-28-2009.
@Anonymous
No it doesn't:
28 feb 2009 Yago = 28 feb 2008 (1230) + 29 feb 2008 (1232) = 1230 + 1232 = 2462
regards
Hi, im having a problem with the AGO function i got the time dimension as time and the chronological keys defined, but it never returns the first value example:
H1 AGO
X1 1
X2 2 1
X3 3 2
If i make a prompt to choose from X2 to X3:
H1 AGO
X2 2
X3 3 2
Do you have any suggestion?
Thks a Lot!
John,
I have problem with OBIEE AGO function. We have the dimension in AGO function as fiscal month, Although we have Year > Quarter > Month hierarchy in Time dimension, the measure created with AGO function is not rolling up to next levels.
If my report has Year, Quarter, Measure, It should return the rollup for 3 months in a quarter. but it displays 3 different rows.
Could you advise work around for AGO/Dimension/Rollup issue.
I Think you are using the wrong function, have a look at TODATE or ROLLINGPERIOD.
regards
John
Post a Comment