DataStage系列教程 (Slowly Changing Dimension)緩慢變化維

weixin_30924079發表於2020-04-04

BI中維表的增量更新一般有2種:

  • Type 1:覆蓋更改。記錄的列值發生變化,直接update成最新記錄。
  • Type 2:歷史跟蹤更改。記錄值發生變化,將該記錄置為失效,再insert一條新的記錄。

這兩種其實都可以通過sql的left join來實現,不過DataStage給我們提供一個元件,可以很好的實現這個功能,這就是slowly changing dimension。

1 緩慢變化維表示例

如圖1所示,是一個常用的緩慢變化維,該表的進數邏輯為:

當記錄新插入到改表時,STARTDATE是當前時間,ENDDATE為未來的某個時間或者為空,NOWSTATE為1;當業務資料反生修改時,維表中新增一條記錄(與第一次插入時的邏輯一樣),同時維表中業務主鍵(即CODE)相同的那條記錄ENDDATE置為當前時間,NOWSTATE置為0。

image

圖1 常用緩慢變化維示例

表DDL及說明如下:

CREATE TABLE D_EMPOLYEE
    (
        ID INTEGER PRIMARY KEY, --物理主鍵,代理鍵,遞增,唯一
        CODE VARCHAR(20), --業務主鍵
        NAME VARCHAR(20),  --姓名
        PLACE VARCHAR(20),  --地址
        STARTDATE DATE,  --記錄有效起始日期
        ENDDATE DATE default '9999-12-31',  --記錄有效結束日期
        NOWSTATE CHAR(1)  --記錄有效狀態。1:有效;0:失效
    );

有同學可能想,為什麼要多一個ID列的代理鍵。這種做法還是有一些好處的:

  • 節省空間:事實表只需要存維表的代理鍵,整型,一般都比邏輯鍵佔用的空間少。
  • 關聯快:數值,一般來說,關聯起來要比一大串字元快一些。
  • 唯一確定記錄:直接用該列關聯就可以,不用再加狀態或其他條件去唯一確定一條記錄。

2 JOB示例

如圖2,我們做出來的JOB最終是這個樣子。

 

image

圖2 JOB示例

大致分為以下幾個部分:

  • 業務資料:資料來源。可以是業務庫中表,也可以BI庫中同步來的ODS表等。其中包含了我們維度表的基本資訊。
  • BI庫維度表(比對):BI庫維度表,也是我們最終維度資料儲存表。業務資料中包含的維度資訊與它進行比較,確定記錄是沒有變化、修改了抑或是新增的。
  • slowly changing dimension stage:比較業務資料與維度表中資料。
  • BI維度表(目標):比較的結果最終反應在該表。與【BI維度表(比對)】是同一個表。
  • 選擇性輸出列:可以選擇性的輸出來自業務資料、BI庫維度表(比對)、slowly changing dimension stage的部分列或所有列的資料。如果沒有地方用到,可以不要該連結。

3 實現步驟

3.1 業務資料來源

實際專案中,這些資料最初來源於業務系統資料。現在我們用一個檔案來模擬。檔案內容如下:

# cat fact.txt 
code,name,place,salary
e_01,xie,CS,10
e_03,hei,BJ,9

3.2 BI庫維表(比對)

從BI庫讀取維度資料,用於比對。該示例中表結構前文已給出。

3.3 BI庫維表(目標)

和比對的維表是同一個表。需要注意2點:

  • 設定主鍵。物理主鍵。該示例為ID
  • Write mode 設定為 Update then insert

3.4 Slowly Changing Dimension

雙擊開啟元件,在最下邊有一個【快速路徑】,如圖3。一共5步,我們一步一步設定好就可以了。

注意:如果該元件只有一個輸出連結的話,步驟將簡化為3步。這裡我們來講一下5步的如何配置,3步的都包括在內了。

imageimage

圖3 快速路徑

 

3.4.1 選擇輸出連結

該連結輸出後續步驟中選擇的需要輸出的一些列。3步的為空就行,5步的選擇非維表的那個。

imageimage

圖4 步驟1(選擇輸出連結)

3.4.2 查詢鍵以及列用途設定

把業務鍵從左側拖動到右側鍵表示式對應位置,完成查詢鍵設定。

列用途常用的分為以下幾種:

  • 代理鍵(Surrogate Key)— 此列是維度表的主鍵,其中填充代理鍵的值。
  • 業務鍵(Business Key)— 此列是維度表所表示的業務物件的識別符號,但它不是維度表的主鍵。此列通常用作查詢列,並對應於源資料的某個鍵或其他一些欄位,用於標識相關業務物件。查詢的作用是找出與源資料行對應的維度錶行。
  • Type 2— 選中此列表示值的更改。如果值發生了變化,則對維度表執行歷史跟蹤更改。
  • Type 1— 選中此列表示值的更改。如果值發生了變化,則對維度表執行覆蓋更改。
  • Current Indicator— 此列的作用相當於一個標記,它指示特定業務鍵的記錄是最新的。
  • Effective Date— 此列用於指定指定某記錄在何時成為了最新的記錄,也就是成為活動(active)記錄。
  • Expiration Date— 此列用於指定記錄處於活動狀態的結束日期。對於當前活動的記錄,該值通常是某個未來的日期,或者為 NULL。
  • SK Chain— 此列用於儲存特定業務鍵歷史中的上一條或下一條代理鍵。
  • (blank) — 此列在 SCD 處理中沒有任何作用。在插入新行時,此欄位中的資料將被插入到表中,但不會檢查此列中的源資料的更改。

image

圖5 步驟2(查詢鍵以及列用途設定)

該例中,id為代理鍵;code為業務主鍵;name採用Type1更新策略,直接修改;place採用Type2歷史跟蹤策略,如果修改,則插入新記錄。

3.4.3 代理鍵設定

這裡提供了2種方式來生成代理鍵,分別為檔案和資料庫。這裡只說一種檔案的方式,資料庫怎麼實現,您可以自己實驗一下。以後如果寫代理鍵元件的文章,將會詳細描述。

  • 輸入名稱:選擇查詢引用的連結。即BI庫維表。
  • 源型別:Flat File
  • 源名稱:Datastage伺服器上的一個檔案,需要事先建立。
  • 初始值:鍵值的起始值。
#生成鍵檔案
touch /DS/Files/keyfile.dat 

#如果JOB執行時提示許可權問題,則修改檔案許可權。圖省事,就777了。
chmod 777  /DS/Files/keyfile.dat

 

image

圖6 步驟3(代理鍵設定)

 
3.4.4 維更新設定

此處的資料流出到BI庫維表。業務資料中需要用到的列對應對映到輸出。接下來就是幾個特殊列的派生值。

  • 代理鍵:在代理鍵所在行雙擊與派生值交叉的單元格,會自動填入一個函式:NextSurrogateKey();
  • 生效日期:設定為當前日期。雙擊單元格,手動填入函式:CurrentDate();
  • 截止日期:設定為未來的某個日期。這裡填入'9999-12-31';
  • 當前指示符:有效記錄的標誌。這裡填入1;

image

圖7 步驟4(維更新設定)

還需要設定記錄失效時,截止日期和當前指示符的值,這裡分別填入CurrentDate()和0。

3.4.5 輸出對映設定

3步的沒有這個步驟。

把需要的列從左邊對映到右邊輸出就好了。

image

圖8 步驟5(輸出對映設定)

 

4 測試

接下就可以測試該JOB是否按照我們的想法生成資料了。主要應該包括以下情況:

  • 新增記錄。也就是BI庫維度表原來沒有的記錄,是不是正確的插入了該記錄,起止日期、當前狀態標誌是否正確;
  • 更新記錄。也就是BI庫維度表已有的記錄,設定了Type1、Type2的欄位,資料來源發生變化時,是否按正確的策略更新了。

提供一些測試資料:

# cat fact.txt 
code,name,place,salary
e_01,xie,CS,10
e_03,hei,BJ,9

5 附JOB匯出檔案下載

點選下載:http://files.cnblogs.com/files/BlueBreeze/slowly_changing_dimension.rar

轉載於:https://www.cnblogs.com/BlueBreeze/p/datastage_series-Slowly_Changing_Dimension.html

相關文章