Showing posts with label matching. Show all posts
Showing posts with label matching. Show all posts

Friday, March 23, 2012

Fuzzy Matching - Address Cleansing

Hi *,

does anyone know if MS supports some kind of breaking strategy within Fuzzy Lookup/Grouping?

Besides that, I'd like to perform a address cleansing operation on a CRM database. I don't have a reference table (Street, Zip, LastLine, etc.) for that. Where can I get an appropriate database? Anyone has some experience with this issue?

Thanks a lot.
S.Is there no one who uses SSIS for address validation?!|||

I don't really understand what you mean by a breaking strategy, do you mean splitting an address out into its component parts, such as street, city, post code? If so then there is nothing in the stock components that does this. You would really want some third-party address verification software. Many of them will have their own API that could then be used to integrate this into a SSIS data flow, in fact we did this for a client recently. They key to provide effective address cleansing is the reference system, more than just a table of data really. If this is a one off or an infrequent requirement then I would just use a bureau type service rather than anything integrated into SSIS, but on the other hand if this is an ongoing requirement then purchasing a product and integrating it would make sense.

The Fuzzy Lookup could be used to try and find a match between an existing table of addresses and a source address, but unless you are looking for existing customers or such like, you would have to purchase an address "file". That is generally what you are paying for in a third-party address product, so I think it would make sense to actually use the address software API rather than the Fuzzy.

|||My experience is that many 3rd party data quality tools (i.e. Firstlogic IQ) have some very impressive features like the mentioned breaking groups (breaking the source data into several parts and perform cleansing only within these parts --> saves a lot processing time).

I think the fuzzy search capabilities of SSIS are really powerful. Other products, like the mentioned firstlogic iq, doesn't perform very well in this respect. So I was thinking why not use SSIS and a comprehensive postal database to take advantage of both worlds. My biggest concerns are a really big slow down in performance and the lack of parsing features.

Regards, S.|||Ok, let me get this straight. Nobody ever tried to use fuzzy matching for adress verification?! What do you use fuzzy matching for instead?|||Have you looked at Intelligent Search Technology SSIS components. Their fuzzy matching components are really good. They also provide address correction and all of that within SSIS. The link is: http://www.intelligentsearch.com/ssis-data-quality/index.html

Wednesday, March 21, 2012

Fuzzy Logic Performance

Hello,
I am just wondering if someone out there has tried some Fuzzy matching on databases of large scale i.e - about 20 million contact records. Suppose I wanted to perform matching/ grouping to 10 000 incoming messages. How fast does this usually take? What is the dependence on the number of fields chosen for the match?

Any insight is greatly appreciated,

Lubomir

Pls take a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

Thanks

Wenyang

|||Thank you Wenyang, great link

Lubomir

Fuzzy grouping matching nulls to empty strings/spaces

Will the fuzzy grouping task match a null value to an empty string (or spaces)? I've got 5 columns I'm matching on, and one of them may be null for certain rows but an empty string for others. Given the 4 other columns may match, will this difference stop similar columns being grouped together?

(Someone's modified my grouped data since it was deduped, which takes a while, and I'm hoping for a quick answer on this).

Thanks in advance.

Ben

Not sure, but building a quick package and adding a data viewer should give you the answer.

I would hope that a NULL wouldn't match to spaces...

Couldn't you run an update query to "fix" the data?|||

Computer says "no"

<cough>

No duplicate matching between null and empty string/spaces, as you suspected. Thanks for the idea of the quick test - seems obvious in retrospect, if only it had been beforehand Smile Unfortunately I can't fix the data, other than rerunning the grouping task, but good to know the source of the problem - thanks Phil!