Friday, December 7, 2007

OBIEE EVALUATE Function

In the documentation for OBIEE 10.3.3.1 you find a very small description of the EVALUATE function:


6.1 EVALUATE Function
This function is intended for scalar and analytic
calculations.
Syntax: EVALUATE('DB_Function(%1)', {
Comma separated Expression})
Example: SELECT
e.lastname,sales.revenue,EVALUATE('dense_rank()
over(order by %1
)',sales.revenue) FROM sales s, employee e;

As often in practice the real world is more complex:
Good practice is to tell the EVALUATE function which type of return value it can expect:
EVALUATE('DB_Function(%1)' as returntype, {Comma separated Expression}). If you omit this it might sometimes complain that it can't concatenated values from a different type.

Another trap is when you use columns from several tables for the input variables, be sure that they have a direct join. Else OBIEE will try to join them underwater which can lead to unwanted Cartesian products.

3 comments:

Raghav Venkat said...

Can You Please Explain More on these EVALUATE functions like where it can be used, predicates and aggregates.

Thanks

John Minkjan said...

Hi Raghev,

The EVALUATE and EVALUATE_AGGR functions can be used to execute a function on the underlying database. These can be on a rowlevel (SIN(COLUMN_X)) and on a aggregate level (SUM(COLUMN_X) over (COLUMN_Y)) The syntax is completly shipped to the underlying database and is database specific.

regards

John

Anonymous said...

How I can see EVALUATE Function start from Oracle BI version 10.1.3.3.
In this case EVALUATE Function not use in Oracle BI SE One 10.1.3.2.1. Is it true???