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

Oracle Analytics 如果使用多維資料庫作為資料來源，在設計上另有一些可能對效能有重大影響的考量。

請記住，效能提升設計解決方案會因使用案例而異。本主題不包括最佳應用或一體適用的解決方案。而是，提供調整方法與技巧，協助使用者提升分析和所產生程式碼的效能。

複查選項、分析 Oracle Analytics 查詢日誌，以及選擇最適用於使用案例的最佳解決方案，完全由開發團隊負責。

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

方法

我們建議您完成以下作業，以提升效能。這些作業的一項先決條件為務必瞭解多維表示式 (MDX) 查詢結構，以及 Oracle Analytics 所產生的查詢日誌。主要作業如下：

  • 簡化產生的 MDX。
  • 減少產生的 MDX 查詢數目。
  • 確定在 MDX 中套用最佳篩選和選擇。
  • 協同資料庫管理員 (DBA) 調整多維資料庫端的效能，並且檢查來源資料庫的效能為何仍然不佳。
  • 根據 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 查詢及提升效能。但是，篩選的效率會受到影響，亦即查詢可能會傳回一些並非必要的記錄。

如以上所述，每種使用案例都是獨特的。其主要目標為簡化 MDX 查詢，同時套用最佳篩選和選擇項目。

使用 CASE 敘述句功能的限制如下：

  • CASE 敘述句若未結合多個成員，則必須在查詢和視觀表中包含敘述句中使用的基本資料欄，且為隱藏的個別資料欄。
  • CASE 敘述句若結合多個成員，在視觀表中包含基本資料欄會影響聚總層級。若為以下情況：
    • 計量的聚總規則如果不是外部聚總，查詢中絕對不可包含基本資料欄。
    • 如果計量的聚總規則是外部聚總，則必須在查詢中包含基本資料欄，但視觀表中不可包含。您必須將計量的聚總規則從預設值變更為簡單的內部聚總規則 (SUM、MAX、MIN)。這只有在使用內部聚總規則結合成員並提供正確結果時才適用。

FILTER 函數

與 CASE 敘述句不同，FILTER 函數可傳送至資料庫並執行。

  • 在報表公式中使用 FILTER 函數的主要優點為，會在 MDX 查詢中套用選擇項目，因而在資料庫計算及擷取的資料量會減少。
  • 使用 FILTER 函數的主要缺點為，執行的 MDX 查詢數目會增加。依照預設，會對每個使用的 FILTER 函數執行一個查詢。

請記住，每一個使用案例都是獨特的。其目標為簡化 MDX 查詢，同時套用最佳篩選和選擇項目。

CASE vs FILTER 案例

讓我們接著看相同的案例在使用 CASE vs 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 進行處理。以下是產生的 MDX：

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]

Filter 敘述句案例

您可以選擇對利潤度量使用篩選，只擷取必要的 LOB 成員。在本案例中，您建立了 3 個度量並套用了相應的篩選。

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

在本案例中，共產生 3 個查詢 (每個篩選一個)，而您發生了效能問題。

查詢 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​

在本案例中，我們建議您移除 CASE 敘述句，然後重新命名資料庫中的成員，或建立別名。

如果您使用多維資料庫作為資料來源，您在 Oracle Analytics 有可能會遇到效能問題，導致無法產生最佳化的 MDX 查詢。您可以透過修改設計，改善 Oracle Analytics 所產生的 MDX 查詢。這會產生巨大的影響，不只是對報表的效能，對於資料庫中所使用資源的數量也有所影響。在 MDX 使用支援或不支援的函數時都要非常小心，因為這對產生的 MDX 查詢和效能都會有極大的影響。