BI項目中經常會有一些提取，轉換，資料處理（ELT）的工作，其中最主要的是處理過贓資料。假設在項目中我們向資料庫中注入了測試資料，但是通過一個外鍵從另外一個表中載入資料的時候沒有對應的資料，那麼這一行就是贓資料。這時候可以使用SQL中的Sound-Ex，full-text，相似度算法等方法查找。這種政策需要花費大量的時間和精力來設計算法，測試，維護，并且它們都是基于詞彙的，複用的可能性很小。也可能你會放棄自己處理并把它抛給一些有經驗的高手專家來做這些工作，也可能你會在表中添加一些新的資料已達到外健關聯的目的，但是這種方法被稱作Lazy-add（懶惰的做法）。因為是手工添加資料難免會帶來拼寫錯誤，例如将職務名稱president錯誤地寫成平parsedent，将further錯誤地寫成future，将present錯誤地寫成parent，等等。
模糊查找和模糊分組提供一種新的方法來處理這種贓資料。這種轉換使得處理資料變得簡單，可相容，可伸縮，可複用，它可以明顯地減少誤差。如果你的表中有贓資料，或者你開始處理資料，你會使用模糊分組來找出備援資料。模糊分組會對表中的一列資料進行分析歸納出相似的并假設他們是某一個單詞的錯誤拼寫，進而計算出他們之間的相似度，利用這個相似度的資料可以 清洗表中資料。模糊分組将在下一個随筆中講述。
如果你使用模糊查找來校正資料，建議先使用關鍵詞查找，這是因為模糊查找非常耗費資源。它會在資料表和參照表中建立索引。可以儲存這些索引，但是這樣做會耗費耗費掉磁盤空間，并在運作的時候耗費寶貴的記憶體資源。通過模糊查找得到一個關鍵詞清單是一個很好的政策。通過關鍵詞查找找出出現頻率較高的關鍵詞，模糊查找再使用内連接配接來超出比對項。如果還是有些資料不能比對，将會把它标記為unknow。
模糊查找要求輸入流中至少有一列是字元串，這點和關鍵詞提取有些不同，關鍵詞提取要求是NULL-terminated Unicode 字元串。模糊查找還需要連接配接到一個外鍵表作為參照。模糊查找的輸出列如下：
- 輸入資料：這些資料包含輸入流中的資料和需要從模糊查找中傳遞的資料
- 參照表資料：這些資料包含參照表中的資料
- 相似度：這一列資料是介于0和1之間的浮點數，用來描述相似程度，相似度是1表明比對完全成功
- Confidence：這一列資料是介于0和1之間的浮點數，用來描述比對的信任程度。Confidence是另外一種形式的相似度，他不是通過一對一的比較得來，而是通過一對多的比較得來。它可以獲得更加準确的資料。
在模糊查找的編輯界面有3個标簽
- 參照表：在這個标簽内設定一個連接配接到參照表的OLE DB Connection。比較之前模糊查找參照這個表中的資料建立一個索引。在這個标簽可以選擇儲存這個索引或者使用先前運作時儲存的索引，還可以維護目前索引，這樣會删除以前的索引儲存本次運作時得到的索引。這裡要提醒的是如果處理的資料量很大，索引也會變得很大。
- 列：在這個标簽内設定輸入資料流中列和參照表中的一列的映射。用滑鼠拖拽的方法将他們連接配接起來。還可以在輸出資料流中添加一個外鍵列，隻需要在Available Input Columns中選擇這個列舊可以了。如圖1是設定之後的界面 圖1
SSIS進階轉換任務—模糊查找
- 進階：這個标簽内設定查找算法。Maximum number of matches to output per lookup設定每一行資料最多可以有多少個比對。預設的值是1，如果設定大于這個值，結果中将産生更多的行，但是如果輸入流資料中有很多類似的資料這種設定還是需要的。Similarity threshold用來設定相似度閥置。Token delimiters用來設定字元分割符，預設的分割符是常見字元串分割符，如圖2 圖2
SSIS進階轉換任務—模糊查找
盡管模糊查找通過一些簡單的設定來實作複雜的邏輯，結果頁并不是完美的。需要花費一些時間來觀察不同設定産生的不同結果。
下面來做一個練習。這個練習用來展示模糊查找的功能，使用一個含有職位名稱的參照表，然後輸入一個個人資訊表，當然個人資訊中包含這個人的職位，但是并不是所有的都能和參照表比對，模糊查詢将會找出比對，然後在在目标表中檢視比對結果。
- 使用下面的内容建立一個文本檔案并命名：c:\import\empdata.txt，這個表包含的内容是個人資訊。我們可以看到這個表中的資料參差不齊，這些是ETL過程中常見的情況。
EMPIDTITLE LNAME
00001EXECUTIVE VICE PRESIDENWASHINGTON
00002EXEC VICE PRES PIZUR
00003EXECUTIVE VP BROWN
00005EXEC VP MILLER
00006EXECUTIVE VICE PRASIDENSWAMI
00007FIELDS OPERATION MGR SKY
00008FLDS OPS MGR JEAN
00009FIELDS OPS MGR GANDI
00010FIELDS OPERATIONS MANAGHINSON
00011BUSINESS OFFICE MANAGERBROWN
00012BUS OFFICE MANAGER GREEN
00013BUS OFF MANAGER GATES
00014BUS OFF MGR HALE
00015BUS OFFICE MNGR SMITH
00016BUS OFFICE MGR AI
00017X-RAY TECHNOLOGIST CHIN
00018XRAY TECHNOLOGIST ABULA
00019XRAY TECH HOGAN
00020X-RAY TECH ROBERSON
- 在資料庫AdventureWorks中使用下面的語句建立一個參照表
CREATE TABLE [Occupation](
[OccupationID] [smallint] IDENTITY(1,1) NOT NULL,
[OccupationLabel] [varchar] (50) NOT NULL
CONSTRAINT [PK_Occupation_OccupationID] PRIMARY KEY CLUSTERED
(
[OccupationID] ASC
) ON [PRIMARY]
GO
INSERT INTO [Occupation] Select 'EXEC VICE PRES'
INSERT INTO [Occupation] Select 'FIELDS OPS MGR'
INSERT INTO [Occupation] Select 'BUS OFFICE MGR'
INSERT INTO [Occupation] Select 'X-RAY TECH'
- 建立一個package命名為FuzzyLookupExample，拖放一個Data Flow task輕按兩下進入Control Flow設計界面。
- 建立一個Flat File Connection命名為Employee Data，将資料源設定為c:\import\empdata.txt，Format屬性設定為Ragged Right，選中Column names in the first data row。如圖3。點選Columns标簽，在Source data columns文本框内點選選擇第5行和第28行，如圖4，點選Advanced标簽設定TITLE列的資料長度是50，如圖5 圖3
SSIS進階轉換任務—模糊查找 圖4 SSIS進階轉換任務—模糊查找 圖5 SSIS進階轉換任務—模糊查找 - 添加一個Flat File Data Source，使用Employee Data連接配接。添加一個OLE DB Destination選擇AdventureWorks資料庫
- 添加一個Fuzzy Lookup将Flat File DataSource和它連接配接起來，再将它和OLE DB Destination連接配接起來
- 輕按兩下打開Fuzzy Lookup的編輯界面，在Reference标簽界面内，設為AdventureWorks資料庫中的Occupation表如圖6，在Columns标簽内将輸入表中的TITLE列和參照表中的OccupationLabel标簽連接配接起來，如圖7，Advanced标簽界面中設定相似門檻為0.5，如圖8 圖6
SSIS進階轉換任務—模糊查找 圖7 SSIS進階轉換任務—模糊查找 圖8 SSIS進階轉換任務—模糊查找 - 輕按兩下打開OLE DB Destination的編輯界面，輕按兩下New檢視動态産生的表，修改表名為FuzzyResults，點選Mappings标簽，預設所有設定
- 在Fuzzy Lookup和OLE DB Destination之間加一個類型為grid的Data View
運作這個package，Data View中的資料如圖9，最終表中的資料也是這樣子的。我們可以看到在設定相似門檻為0.5的情況下所有輸入資料都被比對，表中也将OccupationID附帶進來。如果使用INNER JOIN來選擇的話隻能找到4行完全比對的資料，就是在表中similarity值是1的4行資料
圖9
作者：
Tyler Ning出處：
http://www.cnblogs.com/tylerdonet/本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，如有問題，可以通過以下郵箱位址
[email protected]聯系我，非常感謝。