Microsoft Fabric 仓库中的维度建模：加载表

适用于：✅SQL 分析终结点和 Microsoft Fabric 中的仓库

注意

本文属于维度建模系列文章的一部分。 本系列重点介绍 Microsoft Fabric 仓库中与维度建模相关的指南和设计最佳做法。

本文提供了在维度模型中加载维度和事实数据表的指南和最佳做法。 它为 Microsoft Fabric 中的仓库提供了实用指南，这是一种支持许多 T-SQL 功能的体验，比如创建表和管理表中的数据。 因此，你可以完全控制创建维度模型表并向其加载数据。

注意

在本文中，术语数据仓库是指企业数据仓库，该数据仓库可全面集成整个组织的关键数据。 相比之下，单独的术语仓库是指 Fabric 仓库，它是一种服务型软件 (SaaS) 关系数据库产品/服务，可用于实现数据仓库。 为清楚起见，后者在本文中被称为 Fabric 仓库。

提示

如果你不熟悉维度建模，请考虑将此系列文章作为你的第一步。 本文并非旨在提供有关维度建模设计的完整讨论。 有关更多信息，请直接参考广泛采用的已发布内容，如 Ralph Kimball 等创作的数据仓库工具包：维度建模的最终指南（第三版，2013 年）。

加载维度模型

加载维度模型涉及定期运行提取、转换和加载 (ETL) 进程。 ETL 进程将编排其他进程的运行，这些进程通常涉及暂存源数据、同步维度数据、将行插入事实数据表，以及记录审核数据和错误。

对于 Fabric Warehouse 解决方案，可以使用数据工厂开发和运行 ETL 进程。 该进程可以将源数据暂存、转换和加载到维度模型表中。

具体而言，你可以：

• 使用数据管道生成工作流来编排 ETL 进程。 数据管道可以执行 SQL 脚本、存储过程等。
• 使用数据流开发低代码逻辑以引入来自数百个数据源的数据。 数据流支持合并来自多个源的数据、转换数据，然后将其加载到目的地，例如维度模型表。 数据流是使用熟悉的 Power Query 体验构建的，该体验目前在许多 Microsoft 产品（包括 Microsoft Excel 和 Power BI Desktop）中提供。

注意

ETL 开发可能很复杂，而且开发可能很有挑战性。 据估计，60-80% 的数据仓库开发工作专用于 ETL 进程。

业务流程

ETL 进程的一般工作流如下：

1. （可选）加载临时表。
2. 处理维度表。
3. 处理事实数据表。
4. （可选）执行处理后任务，例如触发依赖 Fabric 内容的刷新（如语义模型）。

首先应处理维度表，以确保它们存储所有维度成员，包括自上次 ETL 进程以来添加到源系统的成员。 当维度之间存在依赖关系时，与子维度的情况一样，应按依赖项顺序处理维度表。 例如，客户维度和供应商维度使用的地理维度应在其他两个维度之前进行处理。

处理所有维度表后，即可以处理事实数据表。

处理所有维度模型表时，可以触发依赖性语义模型的刷新。 同时，最好向相关员工发送通知，告知他们 ETL 进程的结果。

暂存数据

暂存源数据可以帮助支持数据加载和转换要求。 它涉及到提取源系统数据并将其加载到临时表中，你创建这些表是为了支持 ETL 进程。 建议暂存源数据，因为它可以：

• 最大程度减少对操作系统的影响。
• 用于协助和优化 ETL 处理。
• 提供重启 ETL 进程的功能，而无需从源系统重新加载数据。

临时表中的数据永远不应提供给商务用户。 它仅与 ETL 进程相关。

注意

将数据存储在Fabric Lakehouse 中时，可能不需要将其数据暂存到数据仓库中。 如果它实施 Medallion 体系结构，则可以从铜层、银层或金层中获取其数据。

建议在仓库中创建架构，可能命名为 staging。 在列名称和数据类型方面，临时表应尽可能与源表相似。 应在 ETL 进程开始时删除每个表的内容。 但是，请注意，Fabric 仓库表无法截断。 相反，你可以在使用数据加载表之前删除并重新创建每个临时表。

还可以将数据虚拟化替代项视为过渡策略的一部分。 可用工具如下：

• 镜像是一种低成本且低延迟的统包解决方案，可用于在 OneLake 中创建数据的副本。 有关详细信息，请参阅为什么在 Fabric 中使用镜像？。
• OneLake 快捷方式，指向可能包含源数据的其他存储位置。 快捷方式可用作 T-SQL 查询中的表。
• SQL Server 中的 PolyBase，是 SQL Server 的数据虚拟化功能。 借助 PolyBase，T-SQL 查询可以将外部源中的数据连接到 SQL Server 实例中的关系表。
• 借助 Azure SQL 托管实例的数据虚拟化功能，你可以对 Azure Data Lake Storage (ADLS) Gen2 或 Azure Blob 存储中以通用数据格式存储数据的文件执行 T-SQL 查询，并使用联接将其与本地存储的关系数据合并在一起。

转换数据

源数据的结构可能与维度模型表的目标结构不相似。 因此，ETL 进程需要重塑源数据，使其与维度模型表的结构保持一致。

此外，数据仓库必须提供经过清理且一致的数据，因此可能需要转换源数据以确保质量和一致性。

注意

垃圾进，垃圾出的概念当然也适用于数据仓库，因此，避免将垃圾（低质量）数据加载到维度模型表中。

下面是 ETL 进程可以执行的一些转换。

• 合并数据：可以基于匹配键集成（合并）来自不同源的数据。 例如，产品数据存储在不同的系统（如制造和营销），但它们都使用通用库存单位 (SKU)。 当数据共享一个通用结构时，也可以追加数据。 例如，销售数据存储在多个系统中。 每个系统的销售数据并集可以产生所有销售数据的超集。
• 转换数据类型：数据类型可以转换为维度模型表中定义的数据类型。
• 计算：可以进行计算以生成维度模型表的值。 例如，对于员工维度表，可以拼接名字和姓氏来生成全名。 另一个示例是，对于销售事实数据表，可以计算总销售收入，即单位价格和数量的乘积。
• 检测和管理历史更改：可以检测更改并将其适当存储在维度表中。 有关详细信息，请参阅本文后面的管理历史更改。
• 合并数据：合并可用于降低事实数据表维度和/或提高事实粒度。 例如，销售事实数据表不需要存储销售订单号。 因此，按所有维度键分组的合并结果可用于存储事实数据表数据。

加载数据

可以使用以下数据引入选项在 Fabric 仓库中加载表。

• COPY INTO (T-SQL)：当源数据包含存储在外部 Azure 存储帐户（如 ADLS Gen2 或 Azure Blob 存储）中的 Parquet 或 CSV 文件时，此选项非常有用。
• 数据管道：除了编排 ETL 进程外，数据管道还可以包括运行 T-SQL 语句、执行查找或将数据从数据源复制到目标的活动。
• 数据流：作为数据管道的替代方法，数据流提供无代码体验来转换和清理数据。
• 跨仓库引入：将数据存储在同一工作区中时，跨仓库引入允许联接不同的仓库或湖屋表。 它支持 T-SQL 命令，例如 INSERT…SELECT、SELECT INTO 和 CREATE TABLE AS SELECT (CTAS)。 如果要从同一工作区中的临时表转换和加载数据，这些命令尤其有用。 它们也是基于集合的运算，可能是加载维度模型表的最高效和最快的方法。

提示

有关这些数据引入选项的完整说明，包括最佳做法，请参阅将数据引入仓库。

Logging

ETL 进程通常需要专用的监视和维护。 出于这些原因，建议将 ETL 进程的结果记录到仓库中的非维度模型表。 你应该为每个 ETL 进程生成唯一的 ID，并使用它记录有关每个操作的详细信息。

请考虑日志记录：

• ETL 过程：
  • 每个 ETL 执行的唯一 ID
  • 开始时间和结束时间
  • 状态（成功或失败）
  • 遇到的任何错误
• 每个暂存模型表和维度模型表：
  • 开始时间和结束时间
  • 状态（成功或失败）
  • 插入、更新和删除的行
  • 最终表行计数
  • 遇到的任何错误
• 其他操作：
  • 语义模型刷新操作的开始时间和结束时间

提示

你可以创建专用于监视和分析 ETL 进程的语义模型。 进程持续时间有助于识别可能受益于审阅和优化的瓶颈。 行计数可以让你了解每次运行 ETL 时增量加载的大小，还有助于预测数据仓库的未来大小（以及在适用时何时纵向扩展 Fabric 容量）。

处理维度表

处理维度表涉及将数据仓库数据与源系统同步。 首先转换源数据并准备将其加载到维度表中。 然后，通过联接业务键，将此数据与现有维度表数据匹配。 然后，可以确定源数据是表示新数据还是已修改的数据。 当维度表应用渐变维度 (SCD) 类型 1 时，通过更新现有维度表行进行更改。 当表应用 SCD 类型 2 更改时，现有版本将过期，并且会插入新版本。

下图描述了用于处理维度表的逻辑。

考虑 Product 维度表的进程。

• 将新产品添加到源系统时，行将插入到 Product 维度表中。
• 修改产品后，维度表中的现有行将更新或插入。
  • 当 SCD 类型 1 适用时，将对现有行进行更新。
  • 当 SCD 类型 2 适用时，将进行更新以使当前行版本过期，并且会插入表示当前版本的新行。
  • 当 SCD 类型 3 适用时，将发生类似于 SCD 类型 1 的进程，在不插入新行的情况下更新现有行。

代理键

建议每个维度表都有一个代理键，该键应使用尽可能小的整数数据类型。 在基于 SQL Server 的环境中，这通常通过创建标识列来完成，但 Fabric 仓库不支持此功能。 相反，你需要使用一种可生成唯一标识符的解决方法。

重要

当维度表包含自动生成的代理键时，你永远不应该对它执行截断和完全重新加载。 这是因为它将使加载到使用维度的事实数据表中的数据失效。 此外，如果维度表支持 SCD 类型 2 更改，则可能无法重新生成历史版本。

管理历史更改

当维度表必须存储历史更改时，你需要实现渐变维度 (SCD)。

注意

如果维度表行是推断成员（由事实加载过程插入），则应将任何更改视为延迟到达的维度详细信息，而不是 SCD 更改。 在这种情况下，应更新任何已更改的属性，并将推断的成员标志列设置为 FALSE。

维度可以支持 SCD 类型 1 和/或 SCD 类型 2 更改。

SCD 类型 1

检测到 SCD 类型 1 更改时，请使用以下逻辑。

1. 更新任何已更改的属性。
2. 如果表包含上次修改日期和上次修改人列，请设置进行修改的当前日期和进程。

SCD 类型 2

检测到 SCD 类型 2 更改时，请使用以下逻辑。

1. 通过将结束日期有效性列设置为 ETL 处理日期（或源系统中的合适时间戳）并将当前标志设置为 FALSE 来使当前版本过期。
2. 如果表包含上次修改日期和上次修改人列，请设置进行修改的当前日期和进程。
3. 插入将开始日期有效性列设置为结束日期有效期列值（用于更新以前的版本）且将当前版本标志设置为 TRUE 的新成员。
4. 如果表包含创建日期和创建人列，请设置进行插入的当前日期和进程。

SCD 类型 3

检测到 SCD 类型 3 更改时，请使用与处理 SCD 类型 1 相似的逻辑更新属性。

维度成员删除

请注意，如果源数据指示维度成员已删除（因为它们未从源系统检索，或者它们已被标记为“已删除”）。 不应将删除与维度表同步，除非维度成员创建时出错，并且没有与它们相关的事实记录。

处理源删除的适当方法是将其记录为软删除。 软删除将维度成员标记为不再处于活动状态或有效。 为了支持这种情况，维度表应包含具有位数据类型的布尔属性，例如 IsDeleted。 将任何已删除的维度成员的此列更新为 TRUE (1)。 维度成员的当前最新版本可能类似地在 IsCurrent 或 IsActive 列中以布尔（位）值标记。 所有报告查询和 Power BI 语义模型都应筛选掉属于软删除的记录。

日期维度

日历和时间维度是特殊情况，因为它们通常没有源数据。 相反，它们是使用固定逻辑生成的。

应在每个新年开始时加载日期维度表，以将其行扩展到未来的特定年份。 可能会有其他业务数据需要定期更新，例如会计年度数据、节假日、周数。

当日期维度表包含相对偏移属性时，必须每天运行 ETL 进程，以根据当前日期（今天）更新偏移属性值。

建议使用 T-SQL 编写扩展或更新日期维度表的逻辑并将其封装在存储过程中。

处理事实数据表

处理事实数据表涉及将数据仓库数据与源系统事实同步。 首先转换源数据并准备将其加载到事实数据表中。 然后，对于每个维度键，查找将确定要存储在事实数据行中的代理键值。 当维度支持 SCD 类型 2 时，应检索维度成员的当前版本的代理键。

注意

通常，可以为日期和时间维度计算代理键，因为它们应该使用 YYYYMMDD 或 HHMM 格式。 有关详细信息，请参阅日历和时间。

如果维度键查找失败，则可能表示源系统出现完整性问题。 在这种情况下，事实数据行仍然必须插入到事实数据表中。 仍然必须存储有效的维度键。 一种方法是存储特殊的维度成员（如未知）。 此方法需要稍后更新，以便在已知时正确分配真正的维度键值。

重要

由于 Fabric 仓库不强制实施外键，因此 ETL 进程在将数据加载到事实数据表时检查完整性至关重要。

当确信自然键有效时，另一种方法是插入一个新的维度成员，然后存储它的代理键值。 有关详细信息，请参阅本节后面的推断维度成员。

下图描述了用于处理事实数据表的逻辑。

应尽可能以增量方式加载事实数据表，这意味着会检测并插入新的事实数据。 增量加载策略更具可伸缩性，可减少源系统和目标系统的工作负载。

重要

尤其是对于大型事实数据表，它应该是截断并重新加载事实数据表的最后手段。 这种方法在处理时间、计算资源以及可能对源系统造成的中断方面代价高昂。 当事实数据表维度应用 SCD 类型 2 时，它还涉及到复杂性。 这是因为维度键查找需要在维度成员版本的有效期内完成。

希望你可以通过依靠源系统标识符或时间戳来有效地检测新事实数据。 例如，当源系统可靠地按顺序记录销售订单时，你可以存储检索的最新销售订单号（称为高水印）。 下一个流程可以使用该销售订单号来检索新创建的销售订单，并再次存储检索到的最新销售订单号，供下一个流程使用。 还可以使用创建日期列可靠地检测新订单。

如果无法依赖源系统数据来有效检测新事实数据，则可以依赖源系统的功能来执行增量加载。 例如，SQL Server 和 Azure SQL 托管实例具有一项称为变更数据捕获 (CDC) 的功能，该功能可以跟踪表中每一行的更改。 此外，SQL Server、Azure SQL 托管实例和 Azure SQL 数据库具有一项称为更改跟踪的功能，该功能可以标识已更改的行。 启用后，它可以帮助你有效地检测任何数据库表中新的或已更改的数据。 你还可以向关系表添加触发器，以存储插入、更新或删除的表记录的键。

最后，你可以使用属性将源数据关联到事实数据表。 例如，销售订单号和销售订单行号。 但是，对于大型事实数据表，检测新的、已更改或已删除的事实数据可能是一项非常昂贵的操作。 当源系统存档操作数据时，也可能出现问题。

已推理维度成员

当事实数据加载进程插入新的维度成员时，它称为推断成员。 例如，当酒店来宾签入时，系统会要求他们以会员身份加入酒店连锁店。 成员资格号码会立即发放，但来宾的详细信息可能要等到来宾提交文件（如果有的话）才会跟进。

关于维度成员所知道的只有它的自然键。 事实数据加载过程需要使用 Unknown 属性值创建新的维度成员。 重要的是，它必须将 IsInferredMember 审核属性设置为 TRUE。 这样，当获取延迟到达的详细信息时，维度加载过程可以对维度行进行必要的更新。 有关详细信息，请参阅本文中的管理历史更改。

事实数据更新或删除

你可能需要更新或删除事实数据。 例如，取消销售订单或更改订单数量时。 就像前面描述的加载事实数据表一样，需要有效地检测更改并执行对事实数据的相应修改。 在此取消订单示例中，销售订单状态可能会从“打开”更改为“已取消”。 该更改需要更新事实数据，而不是删除行。 对于数量更改，需要更新事实数据行数量度量值。 这项使用软删除的策略会保留历史记录。 软删除会将行标记为不再活动或有效，所有报告查询和 Power BI 语义模型都应筛选掉属于软删除的记录。

预测事实数据更新或删除时，应将属性（如销售订单号及其销售订单行号）包含在事实数据表中，以帮助识别要修改的事实数据行。 请务必为这些列编制索引，以支持高效的修改操作。

最后，如果使用特殊维度成员插入事实数据（如未知），则需要运行一个定期过程，以检索此类事实数据行的当前源数据，并将维度键更新为有效值。

相关内容

有关将数据加载到 Fabric 仓库的详细信息，请参阅：

• 将数据引入仓库
• 使用数据管道将数据引入仓库
• 使用 COPY 语句将数据引入仓库
• 使用 Transact-SQL 将数据引入仓库
• 教程：为 Fabric 仓库设置 dbt