优化多维数据库查询的性能

当您在 Oracle Analytics 中使用多维数据库作为数据源时，您可能会遇到性能问题，这些问题会导致生成的多维表达式 (multidimensional expression, MDX) 查询不是最优的。

您可以通过修改设计来改进 Oracle Analytics 生成的 MDX 查询。这不仅会对报表性能而且还会对数据库中使用的资源量产生巨大影响。如何使用支持的函数或不支持的函数会对生成的 MDX 查询产生巨大影响，进而影响性能。

由于每个用例都是独特的，因此您的开发团队应检查选项、分析 Oracle Analytics 查询日志以及选择最适合您的用例的解决方案。

本主题不解决因基础结构（例如网络、浏览器或报表演示）导致的性能问题。

方法

Oracle 建议您执行以下任务来提高性能。务必了解 MDX 查询结构，以及 Oracle Analytics 生成的查询日志。

简化生成的 MDX 查询。
减少生成的 MDX 查询数量。
确保在 MDX 查询中应用最佳筛选器和选择。
以数据库管理员 (database administrator, DBA) 身份对多维数据库进行性能优化，并确认源数据库性能仍不佳的原因。
根据数据库管理员的反馈修改分析。

选择步骤优化

优化选择步骤时，可以简化 MDX 查询，减少生成的 MDX 查询数量，以及提高性能。

下图显示了已优化与未优化选择步骤的比较示例。

GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg 的说明如下
.jpg 的说明

CASE 语句

MDX 查询不支持 CASE 语句功能，必须始终在 Oracle Analytics 中应用此功能。此部分介绍与 CASE 语句相关的逻辑，其适用于 MDX 查询不支持的大多数函数（if null 等）。

使用 CASE 语句时存在一些优势和缺点。当您在报表公式中包含 CASE 语句时，它们并不包含在 MDX 查询中。这可以简化 MDX 查询并提高性能。但代价是无法有效地进行筛选，这意味着查询返回的记录可能超过所需记录。

使用 CASE 语句功能时存在以下限制：

如果 CASE 语句未组合多个成员，则语句中使用的基础列应作为隐藏的单独列包含在查询和视图中。
如果 CASE 语句组合了多个成员，则基础列不能包含在视图中，并且不影响聚合级别。如果是这种情况：
- 如果度量的聚合规则不是外部聚合，则必须从查询中排除基础列。
- 如果度量的聚合规则是外部聚合，则基础列必须包含在查询中，但从视图中排除。必须将度量的聚合规则从默认规则更改为简单的内部聚合规则 (SUM, MAX, MIN)。这一点仅在内部聚合规则用于组合成员时才适用，并且可以提供正确结果。

FILTER 函数

与 CASE 语句功能不同，FILTER 函数可以传到数据库来执行。

在报表公式中使用 FILTER 函数的主要优点是在 MDX 查询中应用选择，因此计算的数据量和从数据库检索的数据量会减少。

使用 FILTER 函数的主要缺点是执行的 MDX 查询数量可能会增加。默认情况下，对于使用的每个 FILTER 函数执行一个查询。

CASE 与 FILTER 示例

在此示例中，用户请求一个按季度和所选产品 SKU 显示利润的报表。此外，SKU 分组到 12 个类别中。为类别 "Other Cola" 分配了以下 LOB 产品：Cola、Diet Cola 和 Shared Diet Cola。

GUID-7198F143-54E6-4A48-9579-96624936A94D-default.jpg 的说明如下
.jpg 的说明

CASE 语句逻辑查询如下：

SELECT

   0 s_0,

   CASE when XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Cola','Diet Cola','Shared Diet Cola') THEN 'Other Cola' ELSE XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" END s_1,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_2,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_3,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_4,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_5,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_6,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_7,

   XSA('Admin'.'Sample.BasicPM')."Product"."Category" s_8,

   XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" s_9,

   XSA('Admin'.'Sample.BasicPM')."Year"."Quarter" s_10,

   XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" s_11

FROM XSA('Admin'.'Sample.BasicPM')

ORDER BY 8 ASC NULLS LAST, 11 ASC NULLS LAST, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 7 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 9 ASC NULLS LAST, 3 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

没有基于 CASE 语句的分组。生成一个简单的 MDX 查询，CASE 语句由 Oracle Analytics 处理：

With 
  set [_Product3]  as 'Descendants([Product], [Product].Generations(3), leaves)'
  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'
select 
  { [Measures].[Profit]
  } on columns,
  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties GEN_NUMBER, [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows 
from [Sample.Basic]

CASE 语句是在 BI Server 上执行的，这可以通过设置为 database 0:0,0 的数据库设置看到：

 RqList <<11777451>> [for database 0:0,0]
                            D1.c6 as c6 [for database 0:0,0],
                            D1.c4 as c4 [for database 0:0,0],
                            case  when D1.c7 in ([ 'Cola', 'Diet Cola', 'Shared Diet Cola'] ) then 'Other Cola' else D1.c7 end  as c2 [for database 0:0,0],
                            D1.c5 as c5 [for database 0:0,0],
                            D1.c3 as c3 [for database 0:0,0],
                            D1.c1 as c1 [for database 0:0,0],
                            D1.c7 as c7 [for database 0:0,0],
                            D1.c8 as c8 [for database 0:0,0]

或者，您可以对利润度量使用筛选器以仅检索所需的 LOB 成员。在这种情况下，创建三个度量并应用对应的筛选器。

FILTER 语句逻辑查询如下：

SELECT

   0 s_0,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_1,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_2,

   DESCRIPTOR_IDOF(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_3,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Category") s_4,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU") s_5,

   SORTKEY(XSA('Admin'.'Sample.BasicPM')."Year"."Quarter") s_6,

   XSA('Admin'.'Sample.BasicPM')."Product"."Category" s_7,

   XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" s_8,

   XSA('Admin'.'Sample.BasicPM')."Year"."Quarter" s_9,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Cola','Diet Cola','Shared Diet Cola')) s_10,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('Sasprilla','Birch Beer','Dark Cream')) s_11,

   FILTER(XSA('Admin'.'Sample.BasicPM')."Basic"."Profit" USING XSA('Admin'.'Sample.BasicPM')."Product"."Product SKU" in ('xxxxx')) s_12

FROM XSA('Admin'.'Sample.BasicPM')

ORDER BY 7 ASC NULLS LAST, 10 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 9 ASC NULLS LAST, 3 ASC NULLS LAST, 5 ASC NULLS LAST, 8 ASC NULLS LAST, 2 ASC NULLS LAST

FETCH FIRST 125001 ROWS ONLY

在这种情况下，生成三个查询（每个筛选器一个），并且遇到性能问题。

查询 1：

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "xxxxx")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

]]

查询 2：

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Birch Beer") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Dark Cream") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Sasprilla")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

]]

查询 3：

With

  set [_Product3]  as 'Filter([Product].Generations(3).members, ((IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Cola") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Diet Cola") OR (IIF(IsValid([Product].CurrentMember.MEMBER_ALIAS), [Product].CurrentMember.MEMBER_ALIAS, [Product].CurrentMember.MEMBER_Name) = "Shared Diet Cola")))'

  set [_Year2]  as 'Descendants([Year], [Year].Generations(2), leaves)'

select

  { [Measures].[Profit]

  } on columns,

  NON EMPTY {crossjoin({[_Year2]},{[_Product3]})} properties MEMBER_NAME, GEN_NUMBER, property_expr([Product], [MEMBER_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category_Null_Alias_Replacement"), property_expr([Product], [Default], Ancestor(currentaxismember(), [Product].Generations(2)), "Category"), property_expr([Product], [MEMBER_UNIQUE_NAME], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Member Key"), property_expr([Product], [Memnor], Ancestor(currentaxismember(), [Product].Generations(2)), "Category - Memnor"), [Product].[MEMBER_UNIQUE_NAME], [Product].[Memnor], [Year].[MEMBER_UNIQUE_NAME], [Year].[Memnor] on rows

from [Sample.Basic]

应用产品筛选器示例

一个更好的方法是在包含单个度量列而不含筛选器的报表中包含产品列。然后创建一个包含所需产品的筛选器。如果要将产品分组到不同的类别中，可使用 CASE 语句。在这种情况下，将会使用筛选的行生成单个 MDX 查询，并且即使 Oracle Analytics 应用了 CASE 语句，它也将使用一部分数据，而不是所有记录。

下面是另一个由 CASE 语句导致出现性能问题的情况。

开发人员应用 CASE 语句重命名品牌，并且仪表盘提示允许用户选择品牌。

GUID-56356AA9-2AF6-4A67-8ADD-FC4F7F70306C-default.jpg 的说明如下
.jpg 的说明

GUID-E63719C8-9936-412B-8228-F20E8F048C46-default.jpg 的说明如下
.jpg 的说明

由于 MDX 不支持 CASE 语句，因此不能在 MDX 查询中应用基于 Brand2 的筛选器。将会选择所有品牌，这不是优化的。

GUID-6BE1F274-8257-4E31-8D42-406357A07B2A-default.jpg 的说明如下
.jpg 的说明

在这种情况下，Oracle 建议删除 CASE 语句，并重命名数据库中的成员或创建别名。