SSIS進階轉換任務—漸變次元（Slowly Changing Dimension）

漸變次元（SCD）轉換提供了一種維護漸變次元或漸變表的方法，漸變次元的分析處理是一個很大的話題。一般一個次元表通常包含一些描述其他資訊的離散值，例如，價格，重量，銷售地區。問題是如果随着時間的推移這些資訊改變我們将如何維護這個表，特别是在資料轉移的過程中。例如我們檢視AdventureWorks中的product表，如圖1

圖1

　　

通常在聯機事務處理（OLTP）資料庫中，我們會用一行的資料來儲存産品資訊。如果産品的售價從10美元上漲到15美元，我們直接更新StandardCost字段為15，這樣做完成了任務：現在的售價是多少？但是丢失了曆史資料，我們不知道漲價之前的價格是多少？價格上漲了多少？解決這個問題有三種基本的選擇，具體選擇哪一種要基于這個表的我們關注點和想要得到什麼樣的結果，我們将這些基本的選擇簡單地命名為類型1，類型2，類型3

• 類型1 ：重寫曆史 —通過使用新的産品價格來替換掉目前的産品價格，當然這樣會丢失曆史值。這個是最簡單的做法。
• 類型2 ：保留曆史 —添加一行新的完整的資料，保留曆史資料行，當然這樣會有一些副作用，這樣做使得這個表更加複雜，占用空間更大。
• 類型3 ：僅儲存上一次曆史 —添加額外的屬性，僅儲存上一次曆史值，這樣我們隻能看到産品價格變化中的兩次價格。這種方法不常用。

注意：類型3不能直接實作，需要借助其他的task才能實作

和其他task不同，漸變次元轉換提供一個向導設定，通過問答的方式來設定，類似于在計算機中安裝一個新的程式時使用的安裝向導。進階設定提供更多的選擇，但是在我們完全掌握它之前最好還是使用向導設定。輕按兩下次元轉換标簽或者右擊選擇編輯都可以打開向導。設定好之後将會産生一些更新和插入task來完成更新或插入的任務。圖2顯示的設定好之後的界面。可以看到這個task和其他的不同，它是由多個task組合而成的。

圖2

漸變次元是一個自動化的task，概括地說漸變次元接收輸入資料和一個次元表資料，通過配置資訊産生至少兩個輸出，并且這些輸出總共有6種類型，最後使用OLE DB指令來完成資料庫的更新，這6種可能的輸出資料流如下：

• 修改屬性更新輸出：這種輸出屬于類型1。在SCD中被選中作為可變項的屬性都會和輸入資料源做比較。如果比對主鍵的兩行屬性值有差異，輸入資料将會從這個輸出流輸出。
• 曆史屬性插入輸出：這種屬性屬于類型2。在SCD中被選中作為可變項的屬性都會和輸入資料源做比較。如果比對主鍵的兩行屬性值有差異，輸入資料将會從這個輸出流輸出。
• 固定輸出：在SCD中被選作固定的屬性都會和輸入的資料源做比較。如果比對主鍵的兩行屬性值有差異，輸入資料将會從這個輸出流輸出。這種輸出流适用于類型3，但是必須自己編寫sql語句。
• 推斷成員更新：在SCD的設定選中推斷成員的時候，将會有這個輸出流。如果需要将次元表轉到另外一個表中需要設定使用這個輸出流。這個輸出可能在後面的操作中添加到次元表中。
• 新輸出：這個和曆史輸出一樣都要合并到目标表中。
• 不變的輸出：預設沒有這個輸出。如果SCD發現輸入資料和次元表中的資料沒有差異，不會有任何操作。如果感興趣的話你可以為這個輸出建一個目标表，檢視其中的資料。

說了這麼一大堆，我們可以看看這些輸出到底在什麼地方，如上圖2，左邊第一個輸出是修改屬性更新輸出，中間那根線是新輸出，右邊那根線是曆史屬性插入輸出。

在我們這裡我們拿比較熟悉的員工工資這個例子來做一個示例。每個工資周期都會有一些費用被扣掉例如，個人所得稅，保險稅，醫療保險等等。為了模拟這些過程我們可能會建立一個表PayrollDeductItem類似于圖3。PayrollEventFact參照PayrollDeductItem來更新。

圖3

現在假設一年的時間過去了，每個人的工資情況都會有些變動。我們的任務是來更新PayrollDeductItem這個表。我們發現有些工資項減少了，有工資項增加了，如果有些工資項删除了，則記為0。可以在啟用新的工資标準之前執行一次更新，但是如果僅僅跟新這樣會丢失工資走向資訊。在這種情形下我們使用漸變緯度來解決這個問題，參照下面的步驟。

1. 首先為了跟蹤曆史變更我們要建立一個表添加一些資料列。即使隻有其中的一部分會在執行這個任務的時候用得到，還是要添加所有可能會用到的資料列。使用下面的sql語句建立一個表并添加資料：

   Use AdventureWorks
   GO
   CREATE TABLE [dbo].[PayrollDeductItem](
   [PayrollDeductItemID] [int] IDENTITY(1,1) NOT NULL,
   [EmployeePlanIDNbr] [varchar](50) NULL,
   [DeductionAmount] [money] NULL,
   [EmployeeID] [int] NULL,
   
   [PayDeductType] [char](10) NULL,
   [HistTextStatus] [char](10) NULL
   CONSTRAINT [DF_PDI_HistTxtStatus] DEFAULT ('CURRENT'),
   [HistBitStatus] [bit] NULL
   CONSTRAINT [DF_PDI_HistBitStatus] DEFAULT ('TRUE'),
   [HistStartDate] [smalldatetime] NULL,
   [HistEndDate] [smalldatetime] NULL,
   CONSTRAINT [PK_POLICY] PRIMARY KEY CLUSTERED
   (
   [PayrollDeductItemID] ASC
   ) ON [PRIMARY]
   ) ON [PRIMARY]
   GO
   INSERT INTO PAYROLLDEDUCTITEM(EmployeePlanIDNbr, DEDUCTIONAMOUNT, EMPLOYEEID,
         PAYDEDUCTTYPE, HISTSTARTDATE)
   SELECT '000000001', 200.00, 1, '401K', '01/01/2004'
   UNION
   SELECT 'ZZ0-10001', 10.00, 1, 'LIFE', '01/01/2004'
   UNION
   SELECT '000000002', 220.00, 2, '401K', '01/01/2004'
   UNION
   SELECT 'DC001-111', 10.00, 2, 'BUSPASS', '01/01/2004'
   UNION
   SELECT '000000003', 300.00, 3, '401K', '01/01/2004'
   UNION
   SELECT 'ZZ0-10003', 10.00, 3, 'LIFE', '01/01/2004'
   GO      

2. 建立一個文本檔案，使用下面的内容作為工資變更資訊：

   EMPLOYEEID,EMPLOYEEPLANIDNBR,DEDUCTIONAMOUNT,PAYDEDUCTTYPE,ENROLLDATE,COMMENT
   1,000000001,225,401K,'01/01/05',INCREASED 401K DEDUCTION
   1,ZZ0-10001,15,LIFE,'01/01/05',INCREASED LIFE DEDUCTION
   2,000000002,220,401K,'01/01/05',NO CHANGE
   2,DC001-111,0,BUSPASS,'01/01/05',TERMINATED BUSPASS DEDUCTION
   3,000000003,250,401K,'01/01/05',DECREASED DEDUCTION
   3,ZZ0-10003,10,LIFE,'01/01/05',NO CHANGE
   4,000000004,175,401K, ‘01/01/05',NEW 401K DEDUCTION      

3. 建立一個package，命名為SlowChangingDemension。建立一個Data Flow Task點選進入Data Flow界面。在Data Flow界面内下面的ConnectionManageers内建立一個Flat File Connection連接配接上面的文本檔案，選擇第一行作為列名如圖4。在進階标簽内設定EmployeePlanNbr和PayDeductType兩個列的長度是10，設定EmployeeID的類型是32-bit Integer [DT_I4]，設定DeductionAmount的資料類型是currency[DT_CY]，如圖5。

   

   圖4

   

   圖5
4. 使用上述連接配接添加一個Flat File DataSource
5. 添加一個漸變緯度任務，将Flat File DataSource和它連接配接起來

檢視PayrollDeductItem表的内容如圖6，你可能會奇怪為什麼會有一些多餘的字段例如HistTextStatus， HistBitStatus， HistStartDate， HistEndDate，在執行task的時候并不是所有的字段都用得上，我們會根據不同的設定來使用不同的列。注意這個截圖是我執行這個SCD之後的表，資料已經被修改。

圖6

 初次打開SCD的時候，設定向導會展開四個設定步驟，這些步驟如下：

1. 次元表和次元選擇步驟：這個步驟中設定次元表的位置，輸入資料和次元表中對應字段，和使用那些字段作為主鍵以便一一對應。那些需要對應的字段将會被覆寫或者更新，還需要設定那些字段作為主鍵字段，以便對應。輕按兩下打開Slow Changing Demension進入向導設定界面，點選下一步進入Select a Dimension and Keys界面，首先選擇資料庫AdventureWorks和這個資料庫下的表PayrollDeductItem作為Dimension Table，Imcoming columns這一列中的資料一部分被作為Business key來比對Dimension Columns中的對應列，剩下的作為更新資料源，這裡EmployeePlanIDNbr，EmployeeID兩列作為Business key，剩下的兩列預設Not a key column。這裡注意到必須選擇一列作為Business key才能進入下一步設定。設定好的界面如圖7

   

   圖7

2. 漸變次元設定步驟：這個步驟隻關心在第一個步驟中未被設定為主鍵字段的的那些字段。在這個步驟中将設定按照什麼樣的政策來更新這些字段。在運作的時候目标表中的資料将按照這些政策來更新。這些cerulean如下：

   固定屬性:  在次元表中的值是固定的，如果輸入資料源中的資料有變動，将會報錯。

   更改屬性：次元表中的資料總是被輸入資料源中的 資料覆寫掉。這是相面讨論的類型2。

   曆史屬性：次元表中的資料和輸入源中的資料會有不同，但是有重大意義，将會被儲存起來。

   如圖8

   

   圖8

   在這個例子中，點選下一步進入Slow Changing Dimension Clumns界面，上一個步驟中設定為預設Not a key column的兩個列将會出現在這個步驟中，在這裡他們作為更新Dimension Table的兩個候選列。在Changing Type中選擇修改屬性為更新屬性的或者曆史屬性，如果選擇更新的，遇到比對項的時候這一列的值将會被修改。如果選擇為曆史的将會評估變更，如果檢測到更新，将會根據選擇的曆史變更添加一個新的行。這裡我們做一個試驗，将DeductionAmount和PayDeductType都選擇為更新的。

3. 固定和更新屬性設定：點選下一步會看到如圖9。在這裡如果選擇了固定屬性，如果資料不同通過這個設定可以在運作時報錯。前一個步驟我們沒有選擇更新屬性，是以這裡是灰色的不可用。另外一個選項設定是否覆寫目前活動資料，或者覆寫活動和實效的資料。

   

   圖9

4. 曆史屬性設定步驟：隻有在第2步驟選擇曆史屬性會有這二個設定步驟。如果設定了曆史的屬性，那就是類型2。有兩種方法來儲存曆史資料。每一種都會新插入一個行。我們來看這兩個設定。

   使用單獨的行來顯示目前的何過期的資料：這個選項允許在次元表中選擇一個列，這個列用來辨別這一行資料是老舊的，而另一行資料是更新的。在SCD中有兩組值用來辨別資料的時效性：True/False，Current/Expired。

   使用開始時間和結束時間來辨別更新的和老舊的資料：這個選項會使用次元表中的的兩個列來辨別這一行的有效期的開始時間和結束時間。要注意的一點是要使用一個時間變量來給這些列指派。

   這裡既然DeductionAmount字段為曆史的，PayrollDeductItem表中的一些看起來備援的字段就有用了，這些字段可以标記這一行資料的不同的生效時間。選擇變更類型為曆史的時候字段HistStartDate和HistEndDate用來記錄有效時間。當使用新加列的方法時這兩個字段會變成false和Expired，新添加的一行将會是ture和Current。如圖6-23示例說明如何使用HistStartDate和HisEndDate。設定時間值選項有一些系統變量，也可以使用自定義的變量，這裡我們設定為System::StartTime然後點選下一步。

5. 推斷成員選項設定：當你從其他表中将資料導入到次元表中，但是次元表中的資料列不全或者你想過一段時間再執行它，可以在這個步驟中設定。這裡我們不設定這個選項，如圖10。

   

   圖10
6. 完成向導步驟：使用這個步驟來完成整個SCD的設定，如圖11。

   

   圖11

在這個例子中，我們要判斷次元表中那些字段作為主鍵，在PayrollDeductItem表中我們可以判斷出[EmployeeIDNbr]字段是員工号，這個是不會改變的，另外[EmployeeID]也是不會改變的，這兩個建組合成一個主鍵可以唯一辨別一行資料。[PayrollDeductItemID]字段在這裡不選擇作為主鍵，因為在輸入資料源中沒有這樣的一個字段。當運作的時候這個字段不能幫助我們判斷這個行應該更新或插入到次元表中。

另外一個重要的設定是輸入資料源中有比對的資料的時候選擇什麼樣的政策來更新次元表。舉一個例子，在輸入源資料中[EmployeePlanIDNbr] = "0000000001" [EmployeeID]= "1"， [PayDeductType] ="401"這一行資料中對應的DeductionAmount是225.00，而在次元表中對應的值是200.00，我們應該怎麼設定呢？下面的表格給出了選擇項和對應的結果。

圖12

按照表中提示的資訊，我們輕按兩下打開SCD，點選下一步轉到選擇主鍵選擇設定。首選必須建立一個OLE DB資料源，并從資料源中選擇次元表PayrollDeductItem。所有作為主鍵的字段和作為更新的字段都砸這裡選擇設定。預設的設定是“Not a key column”。在這裡[EmployeePlanIDNbr]和[EmployeeID]選為business key。注意這裡至少要選擇一個列作為主鍵，否則不能點選下一步。最後的設定效果如圖7。

下一個步驟将設定那些沒有被選擇作為主鍵的字段，這些字段将作為更新和覆寫的候選字段。每個字段需要設定更新屬性或者曆史屬性。如果選擇更新屬性，次元表中的值将被直接更新。如果選擇曆史屬性，列将會評估更新。當發現有任何變動，根據設定一個新的行将被寫入。這裡我們如果把[DeductionAmount]設定為曆史屬性，[PayDeductType]字段都設定為更新屬性。

既然選擇了一個字段作為曆史屬性，在後面的設定中将會出現曆史屬性向導設定。如果DeductionAmount發生了變動，我們可以選擇兩種方式來儲存曆史資料。現在PayRollDeductionItem表中的那些額外的字段就開始起作用了。這些字段不是必須的，但是這裡我們為了做一些練習把他們加在次元表中。在設定曆史屬性時會用到HistStartDate和HistEndDate這兩個字段，在設定單獨列儲存的時候，HistBitStatus 和HistTextStatus字段會被用到，它将把已經過時的字段辨別為false或者expired，新的資料行将會被辨別為true和current。這裡要注意，如果我們選擇Column to indicate current record屬性值為HistBitStatus的時候因為它是bit類型的，那麼Value when current和Expiration value将會被預設的設定為True和False如圖13。如果我們選擇Column to indicate current record屬性值為HistTextStatus的時候，那麼Value when current和Expiration value應該相應的設定為True和False如圖14

圖13

圖14

下一個步驟是推斷成員選項設定。在這例子中，在次元表中沒有一個新的工資扣除項，是以不需要設定推斷選項。如果在輸入源資料中有一個新的扣除選項需要添加的話設定這個選項可以為次元表添加一個占位符。如圖10，如果設定了推斷成員，必須選擇将曆史屬性或者變更屬性字段設定為null或者使用一個bool列來表示資料是推斷成員。

點選下一步如圖11，在這個步驟中預覽SCD有哪些輸出項，可以在使用這些輸出項定制自己的task，但是一旦這樣整個SCD将會被重新配置，把以前的設定打亂，頁不能使用這個向導。

完成整個設定向導之後如圖2，整個SCD共有三個輸出，最左邊的是Changing Attribute Updates Output，這個輸出使用OLE DB Command來更新次元表中的資料，如圖15。我們可以看到有一個SQL語句來更新[PayDeductType]字段。中間的輸出時New Output，這裡将處理那些新出現的行，最終它将和右邊的曆史屬性輸出合并起來，并輸入到次元表中。最右邊的輸出的作用是當識别出有更新資料的時候要更新[HistEndDate]這個列，如圖16。

圖15

圖16

最後我們來看看運作後的效果。

圖17

圖18

在圖18中我們可以發現第1,3,4,5行現在是老舊的無效的資料，他們的[HistEndDate]字段都被設定成目前時間，對應的新的有效地的資料分别是第8,11,10,9行。第2，6行雖然有比對的主鍵，但是輸入資料源中和次元表中的DeductionAmount值是一樣的，是以沒有更新，但是他們的[HistStartDate]被重新設定成目前時間。

在實際的生産環境中如果要使用SCD，建議認真檢查輸入資料源，看裡面是否有髒資料。使用SCD來講OLTP中的資料更新到資料倉庫中的時候會很省力。如果想這個例子這樣，可以檢查最後輸出中的OLE DB指令，但是總的來說SCD已經為我們做了大部分的工作。

作者：

Tyler Ning

出處：

http://www.cnblogs.com/tylerdonet/

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，如有問題，可以通過以下郵箱位址

[email protected]

 聯系我，非常感謝。