Back to List

My Memory is a Little Fuzzy: Things I Want to Remember When Setting Up Fuzzy Lookups

Jared Kuehn Jared Kuehn  |  
Jun 08, 2017
 
In my last post, I explained why I would want to use Fuzzy Lookups for my various matching needs. Now I want to explain the how: How does one set up a Fuzzy Lookup so that it helps solve my data quality problems?

The simple answer is that I use the available SSIS Fuzzy Lookup component in a Data Flow task. There are various documents and blogs that explain the basics of adding a Fuzzy Lookup component to your process, so I will let those speak for themselves. Here I want to cover the things I’ve learned that most other writings don’t cover. Once you have the basic architecture set up, there are several things you should consider, both in your process and in your data.

Here are some tips to jog your memory:
  1. Formatting your datasets will return higher similarity. Like the Lookup transformation in SSIS, the Fuzzy tools may be case sensitive. That means that ‘Johann’ and ‘johann’ will not be exact matches. To make more accurate matches, it is best that you format both sets of data under the same rules (lower case, remove spaces, etc.). If you are comparing text data that may come in different formats, be sure to either remove or standardize any formatting that could cause unnecessary differences. An example is phone numbers: sometimes they include dashes (123-456-7890) and parentheses, but other times they will be omitted (1234567890). You may be able to improve the column similarity with the token delimiter settings, but from what I’ve tried, if one dataset has the delimiter and one does not, this won’t allow you to find an exact match.
  2. Overall similarity = Sum(each column’s unique similarity) / Number of columns being compared…sort of. As I’ve written before, each column you analyze will have its own similarity percentage calculated. If you then take those percentages, add them together, and divide by the number of compared columns, you get the overall similarity…sometimes. I haven’t figured out yet what all goes into the overall similarity calculation, but at least this will give you a general idea of what similarity to shoot for when defining your threshold.
  3. In Fuzzy tools, NULL will match to NULL. That’s right. The absence of a value will match exactly to the absence of a value, which is different from how SQL Server handles this (by default settings). To get around this, I recommend that you set one of your datasets (most likely your reference index) so that every column has a default value other than NULL. When selecting your default value, make sure it is something that is not likely to find a match in the other dataset (i.e. using ‘Smith’ for a default in a Last Name column may not be wise).
  4. You can set individual columns to either match Exactly or Fuzzy. It took me a while to find this in the Lookup settings, but I consider it a useful find. In the same way that you receive a similarity score for each column that is compared, you can also define whether a column should match exactly, or if it can match fuzzy. You can set similarity thresholds on each column as well. One word of caution: If you set a column as an exact match, you will only find a match when that column matches. If the other columns match, but that column does not, you will not find a match. I would only use this when I am 100% confident in the accuracy of the data in that column, both in the source and reference index.
  5. Only strings (Unicode or non-Unicode) can be evaluated with Fuzzy logic. Fuzzy Lookup looks for similarities in two data sets. That said, I don’t find it surprising Fuzzy logic is used on string columns, which have the highest chance of being incorrect. I do find it surprising that Fuzzy logic is used only on string columns.  You can evaluate other data types as well within a Fuzzy tool, but they will automatically be set to Exact. If you wish to evaluate these columns as Fuzzy, a workaround is to convert non-string columns into string columns. I typically do this in my Fuzzy Lookups.
  6. Make your comparison set as wide as possible. The goal with Fuzzy logic is to find accurate matches based on similarity of entire records, not just exact matches based on a narrow set of fields. The more fields you add to your comparison, the better chance you will find an accurate match if only 1 or 2 fields don’t match. It also reduces the chances of finding a false positive match because incorrectly inputted data matched to something it should not match to.
  7. Check your results with your eyes. Regardless of how you handle your matching process, it is tedious. It would be nice to have an automated process, but the truth of the matter is that someone needs to review the matches and see how good they are. The benefit I’ve found of using Fuzzy Lookups is that you spend less time altering your process, and more time reviewing results. I usually go through multiple rounds of adjusting the similarity threshold before I decide on any setting. I’ve seen some writers recommend setting two thresholds: a higher threshold that is trusted as accurate, and then a lower threshold of ‘potential’ matches that can then be reviewed and decided on. I personally have not used this approach yet, but given my experiences thus far, I intend to incorporate this approach in my future projects.
I hope these tips help you out on your implementations of Fuzzy tools. Stay Fuzzy!
 
Want to learn more about Fuzzy logic? Check out my other blog posts:

 

Love our Blogs?

Sign up to get notified of new Skyline posts.

 

Comments
Blog post currently doesn't have any comments.
 Security code

Related Content


Blog Article
Turn It Up to 11: Dynamically Scaling Azure SQL DB to Get the Power You Need
Jared KuehnJared Kuehn  |  
Aug 24, 2017
We have had opportunities to work in Azure solutions on various occasions, including web applications, data warehousing scenarios, and IoT. Quite often in these solutions, there is a database in place to store information, in which case there may be an Azure SQL Database in the architecture. As...
Blog Article
Enabling a Digital Workplace: How to make progress on the worst performers
Libby FisetteLibby Fisette  |  
Aug 10, 2017
The Digital Workplace Group published their Digital Workplace Maturity Benchmark Insights last week. These benchmarks help organizations develop their overall digital workplace goals and achieve them. Making the list of "worst performing areas" were Workflow, Process Improvement...
Blog Article
Haven't I Seen You Before? Fuzzy Grouping Your Datasets for Data Deduplication
Jared KuehnJared Kuehn  |  
Jun 20, 2017
If you’ve been reading my earlier posts regarding Fuzzy logic (See the end of this blog for links), you would know that I am usually talking about Fuzzy Lookups, taking two sets of data and comparing them to find similarities. Did you know that you can perform the same analysis on a single...
Blog Article
My Memory is a Little Fuzzy: Things I Want to Remember When Setting Up Fuzzy Lookups
Jared KuehnJared Kuehn  |  
Jun 08, 2017
In my last post, I explained why I would want to use Fuzzy Lookups for my various matching needs. Now I want to explain the how: How does one set up a Fuzzy Lookup so that it helps solve my data quality problems? The simple answer is that I use the available SSIS Fuzzy Lookup component in a Data...
Blog Article
Three Steps to Implement AD Security for an On-Premises Data Gateway
Eric SaltzmannEric Saltzmann  |  
Apr 28, 2017
The idea to implement AD groups was first realized when trying to dynamically populate O365 group membership, that can be done with PowerShell. The benefit of that knowledge lead me to thinking how I could reverse the process and extract the list of members in an AD group. A short discussion with...