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