对于缓慢变化维的历史数据的处理

在做数据仓库的时候有一个备件维度，该维度由两张表构成。

一张是R5PARTS这张表记录的是备件维度的当前状况。

R5PARTS
备件编码	备件分类	创建时间	修改时间	修改次数
HCSD0370	STOP	2011/11/12 00:00:00		0

另外一张表记录的是备件所属分类的变化情况，R5AUDVALUES：

R5AUDVALUES
备件编码	变化之前的备件分类	变化之后的备件分类	变化日期
HCSD0370	JD	STOP	2013/11/11 00：00：00

这个维度是变化的，R5PARTS记录维度的当前状况，开始我们项目组没有考虑到维度变化的情况，直接用R5PARTS这张表来构建维度。后来发现维度会存在变化的情况，只用维度的当前情况不能完全反映维度和事实表的真实情况，比如在2013年公司发生了一笔采购，采购了HCSD0370这个备件，在当时这个备件的分类可能是属于机电（JD），后来这个物资被停用了，它现在物资分类是停用物资了（分类代码：STOP）。这时候（2014年）我们如果用现在的维度情况去看，就是在2013年公司采购了分类为停用物资（stop）的备件HCSD0370。而停用物资一般是不会去采购的，只会从现有的库存里面发放和消耗。这时就需要结合维度的变化情况来构建一个变化维。

        根据我们从网上收集的资料，和用户沟通之后，决定采用处理缓慢变化维的TYPE2（保留所有的维度变化情况）。

        我们用的ETL工具是informatica powercenter，在powercenter中有缓慢变化维抽取的wizard，但是我试了之后发现不能很好地满足我们的需求（不过思想是典型的缓慢变化维的处理方式，只是太过死板，每个项目的要求不一样），最终决定分成两个部分来做。

        我们根据缓慢变化维的TYPE2处理方法，重新设计了该备件维度的维度表：

SCD_DIM_PART

ORD	MATERIAL_CODE	START_DATE	END_DATE	IN_USE
1	HCSD0370	2011/11/11	2013/11/11	0
2	HCSD0370	2013/11/11	9999/1/1	1

       ORD表示维度的序号，MATERIAL_CODE是备件编码，START_DATE是这个备件编码和分类使用的开始日期，END_DATE是这个分类使用的终止日期，IN_USE表示的是当前是否在用这个备件分类。

       要构建这个缓慢变化维度，我们准备从两个方面入手，一是对于R5PARTS中那些没有被修改过的分类，直接抽取到SCD_DIM_PART。

                 SELECT 
                 1 AS ORD,
                 备件编码 AS MATERIAL_CODE，
                 创建时间 AS START_DATE，
                 TO_DATE('9999/01/01','YYYY/MM/DD') AS END_DATE，
                 1 as IN_USE
                  FROM R5PARTS 

                 WHERE UPDATECOUNT=0

        对于那些有分类变化的数据，我们直接根据变化表R5AUDVALUES这张表来构建。

        首先我们从R5AUDVALUES这张表找出所有有变化的备件编码，然后对每一个备件编码，分别查出它根据时间的排序，然后将这些数据插入到SCD_DIM_PART。

      对备件分类变化的处理SQL：

  

FOR MATERIAL_CODE IN (SELECT DISTINCT AUD.AVA_PRIMARYID FROM R5AUDVALUES AUD)--从R5AUDVALUES把存在变化的不重复的备件编码取出来,然后依次开始循环
LOOP--从备件编码的集合开始循环
 INSERT INTO SCD_DIM_MATERIAL--把循环中的每一个编码相关的数据插入到SCD_DIM_MATERIAL表中
with T as
 (SELECT RANK() OVER(ORDER BY AUDV.AVA_CHANGED) IN_ORDER,
         AUDV.AVA_PRIMARYID MATERIAL_CODE,
         AUDV.AVA_FROM CHANGED_FROM,
         AUDV.AVA_TO CHANGED_TO,
         AUDV.AVA_CHANGED CHANGED_DATE
    FROM R5AUDVALUES AUDV
   WHERE audv.ava_primaryid = MATERIAL_CODE.AVA_PRIMARYID),--这个是为了每次循环的时候动态地根据备件编码取出R5AUDVALUES的变化数据
  T1 as (SELECT MAX(T.IN_ORDER) AS MAX_ORD FROM T)--取出该备件的最大变化行数
SELECT T.IN_ORDER,
       T.MATERIAL_CODE,
       T.CHANGED_FROM CLASS_CODE,
       (case
         when T.IN_ORDER = '1' THEN--如果是第一行，说明该变化的START_DATE要到R5PARTS表去找
          (SELECT to_date(PAR.PAR_CREATED,'mm/dd/yyyy hh24:mi:ss')
             FROM R5PARTS PAR
            WHERE PAR.PAR_CODE = T.MATERIAL_CODE
              AND ROWNUM < 2)
         ELSE--不是第一行的时候就取上一行的变化时间
          (SELECT to_date(TT.CHANGED_DATE,'mm/dd/yyyy hh24:mi:ss')
             FROM T TT
            WHERE TT.IN_ORDER = T.IN_ORDER - 1)
       END) START_DATE,
       to_date(T.CHANGED_DATE,'mm/dd/yyyy hh24:mi:ss') AS END_DATE,--状态的截止时间就是这一行的变化时间
       0 as in_use--这个状态现在不在使用中
  FROM T
  union all--上句SQL其实是把所有旧状态取出来，这句SQL是为了取出最新的备件分类
   select 
   t.in_order+1,--比原来的最大行号大一
   t.material_code,
   t.changed_to,--这里取的是R5AUDVALUES.AVA_CHANGEDTO表示有分类变化的备件的最新状态
   to_date(t.changed_date,'mm/dd/yyyy hh24:mi:ss') as start_date,--取这一行的变化时间作为最新分类的开始时间
   to_date('9999/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss') as end_date,--最新状态的停止时间设置为最大
   1 as in_use --1表示这个状态是当前状态
   from t 
   where t.in_order= (select T1.MAX_ORD from T1);--取编号最大的那行，若只有1也就取1
END LOOP;

        

           在构建好了维度表之后，事实表的数据需要做相应的修改（在ETL的时候根据时间判断该用哪个维度字段，用备件编码和序号做联合主键），之后就可以做OLAP和BI展现了。

          这样做的一个比较明显的缺点是每次都需要把表truncate之后再重新插入，其实也可以做增量，只是觉得增量做起来比较麻烦，而且维度表本身不会很大，所以就先这样了，希望能找到更好的解决办法。