Transcript Slide 1
Inaport Training Fuzzy Matching Matching Matching • Process of deciding which record or set of records in the target table(s) should be updated • Alternatively, decide if record already exists and take appropriate action © Copyright 2010 InaPlex Inc Matching Techniques Inaport supports different ways to match • Standard • build expressions on source and target • Fuzzy • Refine Standard to allow for poor data • SQL • Use SQL SELECT instead of expressions © Copyright 2010 InaPlex Inc Fuzzy Matching Standard Matching • can use any combination of fields • can use expressions BUT • Ultimately is restricted to exact match “InaPlex” <> “Innerplex Ltd” © Copyright 2010 InaPlex Inc Fuzzy Matching Fuzzy matching compares source and target, and gives a similarity score Score measures how “close” two strings are Score = 1 Score = 0 : Perfect match : No match “InaPlex” and “inaplx” : 98% “InaPlex” and “innerplex” : 87% “InaPlex” and “ibm” : 49% © Copyright 2007 InaPlex Limited See Tools – Fuzzy Match Demo How it Works As with Standard matching, Fuzzy match • Can use any field or combination of fields • Reads the match fields • Builds an in memory index for each table • The target match expression is applied to the field data read from the table © Copyright 2010 InaPlex Inc How it Works Set scoring levels • Score > Upper • good match – accept immediately • Lower < Score < Upper • Possible match – user review • Score < lower • Not a match – reject No match < Lower < Possible < Upper < Good No match < 85% < Possible < 95% © Copyright 2010 InaPlex Inc < Good How it Works When a source record comes in: • Source expression applied to build match value • Source match value scored against every value in target index • “Best” matches used – you set boundaries • No match < Possible match < Good match • No 0.85 Possible 0.95 Good © Copyright 2010 InaPlex Inc How it Works User Review • Shows the source record and possible matches in target • User can select one or more records as match • Options • Review “good” and “possible” matches – For testing purposes • Review just “possible” matches – If there are no possibles, good and no match accepted automatically • No review – Good and no match accepted automatically – Possible treated as bad © Copyright 2010 InaPlex Inc How it Works Customise User Review • May need to see more than the target table to decide on match • Can also display associated tables • E.g. Address, Contact • Can also select which fields from associated tables to display © Copyright 2010 InaPlex Inc Example – Operation Tab Select Fuzzy Match from Match Type © Copyright 2007 InaPlex Limited Example – Match Tab Specify the base match criteria • Source and target match expressions • Boundary scores for no, possible, good matches • Cluster Match covered later © Copyright 2010 InaPlex Inc Example – Match Tab Set up the User Review • Can choose • No review – use in batch mode • Only possible matches – accept good matches • Good + possible – review all matches © Copyright 2010 InaPlex Inc Example – User Review Shows possible matches at run time • • • • Source record Possible matching target records, with score If configured, child records of selected target record Allows selection of desired matches © Copyright 2007 InaPlex Limited Clustering Fuzzy Matching is powerful, flexible BUT Every source record must be scored against EVERY target match, then highest scores selected 100,000 records in target => 100,000 scores per source record Solution is CLUSTERING © Copyright 2010 InaPlex Inc Clustering Specify • an expression to sort target records into clusters Then • an equivalent expression for source to sort it into one cluster Finally • scoring only done against members of the selected cluster 100,000 target divided into 20 clusters • 5,000 records per cluster => 5,000 scores per source record © Copyright 2010 InaPlex Inc Clustering Cluster expression should: • Sort target into roughly equal groups • Guard against allocating source to wrong cluster • Examples • First letter of company name • Zip/Post code • Phone area code © Copyright 2010 InaPlex Inc Clustering No clustering established Alpha Corp Beta Corp Source record scored against every record in target © Copyright 2010 InaPlex Inc Zulu Corp Clustering Set up clustering based on first letter of company name © Copyright 2007 InaPlex Limited Clustering Cluster on first letter Beta Corp Source record only scored against records in “b” cluster © Copyright 2010 InaPlex Inc Alpha Corp Beta Corp Brown Corp Zulu Corp Clustering Important Note Because source records will only be scored against one cluster, if clustering is poorly done can lead to missed matches • “naplex” would look in “n” cluster, not “I” Cluster expression does NOT have to use same fields as match • E.g. Match on name, cluster on ZIP code © Copyright 2010 InaPlex Inc Summary Fuzzy matching provides powerful new tool for handling complex, dirty data Need to • Use carefully, especially clustering • Allow of overhead of user review © Copyright 2010 InaPlex Inc THANK YOU www.inaplex.com www.inaplex.com/cs/forums © Copyright 2010 InaPlex Inc