Lab7：緩時變維度

設計 SCD (Slowly Changing Dimension)

底下練習，我們將使用 Slowly Changing Dimension Transformation來進行累加轉移資料，並搭配 Set-based Update 技巧，提升執行效率。

使用「緩時變維度」元件

底下練習，我們要將 DimCustomers 維度資料表修改成符合 Type 2 SCD 。

A. 修改維度資料表

1. 加入為了 Type 2 SCD 的驗證欄位

ALTER TABLE dbo.DimCustomers
ADD
	ValidFrom DATE,
	ValidTo DATE;

2. 刪除 DimCustomers 中所有資料

TRUNCATE TABLE dbo.DimCustomers;

B. 加入「緩時變維度」元件

修改先前的範例，在流程中加入「緩時變維度」元件，如下圖：

C. 設定「緩時變維度」元件

「緩時變維度」元件是以精靈介面的方式進行設定，底下是幾個設定畫面。

C.1 定義 input 和 dimemsion table 的欄位對應，還有定義 key 值欄位。

C.2 定義欄位變更類型

• Fixed attribute ：此欄位不該變更。
• Changing attribute ：此欄位若有變更直接update即可。
• Historical attribute ：此欄位若有變更必需記錄歷程。

C.3 設定「變更屬性」與「變更屬性」

C.4 設定「歷程記錄屬性」

如果要達到 Type2 支援，這個元件提供二種法：

• 使用一個識別欄位（如：CurrentFlag）來識別該筆資料是否為最新的值，或是過期的資料。
• 使用二個欄位 ValidFrom 和 ValidTo 來區分該筆資料是最新的值還是歷史資料。

C.5 設定「推斷成員」

此例不勾選。

C.6 完成設定

D. 流程說明

下面步驟說明，將分別解說下圖中的標示：

D1. 處理 Type 1 SCD 的資料列

將「Changing Attributes Updates output」連接到「SQL Command」進行更新

「SQL Command」的 SQL Statement

UPDATE [dbo].[DimCustomers] 
SET 
	[EmailAddress] = ?,
	[FullName] = ? 
WHERE 
	[CustomerKey] = ? AND [ValidTo] IS NULL

資料行對應

D2. 處理 Type 2 SCD 的資料列

將「Historical Attributes Inserts output」連接到「衍生資料行」，在「衍生資料行」中加入一個 ValidTo 欄位，預設值設為系統時間。

D3. 更新 ValidTo 欄位

將「衍生資料行」連接到「SQL Command」進行 ValidTo 欄位更新

「SQL Command」的 SQL Statement

UPDATE [dbo].[DimCustomers] 
SET 
	[ValidTo] = ? 
WHERE 
	[CustomerKey] = ? AND [ValidTo] IS NULL

資料行對應

D4. 將新增資料列與 Type 2 SCD 的資料列 Union All

D5. 加入「衍生資料行」，並新增一個 ValidFrom 欄位，預設值設為系統時間。

D6. 加入「OLE DB 目的地」以進行資料新增。

注意：

If you get an error, please change the data access mode on the destination adapter to "Table/View - fast load". There is an issue, because we are using a sequencer to populate the surrogate keys and the SCD wizard sets the destination by default to an access mode that does not allow a trigger with a sequencer on the destination table.

E. 執行封裝

上面程序完成後，第一次執行封裝，資料全部為新增。

隨便找出其中三筆資料。

SELECT
	SC.CustomerID,
	C.CustomerDwKey,
	C.CustomerKey,
	C.FullName,
	C.MaritalStatus,
	C.Gender,
	C.CountryRegion,
	C.ValidFrom,
	C.ValidTo
FROM dbo.DimCustomers C
INNER JOIN stg.Customer SC ON SC.PersonID=C.CustomerKey
WHERE SC.CustomerID IN (15001, 14996, 14997);

上面程序完成後，第二次執行封裝，因為來源資料都沒有變更，所以資料沒有更新。

異動上面那三筆資料，再執行封裝。

UPDATE stg.Customer
SET TerritoryID = 4
WHERE
CustomerID IN (15001, 14996, 14997);

結果會新增三筆歷程，並將已過期資料的 ValidTo 資訊補上。

使用「Set-based Update」技巧

底下練習，我們要將步驟 D1 改成 set-based update 方式。

1. create a table to store rows needed for the update

CREATE TABLE dbo.UpdateCustomers
(
	CustomerKey INT NOT NULL,
	FullName NVARCHAR(150) NULL,
	EmailAddress NVARCHAR(50) NULL
);

2. replace OLE DB Command with a OLE DB destination .

3. setting the key mapping.

4. add two Execute SQL Task , one before Data Flow to truncate UpdateCustomers, and another after Data Flow to run Merge TSQL Update Statement.

UPDATE C
SET
	FullName = U.FullName,
	EmailAddress = U.EmailAddress
FROM dbo.DimCustomers AS C
INNER JOIN dbo.UpdateCustomers AS U ON U.CustomerKey = C.CustomerKey;