調整多維資料庫查詢的效能

如果您在 Oracle Analytics 中使用多維資料庫作為資料來源，可能會遇到效能問題，導致無法產生最佳化的多維表示式 (MDX) 查詢。

您可以透過修改設計，改善 Oracle Analytics 所產生的 MDX 查詢。這會產生巨大的影響，不只是對報表的效能，對於資料庫中所使用資源的數量也有所影響。如何使用支援或不支援的函數對產生的 MDX 查詢會有極大的影響，進而影響效能。

由於每個使用案例不盡相同，您的開發團隊必須複查每個選項、分析 Oracle Analytics 查詢日誌，然後選擇最適合您使用案例的最佳解決方案。

本主題不涵蓋基礎架構所導致的效能問題，例如網路、瀏覽器或報表展示。

方法

Oracle 建議您進行以下作業來提升效能。請務必瞭解 MDX 查詢結構和 Oracle Analytics 所產生的查詢日誌。

  • 簡化產生的 MDX 查詢。
  • 減少產生的 MDX 查詢數目。
  • 確定在 MDX 查詢中套用最佳篩選和選擇。
  • 協同資料庫管理員 (DBA) 調整多維資料庫的效能，並且檢查來源資料庫的效能為何仍然不佳。
  • 根據 DBA 的意見修改分析。

選擇步驟最佳化

將選擇步驟最佳化後，可以簡化 MDX 查詢、減少產生的 MDX 查詢數目，並且提升效能。

下圖的範例顯示已最佳化和未最佳化選擇步驟的比較。

以下為 GUID-43E6F348-B14C-40DC-8C21-DA34DAE44344-default.jpg 的說明
.jpg​

CASE 敘述句

CASE 敘述句功能不受 MDX 查詢支援，而且必須一律在 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 伺服器上執行，因為資料庫設定設為 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 敘述句，然後重新命名資料庫中的成員，或建立別名。