You have a need to match unsanitised data whether it be person names or transaction descriptions, to a list of sanitised reference data for easier analysis. Your transaction data column will only partially match on your reference data column thus the word “Fuzzy”. You only have standard edition of SQL Server so Lets talk Excel goodness.
First lets go back 12 years or so when Excel was a dirty word…proliferation of Excel spreadsheets, multiple versions of the truth throughout the organisation and a business rules tier that was hidden on someone’s hard drive with no backup or business continuity process. Now Excel is the comeback kid in the red corner of the ring for managed Business Intelligence.
To tackle the issue of fuzzy data matching, we might normally utilise the SQL Server Integration Services SSIS Fuzzy Lookup task to achieve this. It will work nicely, except when you don’t have Enterprise or Developer editions (Fuzzy components don’t even come with the new Business intelligence edition).
We turn our hand at Excel (the future of business friendly data integration and analysis) and utilise the fuzzy lookup add-in. Its free
http://www.microsoft.com/en-us/download/details.aspx?id=15011 and easier to use than the SSIS alternative.
Simply download the plugin, run the setup, then next time you open Excel 2010 you will get a prompt to install which add a Fuzzy Lookup tab to play with.
Your worksheet should have at least two worksheets to begin with, a list of dirty unsanitised data, and a list of clean reference data to match against. In fuzzy lookup terminology these two tables will be referred to from now on as the Left (dirty) and the Right (reference) table respectively.
Select a new (third) worksheet, then select the Fuzzy Lookup tab and click the Fuzzy Lookup button at top left of screen. This will launch a Fuzzy Lookup workspace where you can create your transformation.
You now need to tell the Fuzzy lookup engine what a partial match will look like and how it will behave. Clear the existing Match columns, then select the column in the Left Columns list you want to match or cleanse against to the column in the Right Columns list (your reference table). For name matching this could be more than 1 column however this example assumes only a one column match. Select the column from your clean reference worksheet in the Right Columns box.
Move the Similarity Threshold Slider to the desired match score which is how strict you the match to be (1 is the strictest i.e. exact match), less than 1 is a partial match. Select the number of matches you want to return (default is 1).
Adjust the Output columns as required which are the combination of columns from your two tables, that will be returned in the new third worksheet.
Once the fuzzy transformation completes you can use this data for further analysis in your Powerpivot database. Convert your new Fuzzy output data to an Excel table (Ctrl+L) then you will be able to create a Linked table into Powerpivot where the data can be further analysed.
The Fuzzy lookup add in for Excel provides a free means of performing fuzzy lookup transformations on data. This will work well for manual executions, however should you require a seamless automated mechanism, you may want to look at SSIS components (either the Fuzzy components or third party SSIS add-ons). The Fuzzy lookup add-in will also support several match criteria.