OBIEE Ago and and error ORA-00942: table or view does not exist

George did a good one:

http://translate.google.com/translate?u=http://bidwcz.blogspot.com/&sl=cs&tl=en&hl=en&ie=UTF-8

On how to use PERF_PREFER_INTERVAL_STITCH_JOIN here!

Till Next Time

OBIEE Leap Year Challenge

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 correct. But if we do it on a day by day 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

OBIEE AGO and TODATE Series

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