微軟BI 之SSIS 系列 - 使用 SQL Profilling Task (資料探測) 檢測資料來源資料

標籤：

開篇介紹SQL Profilling Task 可能我們很多人都沒有在 SSIS 中真正使用過，所以對於這個控制項的用法可能也不太瞭解。那我們換一個講法，假設我們有這樣的一個需求 - 需要對資料庫表中的一些資料做一些資料分析，比如統計一下資料表中各列中實際資料的長度，各長度區間範圍；比如統計一下各資料列中非空欄位的比例，表的行數，重複欄位等等。那麼如果不是專門做過這種資料來源資料分析的話，可能不知道用什麼方式能夠非常快的得到這些資訊。寫 SQL 陳述式？我想這個過程也是非常耗費時間和精力的。實際上，在 SSIS 2012 中有這麼一個控制項 SQL Profilling Task 可以協助我們做到這些事情，對資料來源的資料快速的做出分析。能夠非常快速的弄清楚我們面對的資料來源是什麼樣的一種特徵，這些將在實際 ETL 處理過程中對我們 BI 開發人員是非常有協助的。並且，還有一種情況，我們可以將這種資料分析的結果直接在 SSIS 中通過郵件發送給相關的資料庫管理員，或者資料分析和管理員，那麼他們對這種資料的瞭解會更加清晰，更早的判斷出資料來源中的資料異常等問題。SQL Profilling Task 的初步體驗建立一個包，並建立一個 ADO.NET 連結管理器，這次我們的測試來源物件可以換成任意一個資料庫，那我這裡把資料來源換成 AdventureWorkLT2012 資料庫。注意：在這裡必須使用 ADO.NET 連結管理器，這是一個特別的限制，並且只支援 SQL Server 版本。另外一個就是必須對 tempdb 資料庫有讀寫權限，因為這裡面有大量的計算，摘要彙總等過程。拖放一個 SQL Profilling Task到控制流程中。雙擊編輯，第一件要做的事情就是建立一個 XML 檔案來儲存 SQL Profilling Task 對指定資料來源，表的分析結果的檔案。OverwriteDestination 可以每次指定讓它覆蓋掉以前的舊檔案，但是另外一種方式就是根據執行日期去產生不同的檔案，關於這一點在前面很多例子中就已經提到了。選擇 Quick Profile 在這裡選擇好之前建立好的 ADO.NET 連結管理器，並選擇表或者視圖； 我們在這裡首先配置的是表 Product，配置完成之後對於後面所有的表是都可以看的到的。上面配置完成後，將跳轉到 Profile Requests 處，在這裡需要選中每一種 Profile，然後根據需要對每一張表的全部欄位 (*) 或者某一個欄位做 NULL 值檢查。裡面的每一項 Profile Type 都是進行差異化配置的，即不同的表，不同的列可以配置或者不配置，預設情況下都是 Product 表的配置。這一點體驗是不太好的，就是如果我需要重新添加一張新的表的話，我們需要建立新的 Profile Type - 在 Profile Type 下面點擊空白的地方新建立一個 Profile Request。我們繼續為 SalesOrderHeader 表的 AccountNumber 做出空值統計處理。儲存之後運行包，並開啟產生之後的 XML 檔案。很明顯，這個 XML 檔案即使拿到手也是看不出來什麼的，正確的開啟檔案應該是通過 SQL Server 2012 內建的 Data Profile Viewer 工具來查看。開啟 Data Profile Viewer 之後載入包產出的 XML 檔案，可以看到圖形化的分析結果。以 Product 表中 Color 為例，NULL 值的條數有50條，佔了總行數的 16.94 %。我們來驗證一下結果，發現和上面的分析結果是一致的。下面就對這幾種 Profile Type 做一個詳細的解釋。單列統計類型Null Ratio Profile (NULL 比率統計)一般用來檢查在資料列中 NULL 值的所佔比例，比如說通過分析的結果發現某一列的 NULL 值所佔比較過高，可能超過了預期，那麼這種情況下是不是資料不太正常，丟值太多。比如在客戶關係管理系統中，作為非常重要的連絡方式 - 手機號碼，電子郵件，地址等。對於這三者來說 NULL 值所佔的比例應該手機號碼最小，地址和電子郵件其次，並且手機號碼的 NULL 值比例應該非常非常小才是正常的。如果作為重要分析因素的手機號碼的 NULL 值比例非常高，這時就說明我們的資料來源資料存在一些問題，那麼就需要引起注意。有可能是業務操作不規範，也有可能是資料來源準備資料的時候出現問題。比如在下面的幾個列中， Color, Size, Weight 就可以通過 SQL Profilling Task 擷取到具體的 NULL Ratio 比例。可以看出資料來源中的 Weight, Size, Color NULL 值比較從高到低，Weight 列種有97條空值，所佔比例 32.88%。至於這個資料是否合理就需要和業務人員來確定了，也就是說我們在設計我們的資料倉儲時，做為資料分析的依據時這些資訊是事先就應該要瞭解的。 Column Length Distribution Profiles ( 列長度分布統計資料 )對於列中的資料長度去重並進行統計各長度所佔全部資料行的個數與比例。這個 Profile 有助於協助我們瞭解，第一是業務資料來源中的資料長度定義是否是合理的，比如說業務資料來源中的這一列在曆史 5 - 10 年中的資料長度最長就是 35，但是定義了255 的長度。那麼我們在資料幫浦的時候，來定義我們表列的長度時這個就是依據，因為我們發現曆史上所有的資料長度都沒有超過 35。第二是可以查看到異常資料，比如社會安全號碼碼一般預設大概是 18 位，那麼在這個圖中就可以非常容易的看出來是否存在異常資料，比如說有大量的超過 18 位，或者小於 18 位的資料。比如中，Name 列中最小長度是5，最大是 32，長度為 23 的資料佔據比例最高。當然在設計長度檢測時候，對於空格的處理也是非常方便的。IgnoreLeadingSpaces - 是否忽略開通的空格，IgnoreTrailingSpaces - 是否忽略結尾的空格。Statistics Profiles (列統計資訊)這種統計是非常有意義的，特別是對於數字，日期類型的欄位，可以非常直觀的看到它們的最大，最小，平均值，以及標準差值（時間只有最大，最小）。通過這些統計可以看到資料是否存在異常情況，比如價格的最大和最小值會不會出現負值，最大值是否偏離的離譜等等。關於這裡面的平均值 Mean 還有 Standard Deviation 請參考統計學中的平均值和標準差等計算方式。Value Distribution (列值分布統計)去重之後列的值分布情況，那麼這種統計一方面可以協助瞭解來源資料中有哪些資料可以作為分類出現，哪些資料基數很大不適合做這種分類。另外一個，這種統計也可以協助我們鑒別列中資料的品質。比如說中國的省份經值分布統計之後發現多達上百個，那麼這種資料是有問題的，會非常容易的看出來。以為例，可以非常明顯的看到各個列中值的分配情況，像 Color 這種列是非常適合作為維度一個屬性用來分析事實表中的資料的，但是像 ProductNumber 這種基數太大不適用用來分析事實資料。並且在 Color 列中也可以非常直觀的看到哪一種顏色的產品目前最多，所佔的比例是多少都非常清楚。Pattern Profiles(列模式，Regex分配統計)為字串類型的資料提供正則統計，比如郵件地址的正則一般只有有一種Regex，如果看到的是其它的運算式有可能說明以前的驗證是錯誤的。還有就是通過對統計資料的分析可以看到哪一種運算式所佔據的比例多，高，越多越高的說明這種比例應該是正常的，可以考慮以後所有的驗證按照這一種走。至於其它的可以考慮如果是業應用中修改，或者在抽取資料的時候認為這種資料是異常的，是需要處理的。例如中產品 Size 大小的運算式正則為 \d\d，佔據了 84% 的資料比，說明大多數 Size 的內容都是數字類型的，但是也有一些填寫的值是 M, L 這種字元類型的；那麼像這種資料再抽取到資料倉儲之前就可以發現，所以才有時間來考慮這種少量的非規則的資料應該如何來處理了。多列或表層級探測類型Candidate Key Profile (候選主鍵探查)對於選擇要探測統計的列進行統計，看看它是否符合主鍵的特徵，或者近似主鍵。比如說，我們可能覺得某一列應該作為主鍵，但是最終統計下來發現因為存在重複值只能作為近似主鍵存在，那麼在選擇時候就要注意了。比如在中的這三列都是非重複列，100%的符合了主鍵的特徵，因此這幾列可以作為主鍵候選列來考慮。當然最終如何選擇，這個分析只能是一個參考，比如我們還需要考慮欄位的類型，長度等因素，盡量選擇整型資料；即使是字串的話，也應該選擇長度更短的列。但是這個值是不是 100% 完全與實際相符的，不一定，這裡的顯示和配置 Candidate Key Profile 時有關。比如在選擇 Specified 的時候就可以列出所有的列 (*)，當非重複率達到 95%以上的時候就應該認為是 100%了。而在選擇 None 的時候這時需要選擇到固定的列，或者一個列兩個列組合形成。Functional Dependency Strength Profile (函數依賴關係統計)可以這麼來理解這個統計所做的事情，比如說在一個銷售統計裡，有國家，省份和城市，一個城市必定屬於一個省份，而一個省份也必定屬於一個國家。如果在資料中比如說我們發現了城市，例如杭州屬於浙江的同時，它在另外的一條記錄中也屬於了省份江蘇，那麼很明顯這個資料是有問題的。函數依賴關係就可以檢測這種依賴關係，比如在 1000 條帶有杭州的資料中，有 999個杭州的資料對應的省份是浙江，有1個不是，那麼杭州對浙江的依賴比率應該達到 90%以上了。比如下面這幅圖中，SalesPerson 對 CompanyName 的“依賴”，CompanyName 對 SalesPerson 的"決定性"達到了 99.76%。換句話說，只要知道了 Company Name 就有 99.76% 的可能能夠確定它的 Sales Person 是誰? 只要知道了城市是杭州，那麼就有百分之多少的可能知道省份是誰，當然城市與省份之間的決定應該達到 100%。在 Friendly Bike Shop 中有 4 條資料，其中兩條是能夠決定 Sales Person 是  adventure-works\david8，另外兩條不是的，因此這種依賴率是 50%，但是整體上所有的資料這種依賴率是 99.76%。Value Inclusion Profile 值包含統計主要用來計算兩個列之間或者多個組合列之間列值的重合情況，通過 Value Inclusion 值統計可以大概決定比如兩個表之間的列是否適合建立外鍵的參考關聯性。或者，從另外的一個角度來說，如果這一張表的某列引用了另外一張表，形成了理論上的這種外部索引鍵關聯；那麼通過值包含統計就可以看出來這種關聯是否正確，比如 B 表的某列的值都是來源於 A 表的某列，但是通過探測發現 B 表某列的值在  A 表中是找不到的。因為在這裡沒有特別好的例子，在已經建立好外鍵關係的表中這種探測肯定是沒有任何問題的，所以我們在這裡只是來理解一下 Value Inclusion 的用法即可。我們來看看 Product 表中的 ProductCategoryID 對 ProductCategory 表中的 ProductCategoryID 的引用情況（值包含情況）。最後的結果不出意料基本上就是 100%的關聯率。總結在 BI 系統中，最重要的一個環節莫過於 ETL 了，而資料來源又與 ETL 的設計與開發息息相關。特別對於一些沒有良好維護的資料來源，其品質的好壞將在很大程度上影響 ETL 的設計。因此理解好資料來源，分析好資料來源的資料對後期的 ETL ，表的設計起著非常重要的作用。 我們可以通過 SQL Profilling Task 對我們的資料來源有一個大致的瞭解，通過以上的這幾種方式的分析，基本上可以滿足絕大部分資料探測的需要。特別對於那種應用系統邊升級，BI 系統邊升級的的項目，應用系統本身並不健全的情況下，其資料的品質上也可能是存在很大問題的，這樣就給我們的 BI 設計帶來了很多不不好的影響。所以在一些 ETL 設計中，我們甚至可以採用在 SSIS 中通過郵件周期性的發送這種探測報告給 BI 系統維護人員，盡量地做到周期性的對來源資料的預防性檢查工作，提前避免一些不規範的資料帶來的意外後果。 最後提一下，還有一件事情要做! 如果老是去分析這個 XML 是不應該的，因為這種資料都不能集中呈現曆史資料的。因此最好的方式是解析 XML 檔案，將資料解析的結果儲存到資料庫中，自己用報表的形式來呈現。這樣不光可以看到當前的結果，而且還可以對比之前所有的曆史結果，做到對資料來源的探測管理 - 資料來源的監控管理也是屬於 BI 監控管理的一部分，關於這一部分就不詳細展開了！

更多 BI 文章請參看 BI 系列隨筆列表 (SSIS, SSRS, SSAS, MDX, SQL Server)  如果覺得這篇文章看了對您有協助，請協助推薦，以方便他人在 BIWORK 部落格推薦欄中快速看到這些文章。

微軟BI 之SSIS 系列 - 使用 SQL Profilling Task (資料探測) 檢測資料來源資料