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:

Anonymous 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???