漸變維度（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。可以在啟用新的工資標準之前執行一次更新，但是如果僅僅跟新這樣會丟失工資走向信息。在這種情形下我們使用漸變緯度來解決這個問題，參照下面的步驟。

首先為了跟蹤歷史變更我們要新建一個表添加一些數據列。即使只有其中的一部分會在執行這個任務的時候用得到，還是要添加所有可能會用到的數據列。使用下面的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 

創建一個文本文件，使用下面的內容作為工資變更信息：

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 

 #p#

創建一個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

使用上述連接添加一個Flat File DataSource

添加一個漸變緯度任務，將Flat File DataSource和它連接起來

查看PayrollDeductItem表的內容如圖6，你可能會奇怪為什么會有一些多余的字段例如HistTextStatus， HistBitStatus， HistStartDate， HistEndDate，在執行task的時候并不是所有的字段都用得上，我們會根據不同的設置來使用不同的列。注意這個截圖是我執行這個SCD之后的表，數據已經被修改。

圖6

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

維度表和維度選擇步驟：這個步驟中設置維度表的位置，輸入數據和維度表中對應字段，和使用那些字段作為主鍵以便一一對應。那些需要對應的字段將會被覆蓋或者更新，還需要設置那些字段作為主鍵字段，以便對應。雙擊打開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

漸變維度設置步驟：這個步驟只關心在***個步驟中未被設置為主鍵字段的的那些字段。在這個步驟中將設置按照什么樣的策略來更新這些字段。在運行的時候目標表中的數據將按照這些策略來更新。這些cerulean如下：

固定屬性:  在維度表中的值是固定的，如果輸入數據源中的數據有變動，將會報錯。

更改屬性：維度表中的數據總是被輸入數據源中的數據覆蓋掉。這是相面討論的類型2。

歷史屬性：維度表中的數據和輸入源中的數據會有不同，但是有重大意義，將會被保存起來。

如圖8

圖8

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

固定和更新屬性設置：點擊下一步會看到如圖9。在這里如果選擇了固定屬性，如果數據不同通過這個設置可以在運行時報錯。前一個步驟我們沒有選擇更新屬性，所以這里是灰色的不可用。另外一個選項設置是否覆蓋當前活動數據，或者覆蓋活動和實效的數據。

圖9

歷史屬性設置步驟：只有在第2步驟選擇歷史屬性會有這二個設置步驟。如果設置了歷史的屬性，那就是類型2。有兩種方法來保存歷史數據。每一種都會新插入一個行。我們來看這兩個設置。

使用單獨的行來顯示當前的何過期的數據：這個選項允許在維度表中選擇一個列，這個列用來標識這一行數據是老舊的，而另一行數據是更新的。在SCD中有兩組值用來標識數據的時效性：True/False，Current/Expired。

使用開始時間和結束時間來標識更新的和老舊的數據：這個選項會使用維度表中的的兩個列來標識這一行的有效期的開始時間和結束時間。要注意的一點是要使用一個時間變量來給這些列賦值。

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

推斷成員選項設置：當你從其他表中將數據導入到維度表中，但是維度表中的數據列不全或者你想過一段時間再執行它，可以在這個步驟中設置。這里我們不設置這個選項，如圖10。

圖10

完成向導步驟：使用這個步驟來完成整個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。

#p#

下一個步驟將設置那些沒有被選擇作為主鍵的字段，這些字段將作為更新和覆蓋的候選字段。每個字段需要設置更新屬性或者歷史屬性。如果選擇更新屬性，維度表中的值將被直接更新。如果選擇歷史屬性，列將會評估更新。當發現有任何變動，根據設置一個新的行將被寫入。這里我們如果把[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已經為我們做了大部分的工作。

關于漸變維度的知識就介紹到這里，謝謝！

【編輯推薦】

1. 誤刪SQL Server日志文件后怎樣附加數據庫
2. 利用mysql的audit審計功能記錄用戶操作信息
3. 詳解Discuz_WIN7_Apache_MySQL_PHP平臺搭建
4. 在SQL SERVER 2005執行存儲過程的權限分配問題
5. T-SQL行列相互轉換命令：PIVOT和UNPIVOT使用詳解