Power BI Desktop 中的查询折叠指南Query folding guidance in Power BI Desktop

本文面向在 Power BI Desktop 中开发模型的数据建模人员。This article targets data modelers developing models in Power BI Desktop. 它提供了关于何时以及如何实现 Power Query 查询折叠的最佳做法指南。It provides best practice guidance on when—and how—you can achieve Power Query query folding.

查询折叠是 Power Query 查询的一项功能,可生成单个查询语句以检索和转换源数据 。Query folding is the ability for a Power Query query to generate a single query statement that retrieves and transforms source data. 有关详细信息,请参阅 Power Query 查询折叠For more information, see Power Query query folding.

指南Guidance

查询折叠指南因模型模式而异。Query folding guidance differs based on the model mode.

对于 DirectQuery 或 Dual 存储模式表,Power Query 查询必须实现查询折叠 。For a DirectQuery or Dual storage mode table, the Power Query query must achieve query folding.

对于导入表,可能实现查询折叠 。For an Import table, it may be possible to achieve query folding. 当查询基于关系源,且可以构造单个 SELECT 语句时,可通过确保发生查询折叠来实现最佳数据刷新性能 。When the query is based on a relational source—and if a single SELECT statement can be constructed—you achieve best data refresh performance by ensuring that query folding occurs. 如果仍需要 Power Query 糅合引擎来处理转换,则应努力减少所需的工作,尤其是对于大型数据集。If the Power Query mashup engine is still required to process transformations, you should strive to minimize the work it needs to do, especially for large datasets.

下面的项目列表提供了具体指导。The following bulleted-list provides specific guidance.

  • 尽可能多地委托处理数据源:如果无法折叠 Power Query 查询的所有步骤,请查找阻止查询折叠的步骤。Delegate as much processing to the data source as possible: When all steps of a Power Query query can't be folded, discover the step that prevents query folding. 尽可能将后续步骤按顺序提前,以便将其纳入查询折叠。When possible, move later steps earlier in sequence so they may be factored into the query folding. 请注意,Power Query 糅合引擎是智能引擎,它也许会在生成源查询时重新排列查询步骤。Note the Power Query mashup engine may be smart enough to reorder your query steps when it generates the source query.

    对于关系数据源,如果可以在单个 SELECT 语句中(或在存储过程的过程逻辑中)实现阻止查询折叠的步骤,请考虑使用本机 SQL 查询,如下所述。For a relational data source, if the step that prevents query folding could be achieved in a single SELECT statement—or within the procedural logic of a stored procedure—consider using a native SQL query, as described next.

  • 使用本机 SQL 查询:虽然 Power Query 查询可从关系源中检索数据,但对某些源而言,可以使用本机 SQL 查询。Use a native SQL query: When a Power Query query retrieves data from a relational source, it's possible for some sources to use a native SQL query. 该查询实际上可以是任何有效的语句,包括存储过程的执行。The query can in fact be any valid statement, including a stored procedure execution. 如果该语句产生多个结果集,则仅返回第一个。If the statement produces multiple result sets, only the first will be returned. 可以在语句中声明参数,建议使用 Value.NativeQuery M 函数。Parameters can be declared in the statement, and we recommend that you use the Value.NativeQuery M function. 此函数旨在安全且方便地传递参数值。This function was designed to safely and conveniently pass parameter values. 必须了解的是,Power Query 糅合引擎无法折叠后续的查询步骤,因此应在本机查询语句中包括所有(或尽可能多的)转换逻辑。It's important to understand that the Power Query mashup engine can't fold later query steps, and so you should include all—or as much—transformation logic in the native query statement.

    使用本机 SQL 查询时,需要牢记两个重要注意事项:There are two important considerations you need to bear in mind when using native SQL queries:

    • 对于 DirectQuery 模型表,查询必须是 SELECT 语句,并且不能使用公用表表达式 (CTE) 或存储过程。For a DirectQuery model table, the query must be a SELECT statement, and it can't use Common Table Expressions (CTEs) or a stored procedure.
    • 增量刷新无法使用本机 SQL 查询。Incremental refresh can't use a native SQL query. 因此,它将强制 Power Query 糅合引擎检索所有源行,然后应用筛选器来确定增量更改。So, it would force the Power Query mashup engine to retrieve all source rows, and then apply filters to determine incremental changes.

    重要

    本机 SQL 查询可执行的操作远不止检索数据。A native SQL query can potentially do more than retrieve data. 任何有效的语句都可以执行(可能多次执行),包括修改或删除数据的语句。Any valid statement can be executed (and possibly multiple times), including one that modifies or deletes data. 务必应用最小特权原则,以确保用于访问数据库的帐户仅具有对所需数据的读取权限。It's important that you apply the principle of least privilege to ensure that the account used to access the database has only read permission on required data.

  • 准备和转换源中的数据:如果确定某些 Power Query 查询步骤无法折叠,也许可以在数据源中应用转换。Prepare and transformation data in the source: When you identify that certain Power Query query steps can't be folded, it may be possible to apply the transformations in the data source. 可通过编写逻辑转换源数据的数据库视图来实现转换。The transformations could be achieved by writing a database view that logically transforms source data. 或者,在 Power BI 查询数据之前,通过物理方式准备和具体化数据。Or, by physically preparing and materializing data, in advance of Power BI querying it. 关系数据仓库是准备好的数据的一个极佳例子,通常由预先集成的组织数据源组成。A relational data warehouse is an excellent example of prepared data, usually consisting of pre-integrated sources of organizational data.

后续步骤Next steps

有关本文的详细信息,请参阅以下资源:For more information about this article, check out the following resources: