设置增量刷新 视频演示

Power BI 从 2018 年 5 月开始支持增量刷新（预览），初期只面向 Premium 用户，2020 年 2 月，增量刷新功能正式发布，并开始支持 Pro 用户（包括世纪互联版本）。

认识 Power BI 的增量刷新

在导入模式（Import mode）下，普通的 Power BI Desktop 模型不适合处理大型数据集（比如上亿行的表），因为 PBIX 文件受台式计算机可用内存资源的限制，无法存储超出内存容量的数据。另一种情况是报告起初的数据量较小，随着时间的推移和数据的累加，默认的全量刷新方式会使得数据导入过程耗时增加，最终显著影响刷新性能。

解决这一问题的行业通用方案是增量刷新，本文我们将介绍如何使用增加刷新以及它的一些注意事项。

增量刷新效果图 来源：cloudpockets.com

增量刷新具备以下优势：

1. 刷新更快捷 – 只需刷新已更改的数据。 例如，只刷新 10 年数据集中最近 5 天的数据。
2. 刷新更可靠 – 不再需要维护与不稳定的源系统的长期连接。
3. 降低资源消耗 – 要刷新的数据量减少，降低了内存和其他资源的整体消耗。

配置增量刷新的两种方式

1. Power BI Desktop ，这种方式在桌面端配置，在 Web 端生效，Pro 和 Premium 用户均可使用
2. Power BI dataflows，目前只面向 Premium 用户，暂不介绍

在 Power BI Desktop 中配置增量刷新

Power BI 产品组的 Adam 和 Christian 为你演示增量刷新的配置过程和背后的故事

定义 RangeStart 和 RangeEnd 参数

增量刷新的数据集使用名称为 RangeStart 和 RangeEnd 的 Power Query 参数进行筛选，类型为日期/时间。这两个参数的间隔定义了每次刷新数据的范围。它们用于筛选导入 Power BI Desktop 的数据，和在 Power BI Service 端将数据动态地划分为多个分区。报告发布后，参数值由 Service 自动替换，用户不需要对 Web 端的数据集额外设置任何参数。

配置筛选参数

定义好参数后，从列的日期筛选器中选择“自定义筛选器”来应用筛选 。

用参数值代替日期值对数据源设置筛选器，注意区间的筛选必须完整（只有一侧使用等于条件），包含重复日期会使每次自动更新产生重复数据，遗漏日期会缺失数据。

如果这一列的的数据类型不是日期/时间，而是日期等其他类型，将不能配置参数。

配置完成后，在编辑查询中选择「关闭并应用」。

Query Folding 查询折叠

因为增量刷新是面向大型数据集设计的功能，刷新过程需要由数据库的引擎完成，Power BI 查询编辑器的 Mashup 引擎不适合执行这种大数据量的更新操作。所以 Power BI 会把我们在编辑查询界面配置好的查询过程转换为 SQL，发回数据库执行。这一过程叫做 Query Folding。借助此特性，PQ 会优先将 M 语言的查询过程转换为数据库的原生查询（Native Query），这么做可以利用数据库的性能更快的完成清洗操作，提高处理效率，减轻本地查询的压力。

支持折叠的数据源

如果数据源不支持 Query Folding，则无法将查询过程推送到数据库执行。 在这种情况下，Power BI 将使用自带的 Mashup 引擎完成查询操作，这可能需要从数据源中检索完整数据集，这实际上从增量刷新变成了全量刷新，造成刷新用时大大增加，并且过程中可能会耗尽 Power BI 服务或本地数据网关（如果使用）中的资源。

Query Folding 生效需要满足两个条件，实际操作中，通过查看查询步骤中「查看本机查询」是否可选，可以判断当前查询是否能被折叠（下图中此选项是灰色的，说明查询没有被折叠）。

• 支持的数据源： 基于 SQL 的数据源，比如关系型数据库（Mysql/SQLServer/Oracle）、OData 数据源和 HDFS 都支持 QueryFolding。而 Excel、Csv 文件、blob、Web 和 OData 源等数据源不支持
• 支持的操作：筛选、合并和追加查询、分组、透视、逆透视等常用操作都可以被折叠。

由于每个数据源对 Query Folding 的支持级别有所不同，建议通过上图中的步骤验证以确保源查询中包含参数设置的筛选器并被有效折叠。 为简化此操作，Power BI Desktop 会尝试执行此验证。 如果无法验证，在定义增量刷新策略时，增量刷新对话框中会显示警告。 基于 SQL 的数据源（如 SQL、Oracle 和 Teradata）可以依赖此警告。 如果没有跟踪查询，其他数据源可能无法进行验证。 如果 Power BI Desktop 无法进行确认，会显示以下警告。

折叠是否发生也和模型所使用的模式有关，对于使用 DirectQuery 或 Dual 存储模式的表，Power Query 查询必须实现查询折叠 。对于导入模式的表，根据上面的介绍，是否发生查询折叠与数据源类型、所使用的查询步骤和先后顺序有关 。 当查询基于关系源，且可以构造单个 SELECT 语句时，Power Query 可通过确保发生查询折叠来实现最佳数据刷新性能 。 如果仍需要 Power Query 引擎来执行数据处理，建议将它们放在查询步骤的最后并尽可能减少这部分工作量，尤其是对于大型数据集而言。

在使用导入模式时，另一个建议是使用本机 SQL 查询(但不支持增量刷新)：虽然 Power Query 可从关系源中检索数据，但对某些源而言，可以使用本机 SQL 查询。 该查询实际上可以是任何有效的语句，包括存储过程。 如果该语句产生多个结果集，则仅返回第一个。你可以使用 M 函数Value.NativeQuery在语句中声明参数，此函数可以安全且方便地传递参数值。 必须了解的是，Power Query 引擎无法折叠后续的查询步骤，因此你应在本机查询语句中包括所有（或尽可能多的）转换逻辑。

使用本机 SQL 查询时，需要牢记两个重要注意事项：

• 对于 DirectQuery 模型表，查询必须是 SELECT 语句，并且不能使用公用表表达式 (CTE) 或存储过程。
• 增量刷新无法使用本机 SQL 查询。 因此，它将强制 Power Query 引擎检索所有源行，然后应用筛选器来确定增量更改。

定义刷新策略

在右侧选择需要增量刷新的表，左键单机选择「增量刷新」

显示「增量刷新」对话框后， 通过切换启用刷新

注意灰色背景的提示信息，即使能够从 Power BI 服务下载包含增量刷新策略的 PBIX 文件，也无法在 Power BI Desktop 中打开该文件。 也行未来可能会支持此功能，但请记住，这些数据集可能变得很大，以至于无法在普通的台式计算机中下载和打开它们

刷新范围设置

以下示例定义了一个刷新策略，用于存储五个完整日历年的数据以及当年至今的数据，并以增量方式刷新 10 天的数据。 第一次刷新操作会加载完整数据，后续开始增量刷新，将执行以下操作（假设计划为每天运行）：

• 添加新的一天的数据。
• 刷新截至当前日期 10 天的数据。
• 删除比当前日期早 5 年的日历年的数据。 例如，如果当前日期为 2019 年 1 月 1 日，则删除 2013 年的数据。

发布到 Service

将报告发布到 Power BI Service，在网关中配置定时刷新。第一次刷新需要较长时间才能导入全部五个完整日历年。 随后的刷新用时会大大减少。

高级策略选项 – 检测数据更改

增量刷新相比全量刷新已经有显著的性能提升。 但仍然有继续提升的空间。 如果选中「检测数据更改」复选框，则可选择用于仅标识和刷新数据更改日期的日期/时间列 。此操作需要数据源中存在用于审核的列。这一列不能与设置了 RangeStart/RangeEnd 参数的列相同。 设置后引擎将针对增量范围中的每个周期检查此列的最大值。如果自上次刷新后数据没有更新，则无需刷新这一周期。 在示例中，可以将增量刷新的天数从 10 天进一步减少到 2 天左右。

Power BI 目前需要将检测数据更改的列保留并缓存到内存中。对于大型数据集，你可能需要考虑采用以下措施来降低列基数和内存消耗：

• 刷新时仅保留此列的最大值（可以通过 Power Query 函数或 DAX 实现）。
• 根据刷新频率要求，最大程度减低列的精度（小数位数）

Power BI 未来计划让用户能够自定义查询检测数据更改，以避免将检测列保留在模型中。

高级策略选项 – 仅刷新完成周期

选择此项后，假设刷新在每天凌晨 4:00 运行，在 0:00 – 4:00 这四个小时期间数据源新增的数据将不会包含在刷新中。 另一个场景是刷新财务系统中的数据，假设上个月的数据在这个月的 12 号获得批准。可将增量范围设置为 1 个月，并安排在每月的第 12 天运行刷新。此后系统将在 2 月 12 日刷新 1 月份的数据。

常见问题

问：为什么增量刷新要使用支持 QueryFolding 的数据源？

答：在不支持 QueryFolding 的数据源上配置增量刷新也是可以可以实现的，但并不是理想方案。假设这样一个场景：数据源中有 100 亿行数据。我们只获取去年的记录。如果数据源支持 QueryFolding，我们只需获得那部分数据并加载它。如果数据源不支持 QueryFolding，我们必须读取整个数据，将其加载到临时内存中，然后执行筛选并仅加载最后一年的数据。在这种情况下，我们实际上已经把整个数据加载到临时内存，所以实际上还不如直接做全量加载。

问：如果我的数据存储在多个 Excel 文件里，能否通过增量刷新只更新最近的文件

答：不可以。只有支持 SQL 查询的数据源才能实现增量刷新，Excel、Csv 等文件不可以。虽然你可以在 Desktop 里完成上面演示的所有配置，但发布后 Service 端仍然是全量刷新。

问：增量刷新生效之后，如果历史数据发生变化，如何更新？

答：目前没有特别好的解决方案，未来开放 XML 编辑功能之后可以比较好的解决这个问题

问：数据源使用日期、整数和文本三种不同类型的参数对增量刷新时间有何影响？

答：前文提到编辑查询中用于执行增量刷新的 RangeStart 和 RangeEnd 参数必须使用日期/时间类型，实际操作中数据源用于刷新的列还可能是整数或文本类型，需要增加一步格式转换操作，不同类型的列对增量刷新效率的影响，群友沈楠在他的博客文章中专门探讨了这个问题，在这里我摘录他的结论作为参考。

日期和整数型的列刷新效率最高，文本列次之

三种数据类型的增量刷新用时对比