Fuzzy lookup transform row scores 'inconsistent' with individual column scores

I am trying to interpret some of the results I observe when trying to match similar records using a fuzzy lookup transform, but it's not entirely clear how the overall row similarity score is calculated. In particular, sometimes rows with lower individual column similarity scores will achieve a higher similarity and confidence score than a matching row with higher individual column scores.

The transform is configured with 6 text fields set to fuzzy mapping and a minimum similarity of 0, and 3 additional numeric fields with an exact mapping. It is set to return a maximum of 2 matches per lookup and to do an exhaustive search of the reference table.

For example, from the following matching pair of records Match 1 is picked over Match 2 even though it's individual scores are lower.

Match 1 Match 2
-- --
_similarity_author 1.0 1.0
_similarity_title 0.85344648 1.0
_similarity_headline 0.0125 0.0125
_similarity_summary 0.0125 0.0125
_similarity_picture 1.0 1.0
_similarity_caption 1.0 1.0

_similarity 7.8429267E-2 7.3196657E-2
_confidence 0.55728668 0.44271332

In another case both matching records have *identical* scores for every mapped column and yet their similarity and confidence scores are different.

Clearly there are other factors involved in calculating the overall row score. Anybody know what these are?

Fernando Tubio

Can't even begin to describe it in my own words. This article describes the Fuzzy Math real well. Don't know if you've seen it.

http://msdn.microsoft.com/msdnmag/issues/05/09/SQLServer2005/

|||

Thank you Martin.

I've read the article and it explains the lookup process well. Unfortunately it doesn't answer my question. Specifically, having found two matches, why does the matching algorithm discard what appears to be a better match, at least judging from individual column similarity scores.

I am trying to understand the mechanism to determine if there is anything I can tweak in order to force the algorithm to make a better choice.

Fuzzy lookup error when adding additional lookup columns

I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.

It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:

Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.

These errors occur regardless of what columns I am attempting to add to the lookup list.

I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.

Any ideas on what else could be causing this?
Have you tried deleting the component and recreating it?|||Yes, I just tried deleting and recreating the fuzzy lookup, and the same errors are occuring.
|||

Uh! there is a similar issue reported in Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=266185

but no response from MSFT.

You may want to add your vote

Fuzzy Grouping Transform Corrupts Pass-through Data

We are working with a client and are using Fuzzy Group transform for de-duping, and hierarchy creation for a national account list.

I've found that if a large number of pass through columns are sent to the Fuzzy Grouping transforms it randomly corrupts the char columns.

Our work around was to only pass through ID columns and then build out the attributes needed from views against the Fuzzy group output however product team should take a look at this.
By corruption I mean random characters from other records would show up in character columns (we had address and name corruption in about 10% of a 1.5 million record dataset).

Thanks.

Michael Slater
Software Architects

Michael,

Thanks for your post. We have been unable to reproduce the problem you have reported in the new test cases that we have created for this issue. We would very much like to get to the bottom of what you are seeing. Can you please contact me directly so that we might work with you to find a better repro case that can be used to further investigate and fix this problem?

Please send an email to KrisGan@.microsoft.com

Thanks,
Kris Ganjamsql