Calling Database Analytics Functions in OBIEE

OBIEE 11g introduces a new function called EVALUATE_ANALYTIC that can ship analytic functions from Answers back to the database. In 10g we had EVALUATE which could ship only normal functions. Analytics functions were not supported. But with 11g, Analytics Functions are supported as well.

There are a lot of database analytics functions that can be put to good use for reporting. One such functions are the LEAD LAG functions that can be used for doing time series reporting. AGO and ToDate functions are one of the costliest functions (in terms of time & performance). Hence any function that can negate the need for doing a lot of time-series joins will be very handy for improving performance. One such technique is here. The problem with this technique is it cannot be used if a report contains any datetime attributes from the time dimension. LEAD/LAG functions can help in achieving time series AGO functionality even if the report contains time dimension attributes.

For example below analysis compares Sales with prior year Sales.

This can be achieved easily by using LEAD/LAG functions.

WITH SAWITH0 AS
 (SELECT SUM(T44322.AMOUNT_SOLD)asc1, T44335.CALENDAR_YEAR asc2, T44335.CALENDAR_YEAR_ID asc3
 FROM SH.TIMES T44335, SH.SALES T44322
 WHERE(T44322.TIME_ID=T44335.TIME_ID)
 GROUP BY T44335.CALENDAR_YEAR,
 T44335.CALENDAR_YEAR_ID
 ),
 SAWITH1 AS
 (SELECT distinct0asc1, D1.c2 asc2, D1.c1 asc3, LAG(D1.c1,1,0)OVER(ORDER BY D1.c2)asc4, D1.c3 asc5
 FROM SAWITH0 D1
 )
 SELECT D1.c1 asc1, D1.c2 asc2, D1.c3 asc3, D1.c4 asc4
 FROM SAWITH1 D1
 ORDER BY c1, c2

There are quite a lot of other use cases where we can put this to good use.

Advertisements