對于緩慢變化維的曆史資料的處理

在做資料倉庫的時候有一個備件次元，該次元由兩張表構成。

一張是R5PARTS這張表記錄的是備件次元的目前狀況。

R5PARTS
備件編碼	備件分類	建立時間	修改時間	修改次數
HCSD0370	STOP	2011/11/12 00:00:00

另外一張表記錄的是備件所屬分類的變化情況，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
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之後再重新插入，其實也可以做增量，隻是覺得增量做起來比較麻煩，而且次元表本身不會很大，是以就先這樣了，希望能找到更好的解決辦法。