在 Power BI Desktop 中使用 DirectQueryUse DirectQuery in Power BI Desktop

有了 Power BI Desktop,當您連接到資料來源時,隨時可將資料的複本匯入 Power BI DesktopWith Power BI Desktop, when you connect to your data source, it is always possible to import a copy of the data into the Power BI Desktop. 對於某些資料來源,可用的替代方式是:使用 DirectQuery直接連接到資料來源。For some data sources, an alternative approach is available: connect directly to the data source using DirectQuery.

支援的資料來源Supported Data Sources

如需支援 DirectQuery 之資料來源的完整清單,請參閱 DirectQuery 支援的資料來源For a full listing of data sources that support DirectQuery, see Data sources supported by DirectQuery.

如何使用 DirectQuery 連接How to Connect using DirectQuery

當您使用 [取得資料] 連接到 DirectQuery 所支援的資料來源時,連接視窗可讓您選取想要的連接方式。When you use Get Data to connect to a data source supported by DirectQuery, the connection window lets you select how you want to connect.

選取 [匯入] 和 [DirectQuery] 的差異如下:The differences between selecting Import and DirectQuery are the following:

匯入 – 將選取的資料表和資料行匯入 Power BI Desktop 中。Import – the selected tables and columns are imported into Power BI Desktop. 當您建立視覺效果或與其互動時,Power BI Desktop 會使用匯入的資料。As you create or interact with a visualization, Power BI Desktop uses the imported data. 您必須重新整理資料,再次匯入完整的資料集,以查看自初始匯入或最近重新整理之後,基礎資料發生的任何變更。You must refresh the data, which imports the full data set again, to see any changes that occurred to the underlying data since the initial import or the most recent refresh.

DirectQuery – 沒有任何資料匯入或複製到 Power BI DesktopDirectQuery – no data is imported or copied into Power BI Desktop. 針對關聯式來源,選取的資料表和資料行會出現在 [欄位] 清單。For relational sources, the selected tables and columns appear in the Fields list. 針對多維度來源,例如 SAP Business Warehouse,選取 Cube 的維度及量值會出現在 [欄位] 清單。For multi-dimensional sources like SAP Business Warehouse, the dimensions and measures of the selected cube appear in the Fields list. 當您建立視覺效果或與其互動時,Power BI Desktop 會查詢基礎資料來源,這表示您一直都在檢視當下的資料。As you create or interact with a visualization, Power BI Desktop queries the underlying data source, which means you’re always viewing current data.

使用 DirectQuery時,有許多資料模型和資料轉換可以使用,但仍有一些限制。Many data modeling and data transformations are available when using DirectQuery, though with some limitations. 在建立視覺效果或與其互動時,必須查詢基礎來源,而且重新整理視覺效果所需的時間會視基礎資料來源的效能而定。When creating or interacting with a visualization, the underlying source must be queried and the time necessary to refresh the visualization is dependent on the performance of the underlying data source. 當服務要求所需的資料在最近已被要求時,Power BI Desktop 便會使用目前的資料,來減少顯示視覺效果所需的時間。When the data necessary to service the request has recently been requested, Power BI Desktop uses recent data to reduce the time required to display the visualization. 從 [主資料夾] 功能區選取 [重新整理] 可確保所有視覺效果都以目前的資料重新整理。Selecting Refresh from the Home ribbon will ensure all visualizations are refreshed with current data.

您可以從 Power BI 和 DirectQuery 文章中取得 DirectQuery 的詳細說明。The Power BI and DirectQuery article describes DirectQuery in detail. 若您需要使用 DirectQuery 時之優點、限制和重要考量的詳細資訊,請參閱下列各節。Also, see the following sections for more information about benefits, limitations, and important considerations when using DirectQuery.

使用 DirectQuery 的優點Benefits of using DirectQuery

使用 DirectQuery 有一些好處:There are a few benefits to using DirectQuery:

  • DirectQuery 可讓您透過非常大型的資料集建立視覺效果,原本在此狀況使用預先彙總先行匯入所有資料並不可行。DirectQuery lets you build visualizations over very large datasets, where it otherwise would be unfeasible to first import all of the data with pre-aggregation
  • 基礎資料變更可能需要重新整理資料,而針對某些報表,顯示目前的資料可能需要大量資料傳輸,造成重新匯入資料不太可行。Underlying data changes can require a refresh of data, and for some reports, the need to display current data can require large data transfers, making re-importing data unfeasible. 相較之下, DirectQuery 報表一律會使用目前的資料By contrast, DirectQuery reports always use current data
  • 1 GB 的資料集限制「不」適用於 DirectQueryThe 1 GB dataset limitation does not apply to DirectQuery

DirectQuery 的限制Limitations of DirectQuery

目前使用 DirectQuery會有一些限制:There are currently a few limitations to using DirectQuery:

  • 所有資料表都必須來自單一資料庫All tables must come from a single database
  • 如果 [查詢編輯器] 查詢過於複雜,將會發生錯誤。If the Query Editor query is overly complex, an error will occur. 若要修正錯誤,您必須在 [查詢編輯器] 中刪除有問題的步驟,或「匯入」資料,而不要使用 DirectQueryTo remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. 多維度來源,例如 SAP Business Warehouse,不存在 [查詢編輯器]For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor
  • 關聯性篩選僅限於單一方向,而非雙向 (雖然您可以透過預覽功能啟用 DirectQuery 的雙向交叉篩選)。Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). 多維度來源,例如 SAP Business Warehouse,不存在模型中定義的關聯性。For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model
  • DirectQuery 中不提供時間智慧功能。Time intelligence capabilities are not available in DirectQuery. 例如,DirectQuery 模式不支援日期資料行 (年、季、月、日等) 的特殊處理。For example, special treatment of date columns (year, quarter, month, day, so on) are not supported in DirectQuery mode.
  • 根據預設,量值中允許的 DAX 運算式會有所限制,詳細資訊請參閱後續段落 (在此項目符號清單後)By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph (after this bulleted list) for more information
  • 使用 DirectQuery 傳回資料時,限制為 1 百萬個資料列。There is a 1 million row limit for returning data when using DirectQuery. 這不會影響用來建立使用 DirectQuery傳回的資料集彙總或計算,只會影響傳回的資料列。This does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. 比方說,您可以使用在資料來源執行的查詢彙總 10 萬個資料列,並使用 DirectQuery準確地將該彙總的結果傳回 Power BI,只要傳回 Power BI 的資料小於 1 百萬個資料列即可。For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. 如果從 DirectQuery傳回的資料列超過 1 百萬個,Power BI 便會傳回錯誤。If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

為確保傳送至基礎資料來源的查詢皆具有足夠的效能,系統根據預設會對量值有所限制。To ensure that queries sent to the underlying data source have acceptable performance, limitations are imposed on measures by default. 進階使用者可以選擇略過此限制,方法是選取 [檔案] > [選項][設定] > [選項]、[設定] > [DirectQuery],然後選取 [允許在 DirectQuery 模式中量值不受限制] 選項 Advanced users can choose to bypass this limitation by selecting **File > Options* and then Settings > Options and settings > DirectQuery, then selecting the option Allow unrestricted measures in DirectQuery mode*. 選取該選項後,即可使用任何適用於量值的 DAX 運算式。When that option is selected, any DAX expression that is valid for a measure can be used. 不過使用者也必須了解,在匯入資料時效能很好的某些運算式,在 DirectQuery 模式中可能會導致後端來源的查詢速度緩慢。Users must be aware, however, that some expressions that perform very well when the data is imported may result in very slow queries to the backend source when in DirectQuery mode.

使用 DirectQuery 時的重要考量Important considerations when using DirectQuery

使用 DirectQuery 時,應考慮下列三點:The following three points should be taken into consideration when using DirectQuery:

  • 效能和負載 - 所有 DirectQuery 要求都會傳送到來源資料庫,因此重新整理視覺效果所需的時間,取決於該後端來源回應一或多個查詢結果所花費的時間。Performance and load - All DirectQuery requests are sent to the source database, so the time required to refresh a visual is dependent on how long that back-end source takes to respond with the results from the query (or queries). 針對視覺效果使用 DirectQuery 的建議回應時間 (正在傳回要求的資料) 為 5 秒 (含) 以下,建議的結果回應時間上限為 30 秒。The recommended response time (with requested data being returned) for using DirectQuery for visuals is five seconds or less, with a maximum recommended results response time of 30 seconds. 超過此時間會讓取用報表的使用者體驗低落至無法接受的程度。Any longer, and the experience of a user consuming the report becomes unacceptably poor. 此外,將報表發行至 Power BI 服務之後,超過幾分鐘的任何查詢都會逾時,而且使用者會收到錯誤。In addition, once a report is published to the Power BI service, any query that takes longer than a few minutes will timeout, and the user will receive an error.

    您也必須考慮來源資料庫上的負載,這會視取用已發行報表的 Power BI 使用者數目而定。Load on the source database should also be considered, based on the number of Power BI users who will consume the published report. 使用「資料列層級安全性」(RLS) 也可能會造成顯著的影響;由多位使用者共用的非 RLS 儀表板磚會對資料庫產生單一查詢,但在儀表板磚使用 RLS 通常表示需要「每位使用者」查詢一次才能重新整理磚,因而大幅增加來源資料庫上的負載並可能影響效能。Using Row Level Security (RLS) can have a significant impact as well; a non-RLS dashboard tile shared by multiple users results in a single query to the database, but using RLS on a dashboard tile usually means the refresh of a tile requires one query per user, thus significantly increasing load on the source database and potentially impacting performance.

    Power BI 會建立盡可能有效率的查詢。Power BI creates queries that are as efficient as possible. 不過在特定情況下,產生的查詢可能效率不足,而無法避免重新整理失敗。Under certain situations however, the generated query may not be efficient enough to avoid refresh that would fail. 這種情況的其中一個範例是,產生的查詢會從後端資料來源擷取非常大量的資料列 (超過 1 百萬個),因而發生下列錯誤:One example of this situation is when a generated query would retrieve an excessively large number of rows (more than 1 million) from the back-end data source, in which case the following error occurs:

    The resultset of a query to external data source has exceeded
    the maximum allowed size of '1000000' rows.
    

    如果有一個簡單圖表包含基數很高的資料行,而且彙總選項設定為 [不摘要],就可能會發生此情況。This situation can occur with a simple chart that includes a very high cardinality column, with the aggregation option set to Don’t Summarize. 此視覺效果必須只能包含其基數低於 1 百萬的資料行,否則就必須套用適當的篩選。The visual needs to only have columns with a cardinality below 1 million, or must have appropriate filters applied.

  • 安全性 - 所有取用已發行報表的使用者,都會使用發行至 Power BI 服務之後輸入的認證來連接到後端資料來源。Security - All users who consume a published report connect to the back-end data source using the credentials entered after publication to the Power BI service. 此情況與匯入資料相同:不論後端來源中是否有定義任何安全性規則,所有使用者都會看到相同的資料。This is the same situation as data that is imported: all users see the same data, irrespective of any security rules defined in the backend source. 想要使用 DirectQuery 資源為每個使用者實作安全性,及使用 RLS 的客戶。Customers who want per-user security implement with DirectQuery sources and use RLS. 深入了解 RLSLearn more about RLS.
  • 支援的功能 - DirectQuery 模式不支援 Power BI Desktop 的所有功能,或有一些限制。Supported features - Not all features in Power BI Desktop are supported in DirectQuery mode, or have some limitations. 此外,使用 DirectQuery 的資料集無法使用 Power BI 服務的某些功能 (例如 [深入資訊摘要])。In addition, there are some capabilities in the Power BI service (such as Quick Insights) that are not available for datasets using DirectQuery. 因此,決定是否要使用 DirectQuery 時,應該考慮使用 DirectQuery 時的這類功能限制。As such, the limitation of such features when using DirectQuery should be taken into consideration when determining whether to use DirectQuery.

發行至 Power BI 服務Publish to the Power BI service

使用 DirectQuery 建立的報表可以發行至 Power BI 服務。Reports created using DirectQuery can be published to the Power BI Service.

如果使用的資料來源不需要內部部署資料閘道 (Azure SQL DatabaseAzure SQL 資料倉儲Redshift),就必須提供認證,才能讓已發行的報表顯示在 Power BI 服務中。If the data source used does not need the on-premises data gateway (Azure SQL Database, Azure SQL Data Warehouse, or Redshift), credentials must be provided before the published report will be displayed in the Power BI Service.

您可以提供認證,方法是選取 Power BI 中的 設定 齒輪圖示,然後選取 [設定] 。You can provide credentials by selecting the Settings gear icon in Power BI, then select Settings.

Power BI 會顯示 [設定] 視窗。Power BI displays the Settings window. 從該處選取 [資料集] 索引標籤,選擇使用 DirectQuery 的資料集,然後選取 [編輯認證]。From there, select the Datasets tab and choose the dataset that uses DirectQuery, and select Edit credentials.

除非提供認證,否則開啟已發行的報表,或探索使用連接至這類資料來源的 DirectQuery 建立的資料集會產生錯誤。Until credentials are supplied, opening a published report or exploring a dataset created with a DirectQuery connection to such data sources results in an error.

若是 Azure SQL DatabaseAzure SQL 資料倉儲Redshift 以外之使用 DirectQuery 的其他資料來源,必須安裝內部部署資料閘道,而且必須註冊資料來源以建立資料連線。For data sources other than Azure SQL Database, Azure SQL Data Warehouse and Redshift that use DirectQuery, an on-premises data gateway must be installed and the data source must be registered to establish a data connection. 您可以深入了解內部部署資料閘道You can learn more about on-premises data gateway.

後續步驟Next steps

如需 DirectQuery 的詳細資訊,請參閱下列資源:For more information about DirectQuery, check out the following resources: