Time series in OBIEE. Analytic functions and range window as an alternative to OBIEE AGO function.

Uli Bethke analytic functions, OBIEE

All intellectual credit for this post goes to my colleague Maciek Kocon. I am simply documenting this.

We had some performance issues recently using the OBIEE AGO function for time series and month ago reporting.

While there are solutions out there using the lag functionality we had two issues with these

(1) They don't cater for sparse data in an easy to implement way.
(2) The lag function is not SQL ANSI compliant

The solution is to use an analytic function with a range window

The trick is to order the column YEAR_MONTH with format YYYY-MM in the correct way. We do this by extracting the year and month and then multiplying the year by 12 and adding the number of months to this figure,
e.g. 2000-12 translates to 2000*12+12=24012, 2001-01 translates to 24013 and so on.

Now that we have the YEAR_MONTH column in the right order we apply the range window function to look at the preceding value in the range and get the value of the measure for this. This is our month ago value.

For scenarios where the time series is broken, e.g. 2000-12, 2001-02 the function returns NULL and does not incorrectly report the value for 2000-12. This is the nature of the range window

Don't get confused by the DECODE statement. This is just there to substitute default value dashes in the date dimension with 0.

This translates into the following SQL

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.