Monday, September 19, 2011

OBIEE11g how to use Time Series

OBIEE has some nice features regarding time series calculations. Must developers are perfectly able to get functions like AGO, TODATE and PERIOD_ROLLING to work. Still I would like to place a couple of remarks on there usage:

- Have you taken a look a the SQL produced by these functions?

It’s definitely not the cheapest / quickest. Most of the AGO functionality can be done much “cheaper” by adding some extra columns to your calendar dimension. (DATE_WEEK_AGO, DATE_MONTH_AGO, DATE_QUARTER_AGO etc.). If you then map an aliases of your fact table against the ago column in your calendar dimension you will have your AGO columns.

TODATE columns can be calculated much cheaper in a ETL process then a recalculation every time a report in run.

- Are the used TODATE and AGO columns meaningful on this report?

Consider a report with the following columns: DAY_DATE, REVENUE, REVENUE_YEAR_TO_DATE, REVENUE_YEAR_TO_DATE_AGO. On normal production calendar you have about 220 working days a year. This means that the average delta between DATE and DATE-1 will be .5% on a YEAR_TO_DATE base. For must users such a delta has no significance. If a PERIOD_TO_DATE column has period more then 2 levels “higher” then the lowest calendar granularity on report, it’s often meaningless. (FI: YEAR vs DAY)  YEAR=>QUARTER=>MONTH=>WEEK=>DAY==> distance = 5

- Is the report really supporting a business process?

How many process which took place on 01-sep-2011 where really depending on what happened on 01-sep-2010 and 01-sep-2009?

Till Next Time

1 comment:

Antony Heljula said...

Hi John,

I agree that logical Time Series calcs generally do not perform as well as the "old" method of using aliases. But not having to create aliases in the RPD for every time series calc is a huge advantage - I once saw a customer with over 400 time series aliases in the physical layer!

The logical time series calcs do have the advantage in other areas - they are much less complex and easier to maintain than physical aliases.

The other bonus is that with OBIEE 11g users can now implement their own time series calcs in Answers (so they don't have to wait for RPD development). With physical aliases you always have to wait for a new RPD release in order to do time series calcs.

So the logical time series calcs do have a good purpose! You just have to weight up the pros and cons: flexibility vs performance :-)