什么是-Power-BI-中的查询折叠-为什么你应该关心-

什么是 Power BI 中的查询折叠，为什么你应该关心？

原文：towardsdatascience.com/what-is-a-query-folding-in-power-bi-and-why-should-i-care/

[“那会破坏查询折叠吗？” “你的查询会折叠吗？”……也许有人问过你这些问题，但你可能像这样回答：“查询……什么？！”]

或者，也许你听说过 Power BI 中的查询折叠，但不知道如何在现实场景中利用它。

如果你（至少）在上述两种情况中认识到了自己，那么请继续阅读这篇文章。

好吧，你对查询折叠是什么感到好奇。但是，首先……在你继续之前，我们需要建立一些理论基础，这将把查询折叠功能置于适当的背景中。

数据塑形

我已经写过了数据塑形以及为什么它是数据准备阶段的关键概念之一。现在，我想以一种（可能）不寻常的方式进一步探讨：

我想你们都知道托马斯·莫尔写的书，名为“乌托邦”。

在那个故事中，一切都很完美，每个人都感到满意。在一个理想的世界里，让我们称之为“数据乌托邦”，我们有干净、高质量的数据直接飞入我们的报告“原样”，无需在途中进行任何美容或转换。不幸的是，“数据乌托邦”只存在于书中——现实是残酷的——因为我们必须在我们培养数据的同时应对众多挑战。

话虽如此，我们必须吸收的一个关键概念是数据塑形。数据塑形是在你熟悉你的数据，并意识到你计划在你的商业智能解决方案中使用的数据中可能存在的潜在陷阱之后应该执行的过程。

我故意使用了“商业智能”这个词而不是“Power BI”，因为这是一个应该用于 Power BI 解决方案之外的一般概念。

简单来说，数据塑形是数据整合的过程，在它成为你的数据模型的一部分之前。关键是要记住这个词：在！所以，一个人会在数据进入报告本身之前进行数据塑形。数据塑形可以在不同的地方进行，并且，根据你应用数据塑形技术的位置，在数据准备过程中的不同时间点进行。

在哪里执行数据塑形？

源数据库——这是最明显的选择，并且在大多数情况下是最理想的情况。它基于传统的数据仓库原则，即提取-转换-加载（ETL）数据。在这种情况下，你定义你想要提取的数据（不是数据库中的所有数据都是必需的，并且通常不建议导入所有数据。如何通过 90%减少你的 Power BI 模型大小）。然后，你决定你的数据是否需要在转换过程中进行转换，以更好地满足你的报告需求——例如，你是否需要进行货币转换，或者你是否需要统一国家和城市名称？

你能认出以下图片中的城市吗？

图片由 Lukas Kloeppel 在 Pexels 提供

是的，它是纽约。或者，它是 NYC？或者，它是纽约市？这三个名称中的哪一个才是正确的？是的，它们都是正确的——但是如果你以这种方式将数据导入你的数据模型，你将得到错误的结果——因为每个 New York、NYC 和 New York City 都将被视为一个单独的实体。这一点以及许多其他潜在的问题，都需要在数据塑形阶段解决，这就是为什么花些时间对数据进行“按摩”很重要。

Power Query

如果你不在源端执行数据转换，下一个站点就是 Power Query——它是 Power BI 内置的工具，它使你能够对你的数据进行各种转换。根据微软的官方文档，你可以应用超过 300 种不同的转换！

Power Query 的关键优势在于，你可以用很少或没有编程技能来执行复杂的数据转换！此外，你在数据转换过程中应用的每一个步骤都被保存下来，所以每次你刷新你的数据集时，这些步骤都会自动应用来塑造你的数据，并准备通过报告进行消费。

在 Power Query 的底层是一个 Mashup 引擎，它使你的数据塑形能够顺利运行。Power Query 使用一种非常强大的 M 语言进行数据处理。现在你可能正在问自己，关于数据塑形、Power Query、Mashup 引擎、M 语言等的故事与查询折叠有什么关系？我不怪你，这是一个合理的问题，但我们很快就会回来回答它。

什么是查询折叠？

对于某些数据源，例如关系型数据库，以及非关系型数据源，例如 OData、AD 或 Exchange，Mashup 引擎能够将 M 语言“翻译”成底层数据源能够“理解”的语言——在大多数情况下，它是 SQL。

照片由 Josh Sorenson 在 Pexels 提供

通过直接将复杂的计算和转换推送到源，Power Query 利用强大关系数据库引擎的能力，这些引擎旨在以最有效的方式处理大量数据。

Power Query 的 Mashup 引擎能够创建一个结合所有 M 语句的单一 SQL 语句的能力，我们称之为查询折叠。

或者，让我们简单一点：如果 Mashup 引擎能够生成一个将在数据源端执行的单个 SQL 查询，我们称该查询为折叠。

支持查询折叠的数据源

如前所述，查询折叠的最大受益者是关系数据库源，例如 SQL Server、Oracle 或 MySQL。然而，并不仅仅是 SQL 数据库利用了查询折叠的概念。本质上，任何支持某种查询语言的数据源都可能利用查询折叠。这些其他数据源包括 OData、SSAS、SharePoint 列表、Exchange 和 Entra ID。

另一方面，当你使用 Excel 文件、BLOB 存储文件、平面文件等数据源作为你的 Power BI 数据集时，查询无法折叠。

支持查询折叠的数据转换

然而，当涉及到通常支持查询折叠的数据源时，重要的是要记住，并非所有转换都可以折叠并推送到数据源。所以，为了明确起见，一个 SQL 数据库支持查询折叠并不一定意味着你的查询会折叠！有些 Power Query 转换根本不能推送到 SQL 数据库引擎。

很常见，Power Query 转换中的一些细微差异在最终结果中可能是决定性的，以及你的查询是否会折叠。我将在以下部分中展示这些细微差异中的几个。

通常来说，以下在 Power Query 中应用的转换可以“翻译”为单个 SQL 语句：

• 删除列

• 重命名列

• 使用静态值或 Power Query 参数过滤行，因为它们在 SQL 中被视为 WHERE 子句谓词

• 分组和汇总，相当于 SQL 的 Group By 子句

• 基于同一源合并可折叠的查询，因为这个操作可以翻译为 SQL 中的 JOIN。当我说合并可折叠查询时——这意味着如果你正在连接两个 SQL 服务器表，它将工作，但如果你试图连接一个 SQL 表和一个 Excel 文件，则不会工作

• 基于同一源追加可折叠的查询——这个转换与 SQL 中的 UNION ALL 操作符相关

• 使用简单逻辑添加自定义列。简单逻辑是什么意思？使用在 SQL 语言中有等价函数的 M 函数，例如数学函数或文本操作函数

• 透视和逆透视转换

另一方面，一些会阻止查询折叠的转换包括：

• 基于不同来源的查询合并，如前所述

• 基于不同来源的追加（并集）查询——与上一个案例中的类似逻辑

• 添加具有复杂逻辑的自定义列或使用一些没有 SQL 对应项的 M 函数

• 添加索引列

• 改变列的数据类型。这是一个典型的“视情况而定”的案例。我很快就会向你展示它取决于什么，但请记住，改变列的数据类型可以是可折叠的也可以是不可折叠的转换。

现在，让我们来探讨为什么实现这种行为很重要——或者，也许更好的说法是，为什么你应该关心查询是否折叠？

为什么你应该关心查询折叠？

当你在 Power BI 中使用导入模式时，当查询折叠时，数据刷新过程将更加高效，无论是在刷新速度还是资源消耗方面。

如果你正在使用 DirectQuery 或双存储模式，因为你直接针对 SQL 数据库，所以所有的转换必须折叠——否则你的解决方案将无法工作。

最后，查询折叠对于增量刷新也非常重要——它如此重要，以至于 Power BI 会在确定无法实现查询折叠时警告你。它不会破坏你的增量刷新“本身”，但没有查询折叠，增量刷新就无法实现其主要目的——减少在数据模型中需要刷新的数据量——因为没有查询折叠，Mashup 引擎需要从源中检索所有数据，然后应用后续步骤来过滤数据。

考虑到所有这些，你应该尽可能实现查询折叠。

慢速报告——不要责怪查询折叠！

这里有一个重要的免责声明，这也是本系列博客文章的关键要点之一：如果你的报告很慢，或者你的视觉效果需要很长时间才能渲染，或者你的数据模型大小很大，查询折叠与此无关！

只有如果你的数据刷新或增量刷新很慢且效率低下，你才应该更深入地调查你的 Power Query 步骤。

全有或全无？

关于查询折叠还有一些其他需要注意的事情。它不是一个全有或全无的过程。这意味着如果你在 Power Query 中有，比如说，10 个转换步骤，并且你的查询折叠到第 6 步，你仍然会从部分查询折叠中获得一些好处。然而，一旦查询折叠被破坏，就无法再实现了。

图片由作者提供

简单来说，如果你有 10 个转换步骤，并且你的查询折叠在第五步失败，所有之前的步骤都会折叠，但一旦折叠失败，就无法再次实现，即使你在第六步到第十步有默认支持查询折叠的转换步骤——就像在我们的例子中，过滤应该是一个可折叠的步骤，这些步骤也不会折叠。记住这一点，并尽可能将所有不可折叠的步骤推到管道的底部。

你怎么知道查询是否折叠了？

好的，现在我们不再是新手了。我们知道什么是查询折叠，为什么我们应该努力实现它，以及一些可以带来巨大差异的微妙技巧。

现在，是时候学习如何检查特定查询是否折叠了。最简单直接的方法是右键点击步骤，查看查看原生查询选项的样式。

如果它变灰了，这个步骤可能不会折叠。另一方面，如果你可以点击这个选项，这意味着你的查询会折叠。我想你可能对这个词感到困惑：可能！

图片由作者提供

但是，这个词用得恰当，因为你不能 100%确定如果查看原生查询选项被禁用，你的查询就不会折叠。我稍后会向你展示这个选项如何让我们误以为查询折叠出了问题，尽管实际上折叠确实发生了。

相反，当你想要确定你的查询是否折叠时，你可以在 Power Query 编辑器中使用查询诊断功能，或者使用SQL Server Profiler，这是一种古老而可靠的检查 Power BI 引擎发送到数据库的查询的方法。

此外，Power Query Online 中有一个很酷的功能，每个步骤都有一个图标，显示该步骤是否折叠、未折叠或未知。正如我所说，这个功能目前仅在 Power Query Online 中可用，所以我们希望 Power BI 团队很快将其实现到桌面版本中。

图片由作者提供

细节决定成败…

好的…你可能听说过“魔鬼藏在细节里”这句话。现在，是时候理解这些微小的细节如何在我们数据处理过程中产生重大影响了。

让我们从 Power Query 编辑器中最有趣的一个案例开始…

恶魔#1 — 合并连接

这个案例非常有趣，因为你几乎无法想象后台发生了什么。假设我想将我的两个查询合并成一个。我将使用Adventure Works样本数据库，并且需要合并 FactInternet Sales 和 DimCustomer 表。

我将从我的事实表中删除一些列，只保留 CustomerKey 列，因为这是 DimCustomer 表的外键，以及销售额列。我将直接将 DimCustomer 表连接起来，在合并之前不需要任何额外的步骤。

图片由作者提供

合并表在 SQL 中相当于 JOIN 操作。本质上，我们选择要执行 MERGE 操作的列，以及连接类型（左连接、外连接或内连接）。

图片由作者提供

问题在于默认情况下，当你合并两个查询时，Power Query 会生成一个嵌套的连接语句，这在 SQL 中无法正确转换。

图片由作者提供

如果我转到工具选项卡并点击诊断步骤，我可以看到 Mashup 引擎向我的底层 SQL Server 数据库发出了两个独立的查询——换句话说，这两个查询不能作为一个单一的 SQL 语句执行，这意味着查询没有折叠！

图片由作者提供

我们该如何解决这个问题？让我们选择一个空白查询，手动编写我们的 M 代码，以实现完全相同的结果。

图片由作者提供

关键在于我们将使用一个类似但仍然不同的 M 函数：Table.Join。

我们现在使用 Table.Join 函数——图片由作者提供

所有函数参数与之前完全相同，现在让我们检查一下结果。

你记得我之前告诉过你，当查看原生查询变灰时，你的查询可能没有折叠，但这并不一定是 100%正确的。这是一个很好的例子。如果你查看查看原生查询，它仍然显示我们的查询没有折叠…

图片由作者提供

…但是让我们转到诊断并检查这是否属实。

图片由作者提供

哦，天哪，我们被骗了——这一步确实折叠了！正如上图所示，我们生成并发送了一个单一的 SQL 查询到 SQL Server 源数据库以执行。

因此，我们在本例中发现了两个问题——第一个是一个连接类型，我们可以通过调整自动生成的 M 代码来解决。另一个问题是查看原生查询选项的不正确行为。我将在本系列的下一部分再给你一个例子，说明查看原生查询何时会出错。

Power BI 中的查询折叠——技巧、谎言和最终性能测试

我假设你现在已经熟悉了 Power BI 中查询折叠的概念，特别是它对于数据刷新和增量刷新过程的重要性。我们还开始探讨 Power Query 转换的一些有趣行为，在本文章的最后一部分，我将向你展示一些更有趣的发现。

最后，我们将通过最终的性能测试来总结——我将向你展示两个相同查询背后的确切数字——一个折叠了，另一个没有！

更改数据类型

在 Power Query 中最常见的转换之一是更改数据类型。这是一个众所周知的数据模型最佳实践，使用适当的数据类型——例如，如果你在报告中不需要小时、分钟和秒的粒度，你最好去掉它们，并将该列的数据类型从日期/时间更改为仅日期。

然而，通往地狱的道路是由善良的意图铺就的……所以，让我向你展示一个可能使你的查询变得非常慢的微妙差异，即使你坚持使用适当的数据类型的建议！

图片由作者提供

正如你在上面的插图中所能看到的，我的 OrderDate 列是日期/时间数据类型。而且，我想将其更改为仅日期。为此，至少有两种可能的方法——第一种是在列上右键单击，展开更改类型选项的下拉菜单（就像我在插图中所做的那样），并选择日期类型（位于日期/时间下方）：

图片由作者提供

这里发生了一些重要的事情，让我逐一解释：

1. 在应用步骤面板中，你可以注意到我们的转换步骤已被记录

2. 在该列本身中，你可以看到时间部分消失了

3. 当我打开查看原生查询对话框时，你可以看到 Mashup 引擎很好地将我们的转换转换为一个 T-SQL 的CONVERT()函数

4. 应用到此转换步骤的 M 公式是：Table.TransformColumnTypes()

现在我们来检查更改我们列数据类型的另一种方法：

图片由作者提供

在我们之前的更改类型选项下方，有一个转换选项。一旦展开下拉菜单，你可以看到仅日期的转换。让我们点击它并查看会发生什么：

图片由作者提供

看起来很相似，对吧？但是，让我们回顾一下现在发生的一切：

1. 我们现在有一个名为“提取日期”的步骤，而不是之前的“更改类型”步骤

2. 该列本身看起来与上一个例子完全相同——没有时间部分

3. 哎呀，查询不再折叠了！正如你所见，查看原生查询选项已变灰！

4. 这次，应用的 M 公式是：Table.TransformColumns()

因此，M 公式中的一个单词的不同（Table.TransformColumnTypes 与 Table.TransformColumns）对我们的查询产生了如此大的影响，以至于它无法被翻译成 SQL！

从这个故事中吸取教训：在选择更改数据类型的选项时要小心，要留意！

说谎者，说谎者…

在文章的前一部分，我承诺将向你展示一个例子，当“查看原生查询”选项会欺骗你，让你认为查询折叠已损坏，即使实际上并非如此…

假设我们只想保留表格中的顶部 X 行。在我的情况下，我想保留我的事实表中的前 2000 行：

图片由作者提供

一旦我应用了这个步骤并检查了原生查询视图，我就能意识到我的查询是折叠的，因为我的转换被翻译成了 SQL 中的 TOP 子句：

图片由作者提供

现在，假设我想在我的销售额列上应用绝对值转换。通常，这种转换很容易折叠，因为 T-SQL 中有 ABS 函数：

图片由作者提供

然而，如果我右键点击这个步骤，我会看到原生查询视图选项被灰色显示，所以我就会假设这个步骤破坏了我的查询折叠！

图片由作者提供

让我们在我们的查询诊断工具中检查一下：

图片由作者提供

哦，我的天！这个步骤确实折叠了！所以，我们又再次被原生查询视图选项欺骗了！

关键点在于：无论何时您认为特定的转换步骤可以折叠（就像在这个例子中，我们知道 SQL 中有 ABS 函数来支持我们的转换），都要检查底层实际发生了什么！

最终的性能测试

好的，如果我没有说服您到目前为止，为什么您应该努力实现查询折叠，那么现在让我拿出我的最后一张王牌！

我想向您展示返回相同结果的查询之间的数据刷新性能差异——其中一个折叠，另一个没有折叠！

测试#1 查询折叠开启

对于这次测试，我将使用 Contoso 样本数据库中的 FactOnlineSales 表。这个表大约有 1260 万行，这对于展示查询折叠概念的重要性是很好的例子。

在第一个例子中，我应用了 9 个不同的转换步骤，所有这些步骤都是可折叠的，正如您可以在下面的插图中所见：

图片由作者提供

请不要关注 Mashup 引擎生成的 SQL 代码：如果您是 SQL 专业人士，当然，您能写出更优化的 SQL 代码——然而，请记住，通过 Mashup 引擎自动生成的脚本，您并没有得到最优化 SQL——您只是得到了正确的 SQL！

我将点击关闭并应用，并打开我的秒表来测量我的数据刷新所需的时间。

图片由作者提供

这个查询在我的 Power BI 报告中加载了 280 万条记录，耗时 32 秒。数据以每批 100,000 至 150,000 条记录的批次加载，这是查询折叠已经实施的良好指标。

测试#2 查询折叠关闭

现在，我将回到 Power Query 编辑器，并故意在第 3 步打破查询折叠（记得上面改变日期/时间类型为日期的例子），使用我知道无法折叠的转换：

图片由作者提供

实话实说，在这里我将实现部分折叠，因为前两步将会折叠，但 Extracted Date 转换之后的所有后续步骤将不会折叠！

让我们再次启动秒表并检查发生了什么：

图片由作者提供

首先要注意的是：这个查询加载到我们的 Power BI 报告中花费了 4 分钟 41 秒，这大约是之前查询折叠情况下的10 倍。这次，加载的数据批次在 10,000 到 20,000 条记录之间。

但是，更令人担忧的是——你可以看到加载的记录总数几乎达到了 1100 万！！！而之前的例子中只有 280 万！这是为什么？好吧，在前面的章节中，我解释了当 Mashup 引擎无法将 M 语言转换为 SQL 时，它需要拉取所有数据（从查询折叠损坏的那一刻起），然后然后对整个导入的数据块应用转换！

最终结果完全相同——我们在 Power BI 报告中有了 2,830,017 条记录——但是，在查询折叠到位的情况下，所有必要的转换都在 SQL 数据库端执行，Mashup 引擎得到了已经准备好的数据集。而在第二种情况下，在我们打破查询折叠之后，Mashup 引擎拉取了剩余的所有行（大约 1100 万），然后才能应用其他转换步骤。

而且，这只是一个基本示例，只有一个表，数据量也不是很大！简单想象一下，在一个包含多个表的大型数据集上，这种影响的程度。

结论

好吧，我们在这篇文章中涵盖了大量的内容。我们学习了数据塑形的概念，介绍了 Power Query 的基本知识，还学习了什么是查询折叠以及为什么我们应该尽力实现它。

我还与你分享了一些基本示例和一些巧妙的小技巧，展示了如何在一些常见用例中实现查询折叠。

最后，请注意，查询折叠是一个持续进行中的工作，来自 Power BI 团队的人们正在不断改进这个功能。因此，可能在我展示的查询折叠问题中，有些问题在这期间已经得到了解决。因此，请确保与最新的改进保持同步。

感谢阅读！