不繫之舟

首頁
用 SSIS 做 Data Profiling 資料分析

如何用 SSIS 做 Data Profiling 資料分析

什麼是 Data Profiling

查資料清理 ( data cleansing ) 的過程中經常遇到 data profiling 這個詞，根據維基百科的定義，data profiling 指的是

對現有資料做統計分析，藉此觀察資料的特性與資料品質，以便評估後續資料處理時的難易
Data profiling is the process of examining the data available from an existing information source (e.g. a database or a file ) and collecting statisticsor informative summaries about that data.

─ 出自 wiki - data profiling ↗

平常做系統整合已經很習慣把資料撈出來看看長相，舉 2 個常見的例子。

  • 檢查外部系統的資料有多長，這樣我方接收資料時才不至於欄位開太小而資料被截斷。
  • 該欄是否有 NULL 值，若有的話接收方的程式要留意處理。

洋洋灑灑可以想到很多，大部份都是用很 ad hoc 的方式處理。如果要把這些檢查資料的過程變得更有系統、結構化且可重覆運行，是否有現成的工具呢 ?

這篇文章要介紹的 SSIS 內建 data profiling 元件就可以達到目的。

什麼情況適合使用 SSIS 內建 Data Profiling 元件

如果你跟我一樣工作上會用到 MS SQL Server 、想分析的資料也都存在 MS SQL 、不想額外花錢買工具或花時間寫程式，那可考慮這個內建的 SSIS 元件。

它可以直接透過 SSIS 取用，如果你沒太複雜的分析需求，想單純看看資料長相、用 UI 稍微設定一下就能得到結果，那麼可以試試看。

分析出來的東西長什麼樣子

整個結果以「左樹狀右長條」的樣子顯示。

樹狀圖是 DB object ，可以展開看各種分析；右邊則用長條圖或數字表現計算結果。若想深入瞭解的話，雙擊長條圖則可 drill down 顯現細部資料

以上圖為例，可看出 Table.Person.Address 的資料有幾個特色

  • (A) 資料長度極端值 : 最短資料 3、最長 21
  • (B) Address.City 長度分佈 : 各種資料長度的筆數
  • © Address.City 細節資料

需注意的是，這個元件只能分析 MS SQL Server 的資料，分析出來的結果是 XML 的格式，必須透過內建的 data profile viewer 來檢閱。

Profile Viewer 的兩種觀看模式

Profile Viewer 有兩種觀看模式，按左右視窗中間的 icon 可切換模式

  • 以分析為主，再看各個欄位
  • 以欄位為主，看所屬分析

這是分析為主，在左邊點選任一分析後可在右邊看到個別欄位

這是欄位為主，底下再列出該欄位的各種分析結果

大概知道分析出來的樣貌後，來看看如何設定

如何設定 SSIS Data Profile Viewer

  1. 新增一個 SSIS 專案
  2. 尋找元件。從 SSIS Toolbox > Common > Data Profiling Task。把 Data Profiling Task 拖到畫面中央的設計區
  1. 雙擊元件進行細部設定
    先看左邊的 General
A : Destination Type : 問你要用何種方式儲存分析結果，這邊選 FileConnection，並指定一個位置擺 output 的 XML 檔。

B : OverwriteDestination : 問你是否要覆蓋上一次的結果。每執行一次 data profiling 就會產生一個新的 XML 檔，若想保留最新的執行結果就把這項設為 True。

C : Open Profile Viewer : 當 data profiling 跑完後，要記得回到 Open Profile Viewer 來看分析結果。點進來就會進到上面提到的左樹狀，右長條的畫面。

若不想開 SSIS，也可以到以下路徑執行 Data Profile Viewer：C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DataProfileViewer.exe

D : Quick Profile : 罐頭設定，讓你針對單一 Table 的欄位一次設好想看的分析。

測試內建分析範本

下面示範用內建分析 AdventureWorks2012 裡的 Person table。直接把所有的選項都打勾。

設好後再執行 data profiling 元件，看到成功的標誌後記得再回到 Open Profile Viewer 查看結果。

內建分析提供的內容 - 單一欄位

SSIS 內建的 data profiling 分析可分成兩類，單一欄位與多個欄位。兩者的差別在於一次拿幾個欄位來分析。

先看單一欄位。

單一欄位 | 資料的 Null 資料百分比

Column Null Ratio Profile ：可以看出欄位資料的 Null 資料百分比，看看 Null 值過多的欄位是否有異常。

單一欄位 | 欄位統計值

Column Statistics Profile : 顯示數值型資料的最大/小值、平均數跟標準差；若資料是時間資料則顯示最大/小值。

單一欄位 | 欄位值的分佈

Column Value Distribution Profile：顯示該欄位的 distinct value，以及各個 value 的資料筆數與百分比。

以下圖為例，FirstName 裡的大宗是 Richard，想看 Richard 姓啥可以再 drill down 看細節。

單一欄位 | 欄位值的長度分佈

Column Length Distribution Profile：看該欄位 value 的長度分布。

單一欄位 | 欄位型態的分佈

Column Pattern Profile：這裡的 Pattern 指的是根據 Regular Expression 來表現資料，讓你看看是否有 pattern 異常的值，比方 email 應該包含 @，若沒有就代表是異常資料。

內建分析提供的內容 - 多個欄位

拿多個欄位分析比單欄位稍微複雜些，雖然平常下 SQL 用 distinct、group by 等等也能做到類似的分析，但當欄位一多，或好幾個欄位兜起來必須是唯一值，此時用這個元件來操作就輕鬆許多。

下面每項會先講概念再談操作。

多個欄位 | 欄位值是否唯一

打個比方，公司的員編應該不得重覆，但如果分析出來發現員編有重覆，那可能就是資料有問題了。此時在 SSIS 就可以把這種資料導到暫存區等待後續人為處理。

在 SSIS data profiling 這個分析叫作 Candidate Key Profile。

多個欄位 | 欄位值是否唯一 | 什麼叫 Candidate Key

來看看 wiki 的定義

A Candidate Key can be any column or a combination of columns that can qualify as unique key in database.

簡單地說就是一個 or 一組具有唯一性的欄位。平常我們說的 Primary Key 就是一種 Candidate Key。

看資料比較好懂。這是 AdventureWorks 裡 HumanResource 的 Department table。

以 Name 來看，可以發現都沒有重覆的值，因此 Name 可以當 Candidate Key。而以 GroupName 來看，有許多重覆值，所以它就不能當 Candidate Key。

接下來看看如何設定

多個欄位 | 欄位值是否唯一 | 如何設定 Candidate Key Profile

延續前面的 Department table 資料，雙擊 SSIS data profiling 元件進入設定畫面。

左邊選到 Profile Requests，右邊 profile type 選擇 Candidate Key Profile Request

我們打算先檢查Department.Name值是否唯一，因此 Key Column 選 Name。

設定好後執行看結果，發現 Key Strength 是 100% ，代表 Name 的資料都是 unique 、沒有重覆，是好的 Candidate Key，資料乾淨。

接著再實驗把 Key Column 從 Name 換成 GroupName，執行。

咦？跑出來怎麼沒資料呢？

原來是被 Threshold 卡住了。這裡要把 ThresholdSetting 設成 None，才能讓 Key Strength 偏低的結果秀出來。

這樣結果就出來了

Key Strength 是很低的 37.5%，代表 GroupName 不適合拿來當 Candidate Key。
再往下看 Key Violations 細節，可以看到 GroupName 並不是 unique 的，而是可以分成好幾個小群，例如有 5 筆資料都是 GroupName = Executive General and Ad. 。

所以比較起來 Name 比 GroupName 更適合拿來當資料的識別欄位，因為它具有唯一性。

多個欄位 | 欄位是否相依

什麼叫做欄位值相依？直接用例子說明。下面是示範資料，請在 AdventureWorks 裡執行 SQL 建假資料

SELECT 
 ROW_NUMBER() OVER (PARTITION BY T2.NAME ORDER BY T1.PostalCode) AS ROW_NUM
, T1.PostalCode
, T2.CountryRegionCode 
, T2.Name AS StateProvinceName
INTO TMP_ADDRESS
FROM [Person].[Address] T1
INNER JOIN Person.StateProvince  T2 ON (T1.StateProvinceID = T2.StateProvinceID)

UPDATE TMP_ADDRESS
SET CountryRegionCode = 'QQQ'
WHERE ROW_NUM = 3

SELECT * FROM TMP_ADDRESS

接著看底下資料，注意紅框部份。

正常 Alabama 應該都是對到 US，代表 Alabama 州屬於 USA。現在卻跑出一種對應到 QQQ 的情況。Functional Dependency 就是要找出這種有問題的對應。

對於事先知道有關聯性的欄位，例如 (郵遞區號, 縣市)、(國家, 首都)、代碼表等等，這個檢查可以找出是否有不合規範的髒資料跑進系統。

瞭解原理後接下來看看如何設定

多個欄位 | 欄位是否相依 | 如何設定 Functional Dependency Profile

雙擊 SSIS data profiling 元件進入設定畫。左邊選到 Profile Requests，右邊 profile type 選擇 Functional Dependency Profile Request

我們想以 StateProvinceName 為主，來看對應的 CountryRegionCode 是否正確，因此 DeterminantColumns 跟 DependentColum 請照下圖設定。

設定好後執行看結果

以 Alabama 與 US 為例，在所有 Alabama 的資料中 (7 筆)，US 的占 6 筆，QQQ 的 1 筆，因此可算出 Alabama 和 US 有依賴性的百分比是 85% 左右。

因為有 QQQ 搗亂，所以整體的 Functional Dependency Strength 不到 100%。

如果事先已經有正確的對照表，從分析結果可以知道 Alabama 跟 US 之間與代碼表不一致的髒資料。

多個欄位 | 資料查找

檢查 A 集合的資料都有在 B 集合裡面，類似 EXCEL 裡的 Lookup 。或是寫 SQL 把欄位值 distinct 出來後，用 IN 或是 EXISTS 來找對應。

多個欄位 | 資料查找 | Superset 與 Subset

先定義兩個詞
Superset : 待檢測區，也就是問題區
Subset : 正解區，提供資料供尋找的集合

以下面兩個集合為例，看得出來問題區 Superset 少了 B 這個元素
Superset (A, C, D)
Subset (A, B, C, D )

接下來示範資料，請在 AdventureWorks 裡執行 SQL 建假資料

SELECT TOP 4 GroupName INTO TMP_GROUP
FROM [HumanResources].[Department]
GROUP BY GroupName

以下圖為例，想找出 Superset 裡有哪些 Group 不存在 Subset (Department) 裡。

資料不多，用肉眼可看到 Superset 少了 Research and Development 和 Sales and Marketing

接著看看 Value Inclusion 可以如何找出這兩個漏網之魚。

多個欄位 | 資料查找 | 如何設定 Value Inclusion Profile

雙擊 SSIS data profiling 元件進入設定畫。左邊選到 Profile Requests。Profile Type 選擇 Value Inclusion Profile

這個設定的重點在 Subset 跟 Superset 要擺對位置，並設好比較的欄位。

為了避免比對的門檻太高而導致秀不出結果，Threshold 先都設成 None，之後有需要都可再調整。

設定好後執行看結果

在中間的 Inclusion Violation 可以看到 Research and Development 和 Sales and Marketing 都被找出來。當資料量大時這個分析可以幫我們節省不少眼力。

結論

玩了一下對這個工具有一些感想

好處

  • 適合熟悉且資料也都存在 MS SQL 的情況
  • SSIS 內建，設計 ETL 時可以直接取用元件來偵測髒資料
  • 想快速對 MS SQL table 做一些簡單分析時可用

不足之處

  • 產出只有 XML，較不方便將結果導去其它地方處理
  • 無法自定更多的分析，稍嫌制式