Lab5：資料流程設計2

前一個練習中，我們已經可以將資料 ETL 到倉儲資料庫。 在這個練習中，我們將變換一些轉換技巧，來提升整個 ETL 轉換過程的執行效率。

Enhancing Extract Data

在上一個Lab中，我們由二個 OLE DB Source 取出資料，先由 Sort Transformation 排序，再經由 Merge Join Transformation 來合併二個資料來源。 我們現在將這些事，全部交由資料庫自已來處理，除了簡化步驟，也可以提升執行效率。

1. 更新資料來源

1.1 將下圖紅色區塊整個刪除

1.2 加入新的資料來源

1.3 設定新的資料來源

開啟 OLE DB 資料來源的編輯視窗，輸入以下 SQL 命令：

2. 設定排序後再輸出

開啟 OLE DB 資料來源的進階編輯視窗，在 OLE DB 來源輸出節點上，將 IsSorted 屬性設為 True。

3. 設定排序欄位

開啟 OLE DB 資料來源的進階編輯視窗，在輸出資料行中，找到你要排序的欄位，並將 SortKeyPosition 屬性設為 1。

• 0：預設值，不使用該欄位排序。
• 1：該欄位當做第１個排序欄位，且使用升冪。
• -1：該欄位當做第１個排序欄位，且使用降冪。
• 2：該欄位當做第２個排序欄位，且使用升冪。
• -2：該欄位當做第２個排序欄位，但使用降冪。

4. 設定流程方向

將流程方向重新設定過後，執行這個新的package，就可以得到和前一個練習相同的結果。

Enhancing Lookup Data

在 Lab4 中，我們用了一個 Lookup Transformation 去查詢 stg.CustomerInformation ，然後分別將二個比對結果再 Union 起來。 你也可以直接用一個輸出結果來達到這樣子的效果。

1. 調整流程圖

調整如下：

2. 變更 Lookup Transformation 的設定

將查詢轉換的無相符資料列的處理方式改成「忽略失敗」。

3. 設定衍生資料行轉換

由於無相符資料列的處理方式已改成「忽略失敗」，所以這些資料列的相關欄位都會被塞 NULL ，我們可以使用以下的判斷式來處理這些欄位。

Enhancing Update Performance（Using Execute SQL Task）

在 Lab4 中，我們用了一個 OLE DB Command Transformation 來更新資料， 由操作過程可以看的出來，這個元件是依結果集的大小，跑迴圈去執行 Update 或 Delete 指令，結果集有幾筆，就執行幾次指令，這種方法就是所謂的「Procedural 」方式，這是一種效能較差的方法。 比較好的方法是使用「Set-Based」方式，這種方法是使用 TSQL 對整個資料庫進行批次更新或刪除。

底下我們將練習如何採用 set-based update 方式來提升更新作業的效能。

1. 建立儲存更新資料用的 stage table

在 MyDW 中建立一個用來儲存更新資料用的 stage table ，它的結構要類似 DimCustomers 。 只需要儲存要用的欄位即可，例如 DimCustomers 中的 surrogate key 在這裡沒有用，就可以不需要。

CREATE TABLE stg.DimCustomersUpdate
(
	CustomerKey INT NOT NULL,
	FullName NVARCHAR(150) NULL,
	EmailAddress NVARCHAR(50) NULL,
	BirthDate DATE NULL,
	MaritalStatus NVARCHAR(3) NULL,
	Gender NVARCHAR(3) NULL,
	Education NVARCHAR(40) NULL,
	Occupation NVARCHAR(100) NULL,
	City NVARCHAR(30) NULL,
	StateProvince NVARCHAR(50) NULL,
	CountryRegion NVARCHAR(50) NULL,
);

2. 調整資料流程

2.1 刪除前例中的 OLE DB Command Transformation ，再加入一個 OLE DB Destination 。

2.2 設定 OLE DB Destination 連接

2.3 設定 OLE DB Destination 對應

3. 調整控制流程

上一步驟後完成後，若是新資料則會被轉移至 dbo.DimCustomer 資料表，而舊有的資料則會被轉移至 stg.DimCustomerUpdate 資料表。 現在，我們可以在 Control Flow 中，加入一個 Execute SQL Task ，來進行更新資料庫操作。

3.1 加入一個 Execute SQL Task

3.2 設定連接與執行命令

SQL Statement

UPDATE C
SET 
	C.FullName = CU.FullName,
	C.EmailAddress = CU.EmailAddress,
	C.BirthDate = CU.BirthDate,
	C.MaritalStatus = CU.MaritalStatus,
	C.Gender = CU.Gender,
	C.Education = CU.Education,
	C.Occupation = CU.Occupation,
	C.City = CU.City,
	C.StateProvince = CU.StateProvince,
	C.CountryRegion = CU.CountryRegion
FROM dbo.DimCustomers C
INNER JOIN stg.DimCustomersUpdate CU ON C.CustomerDwKey = CU.CustomerKey
;
TRUNCATE TABLE stg.DimCustomersUpdate
;

4. 執行轉換

以下是執行結果