微软BI 之SSIS 系列 - 数据仓库中实现 Slowly Changing Dimension 缓慢渐变维度的三种方式

2022-09-26 18:27:09

开篇介绍

关于 Slowly Changing Dimension 缓慢渐变维度的理论概念请参看数据仓库系列 - 缓慢渐变维度 (Slowly Changing Dimension) 常见的三种类型及原型设计

本篇文章总结了实现缓慢渐变维度的几种方式，并且分析了 Changing Attribute 和 Historical Attribute 输出的逻辑过程。

示例一：SSIS 中使用 Slowly Changing Dimension 控件
示例二：使用 SQL 中 Merge 语句实现简单的 SCD 效果
示例三：在 SSIS 中使用 Lookup, Conditional Split, Multicast 等控件实现 SCD 效果

测试表以及测试数据

其中 Customer 是数据源表，DimCustomer 模拟的是数据仓库中的 Customer 维度表。

每个示例都是从空表开始，第一次运行的时候 Dimension 表没有数据，第二次运行之前将添加几条数据到 Customer 数据源表中，并同时修改若干数据。

但是要注意这个示例对数据源数据的加载是全部加载，而不考虑基于数据源数据的增量加载，关于增量加载的实现会放在 BI 系列的其它文章中讲解。

USE BIWORK_SSIS

GO

IF OBJECT_ID('Customer') IS NOT NULL

DROP TABLE Customer

GO

IF OBJECT_ID('DimCustomer') IS NOT NULL

DROP TABLE DimCustomer

GO

CREATE TABLE Customer

(

    ID INT PRIMARY KEY IDENTITY(1,1),

    FullName NVARCHAR(50),

    City NVARCHAR(50),

    Occupation NVARCHAR(50)

)

CREATE TABLE DimCustomer

(

    CustomerID INT PRIMARY KEY IDENTITY(1,1),

    CustomerAlternateKey INT,

    FullName NVARCHAR(50),

    City NVARCHAR(50),

    Occupation NVARCHAR(50),

    StartDate DATETIME,

    EndDate DATETIME,

    IsCurrent BIT DEFAULT(1)

)

INSERT INTO BIWORK_SSIS.dbo.Customer VALUES

('BIWORK','Beijing','IT'),

('ZhangSan','Shanghai','Education'),

('Lisi','Guangzhou','Student')

示例一 SSIS 中的 Slowly Changing Dimension

新建一个 Package 并拖放一个 Data Flow，在 Data Flow 中建立好与 Customer 表的数据源连接，新建 Slowly Changing Dimension SCD_DimCustomer。

双击 SCD_DimCustomer 编辑相关的属性。

Input Columns 来源于上游数据源即 Customer 表，Dimension Columns 描述 DimCustomer 表信息。

Key Type - Business Key 表示 Customer.ID 与 DimCustomer.CustomerAlternateKey 关联，后面的数据更新或者插入就跟这个 Business Key 相关。

其主要逻辑是以 Customer.ID 对比 DimCustomer.CustomerAlternateKey ，如果关联不到则表示 Customer 中有新数据则将新数据插入到 DimCustomer 中。

如果关联到则检查哪些字段是不需要更新 SCD Type 0，哪些字段的数据是需要更新的 SCD Type 1。

下一步设计 DimCustomer 表中几个属性字段。

City - 历史数据，如果 City 发生更改则添加一条新的数据而保留此历史信息 - Type 2。

FullName - 固定的值，此字段的数据在数据仓库中不发生更改 - Type 0。

Occupation - 可更改的值，如果 Occupation 发生更改则只修改它而不保留历史信息 - Type 1。

在这里暂时不设置 - 如果检测到 Customer 中 FullName 发生更改就报错。

对于 Type 2 Historical Attribute 的设计是使用有效时间段来表示的，具体的理论概念请参看数据仓库系列 - 缓慢渐变维度 (Slowly Changing Dimension) 常见的三种类型及原型设计其中有详细的讲解。第一个选择是使用标志字段来表示这个记录是否到期或者是当前使用的，在我们现在的这个例子中可以先设计为有效期，后面可以修改让两种方式都存在。

推断成员的设置，暂时这里不设置推断成员。推断成员一般发生在维度表的数据载入落后于Fact事实表的数据载入，因此Fact事实表数据加载在前因此就引用不到相应的Dimension Key而造成这个问题，这个以后会专门写一篇文章来讨论推断成员。

Slowly Changing Dimension 这个控件此时会产生两个分支逻辑三组输出。

设置完了之后会自动生成其它的所有逻辑，并且已经帮助实现了 SCD 的功能。

执行之后看看具体的效果 -

分析一下 Slowly Changing Dimension 的逻辑。

其中 New Output 输出就是直接插入新的纪录到 DimCustomer 中。

Historical Attribute Insert Output 向下的 OLE DB Command 中 SQL 语句为 -

UPDATE [dbo].[DimCustomer] SET [EndDate] = ? WHERE [CustomerAlternateKey] = ? AND [EndDate] IS NULL

对于历史的数据应该是修改 EndDate 将这条数据表示终止状态，并且继续添加一条新的数据。在这里因为多添加了一个 IsCurrent 来表示记录的状态，因此这条 SQL 语句应该修改为：IsCurrent = 0，这个逻辑需要在 SSIS 中做出细微的调整。

UPDATE [dbo].[DimCustomer] SET [EndDate] = ?, [IsCurrent] = ? WHERE [CustomerAlternateKey] = ? AND [EndDate] IS NULL

Changing Attribute Update Output 向下的 OLE DB Command 1 中 SQL 语句为 -

UPDATE [dbo].[DimCustomer] SET [Occupation] = ? WHERE [CustomerAlternateKey] = ? AND [EndDate] IS NULL

对于 SCD Type 1 的属性只需要直接更改即可，因此直接根据 Customer.ID 即关联到的 DimCustomer.CustomerAlternateKey 修改相应的属性。

对于 Historical Attribute Insert Output 下的 Derived Column 和 OLE DB Command 中作出的修改：

Derived Column 新增加一个 HistoricalCurrent ，其值为0，用来表示当条记录为历史记录。

修改 SQL 语句

修改 Column Mapping

对源数据做出一定的修改：

-- 新插入一条

INSERT INTO BIWORK_SSIS.dbo.Customer VALUES

('Wangwu','Beijing','Finance')

-- 修改 Changing Attribute

UPDATE BIWORK_SSIS.dbo.Customer

SET Occupation = 'IT'

WHERE ID = 3 

-- 同时修改 Changing Attribute 和 Historical Attribute

UPDATE BIWORK_SSIS.dbo.Customer

SET Occupation = 'Publisher',

    City = 'Hangzhou'

WHERE ID = 2

再次执行 SSIS Package 并查询数据库结果 -

新增的一条数据是 Wangwu ，因此将直接添加新的一条记录到 DimCustomer 中。

ZhangSan 因为修改了 City ，因此属于 Type 2 SCD 需要保留历史数据。所以先修改 ZhangSan 的 EndDate 和 IsCurrent 保留这条历史数据，然后再将最新的数据添加到 DimCustomer 中，也就是最后看到的 ZhangSan - Hangzhou - Publisher

Lisi 因为修改了 Occupation 属于 Type 1 SCD 只需要修改原数据即可，所以 Lisi 的 Occupation 直接更新为 IT 即可。

逻辑图解

下面是对 SCD Type 1 和 Type 2 实现逻辑的总结，如果理解了这些逻辑我们也完全可以用其它的 SSIS 控件来实现 SCD 的功能。

Type 2 SCD 要比 Type 1 要复杂一些，它有一个 Update 之后的 Insert 操作。

示例二 - 使用 SQL 中 MERGE 语句实现 SCD Type 1 和 SCD Type 2 的功能

SQL MERGE 语句非常实用，可以非常简单的根据一些关联条件来比较两个表的数据，然后决定匹配的逻辑如何执行和不匹配的时候逻辑如何处理。关于 SQL MERGE 的语法和使用请参照 SQL Server - 使用 Merge 语句实现表数据之间的对比同步

使用 MERGE 语句来实现上面的效果

-- Type 2 SCD

MERGE INTO dbo.DimCustomer AS Dim

USING dbo.Customer AS Src

    ON Dim.CustomerAlternateKey = Src.ID

WHEN NOT MATCHED BY TARGET

    THEN INSERT VALUES(Src.ID,Src.FullName,Src.City,Src.Occupation,GETDATE(),NULL,1)

WHEN MATCHED AND Dim.City <> Src.City

    THEN UPDATE SET Dim.EndDate = GETDATE(),Dim.IsCurrent = 0

;

-- Type 1 SCD

MERGE INTO dbo.DimCustomer AS Dim

USING dbo.Customer AS Src

    ON Dim.CustomerAlternateKey = Src.ID

    AND Dim.IsCurrent = 1

WHEN NOT MATCHED BY TARGET

    THEN INSERT VALUES(Src.ID,Src.FullName,Src.City,Src.Occupation,GETDATE(),NULL,1)

WHEN MATCHED AND Dim.Occupation <> Src.Occupation

    THEN UPDATE SET Dim.Occupation = Src.Occupation

;

因为在 MERGE 语句中有一些语法限制

在 Merge Matched 操作中，只能允许执行 UPDATE 或者 DELETE 语句。
在 Merge Not Matched 操作中，只允许执行 INSERT 语句。
一个 Merge 语句中出现的 Matched 操作，只能出现一次 UPDATE 或者 DELETE 语句，否则就会出现下面的错误 - An action of type 'WHEN MATCHED' cannot appear more than once in a 'UPDATE' clause of a MERGE statement.
Merge 语句最后必须包含分号，以 ; 结束。

所以在这里采取的方式是：

Type 2 SCD 注释的地方 - 根据 Customer.ID = DimCustomer.CustomerAlternateKey 关联如果没有找到匹配的记录，就意味是新数据，直接插入到 DimCustomer 表中。

如果匹配到了即此数据在维度表中也存在，因此先将此记录更新完毕标志此条记录为历史记录 - EndDate 和 IsCurrent 都设置了值表示 SCD Type 2。

Type 1 SCD 注释的地方 - 因为刚才的历史记录已经被标识为 IsCurrent = 0，因此在此时的逻辑将匹配不到数据，因此作为新数据插入，这样就延续了 SCD Type 2 Update 之后的 Insert 操作。

对于匹配到的数据，再来比较 SCD Type 1 的列，如果不匹配的话那么就直接更新掉就可以了。

和示例一使用相同的测试数据和相同的数据修改方式后，执行完的效果也是一样的。

第一次执行

修改完测试数据之后再次执行

在 SSIS 中使用 Lookup, Conditional Split, Multicast 等控件实现 SCD 效果

一旦理解了 SCD 的实现逻辑，我们完全可以自己通过 SSIS 中的其它 Task 来实现 Slowly Changing Dimension。

会使用到的 Task 包括 Lookup，Multicast，Conditional Split 等。

可以参看相应的 Task 的Demo 和一些原理介绍：

微软BI 之SSIS 系列 - Lookup 组件的使用与它的几种缓存模式 - Full Cache, Partial Cache, NO Cache
微软BI 之SSIS系列 - 在 SSIS 中使用 Multicast Task 将数据源数据同时写入多个目标表，备份数据表，以及写入Audit 信息

新建一个 Data Flow Task 并且仍然将 Customer 表作为数据源，拖放一个 Lookup Task 并完成以下配置。

LKP_DimCustomer 中 Reference Table 引用集/引用表是 DimCustomer。

左边是Customer表，右边是要去 Look Up 的 DimCustomer，Customer.ID = DimCustomer.CustomerAlternateKey 关联。

基于 Customer.ID = DimCustomer.CustomerAlternateKey 就会有两种结果，匹配的输出和不匹配的输出。

不匹配的输出就是添加新数据。

匹配的输出就是要去检查 Historical Attribute "City" 有没有更改，如果有更改就是一次 Update 然后加上一次 Insert 操作。

如果 Changing Attribute "Occupation" 有更改就是一次 Update 操作。

中间会使用到的三个状态 - StartDate , EndDate, IsCurrent 都会在整个流程中使用到，主要用来更新它们的状态。

先实现不匹配的逻辑，即先添加一条新的数据。

DC_NewInsertStartDate 需要准备 StartDate 和 IsCurrent = 1

OLE_DST_DimCustomer 的配置

Customer.ID = DimCustomer.CustomerAlternateKey 匹配的情况下有两种情况：

City 不匹配和 Occupation 不匹配，添加一个 Conditional Split 并连接到 Lookup 的匹配输出上。

下面是全部的实现效果 - Changing Update 下的逻辑是直接修改 DimCustomer 的数据，OLE_CMD_Update 中

UPDATE [dbo].[DimCustomer] SET [Occupation] = ? WHERE [CustomerAlternateKey] = ? AND [EndDate] IS NULL

Historical_Update 下使用了一个 Multicast 将数据流分为两个分支，因为它是 Historical Attribute Update，因此逻辑是更新原历史数据，添加新数据。

OLE_CMD_UpdateHistorical 中的 SQL 语句，这里的 IsCurrent 将最终更新为 0 。

UPDATE [dbo].[DimCustomer] SET [EndDate] = ? ,IsCurrent = ? WHERE [CustomerAlternateKey] = ? AND [EndDate] IS NULL

使用前两个示例中的测试数据，第一次执行完 SSIS Package 之后三条数据走向了 Lookup No Match Output 表示新数据。

查询数据表结果

修改完测试数据之后再次执行，数据源 1 条是新数据走向 Lookup No Match Output，1 条是 Historical Update 因此需要 Update 历史数据然后再添加一条新数据，1 条是 Changing Update 因此直接 Update 就可以了。

执行效果如下所示

那么至此，这三种对于 Slowly Chaning Dimension 缓慢渐变维度的实现就全部演示完了。从中可以发现，整个 SCD 处理的逻辑在三个示例中本质上都是一样的。都是围绕着 Business Key 匹配和不匹配的结果来展开的，并且在这个过程中区别了 SCD Type 1 和 Type 2。对于 Type 1 就是一个更新操作，对于 Type 2 不仅有更新操作而且还有插入操作。只要理解了它们的实现逻辑，使用不同的方式实现起来并不困难。

这三种方式中，第一种方式即直接使用 SSIS 中提供的 SCD Task 实现起来最为简单，基本上都是配置性的内容。但是往往因为数据量过大可能造成性能上的问题，因此才会有示例二和示例三中出现的方法。第二种方式代码更为直接，但是如果遇到多个属性变化，在代码上会有一些变化，这个需要仔细认真的检查和测试。第三种方式相对于第一种方式要花费更多的时间，但是在实现方式上可以更为灵活的满足各种需要。

我并没有基于这三个示例做出性能上的测试，因为在实际的维度变化设计中，Historical Attribute 和 Changing Attribute 可能不止一个，可能会有多个。并且维度表的大小，数据源表的大小对性能上的影响也都存在。所以在这里只是提出常用到可以解决 SCD 问题的几种方式，并且可以根据实际的需求进行缓慢维度变化设计，根据实际测试的效率高低来选择合适的方案。

写的比较多，总结如果有不足或者遗漏之处还望指出，谢谢！另外：关于 Inferred Member 会另外专门写随笔总结！

PS：后面两个案例实际上是有错误的，大家如果仔细看的话。在检查历史数据的情况下，Lookup Dim 包括 Merge 里面的查找对比是应该要加上一个条件的 IsCurrent = 1，因为在比较 CustomerAlternateKey 的时候是只能与当前记录对比的，而不应该包含历史记录部分的对比。Multicast 可以去掉，将插入挪到更新之后，这样保证更新历史在前，插入新纪录在后。

上面的图片和文字我就不一一修改了，保留这些错误让大家也能看到这些错误的原因和解决过程，请大家自行测试发现错误纠正。

更多 BI 文章请参看 BI 系列随笔列表 (SSIS, SSRS, SSAS, MDX, SQL Server) 如果觉得这篇文章看了对您有帮助，请帮助推荐，以方便他人在 BIWORK 博客推荐栏中快速看到这些文章。

码农公寓

开篇介绍

测试表以及测试数据

示例一 SSIS 中的 Slowly Changing Dimension

逻辑图解

示例二 - 使用 SQL 中 MERGE 语句实现 SCD Type 1 和 SCD Type 2 的功能

相关文章