使用 Power BI 中的 DirectQueryUsing DirectQuery in Power BI

您可以在使用 Power BI DesktopPower BI 服務時連接到各種不同的資料來源,而且可以利用不同的方法來進行這些資料連接。You can connect to all sorts of different data sources when using Power BI Desktop or the Power BI service, and you can make those data connections in different ways. 您可以將資料「匯入」Power BI (這是取得資料的最常見方法),也可以直接連接到原始來源存放庫中的資料 (也稱為 DirectQuery)。You can either import data to Power BI, which is the most common way to get data, or you can connect directly to data in its original source repository, which is known as DirectQuery. 本文將說明 DirectQuery 及其功能,包括下列主題:This article describes DirectQuery and its capabilities, including the following topics:

  • DirectQuery 的不同連接選項Different connectivity options for DirectQuery
  • 何時應該考慮使用 DirectQuery 而不是匯入的指引Guidance for when you should consider using DirectQuery rather than import
  • 使用 DirectQuery 的缺點Drawbacks of using DirectQuery
  • 使用 DirectQuery 的最佳做法Best practice for using DirectQuery

簡單來說,使用匯入與 DirectQuery 的最佳做法如下:In short, the best practice for using import versus DirectQuery is the following:

  • 您應該盡可能將資料匯入 Power BI。You should import data to Power BI wherever possible. 如此即可利用 Power BI 的高效能查詢引擎,並提供資料的高度互動和全功能體驗。This takes advantage of the high performance query engine of Power BI, and provides a highly interactive and fully featured experience over your data.
  • 如果您的目標無法透過匯入資料來達成,則考慮使用 DirectQueryIf your goals can't be met by importing data, then consider using DirectQuery. 例如,如果資料經常變更且報表必須反映最新資料,則 DirectQuery 可能是最佳選擇。For example, if the data is changing frequently and reports must reflect the latest data, DirectQuery may be best. 不過,通常只有在基礎資料來源可以為一般彙總查詢提供互動式查詢 (不到 5 秒),而且能夠處理即將產生的查詢負載時,才適合使用 DirectQuery。However, using DirectQuery is generally only be feasible when the underlying data source can provide interactive queries (less than 5 seconds) for the typical aggregate query, and is able to handle the query load that will be generated. 此外,您應該仔細考慮使用 DirectQuery 所伴隨的限制清單,以確保仍可達成您的目標。Additionally, the list of limitations that accompany use of DirectQuery should be considered carefully, to ensure your goals can still be met.

Power BI 為這兩種連接模式 (匯入和 DirectQuery) 所提供的功能集,會隨著時間持續發展。The set of capabilities offered by Power BI for both connectivity modes – import and DirectQuery - will evolve over time. 這包括使用匯入的資料時提供更大的彈性 (例如可在更多情況下使用匯入),以及排除使用 DirectQuery 的一些缺點。This will include providing more flexibility when using imported data, such that import can be used in more cases, as well as eliminating some of the drawbacks of using DirectQuery. 不論有哪些改進,使用 DirectQuery 時,基礎資料來源的效能一律是主要考量。Regardless of improvements, when using DirectQuery the performance of the underlying data source will always remain a major consideration. 如果基礎資料來源緩慢,則仍不適合針對該來源使用 DirectQuery。If that underlying data source is slow, then using DirectQuery for that source it will remain unfeasible.

本主題涵蓋 DirectQuery 和 Power BI,但不涵蓋 SQL Server Analysis Services。This topic covers DirectQuery with Power BI, and not SQL Server Analysis Services. DirectQuery 也是 SQL Server Analysis Services 的功能,而且以下所述的許多詳細資料適用於此服務,不過也有重大差異。DirectQuery is also a feature of SQL Server Analysis Services, and many of the details described below apply to its use, there are also important differences. 如需搭配 SQL Server Analysis Services 使用 DirectQuery 的資訊,請參閱 SQL Server Analysis Services 2016 DirectQuery 技術白皮書中的詳細說明。For information about using DirectQuery with SQL Server Analysis Services, see the whitepaper that details DirectQuery in SQL Server Analysis Services 2016.

本文著重於在 Power BI Desktop 中建立報表的建議 DirectQuery 工作流程,但也會涵蓋在 Power BI 服務中直接連接的方式。This article focuses on the recommended workflow for DirectQuery, where the report is created in Power BI Desktop, but also covers connecting directly in the Power BI service.

Power BI 連接模式Power BI connectivity modes

Power BI 會連接到相當大量的各種資料來源,包括:Power BI connects to a very large number of varied data sources, encompassing:

  • 線上服務 (Salesforce、Dynamics 365 等)Online services (Salesforce, Dynamics 365, others)
  • 資料庫 (SQL Server、Access、Amazon Redshift 等)Databases (SQL Server, Access, Amazon Redshift, others)
  • 簡易檔案 (Excel、JSON 等)Simple files (Excel, JSON, others)
  • 其他資料來源 (Spark、網站、Microsoft Exchange 等)Other data sources (Spark, Web sites, Microsoft Exchange, others)

對於這些來源,您通常可以將資料匯入 Power BI。For these sources, it's usually possible to import the data to Power BI. 對於其他來源,您也可以使用 DirectQuery 進行連接。For some it is also possible to connect using DirectQuery. DirectQuery 支援的資料來源一文說明支援 DirectQuery 的一組正確來源。The exact set of sources that support DirectQuery is described in the Data Sources supported by DirectQuery article. 未來將有更多來源啟用 DirectQuery,主要是預期可提供良好互動式查詢效能的來源。More sources will be DirectQuery enabled in the future, focusing primarily on sources that can be expected to deliver good interactive query performance.

SQL Server Analysis Services 是特殊案例。SQL Server Analysis Services is a special case. 連接到 SQL Server Analysis Services 時,您可以選擇匯入資料,或使用「即時連接」。When connecting to SQL Server Analysis Services, you can choose to import the data, or use a live connection. 使用即時連接類似於 DirectQuery,不會匯入任何資料,而且一律會查詢基礎資料來源以重新整理視覺效果;但「即時連接」在許多其他方面並不同,因此使用不同的術語 (「即時」 與 DirectQuery)。Using a live connection is similar to DirectQuery, in that no data is imported, and the underlying data source is always queried to refresh a visual, but a live connection is different in many other regards, so a different term (live versus DirectQuery) is used.

連接資料有三個選項 (匯入DirectQuery即時連接),下列各節將詳細說明。These three options for connecting to data – import, DirectQuery, and live connection – are explained in detail in the following sections.

匯入連接Import connections

Power BI Desktop 中使用 [Get Data](取得資料) 連接到 SQL Server 等資料來源並選擇 [匯入] 時,該連接的行為如下所示:When using Get Data in Power BI Desktop to connect to a data source like SQL Server, and you choose Import, the behavior of that connection is as follows:

  • 在最初的 [Get Data](取得資料) 體驗期間,所選取的一組資料表會各自定義一個查詢以傳回一組資料 (載入資料前可編輯這些查詢,例如套用篩選、彙總資料或聯結不同的資料表)。During the initial Get Data experience, the set of tables selected each define a query that will return a set of data (those queries can be edited prior to loading the data, for example to apply filters, or aggregate the data, or join different tables).
  • 載入時,由這些查詢定義的所有資料都將匯入 Power BI 快取。Upon load, all of the data defined by those queries will be imported into the Power BI cache.
  • Power BI Desktop 中建立視覺效果時,即會查詢匯入的資料。Upon building a visual within Power BI Desktop, the imported data will be queried. Power BI 存放區可確保查詢速度很快,因此會立即反映視覺效果的所有變更。The Power BI store ensures the query will be very fast, hence all changes to the visual will be reflected immediately.
  • 基礎資料的任何變更不會反映在任何視覺效果中。Any changes to the underlying data will not be reflected in any visuals. 請務必「重新整理」,以便重新匯入資料。It is necessary to Refresh, whereupon the data will be re-imported.
  • 將報表 (.pbix 檔) 發佈至 Power BI 服務時,會建立資料集並上傳至 Power BI 服務。Upon publishing the report (the .pbix file) to the Power BI service, a dataset is created and uploaded to the Power BI service. 該資料集會包含匯入的資料。The imported data is included with that dataset. 您可以接著設定該資料的排程重新整理,例如每天重新匯入資料。It is then possible to set up scheduled refresh of that data, for example, to re-import the data every day. 視原始資料來源的位置而定,您可能必須設定內部部署資料閘道。Depending upon the location of the original data source, it might be necessary to configure an on-premises data gateway.
  • Power BI 服務中開啟現有的報表或撰寫新的報表時,會重新查詢匯入的資料,以確保彼此互動。When opening an existing report in the Power BI service, or authoring a new report, the imported data is queried again, ensuring interactivity.
  • 視覺效果或整個報表頁面可以釘選為儀表板磚。Visuals, or entire report pages, can be pinned as dashboard tiles. 每次重新整理基礎資料集都會自動重新整理這些磚。The tiles will be automatically refreshed whenever the underlying dataset is refreshed.

DirectQuery 連接DirectQuery connections

Power BI Desktop 中使用 [Get Data](取得資料) 連接到資料來源並選擇 [DirectQuery] 時,該連接的行為如下所示:When using Get Data in Power BI Desktop to connect to a data source, and you choose DirectQuery, the behavior of that connection is as follows:

  • 在最初的 [Get Data](取得資料) 體驗期間,已選取來源。During the initial Get Data experience, the source is selected. 對於關聯式來源,這表示選取一組資料表,而每個資料表仍會定義一個查詢以邏輯方式傳回一組資料。For relational sources, this means a set of tables are selected and each still define a query that logically returns a set of data. 對於 SAP BW 等多維度來源,只會選取該來源。For multidimensional sources like SAP BW, only the source is selected.
  • 不過,載入時,不會實際將資料匯入 Power BI 存放區。However, upon load, no data will actually be imported into the Power BI store. 相反地,在 Power BI Desktop 中建立視覺效果時,會傳送查詢至基礎資料來源以擷取必要的資料。Instead, upon building a visual within Power BI Desktop, queries will be sent to the underlying data source to retrieve the necessary data. 重新整理視覺效果所需的時間將取決於基礎資料來源的效能。The time then taken to refresh the visual will depend on the performance of the underlying data source.
  • 基礎資料的任何變更不會立即反映在任何現有的視覺效果中。Any changes to the underlying data will not be immediately reflected in any existing visuals. 您仍然必須重新整理,此時會針對每個視覺效果重新傳送必要的查詢,並視需要更新視覺效果。It is still necessary to Refresh, whereupon the necessary queries will be resent for each visual, and the visual updated as necessary.
  • 將報表發佈至 Power BI 服務時,會在 Power BI 服務中再次產生資料集,就如同匯入一樣。Upon publishing the report to the Power BI service, it will again result in a Dataset in the Power BI service, just as for import. 不過,該資料集「不會包含任何資料」。However, no data is included with that dataset.
  • Power BI 服務中開啟現有的報表或撰寫新的報表時,會重新查詢基礎資料來源以擷取必要的資料。When opening an existing report in the Power BI service, or authoring a new one, the underlying data source is again queried to retrieve the necessary data. 視原始資料來源的位置而定,您可能必須設定內部部署資料閘道,就如同匯入模式在重新整理資料時需要的設定。Depending upon the location of the original data source, it might be necessary to configure an on-premises data gateway, just as is needed for Import mode if the data is refreshed.
  • 視覺效果或整個報表頁面可以釘選為儀表板磚。Visuals, or entire report pages, can be pinned as Dashboard tiles. 為了確保快速開啟儀表板,這些磚會依排程自動重新整理 (例如每小時)。To ensure that opening a dashboard will be fast, the tiles are automatically refreshed on a schedule (for example, every hour). 您可以控制此重新整理的頻率,以反映資料變更的頻率,以及查看最新資料的重要性。The frequency of this refresh can be controlled, to reflect how frequently the data is changing, and how important it is to see the very latest data. 因此,開啟儀表板時,磚會反映上次重新整理時的資料,而不一定是對基礎來源所做的最新變更。Thus, when opening a dashboard, the tiles will reflect the data as of the time of the last refresh, and not necessarily the very latest changes made to the underlying source. 您一律可以重新整理開啟的儀表板,以確保儀表板處於最新狀態。An open dashboard can always be Refreshed to ensure it is up-to-date.

即時連接Live connections

連接到 SQL Server Analysis Services (SSAS) 時,可以選擇將資料從選取的資料模型匯入,或即時連接到選取的資料模型。When connecting to SQL Server Analysis Services (SSAS), there is an option to either import data from, or connect live to, the selected data model. 如果您選取 [匯入],然後定義對該外部 SSAS 來源的查詢,則會正常匯入資料。If you select import, then you define a query against that external SSAS source, and the data is imported as normal. 如果您選取 [即時連接],則不會定義任何查詢,而且整個外部模型都會顯示在欄位清單中。If you select to connect live then there is no query defined, and the entire external model is shown in the field list. 如果您選取 [DirectQuery],建立視覺效果時,會傳送查詢至外部 SSAS 來源。If you select DirectQuery, as visuals are built, queries are sent to the external SSAS source. 不過,不同於 DirectQuery,建立新的「模型」並沒有任何意義;換句話說,您無法定義新的計算結果欄、階層、關聯性等等。However, unlike DirectQuery, there is no sense in which a new model is being created; in other words, it's not possible to define new calculated columns, hierarchies, relationships, and so on. 相反地,您只要直接連接到外部 SSAS 模型即可。Instead you are simply connecting directly to the external SSAS model.

前一個段落中所述的情況也適用於連接到下列來源,但無法選擇匯入資料:The situation described in the previous paragraph applies to connecting to the following sources as well, except that there is no option to import the data:

  • Power BI 資料集 (例如,連接到先前已建立並發行至服務的 Power BI 資料集,以在其上撰寫新的報表)Power BI datasets (for example, when connecting to a Power BI dataset that has previously been created and published to the service, to author a new report over it)
  • Common Data ServiceCommon Data Services

透過 SSAS 的報表在發佈至 Power BI 服務時的行為,與 DirectQuery 報表有下列相似處:The behavior of reports over SSAS, upon publishing to the Power BI service, is similar to DirectQuery reports in the following ways:

  • Power BI 服務中開啟現有的報表或撰寫新報表時,會查詢基礎 SSAS 來源 (可能需要內部部署資料閘道)When opening an existing report in the Power BI service or authoring a new report, the underlying SSAS source is queried (possibly requiring an on-premises data gateway)
  • 儀表板磚會依排程自動重新整理 (例如每小時或所定義的任何頻率)Dashboard tiles are automatically refreshed on a schedule (such as every hour, or whatever frequency is defined)

不過也有重大差異,包括針對即時連接,開啟報表的使用者身分識別一律會傳遞至基礎 SSAS 來源。However, there are also important differences, including that for live connections the identity of the user opening the report will always be passed to the underlying SSAS source.

撇開這些比較不談,讓我們在本文的其餘部分只專注於 DirectQueryWith these comparisons out of the way, let's focus solely on DirectQuery for the rest of this article.

何時適合使用 DirectQuery?When is DirectQuery useful?

下表說明使用 DirectQuery 連接可能特別有用的情況,包括將資料保留在原始來源中被視為有幫助的情況。The following table describes scenarios where connecting with DirectQuery could be especially useful, including cases where leaving the data in the original source would be considered beneficial. 該描述包含指定的情況是否適用於 Power BI 的相關討論。The description includes a discussion about whether the specified scenario is available in Power BI.

限制Limitation 描述Description
資料經常變更,而且需要幾近「即時」的報告Data is changing frequently, and near ‘real-time’ reporting is needed 含有已匯入資料的模型最多可以每小時重新整理一次。Models with Imported data can be refreshed at most once per hour. 因此,如果資料持續變更,而且報表必須顯示最新資料,則光是使用匯入並排程重新整理可能無法符合這些需求。Hence, if the data is continually changing, and it is necessary for reports to show the latest data, then using Import with scheduled refresh might simply not meet those needs. 另請注意,您也可以將資料直接串流到 Power BI 中,不過在此情況中,支援的資料量有限。Note also that it is also possible to stream data directly into Power BI, though there a limits on the data volumes supported for this case.

相對之下,使用 DirectQuery 表示開啟或重新整理報表或儀表板一律會顯示來源中的最新資料。Using DirectQuery, by contrast, means that opening or refreshing a report or dashboard will always show the latest data in the source. 此外,可以更頻繁地更新儀表板磚 (最高每 15 分鐘一次)。Additionally, the dashboard tiles can be updated more frequently (as often as every 15 mins).
資料很大Data is very large 如果資料很大,當然無法全部匯入。If the data is very large, then it certainly would not be feasible to import it all. 相對之下,DirectQuery 由於是就地查詢,因此不需要傳送大量資料。DirectQuery, by contrast, requires no large transfer of data, as it is queried in place.

不過,資料很大也可能表示對該基礎來源的查詢效能太慢 (如本文稍後的<使用 DirectQuery 的影響>一節所述)。However, large data might also imply that the performance of the queries against that underlying source are too slow (as discussed in Implications of using DirectQuery section, later in this article). 您不一定要匯入完整的詳細資料。And of course it is not always necessary to import the full detailed data. 相反地,您可以在匯入期間預先彙總資料 (而 [查詢編輯器] 可輕易達成此目的)。Instead the data can be pre-aggregated during import (and Query Editor makes it easy to do exactly this). 如有必要,您可以只匯入每個視覺效果所需的彙總資料。In the extreme it would be possible to import exactly the aggregate data needed for each visual. 因此,雖然 DirectQuery 是處理大型資料的最簡單方法,請一律記住匯入彙總資料可解決基礎來源太慢的問題。So while DirectQuery is the simplest approach to large data, you should always keep in mind that importing aggregate data might offer a solution if the underlying source is too slow.
安全性規則是在基礎來源中定義Security rules are defined in the underlying source 匯入資料之後,Power BI 將會使用目前的使用者認證 (從 Power BI Desktop),或使用設定排程重新整理時所定義的認證 (從 Power BI 服務) 連接到資料來源。When the data is imported, Power BI will connect to the data source using the current users credentials (from Power BI Desktop), or the credentials defined as part of configuring scheduled refresh (from the Power BI service). 因此,在發佈及共用這類報表時務必謹慎,請只與可查看相同資料的使用者共用,或定義資料列層級安全性作為資料集的一部分。Thus, in publishing and sharing such a report, care must be taken to only share with users allowed to see the same data, or to define Row Level Security as part of the dataset.

在理想情況下,DirectQuery 一律會查詢基礎來源,如此可確保套用該基礎來源中的所有安全性。Ideally, because DirectQuery always queries the underlying source, this would allow any security in that underlying source to be applied. 不過,Power BI 現在一律會使用與用於匯入相同的認證來連接基礎來源。However, today Power BI will always connect to the underlying source using the same credentials as would be used for Import.

因此,在 Power BI 允許將報表取用者的身分識別傳遞至基礎來源之前,DirectQuery 不會提供與資料來源安全性相關的任何優勢。Thus, until Power BI allows for the identity of the report consumer to pass through to the underlying source, DirectQuery offers no advantages with regard to data source security.
套用資料主權限制Data sovereignty restrictions apply 某些組織設有資料主權原則,換句話說資料不可以離開組織內部。Some organizations have policies around data sovereignty, meaning that data cannot leave the organization premises. 採用匯入的解決方案很明顯會有問題。A solution based on import would clearly present issues. 相對之下,使用 DirectQuery 可將資料保留在基礎來源中。By contrast, with DirectQuery that data remains in the underlying source.

不過請注意,即使是使用 DirectQuery,還是會有一些視覺效果層級的資料快取保留在 Power BI 服務中 (由於排程重新整理磚所致)。However, it should be noted that even with DirectQuery, some caches of data at the visual level are retained in the Power BI service (due to scheduled refresh of tiles).
基礎資料來源是包含量值的 OLAP 來源Underlying data source is an OLAP source, containing measures 如果基礎資料來源包含 *量值 *(例如 SAP HANA 或 SAP Business Warehouse),則匯入資料會帶來其他問題。If the underlying data source contains *measures *(such as SAP HANA or SAP Business Warehouse) then importing the data brings other issues. 這表示匯入的資料會位於由查詢所定義的特定彙總層級。It means that the data imported is at a particular level of aggregation, as defined by the query. 例如,依 Class、Year 和 City 測量 TotalSales。For example, measure TotalSales by Class, Year, and City. 如果建立要求在更高層級彙總資料的視覺效果 (例如 TotalSales by Year),則會進一步彙總此彙總值。Then if a visual is built asking for data at a higher level aggregate (such as TotalSales by Year) it is further aggregating the aggregate value. 這對加總量值 (例如 Sum、Min) 沒有問題,但對非加總量值 (例如 Average、DistinctCount) 則有問題。This is fine for additive measures (such as Sum, Min) but it's an issue for non-additive (such as Average, DistinctCount).

若要輕鬆地直接從來源取得正確的彙總資料 (特定視覺效果所需的資料),則必須像是在 DirectQuery 中一樣針對每個視覺效果傳送查詢。To make it easy to get the correct aggregate data (as needed for the particular visual) directly from the source, it would be necessary to send queries per visual, as in DirectQuery.

連接到 SAP Business Warehouse (BW) 時,選擇 DirectQuery 可進行此量值處理。When connecting to SAP Business Warehouse (BW), choosing DirectQuery allows for this treatment of measures. DirectQuery 和 SAP BW 中將進一步說明 SAP BW 的支援。Support for SAP BW is covered further in DirectQuery and SAP BW.

不過,透過 SAP HANA 的 DirectQuery 目前會將它視為等同於關聯式來源,因此會提供類似於匯入的行為。However, currently DirectQuery over SAP HANA treats it the same as a relational source, and hence provides similar behavior to import. DirectQuery 和 SAP HANA 中將進一步說明此主題。This is covered further in DirectQuery and SAP HANA.

總而言之,根據 Power BI 中 DirectQuery 的目前功能,在下列情況下會有所幫助:So in summary, given the current capabilities of DirectQuery in Power BI, the scenarios where it offers benefits are the following:

  • 資料經常變更,而且需要幾近「即時」的報告Data is changing frequently, and near ‘real-time’ reporting is needed
  • 處理非常大型的資料,而不需要預先彙總Handling very large data, without the need to pre-aggregate
  • 套用資料主權限制Data sovereignty restrictions apply
  • 來源是包含量值的多維度來源 (例如 SAP BW)The source is a multi dimensional source containing measures (such as SAP BW)

請注意,上述清單中的詳細資料與單獨使用 Power BI 相關。Note that the details in the previous list relate to the use of Power BI alone. 您一律可以選擇改用外部 SQL Server Analysis Services (或 Azure Analysis Services) 模型匯入資料,然後使用 Power BI 連接到該模型。There is always the option of instead using an external SQL Server Analysis Services (or Azure Analysis Services) model to import data, and then using Power BI to connect to that model. 雖然該方法需要具備其他技能,但提供更大的彈性。While that approach would require additional skills, it does provide greater flexibility. 例如,您可以匯入更大量的資料,可重新整理資料的頻率也沒有限制。For example, much larger volumes of data can be imported, and there is no restriction on how frequently the data can be refreshed.

使用 DirectQuery 的影響Implications of using DirectQuery

如本節中所述,使用 DirectQuery 並沒有潛在負面影響。Use of DirectQuery does have potentially negative implications, as detailed in this section. 視所使用的實際來源而定,其中一些限制會稍微不同。Some of those limitations are slightly different depending upon the exact source that is being used. 我們將會適時提出,並以個別主題來說明這些相當不同的來源。This will be called out where applicable, and separate topics cover those sources that are substantially different.

基礎來源的效能和負載Performance and load on the underlying source

使用 DirectQuery 時,整體體驗絕大部分取決於基礎資料來源的效能。When using DirectQuery, the overall experience depends very much on the performance of the underlying data source. 如果重新整理每個視覺效果 (例如變更交叉分析篩選器值之後) 需要幾秒的時間 (<5 秒),則是合理的體驗,不過與將資料匯入 Power BI 時所習慣的立即回應相較之下,可能仍稍嫌緩慢。If refreshing each visual (for example, after changing a slicer value) takes a few seconds (<5s) then the experience would be reasonable, yet might still feel sluggish compared to the immediate response we are used to when importing the data to Power BI. 如果來源緩慢表示個別視覺效果需要更長的時間 (數十秒),則體驗會變得很差,甚至可能導致查詢逾時。If instead the slowness of the source means that individual visuals take longer than that (tens of seconds), then the experience becomes extremely poor, possibly even to the point of queries timing out.

除了基礎來源的效能,也應該仔細考慮對其所產生的負載 (這之後通常會影響效能)。Along with the performance of the underlying source, careful consideration should be paid to the load that will be placed upon it (that of course then often impacts the performance). 如下所述,開啟共用報表的每位使用者,以及定期重新整理的每個儀表板磚,都會針對每個視覺效果傳送至少一個查詢至基礎來源。As discussed further below, each user that opens a shared report, and each dashboard tile that is periodically refreshed, will be sending at least one query per visual to the underlying source. 因此,來源必須能夠處理這類查詢負載,同時仍維持合理的效能。This fact requires that the source be able to handle such a query load, while still maintaining reasonable performance.

限制為單一來源Limited to a single source

匯入資料時,您可以將多個來源中的資料合併成單一模型;例如,輕鬆地將公司的 SQL Server 資料庫中的一些資料,聯結到在 Excel 檔案中維護的一些本機資料。When importing data, it is possible to combine data from multiple sources into a single model, for example, to easily join some data from a corporate SQL Server database with some local data maintained in an Excel file. 您無法使用 DirectQuery 來執行這項作業。This is not possible when using DirectQuery. 針對某個來源選取 DirectQuery 時,只能使用該單一來源 (例如單一 SQL Server 資料庫) 中的資料。When selecting DirectQuery for a source, it will then only be possible to use data from that single source (such as a single SQL Server database).

有限的資料轉換Limited data transformations

同樣地,您可以在 [查詢編輯器] 中套用資料轉換限制。Similarly, there are limitations in the data transformations that can be applied within Query Editor. 匯入資料之後,您可以輕鬆地套用一組複雜的轉換,將資料清除並重新成形,再使用此資料建立視覺效果 (例如剖析 JSON 文件,或將資料行中的資料轉換到來源資料列)。With imported data, a sophisticated set of transformations can easily be applied to clean and re-shape the data before using it to create visuals (such as parsing JSON documents, or pivoting data from a column to a row orientated form). 這些轉換在 DirectQuery 中有更多限制。Those transformations are more limited in DirectQuery. 首先,連接到 SAP Business Warehouse 等 OLAP 來源時,完全無法定義轉換,而且會從來源取出整個外部「模型」。First, when connecting to an OLAP source like SAP Business Warehouse, no transformations can be defined at all, and the entire external ‘model’ is taken from the source. 至於 SQL Server 等關聯式來源,您仍然可以針對每個查詢定義一組轉換,但基於效能原因,這些轉換會受到限制。For relational sources like SQL Server, it is still possible to define a set of transformations per query, but those transformations are limited, for performance reasons. 您必須對基礎來源的每個查詢套用任何這類轉換,而不是在資料重新整理時套用一次,因此僅限於可合理轉譯成單一原生查詢的轉換。Any such transformation will need to be applied on every query to the underlying source, rather than once on data refresh, so they are limited to those transformations that can reasonably be translated into a single native query. 如果您使用的轉換太過複雜,則會收到錯誤,指出必須加以刪除,或將模型切換至匯入模式。If you use a transformation that is too complex, then you will receive an error that either it must be deleted, or the model switched to Import mode.

此外,從 [Get Data](取得資料) 對話方塊或 [查詢編輯器] 產生的查詢,將用於為擷取視覺效果必要資料所產生及傳送之查詢內的子選擇。Additionally, the query that results from the Get Data dialog or Query Editor will be used in a subselect within the queries generated and sent to retrieve the necessary data for a visual. 因此,在查詢編輯器中定義的查詢必須在此內容中有效。Thus the query defined in Query Editor must be valid within this context. 特別是這表示您無法使用採用通用資料表運算式的查詢,也無法使用叫用預存程序的查詢。In particular this means it is not possible to use a query using Common Table Expressions, nor that invokes Stored Procedures.

模型限制Modelling limitations

「模型」一詞在此內容中表示精簡及擴充未經處理資料的功能,屬於撰寫使用模型之報表的一部分。The term modelling in this context means the act of refining and enriching the raw data, as part of authoring a report using it. 範例包括:Examples include:

  • 定義資料表之間的關聯性Defining relationships between tables
  • 新增計算 (計算結果欄和量值)Adding new calculations (calculated columns and measures)
  • 重新命名及隱藏資料行和量值Renaming and hiding columns and measures
  • 定義階層Defining hierarchies
  • 定義資料行的格式、預設摘要和排序次序Defining the formatting, default summarization and sort order for a column
  • 群組或叢集值Grouping or clustering values

使用 DirectQuery 時,仍可進行許多這類模型擴充,而且也有擴充未處理資料的原則,以便改善後續使用。When using DirectQuery, many of these model enrichments can still be made, and certainly there is still the principle that the raw data is being enriched, so as to improve later consumption. 不過,使用 DirectQuery 時,某些模型功能無法使用或受到限制。However, there some modeling capabilities are not available, or are limited, when using DirectQuery. 為避免效能問題,通常會套用這些限制。The limitations are generally applied to avoid performance issues. 下列項目符號清單中列出所有 DirectQuery 來源常見的一組限制。The set of limitations that are common to all DirectQuery sources are listed in the following bulleted list. 還有其他限制可能會套用至個別來源,如本文結尾附近的資料來源的特定詳細資料中所述。Additional limitations might apply to individual sources, as described in Data source specific details found near the end of this article.

  • 沒有內建日期階層:匯入資料時,每個日期/日期時間資料行預設也會有內建日期階層可用。No Built in-date hierarchy: When importing data, then by default every date/datetime column will also have a built-in date hierarchy available by default. 例如,如果匯入包含 OrderDate 資料行的銷售訂單資料表,則在視覺效果中使用 OrderDate 時,可以選擇要使用的適當層級 (Year、Month、Day)。For example, if importing a table of sales orders including a column OrderDate, then upon using OrderDate in a visual, it will be possible to choose the appropriate level (Year, Month, Day) to use. 使用 DirectQuery 模式時無法使用此內建日期階層。This built-in date hierarchy is not available when using DirectQuery mode. 不過請注意,如果基礎來源中有可用的 Date 資料表 (這在許多資料倉儲中很常見),則可以如往常般使用 DAX 時間智慧函式。Note however that if there is Date table available in the underlying source (as is common in many data warehouses) then the DAX Time Intelligence functions can be used as normal.
  • 計算結果欄限制:計算結果欄僅限於內部資料列,換句話說,它們只會參考相同資料表之其他資料行的值,而不會使用任何彙總函式。Limitations in calculated columns: Calculated columns are limited to being intra-row, as in, they can only refer to values of other columns of the same table, without the use of any aggregate functions. 此外,允許的 DAX 純量函式 (例如 LEFT()) 僅限於可直接發送至基礎來源的函式,因此會因來源的實際功能而有所不同。Additionally, the DAX scalar functions (such as LEFT()) that are allowed will be limited to those which can simply be pushed to the underlying source, hence will vary depending upon the exact capabilities of the source. 撰寫計算結果欄的 DAX 時,不會在自動完成功能中列出不支援的函式,而且如果使用這些函式,將會導致錯誤。Functions that are not supported will not be listed in autocomplete when authoring the DAX for a calculated column, and would result in an error if used.
  • 不支援父子式 DAX 函式:在 DirectQuery 模型中,您無法使用通常用來處理父子式結構 (例如會計科目表或員工階層) 的 DAX PATH() 函式系列。No support for parent-child DAX functions: When in DirectQuery model, it is not possible to use the family of DAX PATH() functions, that generally handle Parent-Child structures (such as chart of accounts, or employee hierarchies).
  • 量值限制 (預設情況下):預設會限制可用於量值的 DAX 函式和運算式。Limitations (by default) for measures: By default, the DAX functions and expressions that can be used in measures is restricted. 同樣地,自動完成功能會限制列出的函式,而且如果使用無效的函式或運算式,則會發生錯誤。Again, autocomplete will restrict the functions listed, and an error will occur if an invalid function or expression is used. 這樣做只是為了確保量值預設會限制為本身不太可能造成任何效能問題的簡單量值。The reason is simply to ensure that, by default, measures are restricted to simple measures that are unlikely by themselves cause any performance issues. 進階使用者可以選擇略過此限制,方法是選取 [檔案] > [選項],然後選取 [設定] > [選項] > [DirectQuery],再選取 [允許在 DirectQuery 模式中量值不受限制] 選項。Advanced users can choose to bypass this limitation by selecting File > Options and then Settings > Options > 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 well when the data is imported may result in very slow queries to the backend source when in DirectQuery mode.

    • 例如,根據預設:For example, by default:

      • 您可以撰寫只會加總銷售量的量值:It would be possible to author a measure that simply summed the sales amount:

        SalesAmount = SUMX(Web_Sales, [ws_sales_price]* [ws_quantity])
        
      • 您「無法」撰寫平均所有項目之銷售額的量值:It would not be possible to author a measure that then averaged that SalesAmount over all of the Items:

        AverageItemSalesAmount = AVERAGEX('Item', [SalesAmount])
        

      因為這類量值可能會在有大量項目的情況下導致效能不佳。The reason is that such a measure could result in poor performance if there were a very large number of items.

  • 不支援計算資料表:DirectQuery 模式不支援使用 DAX 運算式定義計算資料表的功能。Calculated tables are not supported: The ability to define a calculated table using a DAX expression is not supported in DirectQuery mode.
  • 關聯性篩選僅限於單一方向:使用 DirectQuery 時,您無法將關聯性的交互篩選方向設定為 [雙向]。Relationship filtering is limited to a single direction: When using DirectQuery, it is not possible to set the Cross Filter direction on a relationship to “Both”. 例如,您可以透過下列三個資料表,建立顯示每個 Customer[Gender] 以及其所購買之 Product[Category] 數目的視覺效果。For example, with the three tables below, it would not be possible to build a visual showing each Customer[Gender], and the number of Product[Category] bought by each. 如需使用這類雙向篩選的說明,請參閱這份詳細的技術白皮書 (此文件提供 SQL Server Analysis Services 內容的範例,但基本重點同樣適用於 Power BI)。Use of such bi-directional filtering is described in this detailed whitepaper (the paper presents examples in the context of SQL Server Analysis Services, but the fundamental points apply equally to Power BI).

    同樣會由於效能影響而有所限制。Again, the limitation is imposed due to the performance implications. 一個特別重要的應用是在定義資料列層級安全性作為報表的一部分時,因為一般模式是讓使用者與使用者可存取的實體之間有多對多關聯性,必須使用雙向篩選才能強制執行此作業。One particularly important application of this is when defining Row Level Security as part of the report, as a common pattern is to have a many-many relationship between the users and the entities they are allowed access to, and use of bi-directional filtering is necessary to enforce this. 不過,針對 DirectQuery 模型使用雙向篩選應該謹慎,並小心注意不要對效能造成任何不良影響。However, use of bi-directional filtering for DirectQuery models should be used judiciously, with careful attention paid to any detrimental impact on performance.

  • 無叢集:使用 DirectQuery 時,您無法使用 [叢集] 功能來自動尋找群組No Clustering: When using DirectQuery, it is not possible to use the Clustering capability, to automatically find groups

報告限制Reporting limitations

DirectQuery 模型支援幾乎所有報告功能。Almost all reporting capabilities are supported for DirectQuery models. 因此,只要基礎來源提供適當的效能等級,則可以使用一組相同的視覺效果。As such, so long as the underlying source offers a suitable level of performance, the same set of visualizations can be used. 不過,Power BI 服務所提供的一些其他功能在發行報表之後有一些重要的限制,如下列項目符號清單中所述:However, there are some important limitations in some of the other capabilities offered in the Power BI service after a report is published, as described in the following bullets:

  • 不支援深入資訊摘要:Power BI 深入資訊摘要可搜尋資料集的不同子集,同時套用一組複雜的演算法來探索潛在相關的深入資訊。Quick Insights is not supported: Power BI Quick Insights searches different subsets of your dataset while applying a set of sophisticated algorithms to discover potentially-interesting insights. 由於需要很高效能的查詢,因此這項功能無法用於使用 DirectQuery 的資料集。Given the need for very high performance queries, this capability is not available on datasets using DirectQuery.
  • 不支援問與答:Power BI 問與答可讓您使用直覺式的自然語言功能來探索資料,並以圖表和圖形形式接收回應。Q&A is not supported: Power BI Q&A enables you to explore your data using intuitive, natural language capabilities and receive answers in the form of charts and graphs. 不過,使用 DirectQuery 的資料集目前不支援。However, it is currently not supported on datasets using DirectQuery.
  • 使用 [Explore in Excel](使用 Excel 探索) 可能會導致效能不佳:您可以對資料集使用 [Explore in Excel](使用 Excel 探索) 功能來探索資料。Using Explore in Excel will likely result in poorer performance: It is possible to explore your data by using the “Explore in Excel” capability on a dataset. 這樣做會在 Excel 中建立樞紐分析表和樞紐分析圖。This will allow Pivot Tables and Pivot Charts to be created in Excel. 雖然使用 DirectQuery 的資料集支援這項功能,但效能通常比在 Power BI 中建立視覺效果還要慢,因此在使用 Excel 對您很重要的情況下,您應該將此列入決定使用 DirectQuery 的考量。While this capability is supported on datasets using DirectQuery, the performance is generally slower than creating visuals in Power BI, and therefore if the use of Excel is important for your scenarios, this should be accounted for in your decision to use DirectQuery.

安全性Security

如本文稍早所述,使用 DirectQuery 的報表一律會在發佈至 Power BI 服務之後,使用相同的固定認證來連接到基礎資料來源。As discussed earlier in this article, a report using DirectQuery will always use the same fixed credentials to connect to the underlying data source, after publish to the Power BI service. 同樣地,請注意,這特別是指 DirectQuery,而不是 SQL Server Analysis Services 的即時連接,後者在這方面不同。Again, note this refers specifically to DirectQuery, not to live connections to SQL Server Analysis Services, which is different in this respect. 因此,您必須在發佈 DirectQuery 報表之後立即設定將使用的使用者認證。Hence immediately after publish of a DirectQuery report, it is necessary to configure the credentials of the user that will be used. 完成這項設定之前,在 Power BI 服務中開啟報表會產生錯誤。Until this is done, opening the report on the Power BI service would result in an error.

一旦提供使用者認證,「不論是哪位使用者開啟報表」,都會使用這些認證。Once the user credentials are provided, then those credentials will be used, irrespective of the user who opens the report. 在這方面與匯入的資料完全相同,除非將資料列層級安全性定義為報表的一部分,否則所有使用者都會看到相同的資料。In this regard it is exactly like imported data, in that every user will see the same data, unless Row Level Security has been defined as part of the report. 因此,如果基礎來源中有定義任何安全性規則,共用報表時同樣必須注意。Hence the same attention must be paid to sharing the report, if there are any security rules defined in the underlying source.

Power BI 服務中的行為Behavior in the Power BI service

本節說明 Power BI 服務中的 DirectQuery 報表行為,主要是為了解將放置於後端資料來源上之負載的程度、將共用報表和儀表板的指定使用者數目、報表的複雜度,以及是否已在報表中定義資料列層級安全性。This section describes the behavior of a DirectQuery report in the Power BI service, primarily so as to be able to understand the degree of load that will be placed on the back end data source, given the number of users that the report and dashboard will be shared with, the complexity of the report, and whether Row Level Security has been defined in the report.

報表 - 開啟、互動、編輯Reports – opening, interacting with, editing

開啟報表之後,會重新整理目前可見頁面上的所有視覺效果。When a report is opened, then all the visuals on the currently visible page will refresh. 每個視覺效果通常需要傳送至少一個查詢至基礎資料來源。Each visual will generally require at least one query to the underlying data source. 某些視覺效果可能需要多個查詢 (例如,如果顯示來自兩個不同事實資料表的彙總值、包含更複雜的量值,或包含 Count Distinct 等非加總量值)。Some visuals might require more than one query (for example, if it showed aggregate values from two different fact tables, or contained a more complex measure, or contained totals of a non-additive measure like Count Distinct). 移至新頁面將會導致重新整理這些視覺效果,並導致傳送一組新的查詢至基礎來源。Moving to a new page will result in those visuals being refreshed, resulting in a new set of queries to the underlying source.

報表上的每個使用者互動都可能會導致重新整理視覺效果。Every user interaction on the report might result in visuals being refreshed. 例如,在交叉分析篩選器上選取不同的值將需要傳送一組新的查詢,以重新整理所有受影響的視覺效果。For example, selecting a different value on a slicer will require sending a new set of queries to refresh all of the effected visuals. 相同規則也適用於在視覺效果上按一下以交叉醒目提示其他視覺效果,或是變更篩選。The same is true for clicking on a visual to cross-highlight other visuals, or changing a filter.

當然同樣地,編輯新的報表需要針對產生最終所需視覺效果之路徑上的每個步驟傳送查詢。Similarly of course, editing a new report with require queries to be sent for each step on the path to produce the final desired visual.

這會產生一些結果快取,因此如果最近取得完全相同的結果,則會即時重新整理視覺效果。There is some caching of results, so that the refresh of a visual will be instantaneous if the exact same results have recently been obtained. 如果將任何資料列層級安全性定義為報表的一部分,則不會在使用者之間共用這類快取。Such caches are not shared across users, if there is any Row Level Security defined as part of the report.

儀表板重新整理Dashboard Refresh

個別視覺效果或整個頁面可以釘選為儀表板磚。Individual visuals, or entire pages, can be pinned to dashboard as tiles. DirectQuery 資料集為基礎的磚接著會根據排程自動重新整理,導致傳送查詢至後端資料來源。Tiles based on DirectQuery datasets are then refreshed automatically according to a schedule, resulting in queries being sent to the backend data source. 根據預設,這會每小時發生一次,但可以在 [資料集設定] 中設定為每週及每 15 分鐘。By default, this occurs every hour, but can be configured as part of Dataset settings to be between weekly, and every 15 minutes.

如果模型中未定義資料列層級安全性,這表示每個磚會重新整理一次,而且所有使用者會共用這些結果。If no Row Level Security is defined in the model, this means that each tile would be refreshed once, and the results shared across all users. 如果已定義資料列層級安全性,則可能會有很大的乘數效應 – 每個磚需要針對每位使用者傳送個別查詢至基礎來源。If Row Level Security is defined, then there can be a large multiplier effect – each tile requires separate queries per user to be sent to the underlying source.

因此,假設儀表板含有 10 個磚、與 100 位使用者共用、在使用 DirectQuery 且具有資料列層級安全性的資料集上建立,而且設定為每 15 分鐘重新整理一次,則會導致每 15 分鐘傳送至少 1000 個查詢至後端來源。Hence a dashboard with ten tiles, shared with 100 users, created on a dataset using DirectQuery with Row Level Security, and configured to refresh every 15 minutes, would result in at least 1000 queries being sent every 15 minutes to the back end source.

因此,請務必仔細考慮使用資料列層級安全性,以及設定重新整理排程。Hence careful consideration must be paid to the use of Row Level Security, and the configuring of the refresh schedule.

逾時Timeouts

系統會將四分鐘的逾時值套用至 Power BI 服務中的個別查詢,需要更長時間的查詢將會失敗。A timeout of four minutes is applied to individual queries in the Power BI service, and queries taking longer than that will simply fail. 如稍早所述,針對提供幾近互動之查詢效能的來源,建議使用 DirectQuery,因此這項限制是為了防止執行時間過長所造成的問題。As stressed earlier, it is recommended that DirectQuery be used for sources that provide near interactive query performance, so this limit is intended to prevent issues from overly long execution times.

其他影響Other implications

使用 DirectQuery 的一些其他一般影響如下:Some other general implications of using DirectQuery are the following:

  • 如果資料將要變更,則必須重新整理以確保顯示最新資料:由於使用快取,因此無法保證視覺效果一律會顯示最新資料。If data is changing, it is necessary to Refresh to ensure the latest data is shown: Given the use of caches, there is no guarantee that the visual is always showing the latest data. 例如,視覺效果可能會顯示最後一天的交易。For example, a visual might show the transactions in the last day. 由於已變更交叉分析篩選器,因此可能會重新整理以顯示最後兩天的交易,包括一些最近的新到達交易。Then due to a slicer being changed, it might refresh to show the transactions for the last two days, including some very recent, newly arrived transactions. 將交叉分析篩選器返回其原始值會導致再次顯示之前取得的快取值,這不包括之前所示的新到達交易。Returning the slicer to its original value would result in it again showing the cached value previously obtained, that would not include the newly arrived transaction seen before.

    選取 [重新整理] 將會清除任何快取,而重新整理頁面上的視覺效果即可顯示最新資料。Selecting Refresh will clear any caches, and refresh all the visuals on the page to show the latest data.

  • 如果資料將要變更,則無法保證視覺效果之間的一致性:不同的視覺效果 (不論是在相同頁面或不同頁面上) 可能會在不同的時間重新整理。If data is changing, there is no guarantee of consistency between visuals: Different visuals, whether on the same page or on different pages, might be refreshed at different times. 因此,如果基礎來源中的資料將要變更,則無法保證每個視覺效果將會顯示當時的資料。Thus if the data in the underlying source is changing, there is no guarantee that each visual will be showing the data at the exact same point of time. 事實上,由於有時候單一視覺效果需要多個查詢 (例如為了取得詳細資料和總計),甚至無法保證單一視覺效果內的一致性。Indeed, given that sometimes more than one query is required for a single visual (for example, to obtain the details and the totals) then consistency even within a single visual is not guaranteed. 若要做出這項保證,需要在每次有任何視覺效果重新整理時重新整理所有視覺效果,這在搭配使用基礎資料來源中成本很高的功能 (例如快照隔離) 時會造成額外成本。To guarantee this would require the overhead of refreshing all visuals whenever any visual refreshed, in tandem with the use of costly features like Snapshot Isolation in the underlying data source.

    同樣地,選取 [重新整理] 來重新整理頁面上的所有視覺效果可大幅減輕此問題。This issue can be mitigated to a large extent by again selecting Refresh, to will refresh all of the visuals on the page. 此外請注意,即使使用匯入模式,如果從多個資料表匯入資料,在確保一致性方面還是會遇到類似的問題。And it should be noted that even if using Import mode, there is a similar problem of guaranteeing consistency if importing data from more than one table.

  • 需要在 Power BI Desktop 中重新整理以反映任何中繼資料變更:發行報表之後,[重新整理] 只會重新整理報表中的視覺效果。Refresh in Power BI Desktop is needed to reflect any metadata changes: After a report is published, Refresh will simply refresh the visuals in the report. 如果基礎來源的結構描述已變更,則不會自動套用這些變更以改變欄位清單中可用的欄位。If the schema of the underlying source has changed, then those changes are not automatically applied to change the available fields in the field list. 因此,如果已從基礎來源移除資料表或資料行,可能會導致查詢在重新整理時失敗。Thus if tables or columns have been removed from the underlying source, it might result in query failure upon refresh. 在 Power BI Desktop 中開啟報表,然後選擇 [重新整理],即會更新模型中的欄位以反映變更。Opening the report in Power BI Desktop, and choosing Refresh, will update the fields in the model to reflect the changes.
  • 任何查詢最多可傳回一百萬個資料列的限制:對基礎來源的任何單一查詢可傳回的資料列數目有固定限制,那就是一百萬個資料列。Limit of one million rows returned on any query: There is a fixed limit of one million rows placed on the number of rows that can be returned in any single query to the underlying source. 這通常不會造成實際影響,而且視覺效果本身不會顯示那麼多的點。This generally has no practical implications, and visuals themselves aren’t going to display that many points. 不過,如果 Power BI 不會將傳送的查詢完整最佳化,而且有些要求的中繼結果超過限制,就可能會出現此限制。However, the limit can occur in cases where Power BI is not fully optimizing the queries sent, and there is some intermediate result being requested that exceeds the limit. 在產生更合理最終狀態的路徑上建立視覺效果時,也可能會發生此情況。It can also occur whilst building a visual, on the path to a more reasonable final state. 例如,在套用一些篩選之前,如果客戶超過一百萬,加入 [客戶] 和 [總銷售量] 會達到此限制。For example, including Customer and TotalSalesQuantity would hit this limit if there were more than 1m customers, until some filter were applied.

    傳回的錯誤會是「外部資料來源的查詢結果集,超過允許列數的最大值 '1000000'」。The error that would be returned would be “The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.”

  • 無法從匯入模式變更為 DirectQuery 模式:請注意,雖然您通常可以將模型從 DirectQuery 模式切換為使用匯入模式,但這表示您必須匯入所有必要的資料。Cannot change from import to DirectQuery mode: Note that while it's generally possible to switch a model from DirectQuery mode to use import mode, this means all the necessary data must be imported. 您也無法切換回之前的模式 (主要是由於功能集在 DirectQuery 模式中不受支援所致)。It is also not possible to switch back (primarily due to the set of features not supported in DirectQuery mode). 對於透過 SAP BW 等多維度來源的 DirectQuery 模型,由於處理外部量值的方式完全不同,因此也無法從 DirectQuery 切換至匯入。DirectQuery models over multidimensional sources like SAP BW also cannot be switched from DirectQuery to import, due to the completely different treatment of external measures.

Power BI 服務中的 DirectQueryDirectQuery in the Power BI service

支援來自 Power BI Desktop 的所有來源。All sources are supported from Power BI Desktop. 也可以直接從 Power BI 服務使用一些來源。Some sources are also available directly from within the Power BI service. 例如,您可以讓企業用戶使用 Power BI 連接到 Salesforce 中的資料,並立即取得儀表板,而不需要使用 Power BI DesktopFor example, it is possible for a business user to use Power BI to connect to their data in Salesforce, and immediately get a dashboard, without use of Power BI Desktop.

只有兩個啟用 DirectQuery 的來源可以在服務中直接使用:Only two of the DirectQuery enabled-sources are available directly in the service:

  • SparkSpark
  • Azure SQL 資料倉儲Azure SQL Data Warehouse

不過,強烈建議在 Power BI Desktop 中開始使用透過這兩個來源的任何 DirectQueryHowever, it is strongly recommended that any use of DirectQuery over those two sources start within Power BI Desktop. 這是因為一開始在 Power BI 服務中進行連接時,會套用許多重要限制,換句話說起點很容易 (在 Power BI 服務中開始),還有些限制可進一步增強產生的報表 (例如,無法接著建立任何計算、使用許多分析功能,或甚至重新整理中繼資料以反映基礎結構描述的任何變更)。The reason is that when the connection is initially made in the Power BI service, many key limitations will apply, meaning that while the start point was easy (starting in the Power BI service), there are limitations on enhancing the resulting report any further (for example, it's not possible then to create any calculations, or use many analytical features, or even refresh the metadata to reflect any changes to the underlying schema).

成功使用 DirectQuery 的指引Guidance for using DirectQuery successfully

如果您打算使用 DirectQuery,本節提供一些如何確保成功的高階指引。If you're going to use DirectQuery, then this section provides you with some high level guidance on how to ensure success. 本節中的指引衍生自本文稍早所述的<使用 DirectQuery 的影響>。The guidance in this section is derived from the implications of using DirectQuery that have been described in this article.

後端資料來源效能Backend data source performance

強烈建議驗證簡單視覺效果能夠在合理時間內重新整理。It is strongly recommended to validate that simple visuals will be able to refresh in a reasonable time. 這應該是在 5 秒內有合理的互動體驗。This should be within 5 seconds to have a reasonable interactive experience. 當然,如果視覺效果所需的時間超過 30 秒,則很有可能在發行報表之後發生其他問題,導致解決方案無法運作。Certainly if visuals are taking longer than 30 seconds, then it's highly likely that further issues will occur following publication of the report, which will make the solution unworkable.

如果查詢速度緩慢,則調查的第一個重點會是檢查傳送至基礎來源的查詢,以及觀察查詢效能的原因。If queries are slow, then the first point of investigation is to examine the queries being sent to the underlying source, and the reason for the query performance being observed. 本主題並未涵蓋在整組可能的基礎來源之間進行資料庫最佳化的各種最佳做法,但適用於應用到大多數情況的標準資料庫做法:This topic doesn't cover the wide range of database optimization best practices across the full set of potential underlying sources, but it does apply to the standard database practices that apply to most situations:

  • 以整數資料行為基礎的關聯性,其效能通常比其他資料類型資料行的聯結更佳Relationships based on integer columns generally perform better than joins on columns of other data types
  • 應該建立適當的索引,這通常表示在支援的來源 (例如 SQL Server) 中使用資料行存放區索引。The appropriate indexes should be created, which generally means the use of column store indexes in those sources that support them (for example, SQL Server).
  • 應該更新來源中任何必要的統計資料Any necessary statistics in the source should be updated

模型設計指引Model Design Guidance

定義模型時,請考慮執行下列動作:When defining the model, consider doing the following:

  • 避免在 [查詢編輯器] 中有複雜的查詢。Avoid complex queries in Query Editor. 定義於 [查詢編輯器] 中的查詢將會轉譯成單一 SQL 查詢,然後包含在傳送至該資料表之每個查詢的子選擇中。The query that's defined in the Query Editor will be translated into a single SQL query, that will then be included in the subselect of every query sent to that table. 如果該查詢很複雜,可能會導致每個傳送的查詢發生效能問題。If that query is complex, it might result in performance issues on ever query sent. 在 [查詢編輯器] 中選取最後一個步驟,然後從操作功能表選擇 [檢視原生查詢],即可取得一組步驟的實際 SQL 查詢。The actual SQL query for a set of steps can be obtained by selecting the last step in Query Editor, and choosing View Native Query from the context menu.
  • 確保量值很簡單。Keep measures simple. 至少在一開始,建議將量值限制在簡單彙總。At least initially, it is recommended to limit measures to simple aggregates. 然後,如果這些量值的效能令人滿意,則可以定義更複雜的量值,但請注意每個量值的效能。Then if those perform in a satisfactory manner, more complex measures can be defined, but paying attention to the performance for each.
  • 避免計算結果欄上有關聯性。Avoid relationships on calculated columns. 這與必須執行多重資料行聯結的資料庫而言特別相關。This is particularly relevant to databases where it is necessary to perform multi-column joins. Power BI 目前不允許以多個作為 FK/PK 的資料行為基礎的關聯性。Power BI today does not allow a relationship to be based on multiple columns as the FK/PK. 常見的因應措施是使用計算結果欄將資料行串連在一起,再讓聯結以此為依據。The common workaround is to concatenate the columns together using a calculated column, and base the join on that. 雖然這個因應措施適用於已匯入的資料,但若為 DirectQuery,則會在運算式上產生聯結,這通常會防止使用任何索引,而導致效能不佳。While this workaround is reasonable for imported data, in the case of DirectQuery it results in a join on an expression, that commonly prevents use of any indexes, and leads to poor performance. 唯一的因應措施是實際將多個資料行具體化為基礎資料庫中的單一資料行。The only workaround is to actually materialize the multiple columns into a single column in the underlying database.
  • 避免 uniqueidentifier 資料行上有關聯性。Avoid relationships on uniqueidentifier columns. Power BI 原本就不支援 uniqueidentifier 資料類型。Power BI does not natively support a datatype of uniqueidentifier. 因此,在 uniqueidentifier 類型資料行之間定義關聯性,會導致使用聯結的查詢需要轉換。Hence defining a relationship between columns of type uniqueidentifier column will result in a query with a join involving a Cast. 同樣地,這通常會導致效能不佳。Again, this commonly leads to poor performance. 在此案例經過特別最佳化之前,唯一的因應措施是將資料行具體化為基礎資料庫中的替代類型。Until this case is specifically optimized, the only workaround is to materialize columns of an alternative type in the underlying database.
  • 隱藏關聯性上的 to 資料行。Hide the to column on relationships. 請隱藏關聯性上的 to 資料行 (通常是 to 資料表上的主索引鍵),使其不會出現在欄位清單中,進而無法在視覺效果中使用。The to column on relationships (commonly the primary key on the to table) should be hidden, so that it does not appear in the field list, and therefore cannot be used in visuals. 通常,關聯性所依據的資料行事實上是「系統資料行」 (例如資料倉儲中的 Surrogate 索引鍵),因此隱藏這類資料行是很好的做法。Often the columns on which relationships are based are in fact system columns (for example, surrogate keys in a data warehouse) and hiding such columns is good practice anyway. 如果資料行具有意義,則引進可見的計算結果欄,以及包含相當於主索引鍵之簡單運算式的計算結果欄。If the column does have meaning, then introduce a calculated column that is visible, and that has a simple expression of being equal to the primary key. 例如:For example:

    ProductKey_PK   (Destination of a relationship, hidden)
    ProductKey (= [ProductKey_PK],   visible)
    ProductName
    ...
    

    執行此動作的原因,只是為了避免在視覺效果包含主索引鍵資料行時,可能發生的效能問題。The reason for doing this is simply to avoid a performance issue that can occur otherwise if a visual includes the primary key column.

  • 檢查計算結果欄的所有使用和資料類型變更。Examine all uses of calculated columns and data type changes. 使用這些功能不一定有害,這會導致傳送至基礎來源的查詢包含運算式,而不是簡單的資料行參考,同樣地這可能會導致無法使用索引。Use of these capabilities are not necessarily harmful, they result in the queries sent to the underlying source containing expressions rather than simple references to columns, that again might result in indexes not being used.
  • 避免對關聯性使用雙向交叉篩選 (預覽)。Avoid use of the (preview) bi-directional cross filtering on relationships.
  • 嘗試設定 [採用參考完整性]。Experiment with setting Assume referential integrity. 關聯性上的 [採用參考完整性] 設定可讓查詢使用內部聯結陳述式,而不是外部聯結。The Assume Referential Integrity setting on relationships enables queries to use INNER JOIN statements rather than OUTER JOIN. 這通常可改善查詢效能,不過會取決於資料來源的詳細資料。This generally improves query performance, though it does depend on the specifics of the data source.
  • 請勿在 [查詢編輯器] 中使用相對資料篩選。Do not use the relative data filtering in Query Editor. 您無法在 [查詢編輯器] 中定義相對資料篩選。It's possible to define relative date filtering in Query Editor. 例如,篩選日期落在過去 14 天內的資料列。For example, to filter to the rows where the date is in the last 14 days.

    不過,這會轉譯成依撰寫查詢時的固定日期進行篩選。However, this will be translated into a filter based on the fixed date, as at the time the query was authored. 檢視原生查詢時會看到此問題。This can be seen from viewing the native query.

    這當然不是想要的結果。This is almost certainly not what was wanted. 若要確保依執行報表時的日期套用篩選,請改為在報表中套用篩選以作為報表篩選。To ensure the filter is applied based upon the date as at the time the report is executed then instead apply the filter in the report as a Report Filter. 目前若要執行這項操作,請建立計算天數前的計算結果欄 (使用 DAX DATE() 函式),然後在篩選中使用該計算結果欄。Currently this would be done by creating a calculated column calculating the number of days ago (using the DAX DATE() function), and then using that calculated column in a filter.

報表設計指引Report Design Guidance

使用 DirectQuery 連接建立報表時,請依循下列指引:When creating a report using a DirectQuery connection, adhere to the following guidance:

  • 先套用篩選:請一律在建立視覺效果一開始就套用任何適用的篩選。Apply filters first: Always apply any applicable filters at the start of building a visual. 例如,與其依序在 [總銷售額] 和 [產品名稱] 中拖曳,然後篩選到特定年份,倒不如一開始就套用年份篩選。For example, rather than drag in the TotalSalesAmount, and ProductName, then filter to a particular year, apply the filter on Year at the very start. 這是因為建立視覺效果的每個步驟都會傳送查詢,雖然可以在完成第一個查詢之前進行其他變更,但仍會在基礎來源中留下不必要的負載。This is because each step of building a visual will send a query, and whilst it is possible to then make another change before the first query has completed, this still leaves unnecessary load on the underlying source. 藉由及早套用篩選,通常可降低這些中繼查詢的成本。By applying filters early, it generally makes those intermediate queries less costly. 此外,未能及早套用篩選可能會導致達到上述的一百萬個資料列限制。Also, failing to apply filters early can result in hitting the 1m row limit above.
  • 限制單頁上的視覺效果數目:開啟頁面時 (或某個頁面層級交叉分析篩選器或篩選變更時),會重新整理頁面上的所有視覺效果。Limit the number of visuals on a page: When a page is opened (or some page level slicer or filter changed) then all of the visuals on a page are refreshed. 平行傳送的查詢數目也受到限制,因此當視覺效果數目增加時,其中一些視覺效果會循序重新整理,而增加重新整理整個頁面所需的時間。There is also a limit on the number of queries that are sent in parallel, hence as the number of visuals increases, some of the visuals will be refreshed in a serial manner, increasing the time taken to refresh the entire page. 因此,建議限制單頁上的視覺效果數目,改為擁有更多、更簡單的頁面。For this reason it's recommended to limit the number of visuals on a single page, and instead have more, simpler pages.
  • 考慮關閉視覺效果之間的互動:報表頁面上的視覺效果預設可用於交叉篩選和交叉醒目提示頁面上的其他視覺效果。Consider switching off interaction between visuals: By default, visualizations on a report page can be used to cross-filter and cross-highlight the other visualizations on the page. 例如,在圓形圖上選取 "1999" 時,會在橫條圖上交叉醒目提示,以顯示 "1999" 類別的銷售量。For example, having selected “1999” on the pie chart, the column chart is cross highlighted to show the sales by category for “1999”.

    不過,此互動可受到控制,如這篇文章中所述。However, this interaction can be controlled as described in this article. 在 DirectQuery 中,這類交叉篩選和交叉醒目提示需要傳送查詢至基礎來源,因此如果回應使用者的選取項目所需的時間過長,則應該關閉互動。In DirectQuery such cross-filtering and cross-highlighting requires queries to be sent to the underlying source, so the interaction should be switched off if the time taken to respond to users' selections would be unreasonably long.

  • 考慮只共用報表:發佈至 Power BI 服務之後有幾種不同的共用內容方式。Consider sharing the report only: There are different ways of sharing content after publishing to the Power BI service. 若是 DirectQuery,建議只考慮共用完成的報表,而不允許其他使用者撰寫新的報表 (可能會在建立特定視覺效果時遇到效能問題)。In the case of DirectQuery, it's advisable to only considering sharing the finished report, rather than allow other users to author new reports (and potentially encounter performance issues for the particular visuals that they build).

除了上述建議清單之外,注意下列每項報告功能可能會導致效能問題:In addition to the above list of suggestions, note that each of the following reporting capabilities can cause performance issues:

  • 量值篩選:含有量值 (或資料行彙總) 的視覺效果可能在這些量值中包含篩選。Measure filters: Visuals containing measures (or aggregates of columns) can contain filters in those measures. 例如,下列視覺效果依類別顯示 SalesAmount,但只包含銷售量超過 2000 萬的類別。For example, the visual below shows SalesAmount by Category, but only including those Categories with more than 20M of sales.

    這會導致傳送兩個查詢至基礎來源:This will result in two queries being sent to the underlying source:

    • 第一個查詢會擷取符合條件 (銷售量 > 2000 萬) 的類別The first query will retrieve the Categories meeting the condition (Sales > 20M)
    • 第二個查詢會接著擷取視覺效果的必要資料,包括符合 WHJERE 子句中條件的類別。The second query will then retrieve the necessary data for the visual, including the Categories that met the condition in the WHJERE clause.

    如果像此範例有數百個或數千個類別,通常執行起來沒有問題。This generally performs just fine if there are hundreds or thousands of categories, as in this example. 如果類別數目更大,效能可能會降低 (事實上,如果有超過一百萬個類別符合條件,由於稍早所述的一百萬個資料列限制,查詢將會失敗)。Performance can degrade if the number of categories is much larger (and indeed, the query will fail if there were more than a million categories meeting the condition, due to the one million row limit discussed earlier).

  • 篩選:您可以定義進階篩選,只篩選依某個量值排列次序的前 (或後) N 個值;例如,只包括上述視覺效果中的前 10 項類別。TopN filters: Advanced filters can be defined to filter on only the Top (or Bottom) N values ranked by some measure, for example, to only include the Top 10 Categories in the visual above. 同樣地,這會導致傳送兩個查詢至基礎來源。This will again result in two queries being sent to the underlying source. 不過,第一個查詢會傳回基礎來源中的所有類別,然後根據傳回的結果來決定前 N 項。However, the first query will return all categories from the underlying source, and then the TopN are determined based on the returned results. 根據所涉及的資料行基數,這可能會導致效能問題 (或由於一百萬個資料列限制而查詢失敗)。Depending on the cardinality of the column involved, this can lead to performance issues (or query failures due to the 1m row limit).
  • 中位數:一般而言,任何彙總 (Sum、Count Distinct 等) 都會發送至基礎來源。Median: Generally, any aggregation (Sum, Count Distinct, so on) is pushed to the underlying source. 不過,中位數則不然,因為基礎來源通常不支援此彙總。However, this is not true for Median, as this aggregate is generally not supported by the underlying source. 在這種情況下,會從基礎來源擷取詳細資料,然後從傳回的結果計算中位數。In such cases, the detail data is retrieved from the underlying source, and the Median calculated from the returned results. 這適用於對較少的結果數目計算中位數,但如果基數很大,則會發生效能問題 (或由於一百萬個資料列限制而查詢失敗)。This is reasonable when the median is to be calculated over a relatively small number of results, but performance issues (or query failures due to the 1m row limit) will occur if the cardinality is large. 例如,計算國家/地區人口的中位數可能適當,但計算售價的中位數可能不適當。For example, Median Country Population might be reasonable, but Median Sales Price might not be.
  • 進階文字篩選 (「包含」及類似的篩選):篩選文字資料行時,進階篩選允許「包含」和「開頭為」等篩選。Advanced text filters (‘contains’ and similar): When filtering on a text column, the advanced filtering allows filters like ‘contains’ and ‘begins with’ and so on. 這些篩選肯定會導致某些資料來源的效能降低。These filters can certainly result in degraded performance for some data sources. 特別是如果真正需要的是完全相符 (「是」或「不是」),則不應該使用預設的「包含」篩選。In particular, the default ‘contains’ filter should not be used if what is really required is an exact match (‘is’ or ‘is not’). 雖然結果可能相同,但根據實際資料,效能可能會因使用索引而有相當大的不同。Although the results might be the same, depending on the actual data, the performance might be drastically different due to the use of indexes.
  • 複選交叉分析篩選器:根據預設,交叉分析篩選器只可單選。Multi select slicers: By default, slicers only allow a single selection to be made. 在篩選中允許複選可能會導致一些效能問題,因為當使用者在交叉分析篩選器中選取一組項目時 (例如十個關注產品),每次新的選取都會導致傳送查詢至後端來源。Allowing multi selection in filters can cause some performance issues, because as the user selects a set of items in the slicer (for example, the ten products they are interested in), then each new selection will result in queries being sent to the backend source. 雖然使用者可以在查詢完成前選取下一個項目,但這樣做會對基礎來源產生額外的負載。Whilst the user can select the next item prior to the query completing, this does result in extra load on the underlying source.

診斷效能問題Diagnosing performance issues

本節說明如何診斷效能問題,或如何取得更詳細的資訊以便對報表進行最佳化。This section describes how to diagnose performance issues, or how to get more detailed information to allow the reports to be optimized.

強烈建議在 Power BI Desktop 中開始進行效能問題的任何診斷,而不是在 Power BI 服務中。It's strongly recommended that any diagnosis of performance issues starts in Power BI Desktop, rather than in the Power BI service. 效能問題通常與基礎來源的效能等級相關,在更加隔離的 Power BI Desktop 環境中比較容易發現及診斷這些問題,而能夠一開始就排除特定元件 (例如 Power BI 閘道)。It's commonly the case that performance issues are simply based on the level of performance of the underlying source, and these are more easily identified and diagnosed in the much more isolated environment of Power BI Desktop, and initially eliminates certain components (such as the Power BI gateway). 只有在發現與 Power BI Desktop 無關的效能問題時,才應該將調查重點放在 Power BI 服務中的報表詳細資料上。Only if the performance issues are found to not be present with Power BI Desktop should investigation focus on the specifics of the report in the Power BI service.

同樣地,建議先嘗試將任何問題隔離到個別視覺效果,而不是頁面上的許多視覺效果。Similarly, it is recommended to first try to isolate any issues to an individual visual, rather than many visuals on a page.

假設已執行這些步驟 (本節中的前幾個段落),我們現在於 Power BI Desktop 中頁面上的單一視覺效果仍稍嫌緩慢。So, let's say those steps (in the previous paragraphs in this section) have been taken - we now have a single visual on a page in Power BI Desktop that is still sluggish. 若要判斷 Power BI Desktop 傳送至基礎來源的查詢,您可以檢視可能由該來源發出的追蹤/診斷資訊。To determine the queries that are sent to the underlying source by Power BI Desktop, it's possible to view traces/diagnostic information that might be emitted by that source. 這類追蹤也可能包含有關如何執行查詢及如何改善之詳細資料的有用資訊。Such traces might also contain useful information about the details of how the query was executed, and how it can be improved.

此外,即使來源中沒有這類追蹤,您也可以檢視 Power BI 所傳送的查詢及其執行時間,如下所述。Further, even in the absence of such traces from the source, it's possible to view the queries sent by Power BI, along with their execution times, as described next.

判斷 Power BI Desktop 所傳送的查詢Determining the queries sent by Power BI Desktop

根據預設,Power BI Desktop 會在指定的工作階段期間,將事件記錄到稱為 FlightRecorderCurrent.trc 的追蹤檔案。By default, Power BI Desktop logs events during a given session to a trace file called FlightRecorderCurrent.trc.

對於某些 DirectQuery 來源,此記錄包含所有傳送至基礎資料來源的查詢 (未來將包含其餘 DirectQuery 來源)。For some DirectQuery sources, this log includes all queries sent to the underlying data source (the remaining DirectQuery sources will be included in the future). 傳送查詢至記錄的來源如下:The sources that send queries to the log are the following:

  • SQL ServerSQL Server
  • Azure SQL DatabaseAzure SQL Database
  • Azure SQL 資料倉儲Azure SQL Data warehouse
  • OracleOracle
  • TeradataTeradata
  • SAP HANASAP HANA

追蹤檔案可能位於目前使用者的 AppData 資料夾中:The trace file can be found in the AppData folder for the current user:

\<User>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces

以下是移至此資料夾的簡單方法:在 Power BI Desktop 中,選取 [檔案] > [選項及設定] > [選項],然後選取 [診斷]。Here's an easy way to get to this folder: In Power BI Desktop select File > Options and settings > Options, and then select Diagnostics. 下列對話方塊視窗隨即出現:The following dialog window appears:

當您選取 [開啟追蹤資料夾] 連結時,[診斷選項] 下會隨即開啟下列資料夾:When you select the Open traces folder link, under Diagnostic Options, the following folder opens:

\<User>\AppData\Local\Microsoft\Power BI Desktop\Traces

巡覽至該資料夾的父資料夾會顯示含有 AnalysisServicesWorkspaces 的資料夾,其中每個開啟的 Power BI Desktop 執行個體會包含一個工作區子資料夾。Navigating to that folder's parent folder displays the folder containing AnalysisServicesWorkspaces, which will contain one workspace subfolder for every open instance of Power BI Desktop. 這些子資料夾會在名稱後面加上整數來命名,例如 AnalysisServicesWorkspace2058279583These subfolders are named with an integer suffix, such as AnalysisServicesWorkspace2058279583.

該資料夾內會有 \Data 子資料夾,其中包含目前 Power BI 工作階段的追蹤檔案 FlightRecorderCurrent.trc。Inside that folder is a \Data subfolder that contains the trace file FlightRecorderCurrent.trc for the current Power BI session. 當相關聯的 Power BI Desktop 工作階段結束時,即會刪除對應的工作區資料夾。The corresponding workspace folder is deleted when the associated Power BI Desktop session ends.

您可以使用 SQL Server Profiler 工具來讀取追蹤檔案,該工具是當作 SQL Server Management Studio 的一部分免費下載取得。The trace files can be read using the SQL Server Profiler tool, which is available as a free download as part of SQL Server Management Studio. 您可以從這個位置取得該工具。You can get that from this location.

下載並安裝 SQL Server Management Studio 之後,請執行 SQL Server ProfilerOnce you download and install SQL Server Management Studio, run SQL Server Profiler.

若要開啟追蹤檔案,請執行下列步驟:To open the trace file, take the following steps:

  1. SQL Server Profiler 中,選取 [檔案] > [開啟] > [追蹤檔案]In SQL Server Profiler, select File > Open > Trace file
  2. 輸入目前開啟之 Power BI 工作階段的追蹤檔案路徑,例如:Enter the path to the trace file for the currently open Power BI session, such as:

      C:\Users\<user>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspace2058279583\Data
    
  3. 開啟 FilghtRecorderCurrent.trcOpen FilghtRecorderCurrent.trc

這會顯示目前工作階段中的所有事件。All events from the current session are displayed. 以下顯示註解的範例,其中醒目提示多組事件。An annotated example is shown below, which highlights groups of events. 每個群組具有下列各項:Each group has the following:

  • 「查詢開始」和「查詢結束」事件,分別代表 UI (例如從視覺效果或透過在篩選 UI 中填入值清單) 所產生之 DAX 查詢的開始和結束A Query Begin and Query End event, which represent the start and end of a DAX query generated by the UI (for example, from a visual, or from populating a list of values in the filter UI)
  • 一或多組「DirectQuery 開始」和「DirectQuery 結束」事件,分別代表在評估 DAX 查詢的過程中傳送至基礎資料來源的一個查詢。One or more pairs of DirectQuery Begin and DirectQuery End events, which represent a query sent to the underlying data source, as part of evaluating the DAX query.

請注意,多個 DAX 查詢可以平行執行,因此可以交錯來自不同群組的事件。Note that multiple DAX queries can be executed in parallel, so events from different groups can be interleaved. ActivityID 的值可以用來判斷哪些事件是屬於相同的群組。The value of the ActivityID can be used to determine which events belong to the same group.

其他關注的資料行如下:Other columns of interest are the following:

  • TextData:事件的文字詳細資料。TextData: The textual detail of the event. 對於「查詢開始/結束」事件,這會是 DAX 查詢。For “Query Begin/End” events this will be the DAX query. 對於「DirectQuery 開始/結束」事件,這會是傳送至基礎來源的 SQL 查詢。For “DirectQuery Begin/End” events this will be the SQL query sent to the underlying source. 目前所選事件的 TextData 也會顯示在下方區域中。The TextData for the currently selected event is also displayed in the region at the bottom.
  • EndTime:事件的完成時間。EndTime: When the event completed.
  • 持續時間:執行 DAX 或 SQL 查詢所需的持續時間 (毫秒)。Duration: The duration, in milliseconds, taken to execute the DAX or SQL query.
  • 錯誤:指出是否發生錯誤 (在此情況下也會以紅色顯示事件)。Error: Indicates if an error occurred (in which case the event is also displayed in red).

請注意,在上圖中,已縮小一些較不相關的資料行,以便更清楚地看到相關的資料行。Note that in the image above, some of the less interesting columns have narrowed, to allow the interesting columns to be seen more easily.

擷取追蹤以協助診斷潛在效能問題的建議方法如下:The recommended approach to capturing a trace to help diagnose a potential performance issue is the following:

  • 開啟單一 Power BI Desktop 工作階段 (以避免多個工作區資料夾造成混淆)Open a single Power BI Desktop session (to avoid the confusion of multiple workspace folders)
  • Power BI Desktop 中執行一組關注的動作。Perform the set of actions of interest in Power BI Desktop. 此外還包含一些其他的動作,以確保將關注的事件排清到追蹤檔案中。Include a few additional actions beyond that, to ensure that the events of interest are flushed into the trace file.
  • 開啟 SQL Server Profiler 並檢查追蹤,如稍早所述。Open SQL Server Profiler and examine the trace, as described earlier. 請記住,關閉 Power BI Desktop 時會刪除追蹤檔案。Remember that the trace file will be deleted upon closing Power BI Desktop. 此外,在 Power BI Desktop 中的進一步動作不會立即顯示 – 必須關閉追蹤檔案再重新開啟,才會看到新的事件。Also, further actions in Power BI Desktop will not immediately appear – the trace file should be closed and re-opened to see the new events.
  • 請將個別工作階段保持得很小 (十秒而不是數百秒的動作),以便更容易解譯追蹤檔案 (而且由於追蹤檔案大小受到限制,因此工作階段若過長,就有可能會卸除早期事件)。Keep individual sessions reasonably small (ten seconds of actions, not hundreds) to make it easier to interpret the trace file (and because there is a limit on the size of the trace file, thus for very long sessions there is a chance of early events being dropped).

了解 Power BI Desktop 所傳送的查詢格式Understanding the form of query sent by Power BI Desktop

Power BI Desktop 所建立及傳送的一般查詢格式會針對每個參考的資料表使用子選擇,其中子選擇是依照定義於 [查詢編輯器] 中的查詢所定義。The general format of queries created and sent by Power BI Desktop use subselects for each of the tables referenced, where the subselect is as defined by the query defined in Query Editor. 例如,假設 SQL Server 中有下列 TPC DS 資料表:For example, assume the following TPC-DS tables in SQL Server:

請考慮下列查詢:Consider the following query:

該查詢會產生下列視覺效果:That query results in the following visual:

重新整理該視覺效果會產生下一個段落底下所示的 SQL 查詢。Refreshing that visual will result in the SQL query shown below the next paragraph. 如您所見,子選擇有三個 (Web Sales、Item 和 Date_dim),各自傳回相關資料表的所有資料行,不過視覺效果實際上只會參考四個資料行。As you can tell, there are three subselects for Web Sales, Item, and Date_dim, that each return all the columns on the respective table, even though only four columns are actually referenced by the visual. 子選擇 (加網底) 中的查詢也就是定義於 [查詢編輯器] 中的查詢結果。These queries in the subselects (they're shaded) are exactly the result of the queries defined in Query editor. 以此方式使用子選擇不會影響效能,因為 DirectQuery 目前支援這些資料來源。Use of subselects in this manner has not been found to impact performance, for the data sources so far supported for DirectQuery. SQL Server 等資料來源會直接最佳化,而不需要參考其他資料行。Data sources like SQL Server simply optimize away the references to the other columns.

Power BI 採用此模式的其中一個原因,是因為分析師可以直接提供所使用的 SQL 查詢,以便「依提供時的現狀」來使用,而不會嘗試將它改寫。One reason Power BI employs this pattern is because the SQL query used can be provided directly by the analyst, so it's used "as provided", without an attempt to rewrite it.

後續步驟Next steps

本文說明常見於所有資料來源的 DirectQuery 層面。This article describes aspects of DirectQuery that are common across all data sources. 某些詳細資料特別針對個別來源。There are certain details that are specific to individual sources. 請參閱涵蓋特定來源的下列主題:See the following topics covering specific sources:

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