Admin at 20:33, 25 May 2018

2018-05-25T20:33:35Z

← Older revision		Revision as of 20:33, 25 May 2018
Line 135:		Line 135:

	{\|class="alternate01" cellspacing="0"		{\|class="alternate01" cellspacing="0"
	!Algorithm		!Algorithm!!Late or Early!!style="border-right:0px;"\|Speed (10=fastest)
	!Late or Early
	!Speed (10 = fastest)
	\|-		\|-
	\|~~Accurate Near~~		\|Jaro\|\|Late\|\|1
	\|Late
	\|1
	\|-		\|-
	\|~~Fast Near~~		\|Jaro-Winkler\|\|Late\|\|1
	\|Late
	\|3
	\|-		\|-
	\|~~Containment~~		\|n-Gram\|\|Late\|\|1
	\|Late
	\|4
	\|-		\|-
	\|~~Frequency Near~~		\|Needleman-Wunch\|\|Late\|\|1
	\|Late
	\|4
	\|-		\|-
	\|~~Frequency~~		\|Smith-Waterman-Gotoh\|\|Late\|\|1
	\|Late
	\|6
	\|-		\|-
	\|~~Phonetex~~		\|Dice’s Coefficient\|\|Late\|\|1
	\|~~Early~~
	\|7
	\|-		\|-
	\|~~Soundex~~		\|Jaccard Similarity Coefficient\|\|Late\|\|1
	\|~~Early~~
	\|8
	\|-		\|-
	\|~~Vowels Only~~		\|Overlap Coefficient\|\|Late\|\|1
	\|~~Early~~
	\|9
	\|-		\|-
	\|~~Numerics Only~~		\|Longest Common Substring\|\|Late\|\|1
	\|~~Early~~
	\|9
	\|-		\|-
	\|~~Consonants Only~~		\|Double Metaphone\|\|Late\|\|1
	\|~~Early~~
	\|9
	\|-		\|-
	\|~~Alphas Only~~		\|Accurate Near\|\|Late\|\|1
	\|~~Early~~
	\|9
	\|-		\|-
	\|Exact		\|Fast Near\|\|Late\|\|3
	\|N/A		\|-
	\|10		\|Containment\|\|Late\|\|4
			\|-
			\|Frequency Near\|\|Late\|\|4
			\|-
			\|Frequency\|\|Late\|\|6
			\|-
			\|Phonetex\|\|Early\|\|7
			\|-
			\|Soundex\|\|Early\|\|8
			\|-
			\|Vowels Only\|\|Early\|\|9
			\|-
			\|Numerics Only\|\|Early\|\|9
			\|-
			\|Consonants Only\|\|Early\|\|9
			\|-
			\|Alphas Only\|\|Early\|\|9
			\|-
			\|Exact\|\|N/A\|\|10
	\|}		\|}

Admin at 00:20, 14 November 2015

2015-11-14T00:20:14Z

@@ Line 1: / Line 1: @@
-[[SSIS:Reference|&larr; SSIS Reference]]
+{{SSISMatchUpNav
+|MatchcodesCollapse=
-{| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; padding:8px 8px;"
+}}

Admin at 22:10, 21 November 2014

2014-11-21T22:10:05Z

← Older revision		Revision as of 22:10, 21 November 2014
Line 28:		Line 28:
	\|-		\|-
	\|[[SSIS:MatchUp:Options\|Options]]		\|[[SSIS:MatchUp:Options\|Options]]
			\|-
			\|[[SSIS:MatchUp:Golden Record\|Golden Record]]
	\|-		\|-
	\|[[SSIS:MatchUp:Survivorship Pass-Through\|Survivorship/Pass-Through]]		\|[[SSIS:MatchUp:Survivorship Pass-Through\|Survivorship/Pass-Through]]

Admin: Created page with "← SSIS Reference {| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; pa..."

2014-11-20T23:12:16Z

Created page with "← SSIS Reference {| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; pa..."

New page

[[SSIS:Reference|← SSIS Reference]]

{| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; padding:8px 8px;"
! style="text-align:left; color:black; border-bottom:1px solid #aaaaaa;"|'''MatchUp Navigation'''
|-
| style="padding-right:220px;"|
|-
|[[SSIS:MatchUp|Overview]]
|-
|[[SSIS:MatchUp Tutorial|Tutorial]]
|-
|
{| class="mw-collapsible mw-collapsed" style="width:100%;"
|- align="left"
! style="color:black;"|[[SSIS:MatchUp:Advanced Configuration|Advanced Configuration]]
|-
|[[SSIS:MatchUp:Advanced Configuration#On-Premise|On-Premise]]
|}
|-
|
{| class="mw-collapsible mw-collapsed" style="width:100%;"
|- align="left"
! style="color:black;"|MatchUp Tabs
|-
|[[SSIS:MatchUp:Matchcode|Matchcode]]
|-
|[[SSIS:MatchUp:Field Mapping|Field Mapping]]
|-
|[[SSIS:MatchUp:Options|Options]]
|-
|[[SSIS:MatchUp:Survivorship Pass-Through|Survivorship/Pass-Through]]
|-
|[[SSIS:MatchUp:Lookup Pass-Through|Lookup Pass-Through Columns]]
|-
|[[SSIS:MatchUp:Output Filter|Output Filter]]
|}
|-
|
{| class="mw-collapsible mw-collapsed" style="width:100%;"
|- align="left"
! style="color:black;"|[[SSIS:MatchUp:Matchcode Editor|Matchcode Editor]]
|-
|[[SSIS:MatchUp:Matchcode List|Matchcode List]]
|-
|[[SSIS:MatchUp:Component List|Component List]]
|-
|[[SSIS:MatchUp:Component Properties|Component Properties]]
|-
|[[SSIS:MatchUp:Algorithms|Algorithms]]
|}
|-
|
{| class="mw-collapsible" style="width:100%;"
|- align="left"
! style="color:black;"|[[SSIS:MatchUp:Matchcodes:Overview|Matchcodes Overview]]
|-
|[[SSIS:MatchUp:Matchcodes:Component Combinations|Component Combinations]]
|-
|[[SSIS:MatchUp:Matchcodes:Blank Field Mapping|Blank Field Mapping]]
|-
|[[SSIS:MatchUp:Matchcodes:Mapping|Mapping]]
|-
|[[SSIS:MatchUp:Matchcodes:Optimization|Optimization]]
|-
|[[SSIS:MatchUp:Matchcodes:Swap Matching|Swap Matching]]
|}
|-
|
{| class="mw-collapsible mw-collapsed" style="width:100%;"
|- align="left"
! style="color:black;"|[[SSIS:MatchUp:Result Codes|Result Codes]]
|-
|[[Returned Result Codes:Enterprise Solutions#Data Quality Components for SSIS|Returned Result Codes]]
|-
|[[Result Codes]]
|}
|}

{{CustomTOC}}

Some matchcodes process much faster than others in spite of the fact that they detect the same matches. This section will assist in creating the most efficient matchcodes. Ninety-nine percent of the time, clicking the Optimize button in the Matchcode Editor will sufficiently optimize a matchcode. This discussion is included so developers can better understand why certain things are done while optimizing, as well as what can be done to make the optimizer work more effectively.

Optimizing can make a significant difference in processing speed. 58-hour runs have been reduced to four hours simply by optimizing the matchcode.

It is important, however, that the developer verifies that a matchcode works in the intended way before attempting any optimizations. If a matchcode is not functioning properly, these optimizations will not help, and could quite possibly make the situation worse.

==Component Sequence==
As discussed in the previous section, the first component of a matchcode has certain restrictions:
*It must be used in every combination.
*It cannot use certain types of Fuzzy Matching: Containment; Frequency; Fast Near; Frequency Near or Accurate Near (other types are okay, though).
*It cannot use Initial Only matching.
*It cannot use One Blank Field matching.
*It cannot use Swap matching.

If the matchcode's second component also follows these conditions, MatchUP Object will incorporate it into its clustering scheme (see page 3 for more information on clustering). Additional components, if they follow in sequence (third, fourth, and so on), will be used if they too satisfy these conditions. Incorporating a component into a cluster greatly reduces the number of comparisons MatchUP Object has to perform which, in turn, speeds up your processing.

This is a simple example of optimization:

{|class="alternate01" cellspacing="0"
!Component
!Size
!Fuzzy
!Blank
!1
!2
|-
|ZIP/PC
|5
|No
|Yes
|X
|X
|-
|Street #
|5
|No
|Yes
|X
|
|-
|Street Name
|5
|No
|No
|X
|
|-
|PO Box
|10
|No
|No
|
|X
|-
|Last Name
|5
|No
|Yes
|X
|X
|}

As shown here, MatchUP Object will only cluster by ZIP/PC. But note that the last component satisfies all the conditions listed earlier.

{|class="alternate01" cellspacing="0"
!Component
!Size
!Fuzzy
!Blank
!1
!2
|-
|ZIP/PC
|5
|No
|Yes
|X
|X
|-
|Last Name
|5
|No
|Yes
|X
|X
|-
|Street #
|5
|No
|Yes
|X
|
|-
|Street Name
|5
|No
|No
|X
|
|-
|PO Box
|10
|No
|No
|
|X
|}

This simple optimization will produce significant improvements in speed. The Matchcode Optimizer will always perform this optimization.

==Fuzzy Algorithms==
Fuzzy algorithms fall into two categories: early matching and late matching.

Early matching algorithms are algorithms where a string is transformed into a (usually shorter) representation and comparisons are performed on this result. In MatchUP, these transformations are performed during key generation (the BuildKey function in each interface), which means that the early matching algorithms pay a speed penalty once per record: as each record’s key is built.

Late matching algorithms are actual comparison algorithms. Usually, one string is shifted in one direction or another, and often a matrix of some sort is used to derive a result. These transformations are performed during key comparison. As a result, late matching algorithms pay a speed penalty every time a record is compared to another record. This may happen several hundred times per record.

Obviously, late matching is much slower than early matching. If a particular matchcode is very slow, changing to a faster fuzzy matching algorithm may improve the speed. Often, a faster algorithm will give nearly the same results, but it is a good idea to test any such change before processing live data.

The fuzzy algorithms, ranked from slowest to fastest:

{|class="alternate01" cellspacing="0"
!Algorithm
!Late or Early
!Speed (10 = fastest)
|-
|Accurate Near
|Late
|1
|-
|Fast Near
|Late
|3
|-
|Containment
|Late
|4
|-
|Frequency Near
|Late
|4
|-
|Frequency
|Late
|6
|-
|Phonetex
|Early
|7
|-
|Soundex
|Early
|8
|-
|Vowels Only
|Early
|9
|-
|Numerics Only
|Early
|9
|-
|Consonants Only
|Early
|9
|-
|Alphas Only
|Early
|9
|-
|Exact
|N/A
|10
|}

The speed values are only rough estimates.

Another benefit of using a faster fuzzy algorithm is that an application may be able to exploit the component sequence optimization shown earlier. All of the early matching algorithms satisfy the restrictions for first components.

The Matchcode Optimizer will not perform this optimization as it can have a significant impact on matching results.

==Unnecessary Components==
Components that are not used in any combinations (in other words, they have no X's in columns 1 through 16) are a sign of poor matchcode design.

Take the following matchcode:

{|class="alternate01" cellspacing="0"
!Component
!Size
!Fuzzy
!Blank
!1
!2
|-
|ZIP/PC
|5
|No
|Yes
|X
|X
|-
|Last Name
|5
|No
|Yes
|X
|X
|-
|First Name
|5
|No
|Yes
|
|
|-
|Street #
|5
|No
|Yes
|X
|
|-
|Street Name
|5
|No
|No
|X
|
|-
|PO Box
|10
|No
|No
|
|X
|}

First name is not being used in any combination. Perhaps it was used in a combination that has since been removed from this matchcode, but is no longer necessary.

==Unnecessary Combinations==
Take the following matchcode:

{|class="alternate01" cellspacing="0"
!Component
!Size
!Fuzzy
!Blank
!1
!2
!3
!4
|-
|ZIP5
|5
|No
|Yes
|X
|X
|X
|X
|-
|Last Name
|5
|No
|Yes
|X
|X
|X
|X
|-
|First Name
|5
|No
|Yes
|
|
|
|
|-
|Street #
|5
|No
|Yes
|X
|
|X
|
|-
|Street Name
|5
|No
|No
|X
|
|X
|
|-
|PO Box
|10
|No
|No
|
|X
|
|X
|}

Here are the four conditions for matching:

{|class="alternate01" cellspacing="0"
!align="right"|Condition #1:
|ZIP/PC
|Last Name
|First Name
|Street #
|Street Name
|
|-
!align="right"|Condition #2:
|ZIP/PC
|Last Name
|First Name
|
|
|PO Box
|-
!align="right"|Condition #3:
|ZIP/PC
|Last Name
|
|Street #
|Street Name
|
|-
!align="right"|Condition #4:
|ZIP/PC
|Last Name
|
|
|
|PO Box
|}

There is no match that will be detected by condition #1 that would not be detected by condition #3. Similarly, matches found by condition #2 will always be found by condition #4. In other words, condition 3 is a subset of condition 1, and condition 2 is a subset of condition 4. Subsets are rarely desirable.

So either conditions 1 and 2 aren’t needed or conditions 3 and 4 were a mistake. If conditions 1 and 2 are eliminated, the First Name component should also be removed, as it will not be needed.

[[Category:SSIS]]
[[Category:SSIS MatchUp]]

SSIS:MatchUp:Matchcodes:Optimization - Revision history

Admin at 20:33, 25 May 2018

Admin at 00:20, 14 November 2015

Admin at 22:10, 21 November 2014

Admin: Created page with "← SSIS Reference {| class="mw-collapsible" cellspacing="2" style="background-color:#f9f9f9; border:1px solid #aaaaaa; font-size:9pt; color:#0645ad; pa..."