使用SSIS Slow Changing Transformation組件管理緩慢變化維，ssistransformation

使用SSIS Slow Changing Transformation組件管理緩慢變化維，ssistransformation

最近嘗試用SSIS內建的 Slow Changing Transformation組件處理緩慢變化維，看到有一篇文章寫的很詳細，就按照步驟進行操作同時進行翻譯。原網址來自：Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS。

 

介紹

作為資料庫專家或者ETL的開發人員你可能偶爾會碰到需要維護和管理緩慢變化唯的情境。在SQL Server中有多種方法來實現，最簡單的是使用SSIS 資料流組件中的Slowly Changing DimensionTransformation。

在這片文章中，我會通過一個例子提供如何使用SSIS的Slowly Changing DimensionTransformation管理緩慢變化唯的步驟和指導。

 

理解緩慢變化維的情境

 

維度是資料管理和資料倉儲中的術語。它指邏輯分組資料比如地理位置，客戶或者產品資訊。通過緩慢變化維(SCDs),資料緩慢變化而不是基於時間，週期性變化。~Wikipedia

有不同類型的緩慢變化唯獨：

• SCD Type 0     (Fixed) – 這種類型是最不常用的，在第一次插入後就固定了不接受變化。這意味著一旦寫入，這些資料就不會被覆蓋。
• SCD Type 1     (Changing) – 這種類型，如果資料被更改，她會被新的值覆蓋。

相關文章：

• SQL Server 2012 Integration Services - Package      Deployment
• SQL Server 2012 Integration Services - Package and      Project Parameters
• SQL Server 2012 Integration Services - Package      Variables
• SQL Server 2012 Integration Services - Package and      Project Configurations
• SQL Server 2012 Integration Services - Unattended      Execution of SSIS Packages
• SQL Server 2012 Integration Services - GUI-Friendly      Ways of Managing Execution of SSIS Packages

例如考慮這個例子：　

 

SupplierCode	SupplierName	Address
S0000001	ABC Company	USA
S0000002	XYZ  Corporation	USA

 

如果供應商的名字隨著時間的推移被更改，正如你在下面看到的供應商的名字已經被新的記錄更新了。這種看起來非常簡單去實現，但是無法追蹤記錄。

 

SupplierCode	SupplierName	Address
S0000001	ABC Company Ltd.	USA
S0000002	XYZ  Corporation	USA

 

• SCD Type 2     (Historical) –在這個類型中，如果資料被更改，它將會儲存一個新的記錄，舊的記錄被標誌位過時的。

 

SupplierCode	SupplierName	Address	EffectiveDate	Expiration  Date
S0000001	ABC  Company	USA	3/2/2013	3/2/2013
S0000002	XYZ  Corporation	USA	3/2/2013
S0000001	ABC Company Ltd.	USA	3/3/2013

 

• 為了維護SCD     type 2，不同的人採用不同的方法。比如，一種方法是通過增加有效日期和到期日期表示記錄是活躍的。如果到期日為NULL表示當前的記錄是活躍的。另外一種方法是添加一個標誌列表示當前活動記錄。通常人們會使用第一種方法或者兩者的結合。
•  
• SCD Type 4     (Limited history) –這不是一個常用的類型因為只能維護有限的更改。在這種ＳＣＤ類型中，通過表中添加額外的列儲存舊值。

 

SupplierCode	SupplierName	Address	OldSupplierName
S0000001	ABC  Company	USA	ABC Company Ltd.
S0000002	XYZ  Corporation	USA

 

在SQLServer中有多種方法實現緩慢變化維度，最簡單的是使用SSIS 資料流組件中的Slowly Changing Dimension Transformation，儘管會有一些限制，文章結尾的時候會提到這些限制。

在我開始Slowly Changing Dimension Transformation組件解釋之前，讓我先解釋一下代理鍵並且為什麼它對資料倉儲很重要。我們經常會在維度中增加一個沒有意義的鍵叫做代理鍵。代理鍵通常是整數，充當唯獨表的唯一鍵或者主鍵，並且作為事實表外鍵約束。代理鍵對於管理緩慢變化唯變得非常重要。

 

使用Slowly Changing Dimension Transformation

 

我們首先建立一個供應商表並添加一些資料。你應該可以看到，我增加了SupplierCode欄位作為主鍵，當作業務鍵。

 

USE [AdventureWorks2012] GO CREATE TABLE[dbo].[Supplier](        [SupplierCode] CHAR(8) PRIMARY KEY,        [SupplierName] [varchar](50)NULL,        [Address] [varchar](50)NULL, ) ON[PRIMARY] GO INSERT INTO[dbo].[Supplier]([SupplierCode],[SupplierName],[Address]) VALUES ('S0000001','ABC Company','USA'), ('S0000002','XYZ Corporation','USA') GO SELECT *FROM[dbo].[Supplier]

 

現在我們建立一個維度資料表儲存供應商資訊，你應該注意到我增加了SupplierId欄位作為代理鍵，生效時間和截止時間用來追蹤曆史變化。另外我增加了CurrentFlag列用來標註目前記錄是否活躍。

 

USE [AdventureWorks2012] GO CREATE TABLE[dbo].[DimSupplier](        [SupplierId] [int] IDENTITY(1,1)NOTNULL,        [SupplierCode] CHAR(8),        [SupplierName] [varchar](50)NULL,        [Address] [varchar](50)NULL,        [EffectiveDate] [date]  NULL,        [ExpirationDate] [date]  NULL,        [CurrentFlag] [char](1) NULL,        CONSTRAINT [PK_DimSupplier]  PRIMARY KEY CLUSTERED ([SupplierId]ASC) ) ON[PRIMARY] GO

 

到目前為止一切順利，現在我們建立一個SSIS包，增加一個資料流程工作，拖入資料來源組件從原始表擷取資料。現在新增一個SlowlyChanging Dimension Transformation元件連線到上面的資料來源組件。雙擊SlowlyChanging Dimension Transformation進行修改，嚮導介面如下：

　

Slowly Changing Dimension Wizard

 

單擊下一步按鈕進入到下一個介面，在新的介面首先要選擇目標維度資料表和欄位對應。緊接著你需要從源表中指定例作為業務鍵。在我的例子中SupplierCode是源表的主鍵因此我把它作為業務鍵，如下：

 

Business key

 

點擊Next按鈕進入到嚮導的下一介面，在這個介面中你需要指定維度資料表中的欄位是否被當作SCDType 0, Type 1 or Type 2處理。

 

Specify each column of the dimension

 

在我的例子中我選擇地址列作為SCD Type 1處理，名字作為SCD Type 2處理，如下：　

 

SCD Type 1 and SCD Type 2

 

點擊Next進入嚮導的下個介面，在這個介面需要指定（因為我們一個列作為SCD Type 2處理）開始日期（生效日期）結束日期列（有效期間），和設定產生日期的變數，

Start and End Dates

 

點擊Next進入嚮導的下個介面，在這個介面指定推斷維度成員設定

 

Inferred Dimension Members

 

點擊Next進入嚮導的下個介面，點擊完成按鈕完成配置。下面就是在資料流程工作中看到的：

 

Complete the Wizard

 

緩慢變化維度嚮導會根據你的選擇和配置添加幾個任務管理緩慢變化維。在上面螢幕中“Changing Attribute Updates Output”路徑將為SCDType 1（基本覆蓋）更新記錄。“New Output”路徑將增加新條目到唯獨表，為了維護記錄。“Historical Attribute Inserts Output”路徑會更新之前到期日期列的記錄。

 

當你第一次執行包你會注意到源表中的兩條記錄被載入到維度資料表，

 

The Dimension Table

 

現在執行下面的語句驗證Supplier維度資料表的資料：　

 

USE [AdventureWorks2012] GO SELECT *FROM[dbo].[DimSupplier] GO

 

這是你在執行上面的指令碼後看到的結果，跟我們預期的差不多：

 

Results of executed query

 

現在我們開啟源表使用下面的指令碼更新一些記錄。我將要更改SupplierCode = ‘S0000001’的供應商名稱。

 

USE [AdventureWorks2012] GO UPDATE [dbo].[Supplier] SET [SupplierName]='ABC Company Ltd.' WHERE [SupplierCode]='S0000001' GO SELECT *FROM[dbo].[Supplier] GO

 

現在再次執行包，你會看到一條記錄（新）已經插入並且一條記錄（舊的）已經被更新或者標記為過時。這是因為更新的列被配置成SCD Type 2：

 

One record inserted and one record outdated

 

現在執行上面的查詢語句驗證資料。正如我們預測的，SupplierCode = ‘S0000001’有兩條記錄。前面的記錄已經更新了截至日期表面資料已經是過時的，最新的記錄對應了最新的供應商名稱：

 

USE [AdventureWorks2012] GO SELECT *FROM[dbo].[DimSupplier] GO

 

Query results

 

限制：

Slowly Changing Dimension transformation 被設計為簡單易用，主要為了小的維度資料表。如我們上面看到的，Slowly Changing Dimension Transformation是一個開箱即用的SSIS組件，對於小的維度可以快速配置。但是Slowly ChangingDimension Transformation並不適合所有的情況尤其是你的維度很大，下面是一些原因：

·        Slowly ChangingDimension transformation根據你的配置將組件添加到資料流程工作去管理緩慢變化維。如果在這些組件上做了一些定製化，然後再次修改Slowly Changing Dimension transformation你做的定製化會丟失。

• 對於大的維度因為沒有緩衝尋找資料，效能會很慢。
• 只可以用到SQL     Server。
• 它使用OLEDB命令進行行更新而不是批次更新。

Conclusion結論

在這篇文章中,我談到了緩慢變化維度轉換,在SSIS提供開箱即用的工具包,可以輕鬆快速地配置用於管理較小的緩慢變化維度。在下一篇文章中我將討論一些備選方案,您可以使用管理更大的緩慢變化維度。