Power BI Desktop 中的 DirectQuery 模型指南

  • 發行項

本文的目標是使用 Power BI Desktop 或 Power BI 服務 開發 Power BI DirectQuery 模型的數據模型。 其描述 DirectQuery 使用案例、限制和指引。 具體來說，本指南旨在協助您判斷 DirectQuery 是否為模型的適當模式，以及根據 DirectQuery 模型改善報表的效能。 本文適用於裝載於 Power BI 服務 或 Power BI 報表伺服器 中的 DirectQuery 模型。

本文並非旨在提供 DirectQuery 模型設計的完整討論。 如需簡介，請參閱 Power BI Desktop 中的 DirectQuery 模型一文。 如需更深入的討論，請參閱 SQL Server 2016 Analysis Services 白皮書中的 DirectQuery。 請記住，白皮書說明在 SQL Server Analysis Services 中使用 DirectQuery。 不過，大部分的內容仍然適用於Power BI DirectQuery模型。

注意

如需針對 Dataverse 使用 DirectQuery 儲存模式時的考慮，請參閱 Power Platform 的 Power BI 模型化指引。

本文不會直接涵蓋複合模型。 複合模型包含至少一個 DirectQuery 來源，而且可能更多。 本文所述的指引仍然與複合模型設計相關，至少部分相關。 不過，將 Import 數據表與 DirectQuery 數據表結合的影響並不屬於本文的範圍。 如需詳細資訊，請參閱 在Power BI Desktop 中使用複合模型。

請務必瞭解 DirectQuery 模型會對 Power BI 環境（Power BI 服務 或 Power BI 報表伺服器）以及基礎數據源施加不同的工作負載。 如果您判斷 DirectQuery 是適當的設計方法，建議您讓專案上的適當人員參與。 我們經常看到成功的 DirectQuery 模型部署是 IT 專業人員小組密切合作的結果。 小組通常由模型開發人員和源資料庫管理員所組成。 它也牽涉到數據架構設計人員，以及數據倉儲和數據倉儲和 ETL 開發人員。 通常，優化需要直接套用至數據源，以達到良好的效能結果。

優化數據源效能

關係資料庫來源可以透過數種方式進行優化，如下列點符清單所述。

注意

我們了解並非所有模型化者都有優化關係資料庫的許可權或技能。 雖然為 DirectQuery 模型準備數據是慣用的層，但您也可以在模型設計中達成某些優化，而不需修改源資料庫。 不過，最佳優化結果通常是藉由將優化套用至源資料庫來達成。

  • 確保數據完整性已完成： 維度類型數據表包含對應至事實類型數據表的唯一值（維度索引鍵）數據行特別重要。 事實類型維度數據行也務必包含有效的維度索引鍵值。 它們將允許設定更有效率的模型關聯性，以預期關聯性兩端的相符值。 當源數據缺乏完整性時，建議加入「未知」維度記錄，以有效修復數據。 例如，您可以將數據列新增至 Product 資料表來代表未知的產品，然後指派超出範圍的索引鍵，例如 -1。 如果 Sales 數據表中的數據列包含遺漏的產品索引鍵值，請以 -1 取代它們。 它可確保每個 Sales 產品索引鍵值在 Product 數據表中都有對應的數據列。

  • 新增索引： 在數據表或檢視表上定義適當的索引，以支援有效率地擷取預期的報表視覺效果篩選和群組數據。 如需 SQL Server、Azure SQL 資料庫 或 Azure Synapse Analytics（先前稱為 SQL 數據倉儲）來源，請參閱 SQL Server 索引架構和設計指南，以取得索引設計指引的實用資訊。 如需 SQL Server 或 Azure SQL 資料庫 揮發性來源，請參閱開始使用數據行存放區以進行即時作業分析。

  • 設計分散式數據表： 針對使用大量平行處理 （MPP） 架構的 Azure Synapse Analytics（先前稱為 SQL 數據倉儲）來源，請考慮將大型事實類型數據表設定為哈希分散式數據表，以及維度類型數據表，以在所有計算節點上複寫。 如需詳細資訊，請參閱在 Azure Synapse Analytics 中設計分散式數據表的指引（先前稱為 SQL 數據倉儲）。

  • 請確定必要的數據轉換已具體化： 針對 SQL Server 關係資料庫來源（和其他關係資料庫來源），計算數據行可以加入數據表。 這些數據行是以表達式為基礎，例如 Quantity 乘以 UnitPrice。 計算數據行可以保存（具體化），就像一般數據行一樣，有時可以編製索引。 如需詳細資訊，請參閱 計算資料行的索引。

    也請考慮索引檢視表，這些檢視表可以在較高的粒紋上預先匯總事實數據表數據。 例如，如果 Sales 資料表將資料儲存在訂單明細層級，您可以建立檢視來摘要此數據。 檢視可以根據 SELECT 語句，依日期（在月份層級）、客戶、產品及摘要來分組 Sales 數據表數據，例如銷售、數量等。然後可以編製檢視的索引。 如需 SQL Server 或 Azure SQL 資料庫 來源，請參閱建立索引檢視表。

  • 具體化日期數據表： 常見的模型需求包括新增日期數據表以支援以時間為基礎的篩選。 若要支持組織中已知的以時間為基礎的篩選，請在源資料庫中建立數據表，並確保其載入的日期範圍包含事實數據表日期。 也請確定它包含有用時間週期的數據行，例如年份、季、月、周等。

優化模型設計

DirectQuery 模型可以透過多種方式進行優化，如下列點符清單所述。

  • 避免複雜的 Power Query 查詢： 可藉由移除 Power Query 查詢套用任何轉換的需求，來達成有效率的模型設計。 這表示每個查詢都會對應至單一關係資料庫源數據表或檢視表。 您可以選取 [檢視原生查詢] 選項，預覽已套用 Power Query 步驟的實際 SQL 查詢 語句表示法。

    Power BI Desktop 的螢幕快照，其中顯示 [套用的步驟] 底下的 [檢視原生查詢] 選項。

    Power BI Desktop 的螢幕快照，其中顯示 [原生查詢] 視窗。查詢語句會聯結兩個源數據表。

  • 檢查計算結果列和數據類型變更的使用： DirectQuery 模型支援新增計算和 Power Query 步驟來轉換數據類型。 不過，在可能的情況下，透過具體化轉換會導致關係資料庫來源取得更好的效能。

  • 請勿使用 Power Query 相對日期篩選： 可以在 Power Query 查詢中定義相對日期篩選。 例如，若要擷取至去年建立的銷售訂單（相對於今天的日期）。 這種類型的篩選會轉譯成效率不佳的原生查詢，如下所示：

    …
from [dbo].[Sales] as [_]
where [_].[OrderDate] >= convert(datetime2, '2018-01-01 00:00:00') and [_].[OrderDate] < convert(datetime2, '2019-01-01 00:00:00'))

    更好的設計方法是在日期數據表中包含相對時間數據行。 這些數據行會儲存相對於目前日期的位移值。 例如，在 RelativeYear 數據行中，零值代表目前年份、-1 代表上一年等。最好是，RelativeYear 數據行會在日期數據表中具體化。 雖然效率較低，但也可以根據使用 TODAY 和 DATE DAX 函數的表達式，將它新增為模型導出數據行。

  • 讓量值保持簡單： 至少一開始，建議將量值限製為簡單的匯總。 聚合函數包括 SUM、COUNT、MIN、MAX 和 AVERAGE。 然後，如果量值有足夠的回應，您可以實驗更複雜的量值，但請注意每個量值的效能。 雖然 CALCULATE DAX 函式可以用來產生複雜的量值表示式來操作篩選內容，但是它們會產生效能不佳的昂貴原生查詢。

  • 避免計算結果列的關聯性： 模型關聯性只能將一個數據表中的單一數據行與不同數據表中的單一數據行產生關聯。 不過，有時候必須使用多個數據行來關聯數據表。 例如， Sales 和 Geography 數據表是由兩個數據行相關： CountryRegion 和 City。 若要建立數據表之間的關聯性，需要單一數據行，而且在 Geography 數據表中，數據行必須包含唯一值。 將國家/地區和城市與連字元分隔符串連，可能會達到此結果。

    您可以使用Power Query 自訂資料行，或在模型中建立結合的數據行做為匯出數據行。 不過，應該避免，因為計算表達式會內嵌到來源查詢中。 它不僅效率不佳，而且通常會防止使用索引。 相反地，在關係資料庫來源中新增具體化數據行，並考慮編製索引。 您也可以考慮將 Surrogate 索引鍵數據行新增至維度類型數據表，這是關係型數據倉儲設計中的常見作法。

    本指南有一個例外狀況，它涉及使用 COMBINEVALUES DAX 函式。 此函式的目的是支援多數據行模型關聯性。 與其產生關聯性的表達式，而是會產生多數據行 SQL 聯結述詞。

  • 避免「唯一標識碼」數據行的關聯性： Power BI 原生不支援唯一標識碼 （GUID） 數據類型。 定義此類型數據行之間的關聯性時，Power BI 會產生包含轉換之聯結的來源查詢。 此查詢時間數據轉換通常會導致效能不佳。 在優化此案例之前，唯一的因應措施是具體化基礎資料庫中替代數據類型的數據行。

  • 隱藏關聯性的單側數據行： 應該隱藏關聯性的單側數據行。 （通常是維度類型數據表的主鍵數據行。隱藏時，它無法在 [ 字段 ] 窗格中使用，因此無法用來設定視覺效果。 如果依數據行值分組或篩選報表很有用，多端數據行仍可保持可見。 例如，假設有 Sales 與 Product 數據表之間有關聯性的模型。 關聯性數據行包含產品 SKU （庫存單位） 值。 如果產品 SKU 必須新增至視覺效果，它應該只會顯示在 Sales 數據表中。 當此數據行用來篩選或群組視覺效果時，Power BI 會產生不需要聯結 Sales 和 Product 數據表的查詢。

  • 設定關聯性以強制執行完整性：DirectQuery 關聯性的假設引用完整性屬性會決定 Power BI 是否使用內部聯結而非外部聯結來產生來源查詢。 它通常會改善查詢效能，不過它確實取決於關係資料庫來源的詳細數據。 如需詳細資訊，請參閱 Power BI Desktop 中的採用參考完整性設定。

  • 避免使用雙向關聯性篩選： 使用雙向關聯性篩選可能會導致查詢語句效能不佳。 只有在必要時才使用此關聯性功能，而且通常是在跨橋接數據表實作多對多關聯性時的情況。 如需詳細資訊，請參閱 Power BI Desktop 中多對多基數的關聯性。

  • 限制平行查詢： 您可以為每個基礎數據源設定 DirectQuery 開啟的連線數目上限。 它會控制同時傳送至數據源的查詢數目。

    • 只有在模型中至少有一個 DirectQuery 來源時，才會啟用此設定。 值會套用至所有 DirectQuery 來源，以及新增至模型的任何新 DirectQuery 來源。
    • 增加每個數據源值的最大 連線 數可確保可以傳送更多查詢（最多指定的數目上限），當多個視覺效果位於單一頁面上，或許多用戶同時存取報表時，這非常有用。 達到連線數目上限后，進一步的查詢會排入佇列，直到聯機變成可用為止。 增加此限制會導致基礎數據源的負載增加，因此不保證此設定可改善整體效能。
    • 當模型發佈至 Power BI 時，傳送至基礎數據源的並行查詢數目上限也取決於環境。 不同的環境（例如 Power BI、Power BI 進階版 或 Power BI 報表伺服器），每個環境都可以施加不同的輸送量限制。 如需容量資源限制的詳細資訊，請參閱 Microsoft Fabric 容量授權和設定和管理 Power BI 中的容量 進階版。

重要

本文有時是指 Power BI 進階版 或其容量訂用帳戶 （P SKU）。 請注意，Microsoft 目前正在合併購買選項，並淘汰每個容量 SKU 的 Power BI 進階版。 新的和現有的客戶應該考慮改為購買網狀架構容量訂用帳戶（F SKU）。

如需詳細資訊，請參閱 Power BI 進階版 授權和 Power BI 進階版 常見問題的重要更新。

優化報表設計

根據 DirectQuery 語意模型（先前稱為數據集）的報表可以透過多種方式進行優化，如下列點符清單所述。

  • 啟用查詢縮減技術：Power BI Desktop 選項和 設定 包含 [減少查詢] 頁面。 此頁面有三個實用的選項。 您可以預設停用交叉醒目提示和交叉篩選，不過可以藉由編輯互動來覆寫。 您也可以在交叉分析篩選器和篩選上顯示 [套用] 按鈕。 在報表使用者按鍵之前，將不會套用交叉分析篩選器或篩選選項。 如果您啟用這些選項，建議您在初次建立報表時執行此動作。
  • 先套用篩選： 第一次設計報表時，建議您先在報表、頁面或視覺效果層級套用任何適用的篩選，再將字段對應至視覺效果字段。 例如，與其拖曳 CountryRegion 和 Sales 量值，然後依特定年份進行篩選，請先在 [年 ] 字段套用篩選。 這是因為建置視覺效果的每個步驟都會傳送查詢，而且在第一個查詢完成之前可以進行另一個變更，但仍會在基礎數據源上放置不必要的負載。 透過提早套用篩選，通常會讓中繼查詢成本較低且更快。 此外，提早套用篩選可能會導致超過1百萬個數據列限制，如 DirectQuery 中所述。
  • 限制頁面上的視覺效果數目： 開啟報表頁面時（以及套用頁面篩選時）頁面上的所有視覺效果都會重新整理。 不過，Power BI 環境可以平行傳送的查詢數目限制，以及每個數據源模型設定的最大 連線 數，如上所述。 因此，當頁面視覺效果數目增加時，他們很有可能會以序列方式重新整理。 這會增加重新整理整個頁面所花費的時間，也會增加視覺效果可能會顯示不一致結果的機會（針對揮發性數據源）。 基於這些理由，建議您限制任何頁面上的視覺效果數目，並改為擁有更簡單的頁面。 將多個卡片視覺效果取代為單一多列卡片視覺效果，即可達成類似的頁面配置。
  • 關閉視覺效果之間的互動： 交叉醒目提示和交叉篩選互動需要將查詢提交至基礎來源。 除非這些互動是必要的，否則如果回應使用者選擇所花費的時間相當長，建議將其關閉。 您可以針對整個報表關閉這些互動（如上述的「減少查詢」選項），或依案例而定。 如需詳細資訊，請參閱 視覺效果如何在Power BI報表中互相交叉篩選。

除了上述優化技術清單之外，下列每個報告功能都有助於效能問題：

  • 量值篩選： 包含量值（或數據行匯總）的視覺效果可以套用至這些量值的篩選條件。 例如，下列視覺效果會顯示依類別銷售，但僅適用於銷售額超過1500萬美元的類別。

    Power BI Desktop 的螢幕快照，其中顯示已套用篩選的表格式數據。

    這可能會導致兩個查詢傳送至基礎來源：

    • 第一個查詢會擷取符合條件的類別 （銷售額 > $1500 萬美元）
    • 第二個查詢接著會擷取視覺效果的必要數據，並將符合條件的類別新增至WHERE 子句

    如果類別有數百或數千個類別，一般會正常執行，如本範例所示。 不過，如果類別數目較大，效能可能會降低（而且，如果超過100萬個類別符合條件，查詢將會失敗，因為上述討論的1百萬個數據列限制）。

  • TopN 篩選： 可以定義進階篩選，只篩選量值排名的頂端（或下層）N 值。 例如，只顯示上述視覺效果中的前五個類別。 如同量值篩選，它也會產生兩個查詢傳送至基礎數據源。 不過，第一個查詢會從基礎來源傳回所有類別，然後根據傳回的結果決定前 N 個類別。 視涉及的數據行基數而定，可能會導致效能問題（或因1百萬個數據列限制而查詢失敗）。

  • 中位數： 一般而言，任何匯總（Sum、Count Distinct 等）都會推送至基礎來源。 不過，中位數並不成立，因為基礎來源不支援此匯總。 在這種情況下，會從基礎來源擷取詳細數據，而Power BI會從傳回的結果評估中位數。 如果基數很大，則中位數要計算在相對較少的結果上，但是如果基數很大，就會發生效能問題（或因 1 百萬個數據列限制而導致的查詢失敗）。 例如，國家/地區人口中位數可能是合理的，但銷售價格中位數可能不是。

  • 多重選取交叉分析篩選器： 允許交叉分析篩選器和篩選中的多重選取可能會導致效能問題。 這是因為當用戶選取其他交叉分析篩選器專案時（例如，最多建置他們感興趣的 10 個產品），每個新選取專案都會產生新的查詢傳送至基礎來源。 雖然使用者可以在查詢完成之前選取下一個專案，但會導致基礎來源的額外負載。 您可以藉由顯示 [套用] 按鈕來避免這種情況，如上述的查詢縮減技術所述。

  • 視覺效果總計： 根據預設，數據表和矩陣會顯示總計和小計。 在許多情況下，必須將其他查詢傳送至基礎來源，以取得總計的值。 每當使用 Count Distinct 或 Median 匯總，以及在透過 SAP HANA 或 SAP Business Warehouse 使用 DirectQuery 時，就會套用它。 如有需要，應該關閉這類總計（使用 [格式] 窗格。

轉換成複合模型

匯入和 DirectQuery 模型的優點可以藉由設定模型數據表的儲存模式，合併成單一模型。 數據表儲存模式可以是 Import 或 DirectQuery，或兩者稱為雙重。 當模型包含具有不同儲存模式的數據表時，即稱為複合模型。 如需詳細資訊，請參閱 在Power BI Desktop 中使用複合模型。

將 DirectQuery 模型轉換成複合模型，即可達成許多功能和效能增強功能。 複合模型可以整合多個 DirectQuery 來源，也可以包含匯總。 匯總數據表可以新增至 DirectQuery 數據表，以匯入數據表的摘要表示法。 當視覺效果查詢較高層級的匯總時，它們可以達到戲劇性的效能增強功能。 如需詳細資訊，請參閱 Power BI Desktop 中的匯總。

教育使用者

請務必教育使用者如何根據 DirectQuery 語意模型有效率地使用報表。 您的報表作者應該接受優化報表設計一節中所述的內容教育。

建議您教育報表取用者瞭解以 DirectQuery 語意模型為基礎的報表。 瞭解一般數據架構很有説明，包括本文所述的任何相關限制。 讓他們知道重新整理回應和互動式篩選有時可能很慢。 當報表使用者瞭解效能降低的原因時，他們不太可能失去對報表和數據的信任。

在傳遞動態數據源的報表時，請務必教育報表使用者使用 [重新整理] 按鈕。 也讓他們知道，可能會看到不一致的結果，而且重新整理報表可以解決報表頁面上的任何不一致問題。

相關內容

如需 DirectQuery 的詳細資訊，請參閱下列資源：