使用經常性和非經常性數據表數據分割來優化非常大的Power BI數據模型

發行項
07/05/2024

本文說明如何使用經常性和非經常性數據表分割區來優化非常大的數據模型。數據分割提供將數據表的數據分割成離散子集的方法。分割區 不會直接在標準 Power BI 數據模型化工具中公開，但您可以在 Power BI Desktop 中設定累加式重新整理原則，利用進階數據分割方法。累加式重新整理依賴資料分割，如累加式重新整理和數據集的實時數據中所述，。不過，設定經常性和非經常性數據表分割超出累加式重新整理原則可以完成的工作，並假設熟悉一般數據表分割配置和 XMLA 型工具。

先決條件

由於此數據分割技術的相對複雜度，因此最適合具有下列領域經驗的進階使用者：

了解數據表分割概念、匯入模式分割、DirectQuery 模式，以及雙重模式的運作方式。
瞭解如何使用 XMLA 型工具建立混合式數據表。混合式數據表會使用一或多個匯入模式分割區，以及一個 DirectQuery 分割區。
瞭解您可以用來指定 DataCoverageDefinition的 DAX 函式需求。這是 DirectQuery 分割區的新屬性，可描述混合式數據表 DirectQuery 分割區所包含的數據，讓 Power BI 引擎可以在適當情況下從查詢處理中排除此分割區。排除 DirectQuery 分割區有助於避免不必要的數據源查詢，並改善 DAX 查詢處理的效能。
瞭解一般和有限數據表關聯性之間的差異。例如，如果您想要根據相關日期維度數據表的值定義事實數據表數據分割的數據涵蓋範圍，RELATED 函式會很有用。請記住，事實數據表分割是 DirectQuery 分割區，其與 RELATED 函式無法擷取值的日期數據表有有限關聯性的機會。在此案例中，RELATED 只有在日期維度數據表是雙重數據表時才能運作。日期數據表必須處於 DirectQuery 或雙重模式。它不能完全匯入。

請注意，錯誤定義的 DataCoverageDefinition 可能會導致錯誤的結果，因為Power BI可能會錯誤地排除查詢處理 DirectQuery 分割區。因此，請務必將結果與和沒有 DataCoverageDefinition 進行比較，以確保結果會加總。

何時使用經常性和非經常性數據表分割區

以下是經常性分割和冷分割區可協助微調混合式數據表以進行歷史分析的範例。假設您擁有非常龐大的數據源，而且已累積多年。主要用途是分析過去幾年的最新數據。有時候，您也會想要分析較舊的數據。也許你注意到最近銷售額逐年大幅增長。這曾經發生過嗎？這是銷售追蹤開始以來的最高銷售尖峰嗎？

若不支持經常性分割和冷分割區，這種歷史分析會要求您將所有歷史數據以及較新的數據匯入事實數據表。這充其量是資源使用效率不佳，因為主要分析甚至不會使用任何較舊的歷程記錄數據。最糟的是，數據量太大，甚至無法完全匯入。您必須將數據模型切換至 DirectQuery 模式，並接受與匯入模式相比的效能懲罰，或者您可以建置不同的模型，並強制使用者在報表之間切換。具有經常性和非經常性分割的混合式數據表可讓您選擇更好的選項。

如何使用經常性和非經常性數據表分割區

首先，使用最新數據的 經常性 匯入模式數據分割來設定銷售數據表，並將較舊的數據保留在冷DirectQuery 分割區中，如下圖所示 AdventureWorks 範例數據模型的 FactInternetSales 數據表。具有 OrderDateKey 大於或等於20200101的任何數據列，會透過經常性匯入模式分割區匯入數據模型。 OrderDateKey 小於20200101的數據列會透過冷 DirectQuery 分割來涵蓋。現在，Power BI 可以使用匯入模式快速傳遞主要使用案例，而且您不需要匯入大量您偶爾分析的歷史數據，因為 DirectQuery 分割區已涵蓋此內容。

Adventure Works 範例數據模型的 Fact Internet Sales 數據表螢幕快照。因特網銷售數據表隨即開啟，並顯示篩選的數據列。

如果您有 AdventureWorks 範例數據倉儲並想要遵循，以下是一般步驟：

建立資料集。使用 Power BI Desktop 建立 AdventureWorks 數據集和報表。以純 DirectQuery 模式包含所有數據表。然後，將數據表以外的所有數據表轉換成雙重模式。讓 FactInternetSales 數據表保持 DirectQuery 模式。
上傳資料集。使用 Power BI Premium 上裝載的工作區，並啟用 XMLA 端點以進行寫入作業。
更新相容性層級。在 SQL Server Management Studio 中，使用 AdventureWorks 數據集開啟工作區（SSMS）。以滑鼠右鍵按下 AdventureWorks 資料集>腳稿>[腳本資料庫][建立] 或 [取代]，然後選取 [[新增查詢編輯器] 視窗。將 compatibilityLevel 屬性設定為 1603 （或更高版本）。選取 [執行或按 F5。確認作業順利完成。

設定 FactInternetSales 資料表分割。以滑鼠右鍵按下 AdventureWorks 資料集>腳稿>[腳本資料庫][建立] 或 [取代]，然後選取 [[新增查詢編輯器] 視窗。將整個分割區區段取代為下列區段。請務必更新 Sql.Database 行，以指向您環境中的 AdventureWorksDW 資料庫。選取 [執行或按 F5。確認作業順利完成。

   "partitions": [ 
    { 
      "name": "FactInternetSales-DQ-Partition", 
      "mode": "directQuery", 
      "dataView": "full", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    }, 
    { 
      "name": "FactInternetSales-Import-Partition", 
      "mode": "import", 
      "source": { 
        "type": "m", 
        "expression": [ 
          "let", 
          "    Source = Sql.Database(\"demo.database.windows.net\", \"AdventureWorksDW\"),", 
          "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
          "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] >= 20200101)", 
          "in", 
          "    #\"Filtered Rows\"" 
        ] 
      } 
    } 
  ],

處理資料模型。在 Power BI 入口網站中，使用您的 AdventureWorks 數據集開啟工作區，然後執行數據集的隨選重新整理，以使用數據載入分割區。
確認報表顯示最近和歷程記錄數據。開啟您的 AdventureWorks，並確認報表能夠在 2020 年 1 月 1 日前後顯示銷售交易的結果，如下列螢幕快照所示。

兩個不同報表的螢幕快照。其中一個顯示 2020 年的數據，另一個顯示 2019 年的數據。

定義 DirectQuery 分割區的數據涵蓋範圍

此解決方案可順暢地在最近和歷程記錄的數據上運作。不過，根據預設，Power BI 會查詢所有數據表分割區，因為它不知道每個分割區涵蓋的數據。因此，即使 DirectQuery 分割區未涵蓋的年份，Power BI 仍會查詢 DirectQuery 分割區。匯入數據分割中隨時提供銷售數據，且 DirectQuery 分割區不會提供任何數據列，但此多餘的來源查詢仍會導致數據源的明顯負載，並導致 DAX 查詢處理延遲。若要避免這種多餘的來源查詢，請使用 DataCoverageDefinition。

如下列螢幕快照所示，Power BI 報表仍會將 2020 的數個不必要的 SQL 查詢傳送至數據源，因為每個視覺效果的 DAX 查詢都會讓 Power BI 查詢 DirectQuery 分割區。

DAX 查詢的螢幕快照。

藉由在 DirectQuery 分割區上設定 dataCoverageDefinition 屬性，如下列TMSL代碼段所示，即可避免這些 SQL 查詢。不過，請記住，套用或變更數據涵蓋範圍定義之後，您必須重新整理數據集。進程重新計算足以評估數據涵蓋範圍定義。如果您忘記此步驟，觸控數據分割的查詢會失敗，並出現錯誤訊息，指出數據表『[數據表名稱]『中 DQ 數據分割的 DataCoverageDefinition 尚未在最近的變更之後計算。它必須重新處理」。

        { 
          "name": "FactInternetSales-DQ-Partition", 
          "mode": "directQuery", 
          "dataView": "full", 
          "source": { 
            "type": "m", 
            "expression": [ 
              "let", 
              "    Source = Sql.Database(\"demopm.database.windows.net\", \"AdventureWorksDW2020\"),", 
              "    dbo_FactInternetSales = Source{[Schema=\"dbo\",Item=\"FactInternetSales\"]}[Data],", 
              "    #\"Filtered Rows\" = Table.SelectRows(dbo_FactInternetSales, each [OrderDateKey] < 20200101)", 
              "in", 
              "    #\"Filtered Rows\"" 
            ] 
          },  
"dataCoverageDefinition": {  
                  "description": "DQ partition with all sales from 2017, 2018, and 2019.",  
                  "expression": "RELATED('DimDate'[CalendarYear]) IN {2017,2018,2019}"  
                }  
        }

如先前所述，dataCoverageDefinition 屬性有助於消除不必要的數據源載入。它也會改善最近數據的分析效能，因為現在 Power BI 可以適當地從 DAX 查詢處理中排除 DirectQuery 數據分割。您可以針對單一值以及具有簡單 AND、OR 和 NOT 運算子的範圍定義簡單的資料涵蓋範圍表示式。您也可以使用 RELATED 函數，根據維度數據表中的數據行定義數據涵蓋範圍，該數據涵蓋範圍與事實數據表有一般關聯性。如果數據涵蓋範圍表達式使用維度數據表中的數據行，請確定維度數據表處於雙模式。您也可以根據事實數據表本身的數據行來定義資料涵蓋範圍。如需支持的作業，請參閱下表，分類為三個群組。 

類型	評論	例子
單一述詞（以值為基礎）	相等、不相等和 IN 運算子同時支援維度和事實數據表	RELATED（'Date'[Year]） = 2020 NOT RELATED（'Date'[Year]） = 2020 RELATED（'Date'[Year]） IN {2020， 2021， 2022} InternetSales'[SalesAmt] = CURRENCY（100.0） NOT InternetSales'[SalesAmt] = CURRENCY（100.0） InternetSales'[SalesAmt] IN {CURRENCY（100.0）， CURRENCY（200.0）}
單一述詞（範圍型）	可以是比較運算符，例如 >、<、>=、<= 要求維度數據表處於雙重模式	RELATED（'Date'[Year]） > 2020 RELATED（'Date'[Year]） <= 2020
多個述詞	相等、不相等和比較不支援 IN 運算子限制為雙模式中的單一維度數據表	RELATED（'Date'[Year]） > 2010 && RELATED（'Date'[Year]） > 2020 RELATED（'Date'[Year]） = 2020 && RELATED（'Date'[Calendar Quarter]） = 1 RELATED（'Date'[Year]） > 2020 && NOT RELATED（'Date'[Calendar Quarter]） = 1 RELATED（'Date'[Year]） > 2020 && RELATED（'Date'[Calendar Quarter]） < 3 RELATED（'Date'[Year]） > 2020 && （RELATED（'Date'[Calendar Quarter]） = 1 \|\|RELATED（'Date'[Calendar Quarter]） = 2）

DirectQuery 分割區上的 DataCoverageDefinition 屬性可讓您避免不必要的數據源查詢，以 DirectQuery 模式中的經常性分割和冷分割區為基礎，將最大的 Power BI 數據模型優化。此來源查詢縮減有助於在分析經常性數據時提升報表效能。它也有助於降低數據源的負載，如此一來，有助於將數據源的規模最大化。不過，請記住，使用 dataCoverageDefinition 屬性將數據模型優化仍然是進階案例。請確定您仔細確認結果。

考慮和限制

目前，DirectQuery 分割區上的 DataCoverageDefinition 屬性需要靜態值，例如 RELATED（'Date'[Year]） = 2020 或 RELATED（'Date'[Year]）中的 {2020， 2021， 2022}。不支持動態指派，例如 RELATED（'Date'[DateKey]） = TODAY（）。
使用即時數據的累加式重新整理不會利用 DataCoverageDefinition 屬性。如果您將數據涵蓋範圍定義套用至 DirectQuery （即時）分割區，累加式重新整理會在重新建立分割區時卸載數據涵蓋範圍定義。

共用方式為