在 Power BI Desktop 中开发 DirectQuery 模型的疑难解答Troubleshoot developing DirectQuery models in Power BI Desktop

本文面向使用 Power BI Desktop 或 Power BI 服务开发 Power BI DirectQuery 模型的数据建模者。This article targets data modelers developing Power BI DirectQuery models, developed by using either Power BI Desktop or the Power BI service. 本文介绍如何诊断性能问题、如何获取更详细的信息以优化报表。It describes how to diagnose performance issues, and how to get more detailed information to allow reports to be optimized.

性能分析器Performance Analyzer

强烈建议在 Power BI Desktop 而不是 Power BI (服务或 Microsoft Power BI 报表服务器)中诊断任何性能问题。It's strongly recommended that any diagnosis of performance issues starts in Power BI Desktop, rather than in Power BI (the service or Power BI Report Server). 通常情况下,性能问题只取决于基础数据源的性能水平,因此,在 Power BI Desktop 更加独立的环境中就更容易识别和诊断这些问题,并且可以一开始就消除某些组件(如 Power BI 网关)。It's commonly the case that performance issues are simply based on the level of performance of the underlying data 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 Power BI. 性能分析器是一个有用工具,可在整个过程中识别问题。The Performance Analyzer is a useful tool for identifying issues throughout this process.

同样,建议先尝试隔离单个视觉对象的任何问题,而不是调查页面上多个视觉对象的问题。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 页面中单个视觉对象仍然缓慢。Let's say those steps (in the previous paragraphs of this topic) 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 what queries are being sent to the underlying source by Power BI Desktop, you can use the Performance Analyzer. 还可以查看基础数据源可能发出的跟踪/诊断信息。It's also possible to view traces/diagnostic information that might be emitted by the underlying data 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.

查看跟踪文件Review trace files

默认情况下,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 may be supported in the future). 将查询写入日志的源如下所示:The sources that write queries to the log are the following:

  • SQL ServerSQL Server
  • Azure SQL 数据库Azure SQL Database
  • Azure SQL 数据仓库Azure SQL Data warehouse
  • OracleOracle
  • TeradataTeradata
  • SAP HANASAP HANA

可在当前用户的 AppData 文件夹中找到该跟踪文件:\<User>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspacesThe 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 the Diagnostics page. 将显示以下对话框窗口:The following dialog window appears:

“Power BI Desktop”窗口处于打开状态,并且已选择“全局诊断”页。

在“故障转储集合”下,选择“打开故障转储/跟踪文件夹”链接,此时会打开以下文件夹:\<User>\AppData\Local\Microsoft\Power BI Desktop\TracesWhen you select the Open crash dump/traces folder link, under Crash Dump Collection, 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. 这些子文件夹名称中带有整数后缀,例如 AnalysisServicesWorkspace2058279583。These 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 opened by 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 Profiler。Once you download and install SQL Server Management Studio, run SQL Server Profiler.

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 会话的跟踪文件的路径,例如:\<User>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspace2058279583\DataEnter the path to the trace file for the currently open Power BI session, such as: \<User>\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\AnalysisServicesWorkspace2058279583\Data
  3. 打开 FlightRecorderCurrent.trcOpen FlightRecorderCurrent.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:

  • 一个 Query Begin 和一个 Query End 事件,分别表示 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 Begin 和 DirectQuery End 事件,表示发送到基础数据源的查询(作为评估 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.

SQL Server Profiler 已打开。

其他较重要的列如下所示:Other columns of interest are as follows:

  • TextData: 事件的文本详细信息。TextData: The textual detail of the event. 对于“Query Begin/End”事件,该内容会是 DAX 查询。For Query Begin/End events this will be the DAX query. 对于“DirectQuery Begin/End”事件,该内容会是发送到基础数据源的 SQL 查询。For DirectQuery Begin/End events, this will be the SQL query sent to the underlying source. 当前选中事件的 TextData 值也显示在底部区域中。The TextData value for the currently selected event is also displayed in the region at the bottom.
  • EndTime: 事件完成的时间。EndTime: When the event completed.
  • Duration: 执行 DAX 或 SQL 查询的持续时间,以毫秒为单位。Duration: The duration, in milliseconds, taken to execute the DAX or SQL query.
  • Error: 指示是否发生了错误(发生错误时,该事件显示为红色)。Error: Indicates if an error occurred, in which case the event is also displayed in red.

在上图中,缩小了部分重要性较低的列,以便更容易看到重要性较高的列。In the image above, some of the less interesting columns have been 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 reopened to see the new events.
  • 保持较短的单个会话时间(10 秒的操作时间,而不是数百秒的操作时间),使跟踪文件更容易解释(并且因为跟踪文件的大小有限制,因此在时间长的会话中可能会丢弃早期事件)。Keep individual sessions reasonably small (10 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 long sessions there is a chance of early events being dropped).

了解发送到源的查询Understand queries sent to the source

Power BI Desktop 生成和发送的查询的一般格式会对每个引用的表使用子查询,其中子查询由 Power Query 查询定义。The general format of queries generated and sent by Power BI Desktop use subqueries for each of the model tables referenced, where the subquery is defined by the Power Query query. 例如,假设 SQL Server 关系数据库中有以下 TPC-DS 表:For example, assume the following TPC-DS tables in a SQL Server relational database:

Power BI Desktop 模型视图会显示四个表,它们都是相关的。

请考虑以下视觉对象及其配置,注意 SalesAmount 度量值是用以下表达式定义的:Consider the following visual and its configuration, noting that the SalesAmount measure is defined with the following expression:


SalesAmount = SUMX(Web_Sales, [ws_sales_price] * [ws_quantity])

Power BI Desktop 报表由堆积柱形图构成,按类别显示销售额。

刷新该视觉对象将生成下一段所示的 T-SQL 查询。Refreshing that visual will result in the T-SQL query shown below the next paragraph. 如你所见,有针对 Web_Sales、Item 和 Date_dim 模型表的三个子查询 。As you can tell, there are three subqueries for the Web_Sales, Item, and Date_dim model tables. 每个表都将返回所有模型表列,但视觉对象实际上只引用了四列。Each of these tables returns all the model table columns, even though only four columns are actually referenced by the visual. 这些子查询(灰显)是 Power Query 查询的定义。These subqueries (they're shaded) are exactly the definition of the Power Query queries. 对于目前 DirectQuery 支持的数据源,尚未发现以这种方式使用子查询会影响性能。Use of subqueries 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 optimize away the references to unused columns.

Power BI 使用此模式原因之一是,你可以定义 Power Query 查询来使用特定的查询语句。One reason Power BI employs this pattern is because you can define a Power Query query to use a specific query statement. 因此,“按提供的形式”使用它,而不会试图重写它。So, it's used "as provided", without an attempt to rewrite it. 注意,此模式限制使用使用公共表表达式 (CTE) 和存储过程的查询语句。Note that this patterns restricts using query statements that use Common Table Expressions (CTEs) and stored procedures. 这些语句不能用于子查询。These statements cannot be used in subqueries.

非常详细的 T-SQL 查询会显示嵌入的子查询,每个模型表一个。

网关性能Gateway performance

有关网关性能故障排除的信息,请参阅对网关进行排除故障 - Power BI 一文。For information about troubleshooting gateway performance, read the Troubleshoot gateways - Power BI article.

后续步骤Next steps

有关 DirectQuery 的详细信息,请查看以下资源:For more information about DirectQuery, check out the following resources: