共用方式為

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 模型。

注意

如需將 DirectQuery 儲存模式用於 Dataverse 時的考量，請參閱 Power Platform 的 Power BI 模型化指引。

本文不會直接討論複合模型。 複合模型由至少一個 DirectQuery 來源 (可能更多) 組成。 本文中描述的指導至少有些部分仍然與複合模型設計相關。 不過，將匯入資料表與 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 Database 或 Azure Synapse Analytics (先前稱為 SQL 資料倉儲來源)，請參閱 SQL Server 索引架構與設計指南，以取得有關索引設計指引的實用資訊。 若為 SQL Server 或 Azure SQL Database 揮發性來源，請參閱開始使用資料行存放區來進行即時作業分析。

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

  • 確定必要的資料轉換會實體化：針對 SQL Server 關係資料庫來源（和其他關係資料庫來源），計算欄位可以新增至資料表。 這些資料行都是以運算式為基礎，例如 Quantity 乘以 UnitPrice。 計算資料行可以進行保存 (具體化)，且與一般資料行相似，有時候也可以為其編製索引。 如需詳細資訊，請參閱 計算資料行的索引。

    請也考慮索引檢視表，這種檢視表可以更細微地預先彙總事實資料表。 例如，如果 Sales 數據表將數據儲存在訂單行層級，您可以建立檢視來摘要此數據。 檢視可以根據 SELECT 語句，將 Sales 表的数据按照日期（以月為單位）、客戶和產品進行分組，並彙總銷售、數量等度量值。接著可以對這個檢視進行索引設定。 針對 SQL Server 或 Azure SQL Database 來源，請參閱建立索引檢視表。

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

最佳化模型設計

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 自訂資料行，或在模型中以計算結果欄來建立合併資料行。 但是，建議避免此操作，因為計算運算式會內嵌至來源查詢中。 這不僅效率不佳，也常會妨礙使用索引。 請改為在關聯式資料庫來源中新增具體化資料行，並考慮為其編製索引。 您也可以考慮在維度表中新增代理鍵欄位，這是關係型資料倉儲設計中的常見做法。

    本指引有一項例外，與使用 COMBINEVALUES DAX 函式有關。 此函式的目的是支援多資料行模型關聯性。 這個函式會產生多資料行 SQL 聯結述詞，而非產生關聯性使用的運算式。

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

  • 隱藏關係的單側欄：關係的單側欄應被隱藏。 （通常是維度表的主鍵欄。隱藏時，將無法在 [資料] 窗格中使用，因此無法用來設定視覺項目。） 若可以使用多側資料行來根據資料行的值分組或篩選報表，即可以將多側資料行維持在可見狀態。 例如，請考慮 Sales 與 Product 數據表之間存在關聯性的模型。 關聯性資料行包含產品的 SKU (庫存單位) 值。 如果產品 SKU 必須新增到視覺化中，則應該只在 Sales 數據表中顯示。 當此欄用來篩選或群組視覺化圖形時，Power BI 會生成一個不需要連接 Sales 和 Product 表的查詢。

  • 設定關聯性以強制執行完整性：DirectQuery 關聯性的 假設引用完整性 屬性會決定 Power BI 是否使用 INNER JOIN 來生成來源查詢，而非使用 OUTER JOIN。 這通常可以改善查詢效能，不過其確實取決於關聯式資料庫來源的詳細規格。 如需詳細資訊，請參閱 Power BI Desktop 中的採用參考完整性設定。

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

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

    • 設定只會在模型中至少包含一個 DirectQuery 來源時啟用。 該值會套用至所有 DirectQuery 來源，以及任何新增至模型的新 DirectQuery 來源。
    • 增加 [每個資料來源的連線數量上限] 值可確保有更多查詢 (最多可達指定的最大數目) 可以傳送至基礎資料來源，這在單一頁面上有許多視覺效果時，或是許多使用者同時存取報表時很有用。 一旦達到最大連線數目，進一步的查詢會排入佇列，直到有連線可供使用。 增加此限制會導致基礎資料來源有更多的負載，所以設定不保證能改善整體效能。
    • 將此模型發佈至 Power BI 時，傳送到基礎資料來源的同時查詢數量上限也會取決於環境。 每個不同環境 (例如 Power BI、Power BI Premium 或 Power BI 報表伺服器) 都可能會施加不同的輸送量條件約束。 如需容量資源限制的詳細資訊，請參閱 Microsoft Fabric 容量授權和設定及管理 Power BI Premium 中的容量。

重要

此文章有時會提及 Power BI Premium 或其容量訂用帳戶 (P SKU)。 請注意，Microsoft 目前正在整合購買選項，並按容量 SKU 淘汰 Power BI Premium。 新客戶和現有客戶應考慮改為購買 Fabric 容量訂用帳戶 (F SKU)。

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

最佳化報表設計

您可以透過多種方式優化以 DirectQuery 語意模型為基礎的報表，如下列點符清單所述。

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

除了上述最佳化技術清單外，每個下列報告功能都可能導致效能問題：

  • 度量篩選：包含度量（或列匯總）的視覺化可以應用篩選條件到這些度量上。 例如，以下的視覺效果會根據 Category 顯示 Sales，但只適用於銷售額超過美金 $1,500 萬元的類別。

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

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

    • 第一個查詢會擷取符合條件的類別 (Sales > 美金 1,500 萬元)
    • 第二個查詢會擷取圖表所需的數據，並將符合條件的類別新增至 WHERE 子句中。

    如此範例中具有的數百個或數千個類別，這執行起來通常沒有問題。 但是，如果類別數目更大，則效能可能會降低 (此外，如果有超過 1 百萬個類別符合條件，基於以上所述的 1 百萬個資料列限制，查詢將會失敗)。

  • TopN 篩選：您可以定義進階篩選，只篩選依量值排名的頂端（或下層）N 值。 例如，只在上述視覺效果中顯示前五個類別。 與量值篩選相似，這也會導致將兩個查詢傳送到基礎資料來源。 不過，第一個查詢會傳回基礎來源中的所有類別，然後根據傳回的結果來決定前 N 項。 根據所涉及的資料行基數，這可能會導致效能問題 (或由於 1 百萬個資料列限制而查詢失敗)。

  • 中位數：一般而言，任何匯總（Sum、Count Distinct 等）都會推送至基礎來源。 不過，中位數則不然，因為基礎來源不支援這項彙總。 在這種情況下，會從基礎來源擷取詳細資料，然後 Power BI 會從傳回的結果評估中位數。 這在針對較少的結果數目計算中位數時沒有什麼問題，但如果基數很大，則會發生效能問題 (或由於 1 百萬個資料列限制而查詢失敗)。 例如，計算國家/地區人口的中位數或許是可行的，但計算售價的中位數則否。

  • 允許多重選取的交叉分析篩選器：在交叉分析篩選器和篩選中允許多重選取可能會影響效能。 這是因為當使用者選取其他交叉分析篩選器項目時 (例如建置最多 10 個使用者有興趣的產品)，每個新的選取項目都會導致將新查詢傳送到基礎來源。 雖然使用者可以在查詢完成前選取下一個項目，但這樣會對基礎來源帶來額外的負載。 可以透過顯示 [套用] 按鈕來避免這種情況，如先前的查詢縮小技術所述。

  • 視覺化總計：根據預設，表格和矩陣會顯示總計和小計。 在許多情況下，額外查詢必須傳送到基礎來源以取得總計值。 這會套用在使用相異計數或中位數彙總時，以及所有透過 SAP HANA 或 SAP Business Warehouse 使用 DirectQuery 的案例。 若非必要，建議關閉這類總計 (使用 [格式] 窗格)。

轉換成複合模型

匯入及 DirectQuery 模型的優點可以透過設定模型資料表儲存模式，來合併成單一模型。 資料表儲存模式可以是 [匯入] 或 [DirectQuery]，或兩者皆是 (稱為「雙重」)。 當模型取得具有不同儲存模式的資料表時，稱為複合模型。 如需詳細資訊，請參閱在 Power BI Desktop 中使用複合模型。

您可以透過將 DirectQuery 模型轉換成複合模型來達成許多功能和效能上的強化。 複合模型的整合程度比單一 DirectQuery 來源更高，也可以包含彙總。 彙總資料表可以新增至 DirectQuery 資料表來匯入資料表的摘要表示。 這可以在視覺效果查詢較高層級的彙總時大幅強化效能。 如需詳細資訊，請參閱 Power BI Desktop 中的彙總。

教育使用者

教育使用者如何有效地根據 DirectQuery 語意模型使用報表非常重要。 報表作者應針對最佳化報表設計一節中描述的內容進行學習。

我們建議針對以 DirectQuery 語意模型為基礎的報表來教育報表取用者。 讓報表取用者了解一般資料架構 (包含任何本文所述的相關限制) 也會有所幫助。 讓報表取用者了解其需要預期有時候重新整理回應和互動篩選可能會相當緩慢。 當報表使用者了解為何會發生效能降低情況時，就不太可能會對報表和資料喪失信心。

傳遞以揮發性資料來源為基礎的報表時，請務必教育報表使用者使用 [重新整理] 按鈕。 讓報表使用者也了解其可能會看到不一致的結果，且重新整理報表可以解決報表頁面上的任何不一致。

相關內容

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