global code: fuzzy

Showing posts with label fuzzy. Show all posts

Friday, March 23, 2012

Fuzzy Street Address Search Code

Anybody have any good code for this case...

Given @.address1 as a parameter.

Look in the providers table for providers that match that Address.

But it needs to be a fuzzy lookup. Meaning it has to find these to be matched

123 Fake St.

123 Fake Street

Even possible some misspellings like

123 Fke St would be nice as well.

I know it will possibly involve a Split function, and/or Soundex and Difference functions. But what is best?

I can't find any code when I google this subject.

Help?!?!?

You may be best served to establish FULL TEXT SEARCH for the address column.

Refer to Books Online, Topic: Full Text Search

|||

I just set up the full-text search... This does not seem to be helpful. I can't do a search like:

SELECT *

FROM providers

WHERE CONTAINS (sv_addr, '"123 Fake St"')

Because it will pull up nothing... However I would need to break up the values with some sort of Split function. Then even still it would not pull up ST as matching Street... or get me results spelled wrong like 123 Fke St.

Help?!?! Is there some functionality I'm missing for using a Full-text Search? Or does someone have code already. It would seem that this functionality is needed in all kinds of projects, and that my inquery would be answered completely immediately.

-Robert

|||

This underscores the need to have a good address validation process in place BEFORE bad and/or mis-spelled addressed are put into your server. You may wish to investigate using the US Postal Service web service for addresses and address correction.

I have assisted in incorporating the USPS web service in applications and the results have been excellent. For example, I can misspell my street name and it will still come back with the correct address. In addition, the Zip+4 is provided, there is cross-check between city and zipcode, abbreviations are standardized, etc.

https://secure.shippingapis.com/Registration/

http://www.codeproject.com/useritems/USPS_Web_Tools_Wrapper.asp

|||

umm... no. I have a legacy address database... and I'm hired to write code to search it. I'm not hired to correct the database. The code I'm looking for has to be out there, I just have to look hard enough... or someone will help me and post it.

Thank you for your suggestions.

|||

I've been there, spent quite a bit of time researching Name and Address issues (on a client's nickel of course.)

There are excellent third party 'add-ins' -very expensive though ($100k+) But not too much that really solves the problems.

Deconstruction can help, storing the address components in separate columns -but still a groaner...

Good luck, and keep us posted. Perhaps there is a new approach out there that would be wonderful to share.

|||bump|||There is no "good" way to do what you want with one field called "Address", with any number of values in it.

What I have done in the past is use an exact search for the value and if that does not return any values, use the SOUNDEX() function to try to match something. On large string lengths, this will have a very large number of hits.

Good luck
|||

Here is a beginning to what I want

Code Snippet

CREATE Function [dbo].[AddressFuzzy] (

@.var1 as Varchar(200),

@.var2 as Varchar(200))

RETURNS int AS

-- Function Address --

-- ? Matches street name

BEGIN

DECLARE

@.a varchar(100), -- street name in var1

@.b varchar(100), -- street name in var2

@.i int

select @.a = Value from dbo.Split(@.var1, ' ') where TokenID = 2

select @.b = Value from dbo.Split(@.var2, ' ') where TokenID = 2

set @.i = difference(@.a, @.b)

RETURN (@.i)

END

The Split function is available as well, if you don't have or understand. This will do a fuzzy match on whatever the second word in the street address is.

so 1) 123 2) Fake 3) Street

It'll fuzzy match 2. I think I can work with this till I get somewhere...

|||

Code Snippet

ALTER Function [dbo].[AddressFuzzy] (

@.var1 as Varchar(200),

@.var2 as Varchar(200))

RETURNS int AS

-- Function Address --

-- ? Matches street name

BEGIN

DECLARE

@.a varchar(100), -- street name in var1

@.b varchar(100), -- street name in var2

@.c varchar(100), -- street name in var1

@.d varchar(100), -- street name in var2

@.e varchar(100), -- street name in var1

@.f varchar(100), -- street name in var2

@.i char(1),

@.j char(1),

@.k char(1),

@.var1Tokens int,

@.var2Tokens int,

@.var1Plus int,

@.var2Plus int

set @.var1Plus = 0

set @.var2Plus = 0

select @.var1tokens = TokenID from dbo.Split(@.var1, ' ')

select @.var2tokens = TokenID from dbo.Split(@.var2, ' ')

set @.k = '0'

select @.a = Value from dbo.Split(@.var1, ' ') where TokenID = 1

select @.b = Value from dbo.Split(@.var2, ' ') where TokenID = 1

if @.a = @.b set @.k = '1'

select @.c = value from dbo.Split(@.var1, ' ') where TokenID = 2

select @.d = Value from dbo.Split(@.var2, ' ') where TokenID = 2

set @.i = Convert(varchar, difference(@.c, @.d))

if Convert(int, @.i) < 3 begin

if @.var1tokens > 3 begin

-- select @.c = @.c + ' ' + value from dbo.Split(@.var1, ' ') where TokenID = 3

select @.c = value from dbo.Split(@.var1, ' ') where TokenID = 3

set @.var1Plus = 1

end

if @.var2tokens > 3 begin

-- select @.d = @.d + ' ' + Value from dbo.Split(@.var2, ' ') where TokenID = 3

select @.d = value from dbo.Split(@.var1, ' ') where TokenID = 3

set @.var2Plus = 1

end

set @.i = Convert(varchar, difference(@.c, @.d))

end

select @.e = Value from dbo.Split(@.var1, ' ') where TokenID = 3 + @.var1Plus

select @.f = Value from dbo.Split(@.var2, ' ') where TokenID = 3 + @.var2Plus

if @.e is null or @.f is null or @.e = '' or @.f = ''

set @.j = '4'

else

set @.j = Convert(varchar, difference(@.e, @.f))

RETURN Convert(int, (@.k + @.i + @.j))

END

It works fine...

|||

And then, without address standardization, there will be:

123 N Fake St

123 N E Fake St

123 North East Fake St

123 Fake N

123 N Fake St S

As you will discover, there is no 'easy or simple' way to handle addresses unless you follow the USPS rules of standardization -and even then it will still be a major headache. (That's partially why the third party tools are so expensive.) Almost all situations where addressing is a mission critical part of the database structure, addresses are deconstructed into constuitent parts, Number, Direction, StreetName, StreetType, etc. -Think Fire, Police, 911 services. Fuzzy doesn't handle it.

A great many of us have had to tackle this problem. If you 'blow off' our suggestions, you most likely won't find a workable solution.

You would do your client a better service by opening the conversation about why and how to standardize the existing data. It is not a complex process.

|||

The above solution I have found does work. I complained of it's slow-ness but once you narrow down the field using Zip &/or city,state it becomes manageable and an acceptable slowness.

I understand your concerns about the USPS address. Our company has had clients that give us raw addresses from there database, and I've been on the task of turning those into distinct locations.

A lot of the time I have to eye-ball address after address and assign them the same location-id because of the above...
123 N fake st = 123 fake st = .... and so on.

In the future your suggestion does make sense... But for now a fuzzy search on the current database is acceptable.

Now you bring up another point however, a bit unrelated to the question.

The USPS would standardize the database and make it...

123 N Fake St...

And i have a guy search for 123 Fake St... and I would send that to get standarized...

How would the USPS system resolve the two above to be equal?

Would I then take the searched address and see if USPS has it on file? Because it would not match my now standardized database. Or would I take each search and get USPS to tell me the correct way to represent the search and then match it to my database?

-Robert

|||

If there was only a N Fake Street, the return is a standardized address.

If there is both a N and S Fake St, usually 123 would be one North (or South), and 125 would be the other. Rarely would exact same numbering scheme be both North and South. Or the ZIP will be used to determine which is correct.

Again, if address searching is mission critical, you MUST deconstruct

|||I agree with Arnie.

The way the USPS standardization service will return a standard address in the form:
House Number, Direction, Street Name, Modifier (DR, ST, AVE, etc), and city/st/zip, etc

The only way to do any kind of search is you have to know if you are looking for N or S or ST or DR or Ave or whatever. Then you prompt the user for 4 fields, which can have blanks representing "any". Then you can use SOUNDEX on the street name to find something similar.

|||

I spent quite a bit of time for several clients working out the name/address matching possibilities for very large databases. Here is a summary of the super-condensed executive briefing abstract. Wink

None of the available 'free' options can hold a candle to some of the third party products (a couple of which are 'Homeland Securty certified' and often paid for by Homeland Security for governmental requirements). But the cost is high. Obviously, some of these folks have made a large investment in working out name search algorythms, and they rightfully expect a 'good' ROI -and with HS's backing, they are getting it.

Soundex was created to solve a Census Bureau problem at the 20th century, and is biased toward northern european names (primary immigrant influx at the time.) That bias still exists and soundex does NOT handle asian, eastern european, and arabic names worth a crap.

Double Metaphone and NYSIIS are a 'recent' alternatives to soundex (there is documentation and SQL functions available on the 'interweb'.) Double Metaphone and NYSIIS are more robust than soundex(), doing a 'plausible' job handling eastern european, asian, and arabic names.

If you are attempting to 'roll you own' name/address search algorythms, DON"T! Explore using Double Metaphone instead. Don't use soundex() -you will be disappointed.

The Double Metaphone code is publically available, expand on it and publish your enhancements to the larger SQL Community. For addresses, use the USPS web service to standardize the address, and deconstruct the address, storing the 'street' name in its own column.

Fuzzy Street Address Search Code

Anybody have any good code for this case...

Given @.address1 as a parameter.

Look in the providers table for providers that match that Address.

But it needs to be a fuzzy lookup. Meaning it has to find these to be matched

123 Fake St.

123 Fake Street

Even possible some misspellings like

123 Fke St would be nice as well.

I know it will possibly involve a Split function, and/or Soundex and Difference functions. But what is best?

I can't find any code when I google this subject.

Help?!?!?

You may be best served to establish FULL TEXT SEARCH for the address column.

Refer to Books Online, Topic: Full Text Search

|||

I just set up the full-text search... This does not seem to be helpful. I can't do a search like:

SELECT*

FROM providers

WHERECONTAINS(sv_addr,'"123 Fake St"')

-Robert

|||

https://secure.shippingapis.com/Registration/

http://www.codeproject.com/useritems/USPS_Web_Tools_Wrapper.asp

|||

Thank you for your suggestions.

|||

I've been there, spent quite a bit of time researching Name and Address issues (on a client's nickel of course.)

There are excellent third party 'add-ins' -very expensive though ($100k+) But not too much that really solves the problems.

Deconstruction can help, storing the address components in separate columns -but still a groaner...

Good luck, and keep us posted. Perhaps there is a new approach out there that would be wonderful to share.

|||bump|||There is no "good" way to do what you want with one field called "Address", with any number of values in it.

Good luck
|||

Here is a beginning to what I want

Code Snippet

CREATEFunction [dbo].[AddressFuzzy] (

@.var1 as Varchar(200),

@.var2 as Varchar(200))

RETURNS intAS

-- Function Address --

-- ? Matches street name

BEGIN

DECLARE

@.a varchar(100),-- street name in var1

@.b varchar(100),-- street name in var2

@.i int

select @.a = Value from dbo.Split(@.var1,' ')where TokenID = 2

select @.b = Value from dbo.Split(@.var2,' ')where TokenID = 2

set @.i =difference(@.a, @.b)

RETURN(@.i)

END

The Split function is available as well, if you don't have or understand. This will do a fuzzy match on whatever the second word in the street address is.

so 1) 123 2) Fake 3) Street

It'll fuzzy match 2. I think I can work with this till I get somewhere...

|||

Code Snippet

ALTERFunction [dbo].[AddressFuzzy] (

@.var1 as Varchar(200),

@.var2 as Varchar(200))

RETURNS intAS

-- Function Address --

-- ? Matches street name

BEGIN

DECLARE

@.a varchar(100),-- street name in var1

@.b varchar(100),-- street name in var2

@.c varchar(100),-- street name in var1

@.d varchar(100),-- street name in var2

@.e varchar(100),-- street name in var1

@.f varchar(100),-- street name in var2

@.i char(1),

@.j char(1),

@.k char(1),

@.var1Tokens int,

@.var2Tokens int,

@.var1Plus int,

@.var2Plus int

set @.var1Plus = 0

set @.var2Plus = 0

select @.var1tokens = TokenID from dbo.Split(@.var1,' ')

select @.var2tokens = TokenID from dbo.Split(@.var2,' ')

set @.k ='0'

select @.a = Value from dbo.Split(@.var1,' ')where TokenID = 1

select @.b = Value from dbo.Split(@.var2,' ')where TokenID = 1

if @.a = @.b set @.k ='1'

select @.c = value from dbo.Split(@.var1,' ')where TokenID = 2

select @.d = Value from dbo.Split(@.var2,' ')where TokenID = 2

set @.i =Convert(varchar,difference(@.c, @.d))

ifConvert(int, @.i)< 3 begin

if @.var1tokens > 3 begin

-- select @.c = @.c + ' ' + value from dbo.Split(@.var1, ' ') where TokenID = 3

select @.c = value from dbo.Split(@.var1,' ')where TokenID = 3

set @.var1Plus = 1

end

if @.var2tokens > 3 begin

-- select @.d = @.d + ' ' + Value from dbo.Split(@.var2, ' ') where TokenID = 3

select @.d = value from dbo.Split(@.var1,' ')where TokenID = 3

set @.var2Plus = 1

end

set @.i =Convert(varchar,difference(@.c, @.d))

end

select @.e = Value from dbo.Split(@.var1,' ')where TokenID = 3 + @.var1Plus

select @.f = Value from dbo.Split(@.var2,' ')where TokenID = 3 + @.var2Plus

if @.e isnullor @.f isnullor @.e =''or @.f =''

set @.j ='4'

else

set @.j =Convert(varchar,difference(@.e, @.f))

RETURNConvert(int,(@.k + @.i + @.j))

END

It works fine...

|||

And then, without address standardization, there will be:

123 N Fake St

123 N E Fake St

123 North East Fake St

123 Fake N

123 N Fake St S

A great many of us have had to tackle this problem. If you 'blow off' our suggestions, you most likely won't find a workable solution.

You would do your client a better service by opening the conversation about why and how to standardize the existing data. It is not a complex process.

|||

The above solution I have found does work. I complained of it's slow-ness but once you narrow down the field using Zip &/or city,state it becomes manageable and an acceptable slowness.

I understand your concerns about the USPS address. Our company has had clients that give us raw addresses from there database, and I've been on the task of turning those into distinct locations.

A lot of the time I have to eye-ball address after address and assign them the same location-id because of the above...
123 N fake st = 123 fake st = .... and so on.

In the future your suggestion does make sense... But for now a fuzzy search on the current database is acceptable.

Now you bring up another point however, a bit unrelated to the question.

The USPS would standardize the database and make it...

123 N Fake St...

And i have a guy search for 123 Fake St... and I would send that to get standarized...

How would the USPS system resolve the two above to be equal?

-Robert

|||

If there was only a N Fake Street, the return is a standardized address.

Again, if address searching is mission critical, you MUST deconstruct

|||I agree with Arnie.

The way the USPS standardization service will return a standard address in the form:
House Number, Direction, Street Name, Modifier (DR, ST, AVE, etc), and city/st/zip, etc

|||

If you are attempting to 'roll you own' name/address search algorythms, DON"T! Explore using Double Metaphone instead. Don't use soundex() -you will be disappointed.

Fuzzy Search?

How do I do a fuzzy search? If I have a table of full names, I'd like the user to be able to do a search and find the record, "Charles Montgomery Burns" with "Monty Burns" or "Montgomry" (mispelling).

Every major web site does this kind of thing (Amazon, Google, etc).

Someone suggested SOUNDEX, but this really doesn't fit the bill. Misspellings often don't use the same sound signature as the originals. Plus, that doesn't handle multi-word searchable texts very well.

Others have suggested tries or suffix trees. If I went this route, wouldn't I have to preload all data out of the database and into this custom structure upon app startup? Is there any way around that? Also, this solution seems like it would require a lot of dev time (building a custom suffix tree with fuzzy lookup capabilities).

Is there a commonly known and acceptable solution to this?

(sorry, also posted to MySQL group; I'm using both databases so a solution in either would be satisfactory)I did something recently where I used DIFFERENCE (I know you already said soundex is bad) on first names and lastnames with a user defined function that weighted results with a point system. An exact match on strings got a lot of points (say 200) a Difference of 4 recieved 100, a Difference of 3 recieved 75 etc... and then I ordered the results by the score, last name and first name. Works pretty well. I would post it but it is part someone else's code who could not get it to work until I fixed it for him.

fuzzy search

Is there a built in capability in Sql server 2005 to do a search which can handle spelling errors. for eg.

We are doing a search for "hanovr" and our database contains "hanover" . In cases when there is a spelling error searching using LIKE,CONTAINS,FREETEXT are not giving me the results. Is there an out of the box solution for this problem.

Please Advice.

Oracle has a function, Soundex if I remember right, that worked off the auditory equivalent value.

I don't remember if sql server has something like that or not.

|||

Duh. Just looked it up and there is one. Check it out!

|||

there is also a version of soundex in sql server. check it here:http://msdn2.microsoft.com/en-us/library/aa259235(sql.80).aspx

|||

There are actually 2 functions in SQL using which you can check whether 2 values are matching or not. SOUNDEX and DIFFERENCE. These functions actually ignore the vowels ( a,e,i,o,u ).

The SOUNDEX function returns a four-character code to evaluate the similarity of two strings.

The DIFFERENCE function returns the difference ( ranging from 0 to 4, 0 is the highest possible difference and 4 is the least possible difference ) between the SOUNDEX values of two character expressions. Run below queries.

select soundex ('hanovr' ) , soundex ('hanover' ) , difference ('hanovr' ,'hanover' )select soundex ('hanovr' ) , soundex ('hanovr' ) , difference ('hanovr' ,'hanovr' )

I've used them previously with not much luck. You can if possible go with some .net libraries to check spelling mistakes.

Hope this will help.

|||

I was looking for something similar - a search mechanism that deals with misspellings. Came acrosshttp://shuffletext.com/highlight, which is a little misspelling search component in beta.

Figured I'd jump on the thread, as it works pretty good. No problems integrating, but there's a couple of annoying things: it doesn't hand you back any info on how good the match was, and sometimes the first result isn't always the best result.

sql

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

Fuzzy Lookups and Groupings Algorithm

Hello,
I'm trying to clean my data using fuzzy lookup algorithm though SSIS, but i get null values everywhere. This is what i did:

I applied the fuzzy lookup in a table (tblValues). As source table i have the tblValues, and as reference table in Fuzzy Lookup i have the tblValues as well, resulting null values in all fields/columns.

Do i have to create my own reference table? If yes, how do i do that and what values will i have in this table?I didn't understand how the reference table must be in order the algorithm to work. Any suggestions?

Thank you in advance!

I'm not sure I understand what your objective is. If you are trying to remove duplicates in tblValues, you're better off using a fuzzy grouping task and not a fuzzy lookup task. See the following article for how to use both if you haven't read it yet.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

|||

Thanks for the article i have already read it and i used it as reference to my project. My objective is to fill the null values with the most possible value.

I'm not sure if fuzzy does that, but in that articles says that "Fuzzy Lookup matches input records that are "dirty" (because of misspellings, truncations, missing or inserted tokens, null fields, unexpected abbreviations, and other irregularities) with clean records in a reference table. ", but i didn't understand how the reference table must be.

Fuzzy Lookup[4506] Error in Integration Services

Hi:

I m developing Integration Services Project with Fuzzy Services.

as Provided in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

am running its Simple example with database AdventureWorks and table Products (I hve also tried other tables). but its failed to execute b/c of this error

[Fuzzy Lookup [4506]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table 'FuzzyLookupMatchIndexEmployee_FLRef_060705_10:21:09_2408_afc874d3-927b-4c70-95ad-a726ef6d7567'. Only one identity column per table is allowed.".

Can any buddy help me out.

Try deselecting the "Store new index" option.|||I hve tried it , but Error still same|||I'm having this problem. I'm curious if you were ever able to resolve it? Any help would be greatly appreciated.

Fuzzy Lookup[4506] Error in Integration Services

Hi:

I m developing Integration Services Project with Fuzzy Services.

as Provided in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

am running its Simple example with database AdventureWorks and table Products (I hve also tried other tables). but its failed to execute b/c of this error

Can any buddy help me out.

Fuzzy Lookup/product level is insufficient

I installed SQL Server 2005 Standard Edition on my XP sp2 workstation, developed an SSIS package, which has run for a week on my workstation.

I installed SQL Server 2005 Standard Edition on a Win2003 sp1 server, created the dtsx and install manifest on my workstation, and installed it on the server.

If I run the package on my workstation, pointed to the server database it runs fine.

If I run the dtsx on the server, it fails with the error:

The product level is insufficient for component "Fuzzy Lookup on Name" (83)

Yes, I have SSIS installed on both machines, I have verified that I have the SSIS service on the server.

Any Ideas?

Thanks!

BobP

Bob,
Fuzzy Lookup/Fuzzy Grouping components are part of the Advanced Transformations that are only available in the Enterprise Edition of SQL.
Per MS all transformations/features are available in the BIDS when you are developing. So you really need to becareful about what features you use in your packages.
Larry Poep|||

Thanks
BobP

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 task - expressions for MatchIndexName and ReferenceTableName

Why is there no Expressions collection for the Fuzzy Lookup task? Is there another good way to dynamically configure the ReferenceTableName and MatchIndexName properties?

I beleive Fuzzy Lookup was built before we had expressionable properties in data flows.

You may consider requesting this feature on the connect site.

Thanks,

-Bob

fuzzy lookup taking too much time

I have a SSIS package where a small table of 270 rows are fuzzy looked up with a table in another sql server and inserts the records to a temporary table. This takes more than 3 hours in debug mode or so and never goes beyond this step.I have used a OLE DB destination to insert to temporary table and temporary table doesn't get a value.

How big is that other table? The fuzzy will build an index to start with, and this can take time. Review the options on the second tab to influence the index. Also check obvious things like blocking or such like? What about a profiler trace, anything untoward?

|||other table also is the same size and has same no of rows.Thank for the clues I found that some of the NOT null columns in the destination table were not mapped which result in failure of insert. Now it inserts correct rows to temp table and fails with a primary key violation which means its going in a never ending sort of a loop. I am going to put a profiler trace to identify the problem.Any advice welcome

Fuzzy Lookup problems.

Fuzzy lookup seems to be causing some problems to me. It seems to work at times and doesn't at other times. It would work a couple of times fine and give me the desired results but then without changing anything in the dataflow or the data the next few times it would not run at all and fail the pre-execute of the.

Now I'm currently getting the following error:

[Fuzzy Lookup [248]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: Could not open a connection to SQL Server [233]. ".

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 249 on component "Fuzzy Lookup" (248) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup" (248) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Any help would be appreciated.

It looks to be a connectivity issue. My first avenue of investigation would be to use Profiler to see if you can see the connection attempt coming into SQL Server.

-Jamie

sql

Fuzzy Lookup problems

Hi everyone,

Ive just started looking at the Fuzzy Lookup feature and i think i must be getting something fundamentally wrong. I have two tables - each contain different meta data representations for a set of potentially similar documents. The only chance i have of matching a document in table A to a document in table B is a common title field. However, manual input means that the titles may differ in both tables although they are potentially quite similar in most cases.

In the lookup i get to specify the output columns from table B (Reference) which is fine, but i don't seem to get to choose the columns from table A that i would also like to see. So my output shows me all the documents from table B that it thinks are similar to ones in table A...but not identifying which record it's similar to.

I initially thought that the "pass through" columns that i identified would appear in the output - but this does not seem to be the case.

I must be using it incorrectly, but i have no idea how to progress with this apart from creating a new source table (C) which is a full outer join of table A and B - and then also using table C as the reference table, but that seems madness.

any help would be appreciated - ta

Andrew

I may have spoken too soon.

It's strange, but if i've been using the 'Advanced Edit' dialog to setup the lookup - which i had assumed contained the same functionality (plus more) as the 'Edit' dialog. It looks like i was wrong. If i setup the Lookup through the Edit page, I can specify the Pass Through columns (which it doesn't look like you can do from inside the Advanced Edit box). Then once i have finished the basic edit i can go in and perform any additional advanced editings.

I suppose it kind of makes sense - my intial attempts at setting Pass Throughs must have been flawed in some other way :)

Hope this helps someone else!

Andrew :)

|||

Perhaps have a look at: http://msdn.microsoft.com/sql/bi/integration/default.aspx?pull=/library/en-us/dnsql90/html/datasol.asp

Although primarily about Master Data Management (MDM) it also covers some of the interesting Fuzzy techniques that can be used with SSIS.

Donald

Fuzzy lookup match issue

Hello,

I have a peculiar problem in my project. My project design is like this

The number in (...) are count of records.

File feed (1000)

Fuzzy Lookup

against Table2

Split Fz Lookup results

(_Similarity >= 0.60 && _Confidence >= 0.85)

| |

| Write matches to Table1 (250)

Fuzzy Group

Remaining rows (750)

Split Fz Group results

| |

Write Canonicals Write Dupes

to Table2 to Table1

(300) (450)

This is basically a customer de-dupification project.

The Table2 has the canonicals and Table1 has the dupes (of the canonicals).

I already have some data in these tables and the new data is matched against the existing data

in these tables and classified as new customers and duplicate customers.

In the above process one could notice that the rows identified as dupes of already exsting canonicals

by the Fuzzy Lookup task are written into the dupes table (Table1) and will not be processed further down

the line in the project.

But in my case I see that those matches identified by Fuzzy lookup are further being included in the

Fuzzy Grouping also.

When I run this in debug mode in BIDS, it shows the correct numbers as I have depicted in the

illustration above. But, after execution, when I query the tables it shows that all 1000 rows

went through Fuzzy Grouping.

Any thoughts?

Btw, is there anyway to upload attachments to the postings here?

I also tried introducing a Derived Column between the 'Split Fz Lookup Results' and 'Write matches to Table1' to write some string into one of the table columns. It did not.

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 Lookup error - Multiple Identity Columns?

I'm developing an ETL solution that needs to look for duplicate records using a fuzzy lookup. If the lookup table has an identity column, I get the following error. I can get this to work on a local desktop instance of SQL server, but not on my development server or production box. Any help is greatly appreciated.

Also I've stripped down the incoming data for the lookup to a very simplified version of what I'd like to use, but if I can't get it to work then I can't add addtional columns to match with.

It's like it's trying to add it's own Id to the temp table created for the tokens

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Multiple identity columns specified for table '##FLRef_070403_10:09:36_3532_aeac56a4-8bc0-4ff4-ac41-0984e293261a'. Only one identity column per table is allowed.".

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Database name 'tempdb' ignored, referencing object in tempdb.".

Error: 0xC004701A at Move Clean Records into Clean Enrollment, DTS.Pipeline: component "Fuzzy Lookup" (300) failed the pre-execute phase and returned error code 0xC0202009.

I have found an interesting solution to this problem, but I'm still left wondering if this is by design or error. The reference table must be in the dbo schema to work. I began with a reference table in a schema owned by me. I finally moved to another DB on the same server and it worked. The difference I then noticed was that the table was now in dbo. I went back to the original development DB and changed ownership on my schema to dbo. I ran the package and got the same error. So I then moved the table to dbo and it worked just fine. If anybody knows why this might be, I'd really like to know. If you'd like to see where I started out, get a copy of Hands-On SQL Server 2005 Integration Services by Ashwani Nanda. In Chapter 10 there is a good section on removing duplicates.

Unaswered questions

Is it a permission issue, a DB confituration issue or the design of the Fuzzy Lookup or an error.

It is interesting that if I remove the ID column (which uses identity(1,1))from my reference table in my schema it will work, but its sort of the point to be able to go back to the duplicate records for updates, inserts or deletes etc.

Fuzzy Lookup Error

I get the following error when I use Fuzzy Lookup in a Data Flow task with TransactionOption property set to “Required”

[Fuzzy Lookup [61]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot create new connection because in manual or distributed transaction mode.".

When I Change the TransactionProperty to “Supported” it works fine.

I need the property set to Required for it does an undo in the event of a failure.

Any ideas on how to get the Fuzzy Lookup to work

Are you executing the package containing the data flow task from a parent package? I have seen an issue where the OLE DB connection does not properly defect from the transaction, and throws an error like you're seeing here. The work around was to execute the child package out of process.

~Matt

|||i dont have any child packages..|||

Set Required on the package container, and Supported on all child containers. Then they will automatically enlist with the transaction from the package container.

Why do you need a transaction around a lookup?

sql

Wednesday, March 21, 2012

Fuzzy Lookup componant eating all available virtual memory

Hey,

I have a large set of data that I need to match against another large set of data. The reference table has 9.8mill rows and my input has 14.6mill rows. I started with a new project. I added my connection, then a task to clear the result table, then my data flow, then my OLE source, then my Fuzzy Lookup task, then my SQL Server Destination. I set the connection of my OLE source and set the query to pull the data. Then I set the connection of my Fuzzy Lookup task, set the reference table and told it to create a new index (the problem also occurs if I use a generated index) and then set up the matching criteria. Then I set the connection and destination for the SQL Server Destination.

After setting all this up, I hit Run. The thing ran great until ~ 800k rows and then it failed. I ran it several times and it always failed right around 800k with a message saying there was not enough space and then an error with buffers being passed to the Fuzzy Lookup component. I opened Task Manager and watched the resources as it ran and was amazed at what I saw. The Fuzzy Lookup component eats up every bit of Virtual Memory available and when it can't take any more, it errors out. I tried setting the Max Memory setting on the component and it seems to have no effect. I also played with the buffer settings on the data flow task to no avail. I even went as far as to put an identity on my input table and create a function that outputs selects that use a between on the identity to break the data into 600k chunks. I set up a ForEach component and DTS variables, but the Fuzzy Lookup component does not free the VM after the iteration of the ForEach component!

I ended up running each chunk of 600k one at a time. I have to automate this for the future, so I need a solution. Does anyone have an idea for me?

The errors I get are:

[Fuzzy Lookup 1 [3067]] Warning: Not enough storage is available to complete this operation.

[DTS.Pipeline] Warning: A call to the ProcessInput method for input 3068 on component "Fuzzy Lookup 1" (3067) unexpectedly kept a reference to the buffer it was passed. The refcount on that buffer was 2 before the call, and 1 after the call returned.

[DTS.Pipeline] Error: The ProcessInput method on component "Fuzzy Lookup 1" (3067) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8007000E.

[OLE DB Source [1923]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1923) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

|||

Pls check to see whether http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191199&SiteID=1 helps.

Thanks

Wenyang

|||

I found that thread after posting and tried the solutions there, but they had no effect on the package. It still almost linearly eats up all virtual memory. By the time it is at 800k records, it has eaten up 40gb of virtual memory.

The server is:

Dual AMD 64bit Processors

2TB storage with 650GB free

8GB ram

Windows Server 2003 64bit

SQL Server 2005 64bit

Of note also is that I have tried running the package from the command line with identical results. Here is what I used:

dtexec /f package1.dtsx /ref n

|||I had the VM size increased to 200gb and I can do 3m sets now. It still isn't to where I need to to be for automation, but it will let me get the job done for now. If anyone has some ideas on what might be wrong, please please let me know!!|||

Hey James,

Did you have any luck with this? I'm in exactly the same situation you are in regarding fuzzy lookup taking all memory. If you or anyone else figured out a work around it would be great to hear.

Thanks,

|||There's been fixes for memory leak issues with the fuzzy components in the past. Could you check KB 912423: "FIX: Memory leaks occur when you use Fuzzy Lookup and Fuzzy Grouping to transform a SQL Server 2005 Integration Services package"?

Fuzzy Lookup componant eating all available virtual memory

Hey,

I ended up running each chunk of 600k one at a time. I have to automate this for the future, so I need a solution. Does anyone have an idea for me?

The errors I get are:

[Fuzzy Lookup 1 [3067]] Warning: Not enough storage is available to complete this operation.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0x8007000E.

[OLE DB Source [1923]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.

|||

Pls check to see whether http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=191199&SiteID=1 helps.

Thanks

Wenyang

|||

The server is:

Dual AMD 64bit Processors

2TB storage with 650GB free

8GB ram

Windows Server 2003 64bit

SQL Server 2005 64bit

Of note also is that I have tried running the package from the command line with identical results. Here is what I used:

dtexec /f package1.dtsx /ref n

Hey James,

Did you have any luck with this? I'm in exactly the same situation you are in regarding fuzzy lookup taking all memory. If you or anyone else figured out a work around it would be great to hear.

Thanks,

Friday, March 23, 2012

Fuzzy Street Address Search Code

Fuzzy Street Address Search Code

Fuzzy Search?

fuzzy search

Fuzzy Matching - Address Cleansing

Fuzzy Lookups and Groupings Algorithm

Fuzzy Lookup[4506] Error in Integration Services

Fuzzy Lookup[4506] Error in Integration Services

Fuzzy Lookup/product level is insufficient

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

Fuzzy Lookup task - expressions for MatchIndexName and ReferenceTableName

fuzzy lookup taking too much time

Fuzzy Lookup problems.

Fuzzy Lookup problems

Fuzzy lookup match issue

Fuzzy lookup error when adding additional lookup columns

Fuzzy Lookup error - Multiple Identity Columns?

Fuzzy Lookup Error

Wednesday, March 21, 2012

Fuzzy Lookup componant eating all available virtual memory

Fuzzy Lookup componant eating all available virtual memory

global code

Blog Archive

About Me