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

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