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