[分享]微软BI专题-渐变维度转换及其实现

 在SQL Server 2005中，BI（Business Intelligence，商业智能）模块功能得以大大的增强。其中有一个重要的模块，被称为SQL Server Integrated Services（SSIS），即SQL Server数据库集成服务，其主要作用是对业务数据库或OLTP数据库中的数据进行抽取、转换和加载（Extract-Transform- Load，ETL）到数据仓库（Data Warehouse），以便针对需求对业务数据进行下一步的数据仓库建模和数据分析等工作。

       渐变维度转换（Slowly Changing Dimension）是SSIS集成服务中的一个重要组件，本文主要探讨实际项目的实施过程中遇到的渐变维度转换组件效率低下的问题并提出一种解决方法。

 

      渐变维度转换简介

      首先介绍一下维度的概念。维度是一种层次关系，对应于客观世界，也就是人们观察事物的角度。在数据仓库中，一个实体的多个属性定义为该实体的维度，使得用户能对不同维度上的数据进行比较。

维度大致的分为三类：稳定维度、渐变维度、快变维度。在SQL Server 2005的SSIS中，提供了对渐变维度的转换处理。

      从较长的一个时间段来看，在数据仓库中，维度表经常会随时间而发生一些改变，为了适应这种需求，也就必须为每个维度属性指定处理变化的策略。SSIS中的渐变维度转换组件的主要功能就是协调数据仓库中维度表中的记录更新和插入，如图1。

在SSIS中，渐变维度转换提供的主要功能有：

• 将传入行与查找表中的行匹配，以标识新行和现有行。
• 在不允许更改时，标识含有更改的传入行。
• 标识需要更新的推断成员记录。
• 标识包含需要插入新记录和更新过期记录的历史更改的传入行。
• 检测包含需要更新现有记录（包括过期记录）的更改的传入行。

常见的使用情况有以下三类：

1）   改写维度记录的属性值：其特点是容易实现，比较方便，但不保存属性变化历史，故不能对旧属性的历史数据进行任何的维护。

2）   增量式添加维度记录行：其特点是容易区分事实表的历史，准确跟踪维度属性变化，但若针对的是海量数据，同时全部是变化的历史属性数据转换，将产生大量的历史数据，使得维度表数据量剧增。

3）   添加指定的维度列：可以方便地按需求建立新的属性关系，若中间关联的属性过多可能会急剧的影响性能。

在实际项目工作中，增量式添加维度记录行最为常用。

 

      增量式添加维度记录行——维度渐变情况分析

      假定从一张表（源表）向另一张表（目标表）转换数据，实现渐变维度转换常用的增量式添加维度记录功能。源表和目标表的初始情况如图2。

 

图2目标表列与源表列对应关系如表1：

目标表列	aa1	bb1	cc1	ee1
源表列	aa	bb	cc	ee

表 1：目标表列与源表列对应关系

 

主要考虑下面几种维度渐变情况：

1）  通过与目标表逐行对比，若源表中存在目标表中没有的记录，则该数据行应属于新增加的记录，此情况下将相应的新增记录插入到目标表，并设置开始时间为执行时所设置的时间，结束时间为数据有效时间。如图3。

 

2）  若目标表中存在源表中没有的记录，即源表中相应的记录已经被删除，此情况下对目标表中的这些数据行设置结束时间为执行时所设置的时间。对所有记录而言，只要不是数据有效时间，都表示所标记的记录失效。如图4。

 

3）  若源表中的记录在导入到目标表之后又经过修改，则再次执行导入时将该记录作为新行插入到目标表中，并置开始时间为执行时所设置的时间，结束时间为最大有效时间，同时设置目标表中原来的记录的结束时间为当前所设置的时间，表示原记录已失效。如图5。

 

      渐变维度转换在实际项目ETL中存在的问题

      在SQL Server 2005中，对增量式添加维度记录并保存历史记录的操作方式在目前可以使用渐变维度转换，虽然与渐变维度转换本意不合（缓慢变化的维度，即变化的数据量应该不大），但针对具体的SSIS组件而言，并没有十分理想的办法。

      通过具体项目的实施表明，在数据更新量达到十万级时，SSIS的渐变维度转换组件效率已经显著下降，更新量达到百万级时，使用渐变维度转换对于ETL将变得异常困难。

      在现有组件不能满足项目ETL需求时，常常需要编写一些SQL语句，如定义用户函数或存储过程来满足项目ETL的顺利进行。

 

      效率上的对比

      为了便于比较，做了一个试验：SQL Server 2005的渐变维度转换组件和T-SQL存储过程均针对同一源表和目标表进行操作。

      源表结构见图1，扩充记录到163840行，使用SQL Server 2005渐变维度转换并按“增量式添加维度记录行”进行组件配置，完全转换运行时间为18分22秒。直接使用存储过程进行“增量式添加维度记录行”，完全转换运行时间为9秒。

      继续扩充源表到786432行记录，耐心等待了将近一个小时，导入了开始几万行记录之后SSIS包中的记录增加的相当的慢。同样的条件，使用存储过程转换则只需要20秒左右。

 

      总结

      从以上试验可以看出，渐变维度转换组件的使用是有条件的，当需要转换的数据量达到一定的数量级（比如一百万条记录以上），转换的效率将大大降低，只有在源表存在少量的历史数据增量更新时使用渐变维度转换组件才会显出它的方便性和稳定性。

       针对渐变维度转换组件效率低的情况，也可以采用搭配更高的硬件设备来提高转换的效率，比如使用大内存、多CPU、高I/O吞吐量、高网络带宽的服务器。但这种方法显然不能从根本上解决问题，而且需要增加投资，对特定条件下项目的实施是不利的。

       在实际项目中，考虑到渐变维度转换组件的便利性和稳定性，可以编写具有相同功能的SQL用户函数或者存储过程与转换组件结合使用。比如在项目数据转换初 期，大量的历史数据插入使用渐变维度组件显然是不利的，此时可以使用效率更高的SQL语句转换数据，在项目中期对维度数据进行修改和更新时，定期以作业的 形式使用组件转换数据。