Use of Evaluate function for Analytics Function (Oracle)

In those rare instances, there is a necessity to utilize the EVALUATE function within OBIEE.  Most cases the use is to utilize a database function that is not available, yet, by OBIEE; but, is available within the database reporting from.  For example, an analytic function within Oracle.  Use of the EVALUATE function will allow to function ship this through the physical sql.  There is not much in Oracle’s documentation that goes over this, whether it be EVALUATE, EVALUATE_AGGR or particularly EVALUATE_PREDICATE.

So playing around with this functionality for an associate the other day, I did notice something quite interesting.  When using the EVALUATE function on an Oracle Analytic function and using physical tables and columns for the bind params you will see something like this in the physical SQL:

State: HY000. Code: 388. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 30483, message: ORA-30483: window functions are not allowed here at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

The physical sql will look something like this:

select distinct D1.c3 as c1,

D1.c1 – D1.c2 as c2

from

(select sum(case  when T1595.METRIC_CODE = ‘ACTUAL’ then T1558.METRIC_VALUE end ) as c1,

sum(case  when T1595.METRIC_CODE = ‘BUDGET’ then T1558.METRIC_VALUE end ) as c2,

cast(ROW_NUMBER() OVER (PARTITION BY T1558.METRIC_VALUE ORDER BY T1558. METRIC_VALUE asc) as  DOUBLE PRECISION  ) as c3

from

ZFCT T1558  ,

ZMETRIC_DIM T1595

where  ( T1558.METRIC_WID = T1595.WID and (T1595.METRIC_CODE in (‘ACTUAL’, ‘BUDGET’)) )

group by cast(ROW_NUMBER() OVER (PARTITION BY T1558.METRIC_VALUE ORDER BY T1558. METRIC_VALUE asc) as  DOUBLE PRECISION  )

) D1

order by c1

As you can see the analytic function, or window function, is within the inner query and part of the group by.  This makes sense, since this is not an aggregate function.  But these functions are not allowed here.  So what to do, in order to get this analytic function to be called after the aggregations, aka in the outer query!?  Well create the logical column expression to “Use Existing logical columns as the source”!  But this will only work if you reference a metric logical column or a metric column with an aggregation rule applied, or you will get the same error and physical sql created.  Now by referencing a logical column which has an aggregation rule set will result in the following physical query:

select distinct cast(ROW_NUMBER() OVER (PARTITION BY D1.c1 - D1.c2 ORDER BY D1.c1 - D1.c2 asc) as  DOUBLE PRECISION  ) as c1,

     D1.c1 - D1.c2 as c2

from 

     (select sum(case  when T1595.METRIC_CODE = 'ACTUAL' then T1558.METRIC_VALUE end ) as c1,

               sum(case  when T1595.METRIC_CODE = 'BUDGET' then T1558.METRIC_VALUE end ) as c2

          from 

               ZFCT T1558 ,

               ZMETRIC_DIM T1595 

          where  ( T1558.METRIC_WID = T1595.WID and (T1595.METRIC_CODE in ('ACTUAL', 'BUDGET')) ) 

     ) D1

order by c1

Since we are referencing a logical column with an aggregation rule set, it makes sense it is created this way.  The aggregation needs to be applied first, since you specified this aggregation column as a bind param.  Then the EVALUATE can be performed afterwards or in our case within the outer query which is what we want.

Lesson learned, when using advanced functionality or for that matter anything look at the physical query created and make sure that is what you want and intended when working with RPD metadata.