Lab13：資料清理

在 Lab12 中我們已經建立好知識庫，現在我們要建立一個「DQS 專案」，並使用該知識庫來進行資料清理的工作。

除了 DQS 專案可以用來分析資料品質，另外，如果在 SSIS 封裝中，你也可以利用「 Data Profiling Task 」來分析資料的分佈狀況，以判別是否有淺在的資料品質問題。

如何使用 DQS Project 清理資料

0. 建立清理用的測試資料

在 DQS_STAGING_DATA 資料庫中，我們建立一個檢視，來取得一個要被清理的客戶資料清單， 在這個 View 中，我們另外加入了二筆含有不正確資訊的資料，以便利用知識庫來進行驗證。

USE DQS_STAGING_DATA;
GO

CREATE VIEW dbo.vCustomersDirty
AS
SELECT 
	C.CustomerKey,
	C.FirstName + ' ' + c.LastName AS FullName,
	C.AddressLine1 AS StreetAddress,
	G.City, G.StateProvinceName AS StateProvince,
	G.EnglishCountryRegionName AS CountryRegion,
	C.EmailAddress,
	C.BirthDate,
	C.EnglishOccupation AS Occupation
FROM AdventureWorksDW2012.dbo.DimCustomer AS C
	INNER JOIN AdventureWorksDW2012.dbo.DimGeography AS G ON C.GeographyKey = G.GeographyKey
WHERE C.CustomerKey % 10 = 0

UNION

SELECT 
	-11000,
	N'Jon Yang',
	N'3761 N. 14th St',
	N'Munich',		-- wrong city
	N'Kingsland',	-- wrong state
	N'Austria',		-- wrong country
	N'jon24#adventure-works.com', -- wrong email
	'18900224',		-- wrong birth date
	'Profesional'	-- wrong occupation

UNION

SELECT -11100,
	N'Jacquelyn Suarez',
	N'7800 Corrinne Ct.', -- wrong term
	N'Muenchen',		-- another wrong city
	N'Queensland',
	N'Australia',
	N'jacquelyn20@adventure-works.com',
	'19680206',
	'Professional';

1. 新增 DQS 專案

新增一個 DQS 專案，並設定選用前一個Lab所建立的[知識庫]，並將[活動]設定為「清理」。

2. 清理資料設定

這個步驟中，主要有二部份要設定：一是設定要被清理的來源資料庫，另一是設定要清理的資料行與其相對應的定義域。

3. 啟動清理

清理結束畫面：

4. 管理和檢視結果

管理和檢視結果中包含五個索引標籤：

• 建議（Suggesion）：該值的信賴等級高於自動建議臨界值，但低於自動更正臨界值。
• 新增（New）：DQS 沒有足夠的資訊，因此無法對應至其他任何索引標籤。
• 無效（Invalid）：不符合定義域規則的值。
• 更正（Corrected）：該值的信賴等級高於自動更正臨界值，所以在自動清理程序期間會自動更正定義域值。
• 正確（Correct）：顯示已發現正確的定義域值。

底下分別就各個定義域來看看這個例子的清理結果：

BirthDate

在 BirthDate 定義域中，發現有1662個值，其中1661是符合定義域的資料。 不過這些數值都不在知識庫中，所以全部列在[新增]頁籤中。 你可以按下[Approve All Terms]，將所有項目全部核准。

另外一個是無效值。（invalid value）

你可以在 Correct To 欄位中輸入正確的值。然後按下［核准］，該項目就會放到正確

最後所有資料就重新歸在[正確]頁籤中。

City

在 City 定義域中發現 2 個值應該要［更正］，且與定義域值達 100% 信賴度。而其他的值都已經在 KB 中，所以 DQS 將他們標示為［Correct］。

StreetAddress

在 StreetAddress 定義域中，我們有定義了以詞彙為主的關聯，希望找出「Ct.」並取代成「Court」。 而且在我們的來源資料庫中，的確也包含這樣子的資料，但是 DQS 專案為什麼沒有找出來放到［更正］頁籤中呢？ 這是因為這種更新不是整個值更新，而只有取代部份詞彙，所以它還是被歸在［新增］頁籤中。

你可以在搜尋欄位輸入7800，就可以看到「7800 Corrinne Ct.」已被更正為「7800 Corrinne Court」，且信賴度為 100% 。

State

先前的Lab中，我們已針對 vCountryStateCity 進行資料探索，得到一些定義域值。 現在這個清理程序中，總共發現 35 個定義域值，其中 1 個是新的。

「Kingsland」這個值並沒有不符合任何規則，和其他值也沒有相似度，所以就被歸類在［新增］。

Country

在 Country 定義域中發現一個［建議］值。 「Austria」這個值與已知的值「Australia」有 70% 信賴度，所以所以就被歸類在［建議］。

你可以在該筆資料列中點選「Approve」功能來核准單筆資料，核准後就改歸到［更正］。

EmailAddress

在 EmailAddress 定義域中發現一個無效值。

Occupation

在 Occupation 定義域中發現所有值都是新的。 不過，在新增頁籤中，你可以發現「Profesional」有紅色變曲底線，這是因為該定義域有使用拼字檢查功能。 在［Correct To］中輸入「Professional」，此時，因為是手動更正資料，所以信賴度會設定成 100% 。

按下［Approve］後，該筆資料就會改到［更正］頁籤中。

5. 匯出

完成以上步驟後，你可以先預覽輸出資料，再將資料匯出。

下圖是匯出的資料，如不需匯出，執行完成即可。

使用 Data Profiling Task 分析資料

上面提到的「資料品質專案」，它會透過知識庫的內容來協助分析資料，以達到資料品質的目的。 其實你也可以使用任何 SQL 所提供工具程式（如 T-SQL 查詢）來分析資料，進而改善資料品質。 或者在 SSIS 封裝中，使用 Data Profiling Task 來分析資料。 下面練習就是示範如何使用 Data Profiling Task 來分析資料？

如何使用「資料分析工作（ Data Profiling Task ）」分析資料

這個練習示範如何使用 Data Profiling Task 找出前一個練習 vCustomersDirty 檢視中不正確的資料。

建立 SSIS 封裝專案

建立一個 SSIS 專案，並在封裝中加入一個 Data Profiling Task 。

設定 Data Profiling Task

在 Data Profiling Task 的編輯器中的[一般]頁籤中，將目的地的[目的類型]設為[FileConnection]， 並輸入一個檔名，用來儲存輸出；同時將 OverwriteDestination 設為 True, 讓 package 重複執行時，可以自動覆寫輸出檔。

設定[分析類別]（Profile Type）

點繫右下角的［快速分析］按鈕，設定一個新的 ADO.NET 連線，連接到 DQS_STAGING_DATA 資料庫，並選擇 vCustomersDirty 檢視表。如下圖：

設定好之後，你可以在[設定檔要求]頁籤中看到以下內容：

設定分析檔[要求屬性]（Profile Requests）

依下表，設定各個分析檔的要求屬性：

• 將 Column Value Distribution Profile Request 的 Column 屬性，由 (*) 變更成 Occupation （也就是只分析這個欄位），同時將 ValueDistributionOption 屬性變更成 AllValues

  

• 將 Column Pattern Profile Request 的 Column 屬性，由 (*) 變更成 EmailAddress 。

執行工作

執行工作，結束後檢查看看是否有產生輸出檔。

如何使用「資料分析檔檢視器」檢視結果（Data Profile Viewer）

接下來，你可以使用「資料分析檔檢視器」（Data Profile Viewer）來檢視上面例子中 Data Profiling Task 所產生的檢查結果。

開啟「資料分析檔檢視器」

你可以在功能選單中，或者 Data Profiling Task 的編輯器視窗中找到這個工具。

檢視 Occupation

「資料行值散發設定檔」會報告選取之資料行中的所有相異值，以及該資料表中每個值所代表之資料列的百分比。 你可以依下圖中的步驟操作，以檢視這個報告。

這個「資料行值散發設定檔」的報告中，顯示了 Occupation 共有 6 個相異值，其中 5 個值都被大量使用，但是 Profesional 這個值卻只被使用一次。 像 Profesional 這種特別奇怪的分怖狀況，是異常資料的機會就比較大。

檢視 EmailAddress

資料行模式設定檔會報告一組規則運算式，其中涵蓋了字串資料行中值的指定百分比。 你可以依下圖中的步驟操作，以檢視這個報告。

這個「資料行模式設定檔」的報告中，顯示了 EmailAddress 欄位共涵蓋了 2 個正規表示式。

• 管理資料品質專案
• 資料分析工作的設定
• 使用 DQS (內部) 知識清理資料