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

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

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

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

这个维度是变化的，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表示的是当前是否在用这个备件分类。

       要构建这个缓慢变化维度，我们准备从两个方面入手，一是对于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之后再重新插入，其实也可以做增量，只是觉得增量做起来比较麻烦，而且维度表本身不会很大，所以就先这样了，希望能找到更好的解决办法。