12 March 2012

Database (Evaluate) Function


Database Functions (EVALUATE)

Users and administrators can create requests 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. Key uses for these functions include the ability to pass through expressions to get advanced calculations, as well as the ability to access custom written functions or procedures on the underlying database.

Support for database functions does not currently extend across all multidimensional sources. Also, you cannot use these functions with XML data sources.

By default, support for the EVALUATE family of database functions is disabled. You must change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI to enable support for the EVALUATE* functions. 


# EVALUATE_SUPPORT_LEVEL:
# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user. 
# other: evaluate is not supported if the value is anything else.

EVALUATE_SUPPORT_LEVEL = 2;

Setting EVALUATE_SUPPORT_LEVEL to 2 will make all users use it properly.

Functions include:

  • EVALUATE
  • EVALUATE_ANALYTIC
  • EVALUATE_AGGR
  • EVALUATE_PREDICATE

EVALUATE


This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for scalar calculations, and is useful when you want to use a specialized database function that is not supported by the Oracle BI Server, but that is understood by the underlying data source.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax

EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])

Where:

db_function is any valid database function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Examples

This example shows an embedded database function.

SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees

Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase Functions

The following examples use the EVALUATE_AGGREGATE and EVALUATE functions. Note that expressions are applied to columns in the logical table source that refers to the physical cube.Use EVALUATE_AGGREGATE to implement custom aggregations. For example, you may want to compare overall regional profit to profits for the top three products in the region. You can define a new measure to represent the profits for top three products resulting in the Logical SQL statement:

SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)',
Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic

The Oracle BI Server generates the following expression for the custom aggregation:

member [Measures].[MS1] AS 'SUM(Topcount([Product].Generations(6).members,3,[Measures].[Profit]),[Measures].[Profit])'

Use the EVALUATE function on projected dimensions to implement scalar functions that are computed post-aggregation. EVALUATE may change the grain of the query, if its definition makes explicit references to dimensions (or attributes) that are not in the query.

For example, if you would like to see the Profits for the top five products ranked by Sales sold in a Region, after creating the applicable measure, the resulting Logical SQL statement is as follows

SELECT Region, EVALUATE('TopCount(%1.members, 5, %2)' as VARCHAR(20), Products, Sales), Profits FROM SampleBasic

The Oracle BI Server generates the following expression to retrieve the top five products:

set [Evaluate0] as '{Topcount([Product].Generations(6).members,5,[Measures].[Sales]) }'



EVALUATE_ANALYTIC


This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax

EVALUATE_ANALYTIC('db_function(%1...%N)' [AS data_type] [, column1, columnN])

Where:

db_function is any valid database analytic function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific analytic function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Examples

This example shows an embedded database analytic function.

EVALUATE_ANALYTIC('dense_rank() over(order by %1 )' AS INT,sales.revenue)

If the preceding example needs to return a double, then an explicit cast should be added, as follows:

CAST(EVALUATE_ANALYTIC('Rank(%1.dimension.currentmember, %2.members)',
"Foodmart93"."Time"."Month" as Double)



EVALUATE_AGGR


This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a GROUP BY clause.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Syntax

EVALUATE_AGGR('db_agg_function(%1...%N)' [AS data_type] [, column1, columnN)

Where:

db_agg_function is any valid aggregate database function understood by the underlying data source.

data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.

column1 through columnN is an optional, comma-delimited list of columns.

Example

EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)



EVALUATE_PREDICATE


This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for functions with a return type of Boolean.

The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.

Note that EVALUATE_PREDICATE is not supported for use with Essbase data sources.

Syntax

EVALUATE_PREDICATE('db_function(%1...%N)', [, column1, columnN)

Where:

db_function is any valid database function with a return type of Boolean that is understood by the underlying data source.

column1 through columnN is an optional, comma-delimited list of columns.

If you want to model a database function for comparison purposes, you should not use EVALUATE_PREDICATE. Instead, use EVALUATE and put the comparison outside the function. For example, do not use EVALUATE_PREDICATE as follows:

EVALUATE_PREDICATE('dense_rank() over (order by 1% ) < 5', sales.revenue)

Instead, use EVALUATE, as follows:

EVALUATE('dense_rank() over (order by 1% ) ', sales.revenue) < 5

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;