Oracle BIEE EVALUATE系列使用数据库函数

  Oracle BIEE11中提供了EVALUATE这个函数，扩展了可以使用oracle函数的功能.
以前看过晃晃悠悠写的EVALUATE的用法。我想补充另外用法。

  EVALUATE系列不只有BIEE Admin tool 或者answer中提到的EVALUATE 和 EVALUATE_AGGR两种函数事实上还有另外的两个

    EVALUATE:

    EVALUATE_AGGR:

    EVALUATE_ANALYTIC:

    EVALUATE_PREDICATE:

    这两天在工作中遇到这样一个需求，因为分析数据需要涉及到排序，而且排序的规则不只有一个不是通常的rank()就可以解决，且通常会变，面对这种情况，排序存在数据库中不是很好的办法, 这时候我想起来有一个叫做EVALUATE的函数。

通过 EVALUATE 结合oracle数据库中的 rank() over(partion group by ... order...)来实现数据排序。

EVALUATE ('rank() over (PARTITION BY %1,%2 ORDER BY %3 desc)' as INT, "D1 Time"."Day Of Year Biee","D2 Platform"."Platform",CASE WHEN "F3 Bug Report"."崩溃IP数">0 THEN "F1 Device Useage"."IP数day ver" ELSE 0 END)

上面的语句出来了,排序也会按照时间和平台给我排了，但是还遇到一个问题在表格中可以显示排序号正确.但是在出排名曲线图时总时会出现数据显示不出来的情况.

接着换成 EVALUATE_ANALYTIC，

EVALUATE_ANALYTIC ('rank() over (PARTITION BY %1,%2 ORDER BY %3 desc)' as INT, "D1 Time"."Day Of Year Biee","D2 Platform"."Platform",CASE WHEN "F3 Bug Report"."崩溃IP数">0 THEN "F1 Device Useage"."IP数day ver" ELSE 0 END)

如表格


同时,曲线图可以正常显示了。


附：
EVALUATE

此函数将包含作为参数的可选引用列的指定数据库函数传递给后端数据源，以供求值时使用。此函数专门用于标量计算；当要使用由基础数据源识别但 Oracle BI Server 不支持的专用数据库函数时，此函数会十分有用。

嵌入式数据库函数可能需要一个或多个列。这些列将由该函数中的 %1 到 %N 引用。实际列必须列在该函数后面。

默认情况下禁用了使用 EVALUATE 的功能。要启用对此函数的支持，请在 NQSConfig.INI 中更改 EVALUATE_SUPPORT_LEVEL 参数。有关详细信息，请参阅 Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition。

语法

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

其中：

db_function 是由基础数据源识别的任何有效数据库函数。

data_type 是可选参数，它指定返回结果的数据类型。每当无法从输入参数可靠地预测返回数据类型时，就可以使用此参数。但是，请勿使用此参数进行类型转换；如果该函数需要返回特定数据类型，请添加显式转换。如果特定于数据库的函数用于生成不必返回到 Oracle BI Server 的中间结果但该函数的返回类型不受 Oracle BI Server 支持，则通常可以忽略此参数。

column1 到 columnN 是可选参数，这是以逗号分隔的列的列表。

示例

此示例显示嵌入式数据库函数。

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

使用 EVALUATE_AGGREGATE 和 EVALUATE 来利用唯一 Essbase 函数的示例

以下示例使用了 EVALUATE_AGGREGATE 和 EVALUATE 函数。请注意，这些表达式应用于引用了物理立方的逻辑表源中的列。请使用 EVALUATE_AGGREGATE 实施定制聚合。例如，您可能希望将总体区域利润与区域中排名前三名的产品的利润进行比较。您可以定义新度量来表示前三名产品的利润，从而生成以下逻辑 SQL 语句：

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

Oracle BI Server 为定制聚合生成以下表达式：

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

在投影维上使用 EVALUATE 函数以实施聚合后计算的标量函数。如果 EVALUATE 的定义对不在查询中的维 (或属性) 进行了显式引用，则该函数可能会更改查询的粒度。

例如，如果您希望查看某个区域中按销售额排名的前 5 个产品的利润，则在创建适用的度量之后，生成的逻辑 SQL 语句如下所示

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

Oracle BI Server 生成以下表达式来检索前 5 个产品：

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

EVALUATE_ANALYTIC

此函数将包含作为参数的可选引用列的指定数据库分析函数传递给后端数据源，以供求值时使用。

嵌入式数据库函数可能需要一个或多个列。这些列将由该函数中的 %1 到 %N 引用。实际列必须列在该函数后面。

默认情况下禁用了使用 EVALUATE_ANALYTIC 的功能。要启用对此函数的支持，请在 NQSConfig.INI 中更改 EVALUATE_SUPPORT_LEVEL 参数。有关详细信息，请参阅 Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition。

语法

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

其中：

db_function 是由基础数据源识别的任何有效数据库分析函数。

data_type 是可选参数，它指定返回结果的数据类型。每当无法从输入参数可靠地预测返回数据类型时，就可以使用此参数。但是，请勿使用此参数进行类型转换；如果该函数需要返回特定数据类型，请添加显式转换。如果特定于数据库的分析函数用于生成不必返回到 Oracle BI Server 的中间结果但该函数的返回类型不受 Oracle BI Server 支持，则通常可以忽略此参数。

column1 到 columnN 是可选参数，这是以逗号分隔的列的列表。

示例

此示例显示嵌入式数据库分析函数。

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

如果前面的示例需要返回双精度类型，则应添加显式转换，如下所示：

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

EVALUATE_AGGR

此函数将包含作为参数的可选引用列的指定数据库函数传递给后端数据源，以供求值时使用。此函数专门用于带有 GROUP BY 子句的聚合函数。

嵌入式数据库函数可能需要一个或多个列。这些列将由该函数中的 %1 到 %N 引用。实际列必须列在该函数后面。

默认情况下禁用了使用 EVALUATE_AGGR 的功能。要启用对此函数的支持，请在 NQSConfig.INI 中更改 EVALUATE_SUPPORT_LEVEL 参数。有关详细信息，请参阅 Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition。

语法

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

其中：

db_agg_function 是由基础数据源识别的任何有效聚合数据库函数。

data_type 是可选参数，它指定返回结果的数据类型。每当无法从输入参数可靠地预测返回数据类型时，就可以使用此参数。但是，请勿使用此参数进行类型转换；如果该函数需要返回特定数据类型，请添加显式转换。如果特定于数据库的函数用于生成不必返回到 Oracle BI Server 的中间结果但该函数的返回类型不受 Oracle BI Server 支持，则通常可以忽略此参数。

column1 到 columnN 是可选参数，这是以逗号分隔的列的列表。

示例

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

EVALUATE_PREDICATE

此函数将包含作为参数的可选引用列的指定数据库函数传递给后端数据源，以供求值时使用。此函数专门用于带有布尔返回类型的函数。

嵌入式数据库函数可能需要一个或多个列。这些列将由该函数中的 %1 到 %N 引用。实际列必须列在该函数后面。

请注意，不支持将 EVALUATE_PREDICATE 用于 Essbase 数据源。

默认情况下禁用了使用 EVALUATE_PREDICATE 的功能。要启用对此函数的支持，请在 NQSConfig.INI 中更改 EVALUATE_SUPPORT_LEVEL 参数。有关详细信息，请参阅 Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition。

语法

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

其中：

db_function 是由基础数据源识别的、具有布尔返回类型的任何有效数据库函数。

column1 到 columnN 是可选参数，这是以逗号分隔的列的列表。

如果希望对数据库函数建立模型以进行比较，则不应使用 EVALUATE_PREDICATE。请改为使用 EVALUATE 并在函数外部进行比较。例如，不要使用 EVALUATE_PREDICATE，如下所示：

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

请改为使用 EVALUATE，如下所示：

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

示例

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;