Time series in OBIEE. Analytic functions and range window as an alternative to OBIEE AGO function.
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
|
1 |
EVALUATE('MAX(%1) OVER(ORDER BY DECODE(substr(%2,1,4),''-'',0,substr(%2,1,4)*12)+substr(%2,6,2) RANGE BETWEEN 1 preceding and 1 preceding)' AS DOUBLE PRECISION , "CO"."FACT"MEASURE", "CO"."DIM - DATE END"."End Year Month") |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
select distinct D1.c2 as c1, D1.c1 as c2, MAX(D1.c1) OVER(ORDER BY DECODE(substr(D1.c2,1,4),'-',0,substr(D1.c2,1,4)*12)+substr(D1.c2,6,2) RANGE BETWEEN 2 preceding and 2 preceding) as c3 from (select sum(MEASURE ) as c1, T51692.YEAR_MONTH_FMT as c2 from D_DATE T51692 /* A84 D_DATE */ , FACT T51244 /* A04 FACT */ where ( T51244.DT_VISIT_END_ID = T51692.DATE_KEY ) group by T51692.YEAR_MONTH_FMT ) D1 order by c1; |
