在ETL过程中,对于渐变维度的处理,一直是大家比较关注的问题。关于渐变维度的概念,我们在2007年8月的“渐变维度转换及其实现”一文中有所介绍。本文将在实际应用的对比中,提供三种处理渐变维度的方法,并比较其效率。
第一代:SSIS控件时代
对于一些会在原业务系统中进行修改/添加维度,不需要记录记录历史属性的维度,我们通常会选择SSIS中控件“渐变维度”来完成这项工作。应对这种情况我们选用Type2。
Type2:变化的属性 说明:如果更改后的值应当覆盖现有值,则选择该类型。 解析:既需要更改又不用记录历史属性的类型。 |
选择好数据源,设置好“业务键”和“非键列”之后(如图1),我们再对需要进行更新的“非键列”设置“更改类型”即可,如图2。
图1
图2
之后SSIS会帮我们生成数据流。简单快捷,3分钟完成一张表格,如图3。
图3
第二代:SQL语句时代
但是,对于稍微大一些的数据量,源1万条,目标几万条的时候就会非常的慢。对于这种情况,我们可以采用自己手写渐变维度的方式完成。需要分为两部分——更新原有数据和添加新增数据。如表1、表2所示。
表1
表2
1、更新原有数据
使用控件“执行SQL任务”。直接实行SQL语句:
以下是代码片段: Update Dim_Team set Team_DisplayName = ss. Team_DisplayName , Sort_ID = ss. Sort_ID From Dim_Team dim inner join SS_Team ss On ss. Team_InternalName = dim. Team_InternalName
|
2、添加新增数据
使用控件“数据流任务”。数据源使用如下语句:
以下是代码片段: Select Team_InternalName , Team_DisplayName , Sort_ID from SS_Team Where Team_InternalName not in ( select distinct Team_InternalName from Dim_Team )
|
然后将结果输入到Dim_Team即可。
插曲:有的表格业务建不止一个,并且not in的效率也不够高,所以采用not exists
则使用如下语句作为数据源:
以下是代码片段: Select Team_InternalName , Team_DisplayName , Sort_ID ,a from SS_Team ss Where not exists ( select distinct Team_InternalName
|
第三代:Lookup时代
虽然上述语句可以高效地完成渐变维度Type2的运行,但是在实际运行中,发现由于目标表被用在了数据源表中,导致数据流产生了死锁。
解决方法1:在目标表中去掉表锁。选择“数据访问模式”为“表或试图-快速加载”,将“检查约束”钩选去掉,如图4。
图4
解决方法2:利用lookup的错误输出,如图5。
图5
SS_Dimteam:
以下是代码片段: Select Team_InternalName , Team_DisplayName , Sort_ID from SS_Team
|
Lookup:
以下是代码片段: __select distinct Team_InternalName from Dim_Team
|
设置Lookup错误输出为重新定向,如图6。
图6
输入目标:Dim_Team
至此,完成了渐变维度Type2的“进化”。效率也提高了不少。