OBIEE - Evaluate - Embedded DB Functions

This Evaluate functions (since version 10.1.3.3) enables users and administrators to create more powerful reports by directly calling Database functions from:

• either Oracle BI Answers
• or by using a Logical column (in the Logical Table source) within the Metadata (repository).

The following new BI Server database functions are supported

This feature is restricted to support SQL sources. MDX or MOLAP data sources are not supported with an exception for Essbase.

The escape character is the quote.

Example: The regular expressions of oracle contain the pattern expression in quote (in the code below '^[A-Z]+').

To prevent a syntax error, you must add a quote before your quotes such as:

EVALUATE('REGEXP_SUBSTR(%1,''^[A-Z]+'')', MyTable.MyColumn)</wrap>

If you don't do it, you can get this error:

[nQSError: 27002] Near <^>: Syntax error [nQSError: 26012] .

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;

This function is intended for aggregate or analytics functions.

Syntax:

EVALUATE_AGGR('DB_Aggregate_Function(%1)', <comma separated Expression)

Example with the Listagg function in a logical SQL (WM_CONCAT works also) :

SELECT ORDER.nummer, sales.qtysold, EVALUATE_AGGR('listagg(%1)', sales.item) FROM SnowFlakeSales;

This function is intended for functions with a return type of boolean.

Syntax:

EVALUATE_PREDICATE('DB_Function(%1)', <comma separated Expression)

Example:

SELECT year, Sales AS DOUBLE,CAST(EVALUATE('OLAP_EXPRESSION(%1,''LAG(units_cube_sales, 1, time, 
time LEVELREL time_levelrel)'')', OLAP_CALC) AS DOUBLE) 
FROM "Global".Time, "Global"."Facts - sales" 
WHERE EVALUATE_PREDICATE('OLAP_CONDITION(%1, ''LIMIT time KEEP ''''1'''', ''''2'''', ''''3'''', ''''4'''' '') =1', OLAP_CALC) 
ORDER BY year;

Odbc driver returned an error (SQLExecDirectW).
  Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 2057371920. [NQODBC] [SQL_STATE: HY000] 
[nQSError: 10058] A general error has occurred. 
[nQSError: 42015] Cannot function ship the following expression: Evaluate(....

This error comes from the fact that OBI is unable to know wich aggregation rule it must apply when you ask for a total in a view. You have to define the answer aggregation rule in the column formula of in the view to resolve this error.

• Whit Paper OBIEE server and Embedded DB Functions
• New Features 10.3.3.3 documentation
• Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase Functions