漸變維度（SCD）轉(zhuǎn)換提供了一種維護(hù)漸變維度或漸變表的方法，對漸變維度的分析處理是一個很大的話題。一般一個維度表通常包含一些描述其他信息的離散值，例如，價格，重量，銷售地區(qū)。問題是如果隨著時間的推移這些信息改變我們將如何維護(hù)這個表，特別是在數(shù)據(jù)轉(zhuǎn)移的過程中。例如我們查看AdventureWorks中的product表，如圖1

圖1

通常在聯(lián)機(jī)事務(wù)處理（OLTP）數(shù)據(jù)庫中，我們會用一行的數(shù)據(jù)來保存產(chǎn)品信息。如果產(chǎn)品的售價從10美元上漲到15美元，我們直接更新StandardCost字段為15，這樣做完成了任務(wù)：現(xiàn)在的售價是多少？但是丟失了歷史數(shù)據(jù)，我們不知道漲價之前的價格是多少？價格上漲了多少？解決這個問題有三種基本的選擇，具體選擇哪一種要基于這個表的我們關(guān)注點(diǎn)和想要得到什么樣的結(jié)果，我們將這些基本的選擇簡單地命名為類型1，類型2，類型3

類型1：重寫歷史—通過使用新的產(chǎn)品價格來替換掉當(dāng)前的產(chǎn)品價格，當(dāng)然這樣會丟失歷史值。這個是最簡單的做法。

類型2：保留歷史—添加一行新的完整的數(shù)據(jù)，保留歷史數(shù)據(jù)行，當(dāng)然這樣會有一些副作用，這樣做使得這個表更加復(fù)雜，占用空間更大。

類型3：僅保存上一次歷史—添加額外的屬性，僅保存上一次歷史值，這樣我們只能看到產(chǎn)品價格變化中的兩次價格。這種方法不常用。

注意：類型3不能直接實(shí)現(xiàn)，需要借助其他的task才能實(shí)現(xiàn)

和其他task不同，漸變維度轉(zhuǎn)換提供一個向?qū)гO(shè)置，通過問答的方式來設(shè)置，類似于在計(jì)算機(jī)中安裝一個新的程序時使用的安裝向?qū)?。高級設(shè)置提供更多的選擇，但是在我們完全掌握它之前***還是使用向?qū)гO(shè)置。雙擊維度轉(zhuǎn)換標(biāo)簽或者右擊選擇編輯都可以打開向?qū)?。設(shè)置好之后將會產(chǎn)生一些更新和插入task來完成更新或插入的任務(wù)。圖2顯示的設(shè)置好之后的界面。可以看到這個task和其他的不同，它是由多個task組合而成的。

圖2

漸變維度是一個自動化的task，概括地說漸變維度接收輸入數(shù)據(jù)和一個維度表數(shù)據(jù)，通過配置信息產(chǎn)生至少兩個輸出，并且這些輸出總共有6種類型，***使用OLE DB命令來完成數(shù)據(jù)庫的更新，這6種可能的輸出數(shù)據(jù)流如下：

修改屬性更新輸出：這種輸出屬于類型1。在SCD中被選中作為可變項(xiàng)的屬性都會和輸入數(shù)據(jù)源做比較。如果匹配主鍵的兩行屬性值有差異，輸入數(shù)據(jù)將會從這個輸出流輸出。

歷史屬性插入輸出：這種屬性屬于類型2。在SCD中被選中作為可變項(xiàng)的屬性都會和輸入數(shù)據(jù)源做比較。如果匹配主鍵的兩行屬性值有差異，輸入數(shù)據(jù)將會從這個輸出流輸出。

固定輸出：在SCD中被選作固定的屬性都會和輸入的數(shù)據(jù)源做比較。如果匹配主鍵的兩行屬性值有差異，輸入數(shù)據(jù)將會從這個輸出流輸出。這種輸出流適用于類型3，但是必須自己編寫sql語句。

推斷成員更新：在SCD的設(shè)置選中推斷成員的時候，將會有這個輸出流。如果需要將維度表轉(zhuǎn)到另外一個表中需要設(shè)置使用這個輸出流。這個輸出可能在后面的操作中添加到維度表中。

新輸出：這個和歷史輸出一樣都要合并到目標(biāo)表中。

不變的輸出：默認(rèn)沒有這個輸出。如果SCD發(fā)現(xiàn)輸入數(shù)據(jù)和維度表中的數(shù)據(jù)沒有差異，不會有任何操作。如果感興趣的話你可以為這個輸出建一個目標(biāo)表，查看其中的數(shù)據(jù)。

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

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

圖3

現(xiàn)在假設(shè)一年的時間過去了，每個人的工資情況都會有些變動。我們的任務(wù)是來更新PayrollDeductItem這個表。我們發(fā)現(xiàn)有些工資項(xiàng)減少了，有工資項(xiàng)增加了，如果有些工資項(xiàng)刪除了，則記為0?？梢栽趩⒂眯碌墓べY標(biāo)準(zhǔn)之前執(zhí)行一次更新，但是如果僅僅跟新這樣會丟失工資走向信息。在這種情形下我們使用漸變緯度來解決這個問題，參照下面的步驟。

首先為了跟蹤歷史變更我們要新建一個表添加一些數(shù)據(jù)列。即使只有其中的一部分會在執(zhí)行這個任務(wù)的時候用得到，還是要添加所有可能會用到的數(shù)據(jù)列。使用下面的sql語句創(chuàng)建一個表并添加數(shù)據(jù)：

 
 
 
 

  
  
  
  
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
 
 
 
 

創(chuàng)建一個文本文件，使用下面的內(nèi)容作為工資變更信息：

 
 
 
 

  
  
  
  
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#

創(chuàng)建一個package，命名為SlowChangingDemension。創(chuàng)建一個Data Flow Task點(diǎn)擊進(jìn)入Data Flow界面。在Data Flow界面內(nèi)下面的ConnectionManageers內(nèi)新建一個Flat File Connection連接上面的文本文件，選擇***行作為列名如圖4。在高級標(biāo)簽內(nèi)設(shè)置EmployeePlanNbr和PayDeductType兩個列的長度是10，設(shè)置EmployeeID的類型是32-bit Integer [DT_I4]，設(shè)置DeductionAmount的數(shù)據(jù)類型是currency[DT_CY]，如圖5。

圖4

圖5

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

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

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

圖6

初次打開SCD的時候，設(shè)置向?qū)归_四個設(shè)置步驟，這些步驟如下：

維度表和維度選擇步驟：這個步驟中設(shè)置維度表的位置，輸入數(shù)據(jù)和維度表中對應(yīng)字段，和使用那些字段作為主鍵以便一一對應(yīng)。那些需要對應(yīng)的字段將會被覆蓋或者更新，還需要設(shè)置那些字段作為主鍵字段，以便對應(yīng)。雙擊打開Slow Changing Demension進(jìn)入向?qū)гO(shè)置界面，點(diǎn)擊下一步進(jìn)入Select a Dimension and Keys界面，首先選擇數(shù)據(jù)庫AdventureWorks和這個數(shù)據(jù)庫下的表PayrollDeductItem作為Dimension Table，Imcoming columns這一列中的數(shù)據(jù)一部分被作為Business key來匹配Dimension Columns中的對應(yīng)列，剩下的作為更新數(shù)據(jù)源，這里EmployeePlanIDNbr，EmployeeID兩列作為Business key，剩下的兩列默認(rèn)Not a key column。這里注意到必須選擇一列作為Business key才能進(jìn)入下一步設(shè)置。設(shè)置好的界面如圖7

圖7

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

固定屬性: 在維度表中的值是固定的，如果輸入數(shù)據(jù)源中的數(shù)據(jù)有變動，將會報(bào)錯。

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

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

如圖8

圖8

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

固定和更新屬性設(shè)置：點(diǎn)擊下一步會看到如圖9。在這里如果選擇了固定屬性，如果數(shù)據(jù)不同通過這個設(shè)置可以在運(yùn)行時報(bào)錯。前一個步驟我們沒有選擇更新屬性，所以這里是灰色的不可用。另外一個選項(xiàng)設(shè)置是否覆蓋當(dāng)前活動數(shù)據(jù)，或者覆蓋活動和實(shí)效的數(shù)據(jù)。

圖9

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

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

使用開始時間和結(jié)束時間來標(biāo)識更新的和老舊的數(shù)據(jù)：這個選項(xiàng)會使用維度表中的的兩個列來標(biāo)識這一行的有效期的開始時間和結(jié)束時間。要注意的一點(diǎn)是要使用一個時間變量來給這些列賦值。

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

推斷成員選項(xiàng)設(shè)置：當(dāng)你從其他表中將數(shù)據(jù)導(dǎo)入到維度表中，但是維度表中的數(shù)據(jù)列不全或者你想過一段時間再執(zhí)行它，可以在這個步驟中設(shè)置。這里我們不設(shè)置這個選項(xiàng)，如圖10。

圖10

完成向?qū)Р襟E：使用這個步驟來完成整個SCD的設(shè)置，如圖11。

圖11

在這個例子中，我們要判斷維度表中那些字段作為主鍵，在PayrollDeductItem表中我們可以判斷出[EmployeeIDNbr]字段是員工號，這個是不會改變的，另外[EmployeeID]也是不會改變的，這兩個建組合成一個主鍵可以唯一標(biāo)識一行數(shù)據(jù)。[PayrollDeductItemID]字段在這里不選擇作為主鍵，因?yàn)樵谳斎霐?shù)據(jù)源中沒有這樣的一個字段。當(dāng)運(yùn)行的時候這個字段不能幫助我們判斷這個行應(yīng)該更新或插入到維度表中。

另外一個重要的設(shè)置是輸入數(shù)據(jù)源中有匹配的數(shù)據(jù)的時候選擇什么樣的策略來更新維度表。舉一個例子，在輸入源數(shù)據(jù)中[EmployeePlanIDNbr] = "0000000001" [EmployeeID]= "1"， [PayDeductType] ="401"這一行數(shù)據(jù)中對應(yīng)的DeductionAmount是225.00，而在維度表中對應(yīng)的值是200.00，我們應(yīng)該怎么設(shè)置呢？下面的表格給出了選擇項(xiàng)和對應(yīng)的結(jié)果。

圖12

按照表中提示的信息，我們雙擊打開SCD，點(diǎn)擊下一步轉(zhuǎn)到選擇主鍵選擇設(shè)置。***必須新建一個OLE DB數(shù)據(jù)源，并從數(shù)據(jù)源中選擇維度表PayrollDeductItem。所有作為主鍵的字段和作為更新的字段都砸這里選擇設(shè)置。默認(rèn)的設(shè)置是“Not a key column”。在這里[EmployeePlanIDNbr]和[EmployeeID]選為business key。注意這里至少要選擇一個列作為主鍵，否則不能點(diǎn)擊下一步。***的設(shè)置效果如圖7。

#p#

下一個步驟將設(shè)置那些沒有被選擇作為主鍵的字段，這些字段將作為更新和覆蓋的候選字段。每個字段需要設(shè)置更新屬性或者歷史屬性。如果選擇更新屬性，維度表中的值將被直接更新。如果選擇歷史屬性，列將會評估更新。當(dāng)發(fā)現(xiàn)有任何變動，根據(jù)設(shè)置一個新的行將被寫入。這里我們?nèi)绻裑DeductionAmount]設(shè)置為歷史屬性，[PayDeductType]字段都設(shè)置為更新屬性。

既然選擇了一個字段作為歷史屬性，在后面的設(shè)置中將會出現(xiàn)歷史屬性向?qū)гO(shè)置。如果DeductionAmount發(fā)生了變動，我們可以選擇兩種方式來保存歷史數(shù)據(jù)。現(xiàn)在PayRollDeductionItem表中的那些額外的字段就開始起作用了。這些字段不是必須的，但是這里我們?yōu)榱俗鲆恍┚毩?xí)把他們加在維度表中。在設(shè)置歷史屬性時會用到HistStartDate和HistEndDate這兩個字段，在設(shè)置單獨(dú)列保存的時候，HistBitStatus 和HistTextStatus字段會被用到，它將把已經(jīng)過時的字段標(biāo)識為false或者expired，新的數(shù)據(jù)行將會被標(biāo)識為true和current。這里要注意，如果我們選擇Column to indicate current record屬性值為HistBitStatus的時候因?yàn)樗莃it類型的，那么Value when current和Expiration value將會被默認(rèn)的設(shè)置為True和False如圖13。如果我們選擇Column to indicate current record屬性值為HistTextStatus的時候，那么Value when current和Expiration value應(yīng)該相應(yīng)的設(shè)置為True和False如圖14

圖13

圖14

下一個步驟是推斷成員選項(xiàng)設(shè)置。在這例子中，在維度表中沒有一個新的工資扣除項(xiàng)，所以不需要設(shè)置推斷選項(xiàng)。如果在輸入源數(shù)據(jù)中有一個新的扣除選項(xiàng)需要添加的話設(shè)置這個選項(xiàng)可以為維度表添加一個占位符。如圖10，如果設(shè)置了推斷成員，必須選擇將歷史屬性或者變更屬性字段設(shè)置為null或者使用一個bool列來表示數(shù)據(jù)是推斷成員。

點(diǎn)擊下一步如圖11，在這個步驟中預(yù)覽SCD有哪些輸出項(xiàng)，可以在使用這些輸出項(xiàng)定制自己的task，但是一旦這樣整個SCD將會被重新配置，把以前的設(shè)置打亂，頁不能使用這個向?qū)А?/p>

完成整個設(shè)置向?qū)е笕鐖D2，整個SCD共有三個輸出，最左邊的是Changing Attribute Updates Output，這個輸出使用OLE DB Command來更新維度表中的數(shù)據(jù)，如圖15。我們可以看到有一個SQL語句來更新[PayDeductType]字段。中間的輸出時New Output，這里將處理那些新出現(xiàn)的行，最終它將和右邊的歷史屬性輸出合并起來，并輸入到維度表中。最右邊的輸出的作用是當(dāng)識別出有更新數(shù)據(jù)的時候要更新[HistEndDate]這個列，如圖16。

圖15

圖16

***我們來看看運(yùn)行后的效果。

圖17

圖18

在圖18中我們可以發(fā)現(xiàn)第1,3,4,5行現(xiàn)在是老舊的無效的數(shù)據(jù)，他們的[HistEndDate]字段都被設(shè)置成當(dāng)前時間，對應(yīng)的新的有效地的數(shù)據(jù)分別是第8,11,10,9行。第2，6行雖然有匹配的主鍵，但是輸入數(shù)據(jù)源中和維度表中的DeductionAmount值是一樣的，所以沒有更新，但是他們的[HistStartDate]被重新設(shè)置成當(dāng)前時間。

在實(shí)際的生產(chǎn)環(huán)境中如果要使用SCD，建議認(rèn)真檢查輸入數(shù)據(jù)源，看里面是否有臟數(shù)據(jù)。使用SCD來講OLTP中的數(shù)據(jù)更新到數(shù)據(jù)倉庫中的時候會很省力。如果想這個例子這樣，可以檢查***輸出中的OLE DB命令，但是總的來說SCD已經(jīng)為我們做了大部分的工作。

關(guān)于漸變維度的知識就介紹到這里，謝謝！

當(dāng)前名稱：SSIS高級轉(zhuǎn)換任務(wù)之漸變維度詳解
鏈接URL：http://uogjgqi.cn/article/djdpged.html