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。
圖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最終是這個樣子。
圖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步的都包括在內了。
圖3 快速路徑
3.4.1 選擇輸出連結
該連結輸出後續步驟中選擇的需要輸出的一些列。3步的為空就行,5步的選擇非維表的那個。
圖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 處理中沒有任何作用。在插入新行時,此欄位中的資料將被插入到表中,但不會檢查此列中的源資料的更改。
圖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
圖6 步驟3(代理鍵設定)
3.4.4 維更新設定
此處的資料流出到BI庫維表。業務資料中需要用到的列對應對映到輸出。接下來就是幾個特殊列的派生值。
- 代理鍵:在代理鍵所在行雙擊與派生值交叉的單元格,會自動填入一個函式:NextSurrogateKey();
- 生效日期:設定為當前日期。雙擊單元格,手動填入函式:CurrentDate();
- 截止日期:設定為未來的某個日期。這裡填入'9999-12-31';
- 當前指示符:有效記錄的標誌。這裡填入1;
圖7 步驟4(維更新設定)
還需要設定記錄失效時,截止日期和當前指示符的值,這裡分別填入CurrentDate()和0。
3.4.5 輸出對映設定
3步的沒有這個步驟。
把需要的列從左邊對映到右邊輸出就好了。
圖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