SCD缓慢渐变维度.docx
《SCD缓慢渐变维度.docx》由会员分享，可在线阅读，更多相关《SCD缓慢渐变维度.docx（27页珍藏版）》请在冰点文库上搜索。
SCD缓慢渐变维度
SCD常见的三种类型
（SlowlyChangingDimension）
在从 OLTP 业务数据库向 DW 数据仓库抽取数据的过程中，特别是第一次导入之后的每一次增量抽取往往会遇到这样的问题：
业务数据库中的一些数据发生了更改，到底要不要将这些变化也反映到数据仓库中？
在数据仓库中，哪些数据应该随之变化，哪些可以不用变化？
考虑到这些变化，在数据仓库中的维度表又应该如何设计以满足这些需要。
很显然在业务数据库中数据的变化是非常自然和正常的，比如顾客的联系方式，手机号码等信息可能随着顾客的所在地的更改发生变化，比如商品的价格在不同时期有上涨和下降的变化。
那么在业务数据库中，很自然的就会修改并马上反映到实际业务当中去。
但是在数据仓库中，其数据主要的特征一是静态历史数据，二是少改变不删除，三是定期增长，其作用主要用来数据分析。
因此分析的过程中对历史数据就提出了要求，有一些数据是需要能够反映出在周期内的变化历史，有一些数据缺不需要，那么这些数据应该如何来控制。
假设在第一次从业务数据库中加载了一批数据到数据仓库中，当时业务数据库有这样的一条顾客的信息。
顾客 BIWORK ，居住在北京，目前是一名 BI 的开发工程师。
假设 BIWORK 因为北京空气质量 PM2.5 等原因从北京搬到了三亚。
那么这条信息在业务数据库中应该被更新了 -
那么当下次从业务数据库中抽取这类信息的时候，数据仓库又应该如何处理呢？
我们假设在数据仓库中实现了与业务数据库之间的同步，数据仓库中也直接将词条数据修改更新。
后来我们创建报表做一些简单的数据统计分析，这时在数据仓库中所有对顾客 BIWORK 的销售都指向了 BIWORK 新的所在地 - 城市三亚，但是实际上 BIWORK 在之前所有的购买都发生在 BIWORK 居住在北京的时候。
这是一个非常简单的例子，它描述了因一些基本信息的更改可能会引起数据归纳和分析出现的问题。
但是有时，这种场景的的确确可能是存在的。
为了解决类似于这样的问题需要了解数据仓库中的一个非常重要的概念 - 缓慢渐变维度。
1缓慢渐变类型一 （Type1SCD）
在数据仓库中，我们可以保持业务数据和数据仓库中的数据始终处于一致。
可以在 Customer 维度中使用来自业务数据库中的 BusinessKey-CustomerID 来追踪业务数据的变化，一旦发生变化那么就将旧的业务数据覆盖重写。
DW 中的记录根据业务数据库中的 CustomerID 获取了最新的 City 信息，直接更新到 DW 中。
2缓慢渐变类型二 （Type2SCD）
当然在数据仓库中更多是对相对静态的历史数据进行数据的汇总和分析，因此会尽可能的维护来自业务系统中的历史数据，能够真正捕获到这种历史数据的变化。
以上面的例子来说，可能需要分析的结果是 BIWORK 在 2012年的时候购买额度整体平稳，但是从2013年开始购买额度减少了，出现的原因可能与所在的城市有关系，在北京的门店可能比在三亚的门店相对要多一些。
像这种情况，就不能很简单在数据仓库中将 BIWORK 当前所在城市直接更新，而应该新增加一条数据来说明现在 BIWORK 所在地是在 Sanya。
但是如果仅仅在 DW 中新增一条新的数据仍然会出现新的问题，因为在 DW 中标识这个顾客是通过 CustomerID 来实现的，这条 CustomerID 来源于业务数据库，它是唯一的。
然而在 DW 中新增一条数据来保存业务数据库中历史信息，就无法保证这条数据在 DW 中的唯一性了，其它的 DW 数据表关联到这张表就无法知道应该如何引用这个 Customer 的信息。
实际上，如果 CustomerID 在 DW 中也作为主键来唯一标识 Customer 的话，在插入新数据的时候就会发生失败。
因此我们需要继续保持 BusinessKey 业务键，因为它是关联到业务数据库的唯一纽带。
做出改变的部分就是新增加一个 Key，一个数据仓库的键。
在数据仓库的术语里面，这个唯一标识数据仓库表记录的键我们称之为 SurrogateKey 代理键，通常设置为DW表的主键。
在上面这张表中，其中-
CustomerID-BusinessKey 业务键，用来连接业务数据库和数据仓库的键，注意无论在业务数据库还是数据仓库无论任何时候都不应该发生改变。
DWID-SurrogateKey 代理键，一般设置为 DW 维度表的主键，用来在数据仓库内部中的维度表和事实表建立关联。
为什么使用代理键，有什么好处？
假设我们的业务数据库来自于不同的系统，对这些数据进行整合的时候有可能出现相同的 BusinessKey，这时通过 SurrogateKey 就可以解决这个问题。
一般来自业务数据库中的 BusinessKey 可能字段较长，比如 GUID，长字符串标识等，使用SurrogateKey 可以直接设置成整形的。
事实表本身体积就很大，关联 SurrogateKey 与关联 BusinessKey 相比，SurrogateKey 效率更高，并且节省事实表体积。
最重要的一点就是上面举到的这个例子，使用 SurrogateKey 可以更好的解决这种缓慢渐变维度，维护历史信息记录。
什么时候可以不用代理键？
我觉得可以结合我们的实际业务，比如像有些业务表本身的 BusinessKey 就已经是整形的了，并且表中的属性基本上不随着时间或地理发生改变。
比如像某些国家名称，地区编号编码等等基本上不会怎么发生改变，即使改变了也不需要维护历史记录这样的情况下可以直接使用业务数据库中的 BusinessKey 而不需要设置新的 SurrogateKey。
接着上面的表结构讲，光这样设置了新的 SurrogateKey-DWID 是不够的，因为还需要告诉数据仓库哪一条信息是现在正在使用的。
当然可以根据 DWID 的顺序来查出最新的记录，但是每次都要比较 CustomerID 然后找出最大的 DWID 这样的查询比较麻烦。
因此可以额外一个标志表示这条数据是最新更改的。
另外的一种方式就是通过起始时间来标识，ValidTo 为 NULL 的标识当前数据。
当然，也有将两者都综合的。
还有一种情况就是混合使用 Type1 和 Type2 的，比如说 Occupation 这个字段在业务数据库中发生了变化，但是可以不用维护这个历史信息，因此可能的做法是直接将最新的 Occupation 在数据仓库中覆盖掉。
根据实际情况，还有一种做法就是全部覆盖掉。
3缓慢渐变类型三 （Type3SCD）
实际上 Type1and2 可以满足大多数需求了，但是仍然有其它的解决方案，比如说 Type3SCD。
Type3SCD 希望只维护更少的历史记录，
比如说把要维护的历史字段新增一列，然后每次只更新 CurrentColumn 和 PreviousColumn。
这样，只保存了最近两次的历史记录。
但是如果要维护的字段比较多，就比较麻烦，因为要更多的 Current 和 Previous 字段。
所以 Type3SCD 用的还是没有 Type1 和 Type2 那么普遍。
4总结
Type1SCD - 不记录历史数据。
一切不需要维护的历史数据都可以选择 Type1 ，假设地理信息中的国家名称发生更改，像这种数据基本上不需要维护的话，那么就直接使用 Type1SCD 覆盖旧的国家名称。
Type2SCD - 添加新的数据。
使用的比较常见，基本上除了 Type1SCD 之外的情形都会优先考虑 Type2SCD。
Type3SCD - 添加历史列。
不会追踪所有的历史记录，只会追踪上一次的历史信息。
这种情况往往介于 Type1 和 Type2 的时候会考虑，需要记录历史数据，但是又不需要记录那么多。
其它的相关文章
关于在SSIS中如何实现SCD请参看 微软BISSIS系列-数据仓库中实现SlowlyChangingDimension缓慢渐变维度的三种方式
PS
在不同的工具中对SCD的实现是不一样的，比如在微软SSISSCD控件的设计当中对SCD的实现:
Type0-FixedAttribute不变化的属性。
Type1-ChangingAttribute可变化的属性，会重写数据。
Type2-HistoricalAttribute历史属性。
5SCD案例
本篇文章总结了实现缓慢渐变维度的几种方式，并且分析了ChangingAttribute和HistoricalAttribute输出的逻辑过程。
示例一：
SSIS中使用SlowlyChangingDimension控件
示例二：
使用SQL中Merge语句实现简单的SCD效果
示例三：
在SSIS中使用Lookup,ConditionalSplit,Multicast等控件实现SCD效果
5.1测试表以及测试数据
其中Customer是数据源表，DimCustomer模拟的是数据仓库中的Customer维度表。
每个示例都是从空表开始，第一次运行的时候Dimension表没有数据，第二次运行之前将添加几条数据到Customer数据源表中，并同时修改若干数据。
但是要注意这个示例对数据源数据的加载是全部加载，而不考虑基于数据源数据的增量加载，关于增量加载的实现会放在BI系列的其它文章中讲解。
USEBIWORK_SSIS
GO
IFOBJECT_ID（'Customer'）ISNOTNULL
DROPTABLECustomer
GO
IFOBJECT_ID（'DimCustomer'）ISNOTNULL
DROPTABLEDimCustomer
GO
CREATETABLECustomer
（
IDINTPRIMARYKEYIDENTITY（1,1）,
FullNameNVARCHAR（50）,
CityNVARCHAR（50）,
OccupationNVARCHAR（50）
）
CREATETABLEDimCustomer
（
CustomerIDINTPRIMARYKEYIDENTITY（1,1）,
CustomerAlternateKeyINT,
FullNameNVARCHAR（50）,
CityNVARCHAR（50）,
OccupationNVARCHAR（50）,
StartDateDATETIME,
EndDateDATETIME,
IsCurrentBITDEFAULT
（1）
）
INSERTINTOBIWORK_SSIS.dbo.CustomerVALUES
（'BIWORK','Beijing','IT'）,
（'ZhangSan','Shanghai','Education'）,
（'Lisi','Guangzhou','Student'）
5.2示例一 SSIS中的SlowlyChangingDimension
新建一个Package并拖放一个DataFlow，在DataFlow中建立好与Customer表的数据源连接，新建SlowlyChangingDimensionSCD_DimCustomer。
双击SCD_DimCustomer编辑相关的属性。
InputColumns来源于上游数据源即Customer表，DimensionColumns 描述DimCustomer表信息。
KeyType-BusinessKey表示Customer.ID与DimCustomer.CustomerAlternateKey关联，后面的数据更新或者插入就跟这个BusinessKey相关。
其主要逻辑是以Customer.ID 对比DimCustomer.CustomerAlternateKey，如果关联不到则表示Customer中有新数据则将新数据插入到DimCustomer中。
如果关联到则检查哪些字段是不需要更新SCDType0，哪些字段的数据是需要更新的SCDType1。
下一步设计DimCustomer表中几个属性字段。
City-历史数据，如果City发生更改则添加一条新的数据而保留此历史信息-Type2。
FullName-固定的值，此字段的数据在数据仓库中不发生更改-Type0。
Occupation-可更改的值，如果Occupation发生更改则只修改它而不保留历史信息-Type1。
在这里暂时不设置-如果检测到Customer中FullName发生更改就报错。
第一个选择是使用标志字段来表示这个记录是否到期或者是当前使用的，在我们现在的这个例子中可以先设计为有效期，后面可以修改让两种方式都存在。
推断成员的设置，暂时这里不设置推断成员。
推断成员一般发生在维度表的数据载入落后于Fact事实表的数据载入，因此Fact事实表数据加载在前因此就引用不到相应的DimensionKey而造成这个问题，这个以后会专门写一篇文章来讨论推断成员。
SlowlyChangingDimension这个控件此时会产生两个分支逻辑三组输出。
设置完了之后会自动生成其它的所有逻辑，并且已经帮助实现了SCD的功能。
执行之后看看具体的效果-
分析一下SlowlyChangingDimension的逻辑。
1）其中NewOutput输出就是直接插入新的纪录到DimCustomer中。
2）HistoricalAttributeInsertOutput向下的OLEDBCommand中SQL语句为-
UPDATE[dbo].[DimCustomer]SET[EndDate]=?
WHERE[CustomerAlternateKey]=?
AND[EndDate]ISNULL
对于历史的数据应该是修改EndDate将这条数据表示终止状态，并且继续添加一条新的数据。
在这里因为多添加了一个IsCurrent来表示记录的状态，因此这条SQL语句应该修改为：
IsCurrent=0，这个逻辑需要在SSIS中做出细微的调整。
UPDATE[dbo].[DimCustomer]SET[EndDate]=?
[IsCurrent]=?
WHERE[CustomerAlternateKey]=?
AND[EndDate]ISNULL
3）ChangingAttributeUpdateOutput向下的OLEDBCommand1中SQL语句为-
UPDATE[dbo].[DimCustomer]SET[Occupation]=?
WHERE[CustomerAlternateKey]=?
AND[EndDate]ISNULL
对于SCDType1的属性只需要直接更改即可，因此直接根据Customer.ID即关联到的DimCustomer.CustomerAlternateKey修改相应的属性。
对于HistoricalAttributeInsertOutput下的DerivedColumn和OLEDBCommand中作出的修改：
DerivedColumn新增加一个HistoricalCurrent，其值为0，用来表示当条记录为历史记录。
修改SQL语句
修改ColumnMapping
对源数据做出一定的修改：
--新插入一条
INSERTINTOBIWORK_SSIS.dbo.CustomerVALUES
（'Wangwu','Beijing','Finance'）
--修改ChangingAttribute
UPDATEBIWORK_SSIS.dbo.Customer
SETOccupation='IT'
WHEREID=3
--同时修改ChangingAttribute和HistoricalAttribute
UPDATEBIWORK_SSIS.dbo.Customer
SETOccupation='Publisher',
City='Hangzhou'
WHEREID=2
再次执行SSISPackage并查询数据库结果-
新增的一条数据是Wangwu，因此将直接添加新的一条记录到DimCustomer中。
ZhangSan因为修改了City，因此属于Type2SCD需要保留历史数据。
所以先修改ZhangSan的EndDate和IsCurrent保留这条历史数据，然后再将最新的数据添加到DimCustomer中，也就是最后看到的ZhangSan-Hangzhou-Publisher
Lisi因为修改了Occupation属于Type1SCD只需要修改原数据即可，所以Lisi的Occupation直接更新为IT即可。
逻辑图解
下面是对SCDType1和Type2实现逻辑的总结，如果理解了这些逻辑我们也完全可以用其它的SSIS控件来实现SCD的功能。
Type2SCD要比Type1要复杂一些，它有一个Update之后的Insert操作。
5.3示例二使用SQL中MERGE语句实现SCDType1和SCDType2的功能
SQLMERGE语句非常实用，可以非常简单的根据一些关联条件来比较两个表的数据，然后决定匹配的逻辑如何执行和不匹配的时候逻辑如何处理。
使用MERGE语句来实现上面的效果
--Type2SCD
MERGEINTOdbo.DimCustomerASDim
USINGdbo.CustomerASSrc
ONDim.CustomerAlternateKey=Src.ID
WHENNOTMATCHEDBYTARGET
THENINSERTVALUES（Src.ID,Src.FullName,Src.City,Src.Occupation,GETDATE（）,NULL,1）
WHENMATCHEDANDDim.City<>Src.City
THENUPDATESETDim.EndDate=GETDATE（）,Dim.IsCurrent=0
--Type1SCD
MERGEINTOdbo.DimCustomerASDim
USINGdbo.CustomerASSrc
ONDim.CustomerAlternateKey=Src.ID
ANDDim.IsCurrent=1
WHENNOTMATCHEDBYTARGET
THENINSERTVALUES（Src.ID,Src.FullName,Src.City,Src.Occupation,GETDATE（）,NULL,1）
WHENMATCHEDANDDim.Occupation<>Src.Occupation
THENUPDATESETDim.Occupation=Src.Occupation
因为在MERGE语句中有一些语法限制
在MergeMatched操作中，只能允许执行UPDATE或者DELETE语句。
在MergeNotMatched操作中，只允许执行INSERT语句。
一个Merge语句中出现的Matched操作，只能出现一次UPDATE或者DELETE语句，否则就会出现下面的错误- Anactionoftype'WHENMATCHED'cannotappearmorethanonceina'UPDATE'clauseofaMERGEstatement.
Merge语句最后必须包含分号，以;结束。
所以在这里采取的方式是：
Type2SCD注释的地方-根据Customer.ID=DimCustomer.CustomerAlternateKey关联如果没有找到匹配的记录，就意味是新数据，直接插入到DimCustomer表中。
如果匹配到了即此数据在维度表中也存在，因此先将此记录更新完毕标志此条记录为历史记录-EndDate和IsCurrent都设置了值表示SCDType2。
Type1SCD注释的地方-因为刚才的历史记录已经被标识为IsCurrent=0，因此在此时的逻辑将匹配不到数据，因此作为新数据插入，这样就延续了SCDType2Update之后的Insert操作。
对于匹配到的数据，再来比较SCDType1的列，如果不匹配的话那么就直接更新掉就可以了。
和示例一使用相同的测试数据和相同的数据修改方式后，执行完的效果也是一样的。
第一次执行
修改完测试数据之后再次执行
在SSIS中使用Lookup,ConditionalSplit,Multicast等控件实现SCD效果
一旦理解了SCD的实现逻辑，我们完全可以自己通过SSIS中的其它Task来实现SlowlyChangingDimension。
会使用到的Task包括Lookup，Multicast，ConditionalSplit等。
可以参看相应的Task的Demo和一些原理介绍：
微软BI 之SSIS 系列-Lookup组件的使用与它的几种缓存模式-FullCache,PartialCache,NOCache
微软BI 之SSIS 系列-在SSIS中使用MulticastTask将数据源数据同时写入多个目标表，备份数据表，以及写入Audit信息
5.4示例三在SSIS中使用MulticastTask将数据源数据同时写入多个目标表，备份数据表，以及写入Audit信息
新建一个DataFlowTask并且仍然将Customer表作为数据源，拖放一个LookupTask并完成以下配置。
LKP_DimCustomer中ReferenceTable引用集/引用表是DimCustomer。
左边是Customer表，右边是要去LookUp的DimCustomer，Customer.ID=DimCustomer.CustomerAlternateKey关联。
基于Customer.ID=DimCustomer.CustomerAlternateKey就会有两种结果，匹配的输出和不匹配的输出。
不匹配的输出就是添加新数据。
匹配的输出就是要去检查HistoricalAttribute"City"有没有更改，如果有更改就是一次Update然后加上一次Insert操作。
如果ChangingAttribute"Occupatio
升级会员 