Sunday, January 11, 2009

OBIEE Leap Year Challenge

Have a look at these tables of February 2008 and 2009: (remember 2008 was a "leap" year)

image image

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:

image which is correct. But if we do it on a day by day level we get:image

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:

image

If connect an alias of our fact table to the DT_ID_YAGO we get:

 image image

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:

Anonymous said...

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?

Anonymous said...

well, I fixed this problem by restart all the BI Server. Don't know why.

Anonymous said...

This solution doubles transaction amounts that are dated 2-28-2009.

John Minkjan said...

@Anonymous

No it doesn't:

28 feb 2009 Yago = 28 feb 2008 (1230) + 29 feb 2008 (1232) = 1230 + 1232 = 2462

regards

Andre said...

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!

Anonymous said...

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.

John Minkjan said...

I Think you are using the wrong function, have a look at TODATE or ROLLINGPERIOD.

regards

John