Oracle BI EE 11g - Calling Database Analytic Functions

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

Though the documentation states that EVALUATE_ANALYTIC handles analytic functions, i wasn't actually able to get EVALUATE_ANALYTIC function working from the UI or Repository. EVALUATE_ANALYTIC seems to push down the wrong queries that EVALUATE did in 10g(probably a bug). Having said that, EVALUATE itself seems to have got the ability to handle analytic functions. In 10g, one of the problems in using EVALUATE for analytic functions was, all the analytic functions got pushed into a GROUP BY clause which oracle does not support. In 11g, EVALUATE functions that are called from UI are automatically pushed to an outer query which do not have a GROUP BY on them. So, it is possible to use EVALUATE itself for function shipping analytic functions.

There are a lot of database analytic functions that can be put to good use for reporting. One such function is the LEAD/LAG analytic functions that can be used for doing time series AGO reporting. AGO and ToDate functions are probably the costliest functions(in terms of time & performance) available in the repository. Hence any function that can negate the need for doing a lot of time-series joins will be very handy for improving performance. I had blogged about one such technique 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, a sample report shown below compares the Sales of the various years with their respective prior year values.

This can be achieved easily by using LEAD/LAG functions as shown below. We start with including the Year and Sales column in the report. Then we include a new calculated column with the formula shown below

EVALUATE('LAG(%1,1,0) OVER (ORDER BY %2)',"Sales"."AMOUNT_SOLD","Times"."CALENDAR_YEAR")

As you see, all we have done is used the LAG function on the Sales column and then got the prior year value by extracting the previous row's sales value ordered on Year. If we look at the final query generated you will notice that the LAG function is actually applied only on a small set of data (outer query without the group by).

WITH
SAWITH0 AS (
select sum(T44322.AMOUNT_SOLD) as c1,
T44335.CALENDAR_YEAR as c2,
T44335.CALENDAR_YEAR_ID as c3
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 distinct 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
LAG(D1.c1,1,0) OVER (ORDER BY D1.c2) as c4,
D1.c3 as c5
from
SAWITH0 D1)
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4
from
SAWITH1 D1
order by c1, c2

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