提示
可以下载包含完整渐变维度类型 2 模式解决方案的 Power Query 模板文件，以按照本教程进行操作。 若要详细了解如何使用 Power Query 模板文件，请查看有关 Power Query 模板的文档文章。
渐变维度类型 2 是数据仓库中用于管理和跟踪维度数据的历史更改的一种方法。 当属性值发生变化时，将创建具有唯一标识符的新记录，并保留旧记录。 它允许一段时间内的更改完整历史记录，从而能够根据不同的时间点进行准确的报告和分析。
本文展示了一个教程和一个示例，说明如何使用 Microsoft Fabric 数据工厂内的 Dataflow Gen2 为渐变维度类型 2 概念实施解决方案。
解决方案体系结构
在为渐变维度类型 2 实施解决方案时，必须定义源表以及源表中的哪些字段驱动逻辑来识别新记录。
总体而言，体系结构至少需要四个组件：
- 源表：操作表，可根据需要更改其中的值
- 维度表：源表所具有的所有状态的历史表。 通过自定义逻辑来定义，以识别更改及其生效日期
- 识别更改的逻辑：从根本上通过对源表的当前状态创建快照并将其与维度表中的可用记录进行比较来完成。
- 更新维度表的逻辑：在“识别更改的逻辑”中识别所有更改后，可以使用包含要添加和更新的记录的表来更新维度表。
注意
该解决方案在 Microsoft 数据工厂中使用 Dataflow Gen2。 虽然可以根据特定需求更改和修改逻辑，但本教程的目标是展示一种使用低代码和可视化解决方案（如 Dataflow Gen2）完成渐变维度类型 2 模式的简单方法。
源表
本教程从包含四列的员工示例源表开始：
| RepSourceID | 名字 | 姓氏 | 区域 |
|---|---|---|---|
| 312 | Vance | DeLeon | Southwest |
| 331 | Adrian | King | Northwest |
表中的数据预计会发生变化。 用户的姓氏或可能被分配到的工作区域可能会发生变化。
维度表
本教程的维度表查看了字段 FirstName、LastName 和 Region 可能发生的更改。 维度表及其数据：
| SalesRepID | RepSourceID | 名字 | 姓氏 | 区域 | StartDate | EndDate | IsCurrent | 哈希 |
|---|---|---|---|---|---|---|---|---|
| 1 | 312 | Vance | DeLeon | Southwest | 2021/3/20 | 12/31/9999 | TRUE | 3331327c56616e63657c44654c656f6e7c536f75746877657374 |
| 2 | 331 | Adrian | King | 中南部 | 2021/3/20 | 12/31/9999 | TRUE | 3333317c41647269616e7c4b696e677c536f75746863656e7472616c |
| 3 | 334 | 德文郡 | Torres | 巴拿马 | 2024/2/14 | 12/31/9999 | TRUE | 3333347c4465766f6e7c546f727265737c50616e616d61 |
这是此表的架构定义以及字段的描述：
| 字段名称 | 数据类型 | 描述 |
|---|---|---|
| SalesRepID | Number | 用于唯一标识整个维度表中的记录的代理键 |
| RepSourceID | Number | 源表中的自然键，表示员工的标识符 |
| FirstName | 文本 | 员工的名字。 此字段来自源表 |
| LastName | 文本 | 员工的姓氏。 此字段来自源表 |
| 区域 | 文本 | 员工的工作区域。 此字段来自源表 |
| StartDate | 日期 | 确定记录生效时间的日期戳 |
| EndDate | 日期 | 确定记录有效期的日期戳 |
| IsCurrent | 逻辑 | 用于表示记录是否为最新记录的简单标志。 True 表示记录是最新记录 |
| 哈希 | 文本 | 字段 RepSourceID、FirstName、LastName 和 Region 的哈希编码组合 |
注意
强烈建议在建立此过程之前创建具有正确架构的维度表。 本教程考虑到你已经提前创建了维度表，并且已经建立了可以在 Dataflow Gen2 逻辑中使用的哈希或查找机制。
所需结果是更新的维度表，其中包含 SalesRepID 2 和 3 的更新以及新记录 4。 该表如下所示：
| SalesRepID | RepSourceID | 名字 | 姓氏 | 区域 | StartDate | EndDate | IsCurrent | 哈希 |
|---|---|---|---|---|---|---|---|---|
| 1 | 312 | Vance | DeLeon | Southwest | 2021/3/20 | 12/31/9999 | TRUE | 3331327c56616e63657c44654c656f6e7c536f75746877657374 |
| 2 | 331 | Adrian | King | 中南部 | 2021/3/20 | 2024/8/16 | FALSE | 3333317c41647269616e7c4b696e677c536f75746863656e7472616c |
| 3 | 334 | 德文郡 | Torres | 巴拿马 | 2024/2/14 | 2024/8/16 | FALSE | 3333347c4465766f6e7c546f727265737c50616e616d61 |
| 4 | 331 | Adrian | King | Northwest | 2024/8/16 | 12/31/9999 | TRUE | 3333317c41647269616e7c4b696e677c4e6f7274687657374 |
识别更改的逻辑
为了识别更改，首先需要创建源表的快照，并建立一个逻辑来将其与维度表中的记录进行比较。 可通过多种方式建立逻辑来比较这些表。 以下是其中一些原因：
- 合并/联接模式
- 使用自然键
- 使用哈希技术创建查找字段
- 表之间的显式联接
- 使用 Table.SelectRows 进行动态记录匹配的自定义逻辑
本教程演示了一种哈希处理技术，使用可在两个表中为联接（也称为合并操作）创建的单个值来比较两个表中的记录。
将源表加载到 Dataflow Gen2 中后，可以从功能区中选择“添加列”选项卡，并使用“添加自定义列”选项。 在“自定义列”对话框中，可以使用“文本”数据类型和公式创建名为 Hash 的新列：
Binary.ToText( Text.ToBinary( Text.Combine(List.Transform({[RepSourceID],[FirstName],[LastName],[Region]}, each if _ = null then "" else Text.From(_)), "|")), BinaryEncoding.Hex)
重要
虽然此示例公式展示了如何使用这四列，但可以将列的引用更改为自己的列，并定义需要使用表中的哪些特定字段来创建哈希。
现在，使用源表中的 Hash 列，可以通过一种简单的方法来比较两个表以找到完全匹配的内容。
加载维度表后，请通过右键单击查询窗格或关系图视图中的查询并选择引用选项来创建此查询的引用。 将此新查询重命名为 AggregatedDimHash。 可以通过 Hash 字段来合计表中记录的数量。 为此，请转到功能区中的“开始”选项卡，然后选择“转换”组中的“分组依据”选项。 在对话框中，确保按 Hash 列进行分组，并为新的 Count 列选择操作“统计行数”。
新记录
注意
将源表与维度表进行比较将从根本上说明需要向维度表添加哪些新记录。
选择“源”查询，转到功能区中的“开始”选项卡，然后选择“合并”组内的“将查询合并为新查询”选项。 将此查询重命名为 Compare。 在“合并”对话框中，确保在“合并右侧表”下拉菜单中选择 AggregatedDimHash，并从两个表中选择 Hash 列，同时保留默认的左外联接类型。
合并完成后，确保仅选择要展开的 Count 列来展开新创建的列。
筛选此列以仅保留 null 值，其表示当前维度表中不存在的值。 结果为西北区域的 Adrian King 生成了一条记录。
删除 Count 列并将此查询重命名为 CompareStoM。
下一步要求在记录中添加缺失的字段，如 StartDate、EndDate、IsCurrent 甚至 SalesRepID。 但是，虽然前三个很容易用简单的公式来定义，但 SalesRepID 要求首先根据维度表中的现有值来计算此值。
从维度表中获取标识符序列
引用现有的维度表并将查询重命名为 LastID。 将来将引用此查询。
假设查询的值是一个整数，每次添加新记录时该整数就会递增 1，则可以实施一个在 SalesRepID 中查找最大值的逻辑。 右键单击 SalesRepID，然后选择向下钻取选项。
这会生成一个列表，在功能区中，现在你将拥有“统计信息”选项，可以在其中选择用于计算此列表的最大值的选项：
在添加上一个步骤后添加另一个自定义步骤，并将此查询步骤的公式替换为以下公式，该公式根据 SalesRepID 计算最大值并为其加 1，或者在表中没有任何记录的情况下，将值 1 设置为新记录的种子 try #"Calculated maximum" +1 otherwise 1
此示例的 LastID 查询的输出是数字 4。
重要
#"Calculated maximum" 表示上一步的名称。 如果这不是查询的确切名称，请相应地修改公式，以反映上一步的名称。
引用 CompareStoM 查询，在该查询中，你拥有西北区域 Adrian King 的单条记录，并将此新查询称为“NewRecords”。 通过功能区中的“添加列”选项卡添加一个从数字 0 开始的新索引列。
检查所创建步骤的公式，并将 0 替换为查询 LastID 的名称。 这将产生代表维度表中记录的新值的起始值。
将此 Index 列重命名为 SalesRepID。
将缺失的字段添加到新记录
现在是时候使用“添加自定义列”来添加缺失的列了。 下面是一个表，其中包含每个新列要使用的所有公式
| 列名称 | Data type | 公式 |
|---|---|---|
| StartDate | 日期 | Date.From(DateTime.LocalNow()) |
| EndDate | 日期 | #date(9999,12,31) |
| IsCurrent | True/False | 是 |
结果现在符合维度表所期望的架构。
要更新的记录
注意
将维度表与源表进行比较将说明维度表中应更新哪些记录。
使用原始维度查询 (Dimension)，执行新的“将查询合并为新查询”操作，并选择源表查询作为右侧表。 从两个表中选择 Hash 列，然后选择“左反”作为联接类型。
输出是一个包含源表中不再使用的记录的表。 确保使用表值扩展新创建的列，并且仅扩展哈希列，然后将其删除。 将查询重命名为 RecordsToUpdate。
现在，需要更新维度表中的记录，以在源表中反映此更改。 这些更改微不足道，需要更新 EndDate 和 IsCurrent 字段的值。 为此，可以右键单击 IsCurrent 字段，然后选择“替换值...”选项。在“替换值”对话框中，可以将值 TRUE 替换为 FALSE。
也可以右键选择 EndDate 字段，然后选择“替换值...”。 输入值 1999/12/31 或所选的任何日期，因为稍后将替换此值。
提交对话框后，将添加一个新的替换值步骤。 转到步骤的编辑栏，并使用下面的公式更改具有 #date(1999,12,31) 的组件。
Date.From(DateTime.LocalNow())
此新公式添加了一个日期戳，表示逻辑何时运行以确定该特定记录的 EndDate。
其结果将是一个表，其中包含应使用相应新值更新的记录。
将要添加和更新的记录合并到单个表中
可以将 NewRecords 的查询与包含要更新的记录的查询 (RecordsToUpdate) 追加到单个查询中，以简化以下更新维度表的过程。
若要追加查询，请务必选择 NewRecords 查询，转到功能区的“开始”选项卡，然后在“合并”组中找到“将查询追加为新查询”选项。 在“追加”对话框中，确保还选择包含要更新的记录的查询作为第二个表。
将此新查询重命名为 StagingTableForUpdates，它应包含 3 行。 此查询在逻辑中用于更新维度表。 可根据需要移动 SalesRepID 或重新排列列。 为简单起见和演示目的，本教程使用与维度表中相同的字段顺序显示此查询的输出。
更新维度表的逻辑
到目前为止，该解决方案提供了一个查询，其中包含向目标进行更新插入操作的所有记录。 从此时开始，可以定义要用于将数据加载到维度表的逻辑，但通常有两个：
- 更新插入操作：现在可以通过将 StagingTableForUpdates 查询的结果存储到数据源中的临时表，然后在服务器/数据源引擎中运行存储过程来实现此目的。 还可以使用其他机制，如 Microsoft Fabric 中的笔记本。 最后，可以建立一个数据管道，该管道可以在 Dataflow Gen2 完成其操作后触发笔记本或存储过程，为未来的操作自动执行此操作，并按计划进行设置。
- 删除现有数据并重新创建表：现在可以在 Dataflow Gen2 中完成此操作，而无需其他工具，但也可以使用其他工具来实现此逻辑。 本教程展示了这种方法。
使用 Dataflow Gen2 将数据加载到维度目标表
可以创建一个逻辑，该逻辑使用三个查询来提出一个查询，其中包含维度表中应存在的所有记录。 利用新查询，可以使用 Dataflow Gen2 中的数据目标功能来加载数据。
原始维度表中要保留的记录
要实现的第一个逻辑是原始维度表中要保留的记录。
选择“维度”查询后，转到功能区中的“开始”选项卡，并使用“将查询合并为新查询”选项。 在“合并”对话框中，选择 RecordsToUpdate 查询作为右侧表。 从这两列中选择 SalesRepID 列，并使用“左反”作为联接类型。 选择“确定”。
确保从新创建的列展开 Hash 字段。 展开后，可以删除该列。
现在已经确切知道需要从原始维度表中保留哪些记录，可以将 StagingTableForUpdates 追加到现有查询，以便查询包含应位于维度表中的所有记录。 为此，在功能区的“开始”选项卡中，在现有查询中选择“追加”选项，并追加 StagingTableForUpdates 查询。
可以使用 SalesRepID 字段按升序对此表进行排序，输出可与数据目标功能一起使用，以将数据加载到维度表。
可以从有关 Dataflow Gen2 数据目标和托管设置的文章中了解有关如何为查询设置数据目标以及如何将查询输出加载到维度表的更多信息。
注意
考虑到在 Dataflow Gen2 中，可以在查询级别利用暂存机制。 详细了解 Dataflow Gen2 中的暂存机制