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

本篇文章总结了实现缓慢渐变维度的几种方式，并且分析了 Changing Attribute 和 Historical Attribute 输出的逻辑过程。 测试表以及测试数据，其中 Customer 是数据源表，DimCustomer 模拟的是数据仓库中的 Customer 维度表。每个示例都是从空表开始，第一次运行的时候 Dimension 表没有数据，第二次运行之前将添加几条数据到 Customer 数据源表中，并同时修改若干数据。但是要注意这个示例对数据源数据的加载是全部加载，而不考虑基于数据源数据的增量加载，关于增量加载的实现会放在 BI 系列的其它文章中讲解。USE BIWORK_SSISGOIF OBJECT_ID(Customer) IS NOT NULLDROP TABLE Customer GO IF OBJECT_ID(DimCustomer) IS NOT NULLDROP TABLE DimCustomer GOCREATE 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，哪些字段的数据需要重新添加一条数据以保留历史信息 SCD Type 2。下一步设计 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 NULLChanging 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.CustomerSET Occupation = ITWHERE ID = 3 - 同时修改 Changing Attribute 和 Historical Attribute UPDATE BIWORK_SSIS.dbo.CustomerSET Occupation = Publisher, City = HangzhouWHERE ID = 2再次执行 SSIS Package 并查询数据库结果 -新增的一条数据是 Wangwu ，因此将直接添加新的一条记录到 DimCustomer 中。ZhangSan 因为修改了 City ，因此属于 Type 2 SCD 需要保留历史数据。所以先修改 ZhangSan 的 EndDate 和 IsCurrent 保留这条历史数据，然后再将最新的数据添加到 DimCustomer 中，也就是最后看到的 ZhangSan - Hangzhou - PublisherLisi 因为修改了 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 SCDMERGE INTO dbo.DimCustomer AS DimUSING dbo.Customer AS Src ON Dim.CustomerAlternateKey = Src.IDWHEN 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 SCDMERGE INTO dbo.DimCustomer AS DimUSING dbo.Customer AS Src ON Dim.CustomerAlternateKey = Src.ID AND Dim.IsCurrent = 1WHEN 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 和一些原理介绍：SSIS 系列 - Lookup 组件的使用与它的几种缓存模式 - Full Cache, Partial Cache, NO CacheSSIS 系列 - 在 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 = 1OLE_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 NULLHistorical_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 Outpu