SCD缓慢渐变维度.docx

SCD常见的三种类型(Slowly Changing Dimension)在从OLTP业务数据库向DW数据仓库抽取数据的过程中，特别是第一次导入之后的每一次增量抽取往往会遇到这样的问题：业务数据库中的一些数据发生了更改，到底要不要将这些变化也反映到数据仓库中？在数据仓库中，哪些数据应该随之变化，哪些可以不用变化？考虑到这些变化，在数据仓库中的维度表又应该如何设计以满足这些需要。很显然在业务数据库中数据的变化是非常自然和正常的，比如顾客的联系方式，手机号码等信息可能随着顾客的所在地的更改发生变化，比如商品的价格在不同时期有上涨和下降的变化。那么在业务数据库中，很自然的就会修改并马上反映到实际业务当中去。但是在数据仓库中，其数据主要的特征一是静态历史数据，二是少改变不删除，三是定期增长，其作用主要用来数据分析。因此分析的过程中对历史数据就提出了要求，有一些数据是需要能够反映出在周期内的变化历史，有一些数据缺不需要，那么这些数据应该如何来控制。假设在第一次从业务数据库中加载了一批数据到数据仓库中，当时业务数据库有这样的一条顾客的信息。顾客BIWORK，居住在北京，目前是一名BI的开发工程师。假设BIWORK因为北京空气质量PM2.5等原因从北京搬到了三亚。那么这条信息在业务数据库中应该被更新了-那么当下次从业务数据库中抽取这类信息的时候，数据仓库又应该如何处理呢？我们假设在数据仓库中实现了与业务数据库之间的同步，数据仓库中也直接将词条数据修改更新。后来我们创建报表做一些简单的数据统计分析，这时在数据仓库中所有对顾客BIWORK的销售都指向了BIWORK新的所在地-城市三亚，但是实际上BIWORK在之前所有的购买都发生在BIWORK居住在北京的时候。这是一个非常简单的例子，它描述了因一些基本信息的更改可能会引起数据归纳和分析出现的问题。但是有时，这种场景的的确确可能是存在的。为了解决类似于这样的问题需要了解数据仓库中的一个非常重要的概念-缓慢渐变维度。1 缓慢渐变类型一(Type 1 SCD)在数据仓库中，我们可以保持业务数据和数据仓库中的数据始终处于一致。可以在Customer维度中使用来自业务数据库中的Business Key - CustomerID来追踪业务数据的变化，一旦发生变化那么就将旧的业务数据覆盖重写。DW中的记录根据业务数据库中的CustomerID获取了最新的City信息，直接更新到DW中。2 缓慢渐变类型二(Type 2 SCD)当然在数据仓库中更多是对相对静态的历史数据进行数据的汇总和分析，因此会尽可能的维护来自业务系统中的历史数据，能够真正捕获到这种历史数据的变化。以上面的例子来说，可能需要分析的结果是BIWORK在2012年的时候购买额度整体平稳，但是从2013年开始购买额度减少了，出现的原因可能与所在的城市有关系，在北京的门店可能比在三亚的门店相对要多一些。像这种情况，就不能很简单在数据仓库中将BIWORK当前所在城市直接更新，而应该新增加一条数据来说明现在BIWORK所在地是在Sanya。但是如果仅仅在DW中新增一条新的数据仍然会出现新的问题，因为在DW中标识这个顾客是通过CustomerID来实现的，这条CustomerID来源于业务数据库，它是唯一的。然而在DW中新增一条数据来保存业务数据库中历史信息，就无法保证这条数据在DW中的唯一性了，其它的DW数据表关联到这张表就无法知道应该如何引用这个Customer的信息。实际上，如果CustomerID在DW中也作为主键来唯一标识Customer的话，在插入新数据的时候就会发生失败。因此我们需要继续保持Business Key业务键，因为它是关联到业务数据库的唯一纽带。做出改变的部分就是新增加一个Key，一个数据仓库的键。在数据仓库的术语里面，这个唯一标识数据仓库表记录的键我们称之为Surrogate Key代理键，通常设置为DW表的主键。在上面这张表中，其中 -CustomerID - Business Key业务键，用来连接业务数据库和数据仓库的键，注意无论在业务数据库还是数据仓库无论任何时候都不应该发生改变。DWID - Surrogate Key代理键，一般设置为DW维度表的主键，用来在数据仓库内部中的维度表和事实表建立关联。为什么使用代理键，有什么好处？假设我们的业务数据库来自于不同的系统，对这些数据进行整合的时候有可能出现相同的Business Key，这时通过Surrogate Key就可以解决这个问题。一般来自业务数据库中的Business Key可能字段较长，比如GUID，长字符串标识等，使用Surrogate Key可以直接设置成整形的。事实表本身体积就很大，关联Surrogate Key与关联Business Key相比，Surrogate Key效率更高，并且节省事实表体积。最重要的一点就是上面举到的这个例子，使用Surrogate Key可以更好的解决这种缓慢渐变维度，维护历史信息记录。什么时候可以不用代理键？我觉得可以结合我们的实际业务，比如像有些业务表本身的Business Key就已经是整形的了，并且表中的属性基本上不随着时间或地理发生改变。比如像某些国家名称，地区编号编码等等基本上不会怎么发生改变，即使改变了也不需要维护历史记录这样的情况下可以直接使用业务数据库中的Business Key而不需要设置新的Surrogate Key。接着上面的表结构讲，光这样设置了新的Surrogate Key - DWID是不够的，因为还需要告诉数据仓库哪一条信息是现在正在使用的。当然可以根据DWID的顺序来查出最新的记录，但是每次都要比较CustomerID然后找出最大的DWID这样的查询比较麻烦。因此可以额外一个标志表示这条数据是最新更改的。另外的一种方式就是通过起始时间来标识，Valid To为NULL的标识当前数据。当然，也有将两者都综合的。还有一种情况就是混合使用Type 1和Type 2的，比如说Occupation这个字段在业务数据库中发生了变化，但是可以不用维护这个历史信息，因此可能的做法是直接将最新的Occupation在数据仓库中覆盖掉。根据实际情况，还有一种做法就是全部覆盖掉。3 缓慢渐变类型三(Type 3 SCD)实际上Type 1 and 2可以满足大多数需求了，但是仍然有其它的解决方案，比如说Type 3 SCD。Type 3 SCD希望只维护更少的历史记录，比如说把要维护的历史字段新增一列，然后每次只更新Current Column和Previous Column。这样，只保存了最近两次的历史记录。但是如果要维护的字段比较多，就比较麻烦，因为要更多的Current和Previous字段。所以Type 3 SCD用的还是没有Type 1和Type 2那么普遍。4 总结Type 1 SCD-不记录历史数据。一切不需要维护的历史数据都可以选择Type 1，假设地理信息中的国家名称发生更改，像这种数据基本上不需要维护的话，那么就直接使用Type 1 SCD覆盖旧的国家名称。Type 2 SCD-添加新的数据。使用的比较常见，基本上除了Type 1 SCD之外的情形都会优先考虑Type 2 SCD。Type 3 SCD-添加历史列。不会追踪所有的历史记录，只会追踪上一次的历史信息。这种情况往往介于Type 1和Type 2的时候会考虑，需要记录历史数据，但是又不需要记录那么多。其它的相关文章关于在 SSIS 中如何实现 SCD 请参看微软BI SSIS 系列 - 数据仓库中实现 Slowly Changing Dimension 缓慢渐变维度的三种方式PS在不同的工具中对 SCD 的实现是不一样的，比如在微软 SSIS SCD 控件的设计当中对 SCD 的实现:Type 0 - Fixed Attribute 不变化的属性。Type 1 - Changing Attribute 可变化的属性，会重写数据。Type 2 - Historical Attribute 历史属性。5 SCD案例本篇文章总结了实现缓慢渐变维度的几种方式，并且分析了 Changing Attribute 和 Historical Attribute 输出的逻辑过程。示例一：SSIS 中使用 Slowly Changing Dimension 控件示例二：使用 SQL 中 Merge 语句实现简单的 SCD 效果示例三：在 SSIS 中使用 Lookup, Conditional Split, Multicast 等控件实现 SCD 效果5.1 测试表以及测试数据其中 Customer 是数据源表，DimCustomer 模拟的是数据仓库中的 Customer 维度表。每个示例都是从空表开始，第一次运行的时候 Dimension 表没有数据，第二次运行之前将添加几条数据到 Customer 数据源表中，并同时修改若干数据。但是要注意这个示例对数据源数据的加载是全部加载，而不考虑基于数据源数据的增量加载，关于增量加载的实现会放在 BI 系列的其它文章中讲解。USE BIWORK_SSISGOIF OBJECT_ID(Customer) IS NOT NULLDROP TABLE Customer GOIF 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)5.2 示例一 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 发生更改就报错。第一个选择是使用标志字段来表示这个记录是否到期或者是当前使用的，在我们现在的这个例子中可以先设计为有效期，后面可以修改让两种方式都存在。推断成员的设置，暂时这里不设置推断成员。推断成员一般发生在维度表的数据载入落后于Fact事实表的数据载入，因此Fact事实表数据加载在前因此就引用不到相应的Dimension Key而造成这个问题，这个以后会专门写一篇文章来讨论推断成员。Slowly Changing Dimension 这个控件此时会产生两个分支逻辑三组输出。设置完了之后会自动生成其它的所有逻辑，并且已经帮助实现了 SCD 的功能。执行之后看看具体的效果 -分析一下 Slowly Changing Dimension 的逻辑。1) 其中 New Output 输出就是直接插入新的纪录到 DimCustomer 中。2) 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 NULL3) 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.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 操作。5.3 示例二 使用 SQL 中 MERGE 语句实现SCD Type 1 和 SCD Type 2 的功能SQL MERGE 语句非常实用，可以非常简单的根据一些关联条件来比较两个表的数据，然后决定匹配的逻辑如何执行和不匹配的时候逻辑如何处理。使用 MERGE 语句来实现上面的效果- Type 2 SCDMERGE INTO dbo.DimCustomer AS DimUSING dbo.Customer AS Src ON Dim.CustomerAlternateKey = Src.IDWHEN NOT MATCHED BY TARGETTHEN 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 TARGETTHEN 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 信息5.4 示例三 在 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