SCD缓慢渐变维度.docx
《SCD缓慢渐变维度.docx》由会员分享,可在线阅读,更多相关《SCD缓慢渐变维度.docx(24页珍藏版)》请在冰豆网上搜索。
SCD缓慢渐变维度
SCD缓慢渐变维度
SCD常见的三种类型
(SlowlyChangingDimension)
在从 OLTP 业务数据库向 DW 数据仓库抽取数据的过程中,特别是第一次导入之后的每一次增量抽取往往会遇到这样的问题:
业务数据库中的一些数据发生了更改,到底要不要将这些变化也反映到数据仓库中?
在数据仓库中,哪些数据应该随之变化,哪些可以不用变化?
考虑到这些变化,在数据仓库中的维度表又应该如何设计以满足这些需要。
很显然在业务数据库中数据的变化是非常自然和正常的,比如顾客的联系方式,手机号码等信息可能随着顾客的所在地的更改发生变化,比如商品的价格在不同时期有上涨和下降的变化。
那么在业务数据库中,很自然的就会修改并马上反映到实际业务当中去。
但是在数据仓库中,其数据主要的特征一是静态历史数据,二是少改变不删除,三是定期增长,其作用主要用来数据分析。
因此分析的过程中对历史数据就提出了要求,有一些数据是需要能够反映出在周期内的变化历史,有一些数据缺不需要,那么这些数据应该如何来控制。
为了解决类似于这样的问题需要了解数据仓库中的一个非常重要的概念 - 缓慢渐变维度。
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。
Full