介紹

?

作為數(shù)據(jù)庫專家或者ETL的開發(fā)者你可能偶爾會碰到需要維護(hù)和管理緩慢變化唯的場景。在SQL Server中有多種方法來實現(xiàn)，最簡單的是使用SSIS 數(shù)據(jù)流組件中的Slowly Changing DimensionTransformation。

?

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

?

理解緩慢變化維的場景

?

維度是數(shù)據(jù)管理和數(shù)據(jù)倉庫中的術(shù)語。它指邏輯分組數(shù)據(jù)比如地理位置，客戶或者產(chǎn)品信息。通過緩慢變化維(SCDs),數(shù)據(jù)緩慢變化而不是基于時間，定期的變化。~Wikipedia

有不同類型的緩慢變化唯獨(dú)：

?

• SCD Type 0 ? ? (Fixed)?–?這種類型是最不常用的，在第一次插入后就固定了不接受變化。這意味著一旦寫入，這些數(shù)據(jù)就不會被覆蓋。

• SCD Type 1 ? ? (Changing)?–?這種類型，如果數(shù)據(jù)被更改，她會被新的值覆蓋。

相關(guān)文章：

• • 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

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

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

• SCD Type 2 ? ? (Historical)?–在這個類型中，如果數(shù)據(jù)被更改，它將會保存一個新的記錄，舊的記錄被標(biāo)志位過時的。

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

• 為了維護(hù)SCD ? ? type 2，不同的人采用不同的方法。比如，一種方法是通過增加有效日期和過期日期表示記錄是活躍的。如果截止日期為NULL表示當(dāng)前的記錄是活躍的。另外一種方法是添加一個標(biāo)志列表示當(dāng)前活動記錄。通常人們會使用第一種方法或者兩者的結(jié)合。

• ?

• SCD Type 4 ? ? (Limited history)?–這不是一個常用的類型因為只能維護(hù)有限的更改。在這種ＳＣＤ類型中，通過表中添加額外的列保存舊值。

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

在SQLServer中有多種方法實現(xiàn)緩慢變化維度，最簡單的是使用SSIS 數(shù)據(jù)流組件中的Slowly Changing Dimension Transformation，盡管會有一些限制，文章結(jié)尾的時候會提到這些限制。

在我開始Slowly Changing Dimension Transformation組件解釋之前，讓我先解釋一下代理鍵并且為什么它對數(shù)據(jù)倉庫很重要。

我們經(jīng)常會在維度中增加一個沒有意義的鍵叫做代理鍵。代理鍵通常是整數(shù)，充當(dāng)唯獨(dú)表的唯一鍵或者主鍵，并且作為事實表外鍵約束。代理鍵對于管理緩慢變化唯變得非常重要。

?

使用Slowly Changing Dimension Transformation

?

我們首先創(chuàng)建一個供應(yīng)商表并添加一些數(shù)據(jù)。你應(yīng)該可以看到，我增加了SupplierCode字段作為主鍵，當(dāng)作業(yè)務(wù)鍵。

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]

現(xiàn)在我們創(chuàng)建一個維度表存儲供應(yīng)商信息，你應(yīng)該注意到我增加了SupplierId字段作為代理鍵，生效時間和截止時間用來追蹤歷史變化。另外我增加了CurrentFlag列用來標(biāo)注當(dāng)前記錄是否活躍。

USE?[AdventureWorks2012] GO CREATE?TABLE?[dbo].[DimSupplier]( ???????[SupplierId]?[int]?IDENTITY(1,1)?NOT?NULL, ???????[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

到目前為止一切順利，現(xiàn)在我們創(chuàng)建一個SSIS包，增加一個數(shù)據(jù)流任務(wù)，拖入數(shù)據(jù)源組件從原始表獲取數(shù)據(jù)。現(xiàn)在新增一個SlowlyChanging Dimension Transformation組件連接到上面的數(shù)據(jù)源組件。雙擊SlowlyChanging Dimension Transformation進(jìn)行修改，向?qū)Ы缑嫒缦?#xff1a;　

?

Slowly ChangingDimension Wizard

?

單擊下一步按鈕進(jìn)入到下一個界面，在新的界面首先要選擇目標(biāo)維度表和字段映射。緊接著你需要從源表中指定例作為業(yè)務(wù)鍵。在我的例子中SupplierCode是源表的主鍵因此我把它作為業(yè)務(wù)鍵，如下：

?

Business key

?

點擊Next按鈕進(jìn)入到向?qū)У南乱唤缑?#xff0c;在這個界面中你需要指定維度表中的字段是否被當(dāng)作SCDType 0, Type 1 or Type 2處理。

?

Specify eachcolumn of the dimension

?

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

?

SCD Type 1 and SCDType 2

?

點擊Next進(jìn)入向?qū)У南聜€界面，在這個界面需要指定（因為我們一個列作為SCD Type 2處理）開始日期（生效日期）結(jié)束日期列（有效期），和設(shè)置產(chǎn)生日期的變量，如圖：

?

Start and EndDates

?

點擊Next進(jìn)入向?qū)У南聜€界面，在這個界面指定推斷維度成員設(shè)定如圖：

?

Inferred DimensionMembers

?

點擊Next進(jìn)入向?qū)У南聜€界面，點擊完成按鈕完成配置。下面就是在數(shù)據(jù)流任務(wù)中看到的：

?

Complete theWizard

?

緩慢變化維度向?qū)鶕?jù)你的選擇和配置添加幾個任務(wù)管理緩慢變化維。在上面屏幕中“Changing Attribute Updates Output”路徑將為SCDType 1（基本覆蓋）更新記錄。“New Output”路徑將增加新條目到唯獨(dú)表，為了維護(hù)歷史記錄。“Historical Attribute Inserts Output”路徑會更新之前過期日期列的記錄。

?

當(dāng)你第一次執(zhí)行包你會注意到源表中的兩條記錄被加載到維度表，如圖：

?

The DimensionTable

?

現(xiàn)在執(zhí)行下面的語句驗證Supplier維度表的數(shù)據(jù)：　

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

這是你在執(zhí)行上面的腳本后看到的結(jié)果，跟我們預(yù)期的差不多：

?

Results ofexecuted query

?

現(xiàn)在我們打開源表使用下面的腳本更新一些記錄。我將要更改SupplierCode = ‘S0000001’的供應(yīng)商名稱。

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

現(xiàn)在再次執(zhí)行包，你會看到一條記錄（新）已經(jīng)插入并且一條記錄（舊的）已經(jīng)被更新或者標(biāo)記為過時。這是因為更新的列被配置成SCD Type 2：

?

One recordinserted and one record outdated

?

現(xiàn)在執(zhí)行上面的查詢語句驗證數(shù)據(jù)。正如我們預(yù)測的，SupplierCode = ‘S0000001’有兩條記錄。前面的記錄已經(jīng)更新了截至日期表面數(shù)據(jù)已經(jīng)是過時的，最新的記錄對應(yīng)了最新的供應(yīng)商名稱：

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

Query results

?

限制：

?

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

• Slowly ChangingDimension transformation根據(jù)你的配置將組件添加到數(shù)據(jù)流任務(wù)去管理緩慢變化維。如果在這些組件上做了一些定制化，然后再次修改Slowly Changing Dimension transformation你做的定制化會丟失。

• 對于大的維度因為沒有緩存查找數(shù)據(jù)，性能會很慢。

• 只可以用到SQL ? ? Server。

• 它使用OLEDB命令進(jìn)行行更新而不是批量更新。

Conclusion結(jié)論

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

?

本文轉(zhuǎn)自 lzf328 51CTO博客，原文鏈接:

http://blog.51cto.com/lzf328/1533666

總結(jié)

以上是生活随笔為你收集整理的使用SSIS Slow Changing Transformation组件管理缓慢变化维的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。