SSIS-系列---數(shù)據(jù)倉庫中實現(xiàn)-Slowly-Changing-Dimension-緩慢漸變維度的三種方式

本篇文章總結了實現(xiàn)緩慢漸變維度的幾種方式，并且分析了 Changing Attribute 和 Historical Attribute 輸出的邏輯過程。 測試表以及測試數(shù)據(jù)，其中 Customer 是數(shù)據(jù)源表，DimCustomer 模擬的是數(shù)據(jù)倉庫中的 Customer 維度表。每個示例都是從空表開始，第一次運行的時候 Dimension 表沒有數(shù)據(jù)，第二次運行之前將添加幾條數(shù)據(jù)到 Customer 數(shù)據(jù)源表中，并同時修改若干數(shù)據(jù)。但是要注意這個示例對數(shù)據(jù)源數(shù)據(jù)的加載是全部加載，而不考慮基于數(shù)據(jù)源數(shù)據(jù)的增量加載，關于增量加載的實現(xiàn)會放在 BI 系列的其它文章中講解。USE BIWORK_SSISGOIF OBJECT_ID(Customer) IS NOT NULLDROP TABLE Customer GO IF OBJECT_ID(DimCustomer) IS NOT NULLDROP TABLE DimCustomer GOCREATE TABLE Customer( ID INT PRIMARY KEY IDENTITY(1,1), FullName NVARCHAR(50), City NVARCHAR(50), Occupation NVARCHAR(50)CREATE TABLE DimCustomer ( CustomerID INT PRIMARY KEY IDENTITY(1,1), CustomerAlternateKey INT, FullName NVARCHAR(50), City NVARCHAR(50), Occupation NVARCHAR(50), StartDate DATETIME, EndDate DATETIME, IsCurrent BIT DEFAULT(1) INSERT INTO BIWORK_SSIS.dbo.Customer VALUES(BIWORK,Beijing,IT),(ZhangSan,Shanghai,Education),(Lisi,Guangzhou,Student)示例一 SSIS 中的 Slowly Changing Dimension新建一個 Package 并拖放一個 Data Flow，在 Data Flow 中建立好與 Customer 表的數(shù)據(jù)源連接，新建 Slowly Changing Dimension SCD_DimCustomer。雙擊 SCD_DimCustomer 編輯相關的屬性。Input Columns 來源于上游數(shù)據(jù)源即 Customer 表，Dimension Columns描述 DimCustomer 表信息。Key Type - Business Key 表示 Customer.ID 與 DimCustomer.CustomerAlternateKey 關聯(lián)，后面的數(shù)據(jù)更新或者插入就跟這個 Business Key 相關。其主要邏輯是以 Customer.ID對比 DimCustomer.CustomerAlternateKey ，如果關聯(lián)不到則表示 Customer 中有新數(shù)據(jù)則將新數(shù)據(jù)插入到 DimCustomer 中。如果關聯(lián)到則檢查哪些字段是不需要更新 SCD Type 0，哪些字段的數(shù)據(jù)是需要更新的 SCD Type 1，哪些字段的數(shù)據(jù)需要重新添加一條數(shù)據(jù)以保留歷史信息 SCD Type 2。下一步設計 DimCustomer 表中幾個屬性字段。City - 歷史數(shù)據(jù)，如果 City 發(fā)生更改則添加一條新的數(shù)據(jù)而保留此歷史信息 - Type 2。FullName - 固定的值，此字段的數(shù)據(jù)在數(shù)據(jù)倉庫中不發(fā)生更改 - Type 0。Occupation - 可更改的值，如果 Occupation 發(fā)生更改則只修改它而不保留歷史信息 - Type 1。在這里暫時不設置 - 如果檢測到 Customer 中 FullName 發(fā)生更改就報錯。對于 Type 2 Historical Attribute 的設計是使用有效時間段來表示的，具體的理論概念請參看數(shù)據(jù)倉庫系列 - 緩慢漸變維度 (Slowly Changing Dimension) 常見的三種類型及原型設計其中有詳細的講解。第一個選擇是使用標志字段來表示這個記錄是否到期或者是當前使用的，在我們現(xiàn)在的這個例子中可以先設計為有效期，后面可以修改讓兩種方式都存在。推斷成員的設置，暫時這里不設置推斷成員。推斷成員一般發(fā)生在維度表的數(shù)據(jù)載入落后于Fact事實表的數(shù)據(jù)載入，因此Fact事實表數(shù)據(jù)加載在前因此就引用不到相應的Dimension Key而造成這個問題，這個以后會專門寫一篇文章來討論推斷成員。Slowly Changing Dimension 這個控件此時會產(chǎn)生兩個分支邏輯三組輸出。設置完了之后會自動生成其它的所有邏輯，并且已經(jīng)幫助實現(xiàn)了 SCD 的功能。執(zhí)行之后看看具體的效果 -分析一下 Slowly Changing Dimension 的邏輯。其中 New Output 輸出就是直接插入新的紀錄到 DimCustomer 中。Historical Attribute Insert Output 向下的 OLE DB Command 中 SQL 語句為 -UPDATE dbo.DimCustomer SET EndDate = ? WHERE CustomerAlternateKey = ? AND EndDate IS NULL對于歷史的數(shù)據(jù)應該是修改 EndDate 將這條數(shù)據(jù)表示終止狀態(tài)，并且繼續(xù)添加一條新的數(shù)據(jù)。在這里因為多添加了一個 IsCurrent 來表示記錄的狀態(tài)，因此這條 SQL 語句應該修改為：IsCurrent = 0，這個邏輯需要在 SSIS 中做出細微的調整。UPDATE dbo.DimCustomer SET EndDate = ?, IsCurrent = ? WHERE CustomerAlternateKey = ? AND EndDate IS NULLChanging Attribute Update Output 向下的 OLE DB Command 1 中 SQL 語句為 -UPDATE dbo.DimCustomer SET Occupation = ? WHERE CustomerAlternateKey = ? AND EndDate IS NULL對于 SCD Type 1 的屬性只需要直接更改即可，因此直接根據(jù) Customer.ID 即關聯(lián)到的 DimCustomer.CustomerAlternateKey 修改相應的屬性。對于 Historical Attribute Insert Output 下的 Derived Column 和 OLE DB Command 中作出的修改：Derived Column 新增加一個 HistoricalCurrent ，其值為0，用來表示當條記錄為歷史記錄。修改 SQL 語句修改 Column Mapping對源數(shù)據(jù)做出一定的修改：- 新插入一條INSERT INTO BIWORK_SSIS.dbo.Customer VALUES(Wangwu,Beijing,Finance)- 修改 Changing Attribute UPDATE BIWORK_SSIS.dbo.CustomerSET Occupation = ITWHERE ID = 3 - 同時修改 Changing Attribute 和 Historical Attribute UPDATE BIWORK_SSIS.dbo.CustomerSET Occupation = Publisher, City = HangzhouWHERE ID = 2再次執(zhí)行 SSIS Package 并查詢數(shù)據(jù)庫結果 -新增的一條數(shù)據(jù)是 Wangwu ，因此將直接添加新的一條記錄到 DimCustomer 中。ZhangSan 因為修改了 City ，因此屬于 Type 2 SCD 需要保留歷史數(shù)據(jù)。所以先修改 ZhangSan 的 EndDate 和 IsCurrent 保留這條歷史數(shù)據(jù)，然后再將最新的數(shù)據(jù)添加到 DimCustomer 中，也就是最后看到的 ZhangSan - Hangzhou - PublisherLisi 因為修改了 Occupation 屬于 Type 1 SCD 只需要修改原數(shù)據(jù)即可，所以 Lisi 的 Occupation 直接更新為 IT 即可。下面是對 SCD Type 1 和 Type 2 實現(xiàn)邏輯的總結，如果理解了這些邏輯我們也完全可以用其它的 SSIS 控件來實現(xiàn) SCD 的功能。Type 2 SCD 要比 Type 1 要復雜一些，它有一個 Update 之后的 Insert 操作。示例二 - 使用 SQL 中 MERGE 語句實現(xiàn) SCD Type 1 和 SCD Type 2 的功能SQL MERGE 語句非常實用，可以非常簡單的根據(jù)一些關聯(lián)條件來比較兩個表的數(shù)據(jù)，然后決定匹配的邏輯如何執(zhí)行和不匹配的時候邏輯如何處理。關于 SQL MERGE 的語法和使用請參照SQL Server - 使用 Merge 語句實現(xiàn)表數(shù)據(jù)之間的對比同步使用 MERGE 語句來實現(xiàn)上面的效果- Type 2 SCDMERGE INTO dbo.DimCustomer AS DimUSING dbo.Customer AS Src ON Dim.CustomerAlternateKey = Src.IDWHEN NOT MATCHED BY TARGET THEN INSERT VALUES(Src.ID,Src.FullName,Src.City,Src.Occupation,GETDATE(),NULL,1)WHEN MATCHED AND Dim.City Src.City THEN UPDATE SET Dim.EndDate = GETDATE(),Dim.IsCurrent = 0 ;- Type 1 SCDMERGE INTO dbo.DimCustomer AS DimUSING dbo.Customer AS Src ON Dim.CustomerAlternateKey = Src.ID AND Dim.IsCurrent = 1WHEN NOT MATCHED BY TARGET THEN INSERT VALUES(Src.ID,Src.FullName,Src.City,Src.Occupation,GETDATE(),NULL,1)WHEN MATCHED AND Dim.Occupation Src.Occupation THEN UPDATE SET Dim.Occupation = Src.Occupation ;因為在 MERGE 語句中有一些語法限制 在 Merge Matched 操作中，只能允許執(zhí)行 UPDATE 或者 DELETE 語句。 在 Merge Not Matched 操作中，只允許執(zhí)行 INSERT 語句。 一個 Merge 語句中出現(xiàn)的 Matched 操作，只能出現(xiàn)一次 UPDATE 或者 DELETE 語句，否則就會出現(xiàn)下面的錯誤 -An action of type WHEN MATCHED cannot appear more than once in a UPDATE clause of a MERGE statement. Merge 語句最后必須包含分號，以 ; 結束。 所以在這里采取的方式是：Type 2 SCD 注釋的地方 - 根據(jù) Customer.ID = DimCustomer.CustomerAlternateKey 關聯(lián)如果沒有找到匹配的記錄，就意味是新數(shù)據(jù)，直接插入到 DimCustomer 表中。如果匹配到了即此數(shù)據(jù)在維度表中也存在，因此先將此記錄更新完畢標志此條記錄為歷史記錄 - EndDate 和 IsCurrent 都設置了值表示 SCD Type 2。Type 1 SCD 注釋的地方 - 因為剛才的歷史記錄已經(jīng)被標識為 IsCurrent = 0， 因此在此時的邏輯將匹配不到數(shù)據(jù)，因此作為新數(shù)據(jù)插入，這樣就延續(xù)了 SCD Type 2 Update 之后的 Insert 操作。對于匹配到的數(shù)據(jù)，再來比較 SCD Type 1 的列，如果不匹配的話那么就直接更新掉就可以了。和示例一使用相同的測試數(shù)據(jù)和相同的數(shù)據(jù)修改方式后，執(zhí)行完的效果也是一樣的。第一次執(zhí)行修改完測試數(shù)據(jù)之后再次執(zhí)行在 SSIS 中使用 Lookup, Conditional Split, Multicast 等控件實現(xiàn) SCD 效果一旦理解了 SCD 的實現(xiàn)邏輯，我們完全可以自己通過 SSIS 中的其它 Task 來實現(xiàn) Slowly Changing Dimension。會使用到的 Task 包括 Lookup，Multicast，Conditional Split 等?？梢詤⒖聪鄳?Task 的Demo 和一些原理介紹：SSIS 系列 - Lookup 組件的使用與它的幾種緩存模式 - Full Cache, Partial Cache, NO CacheSSIS 系列 - 在 SSIS 中使用 Multicast Task 將數(shù)據(jù)源數(shù)據(jù)同時寫入多個目標表，備份數(shù)據(jù)表，以及寫入Audit 信息新建一個 Data Flow Task 并且仍然將 Customer 表作為數(shù)據(jù)源，拖放一個 Lookup Task 并完成以下配置。LKP_DimCustomer 中 Reference Table 引用集/引用表是 DimCustomer。左邊是Customer表，右邊是要去 Look Up 的 DimCustomer，Customer.ID = DimCustomer.CustomerAlternateKey 關聯(lián)?；?Customer.ID = DimCustomer.CustomerAlternateKey 就會有兩種結果，匹配的輸出和不匹配的輸出。不匹配的輸出就是添加新數(shù)據(jù)。匹配的輸出就是要去檢查 Historical Attribute City 有沒有更改，如果有更改就是一次 Update 然后加上一次 Insert 操作。如果 Changing Attribute Occupation 有更改就是一次 Update 操作。中間會使用到的三個狀態(tài) - StartDate , EndDate, IsCurrent 都會在整個流程中使用到，主要用來更新它們的狀態(tài)。先實現(xiàn)不匹配的邏輯，即先添加一條新的數(shù)據(jù)。DC_NewInsertStartDate 需要準備 StartDate 和 IsCurrent = 1OLE_DST_DimCustomer 的配置Customer.ID = DimCustomer.CustomerAlternateKey 匹配的情況下有兩種情況：City 不匹配 和 Occupation 不匹配，添加一個 Conditional Split 并連接到 Lookup 的匹配輸出上。下面是全部的實現(xiàn)效果 - Changing Update 下的邏輯是直接修改 DimCustomer 的數(shù)據(jù)，OLE_CMD_Update 中UPDATE dbo.DimCustomer SET Occupation = ? WHERE CustomerAlternateKey = ? AND EndDate IS NULLHistorical_Update 下使用了一個 Multicast 將數(shù)據(jù)流分為兩個分支，因為它是 Historical Attribute Update，因此邏輯是更新原歷史數(shù)據(jù)，添加新數(shù)據(jù)。OLE_CMD_UpdateHistorical 中的 SQL 語句，這里的 IsCurrent 將最終更新為 0 。UPDATE dbo.DimCustomer SET EndDate = ? ,IsCurrent = ? WHERE CustomerAlternateKey = ? AND EndDate IS NULL使用前兩個示例中的測試數(shù)據(jù)，第一次執(zhí)行完 SSIS Package 之后三條數(shù)據(jù)走向了 Lookup No Match Output 表示新數(shù)據(jù)。查詢數(shù)據(jù)表結果修改完測試數(shù)據(jù)之后再次執(zhí)行，數(shù)據(jù)源 1 條是新數(shù)據(jù)走向 Lookup No Match Outpu