SSIS:MatchUp:Golden Record - Revision history

Admin at 23:45, 30 December 2016

2016-12-30T23:45:36Z

← Older revision		Revision as of 23:45, 30 December 2016
Line 231:		Line 231:
	Double-clicking any element in the expression elements section will insert it into the "Use the specified expression:" field.		Double-clicking any element in the expression elements section will insert it into the "Use the specified expression:" field.

	For more information, see [[SSIS:MatchUp:Custom Expression:Elements\|Expression Elements]].		For more information, see [[SSIS:MatchUp:Custom Expression Elements\|Custom Expression Elements]].

	The following elements are available for your use:		The following elements are available for your use:
	*[[SSIS:MatchUp:Custom Expression:Elements#Columns\|Columns]]		*[[SSIS:MatchUp:Custom Expression Elements#Columns\|Columns]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Variables\|Variables]]		*[[SSIS:MatchUp:Custom Expression Elements#Variables\|Variables]]
	*[[SSIS:MatchUp:Custom Expression:Elements#String Functions/Operators\|String Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#String Functions/Operators\|String Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Numeric Functions/Operators\|Numeric Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#Numeric Functions/Operators\|Numeric Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Boolean Functions/Operators\|Boolean Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#Boolean Functions/Operators\|Boolean Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Date/Time Functions/Operators\|Date/Time Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#Date/Time Functions/Operators\|Date/Time Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Misc. Functions/Operators\|Misc. Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#Misc. Functions/Operators\|Misc. Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#DataType Casts\|DataType Casts]]		*[[SSIS:MatchUp:Custom Expression Elements#DataType Casts\|DataType Casts]]

	For further information on SSIS Operators, see http://msdn.microsoft.com/en-us/library/ms137538(v=sql.100).aspx.		For further information on SSIS Operators, see http://msdn.microsoft.com/en-us/library/ms137538(v=sql.100).aspx.

Admin at 00:23, 14 November 2015

2015-11-14T00:23:35Z

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

Admin at 02:24, 25 November 2014

2014-11-25T02:24:22Z

← Older revision		Revision as of 02:24, 25 November 2014
Line 208:		Line 208:
	!Address		!Address
	\|-		\|-
	\|Joseph Doe 123 Main St		\|Joseph Doe
			\|123 Main St
	\|-		\|-
	\|Joe Doe 123 Main St		\|Joe Doe
			\|123 Main St
	\|-		\|-
	\|J. Doe 123 Main St		\|J. Doe
			\|123 Main St
	\|}		\|}

Line 224:		Line 227:
	!Results		!Results
	\|-		\|-
	\|Joseph Doe 123 Main St MS02		\|Joseph Doe
			\|123 Main St
			\|MS02
	\|-		\|-
	\|Joe Doe 123 Main St MS03		\|Joe Doe
			\|123 Main St
			\|MS03
	\|-		\|-
	\|J. Doe 123 Main St MS03		\|J. Doe
			\|123 Main St
			\|MS03
	\|}		\|}

Line 237:		Line 246:
	This algorithm allows for selecting the Golden Record based on the Quality of Your Data. The Data Quality Score is required to be used in tandem with the Melissa Data Contact Verify Component (CVC).		This algorithm allows for selecting the Golden Record based on the Quality of Your Data. The Data Quality Score is required to be used in tandem with the Melissa Data Contact Verify Component (CVC).

	For more information on the Contact Verify Component, ~~click here~~: ~~http~~:~~//www~~.~~melissadata.com/webhelp/ssis/cvc/index.htm~~		For more information on the Contact Verify Component, see [[SSIS:CVC:Overview\|CVC Overview]].


Line 246:		Line 255:
	:Input requires a valid column containing the Melissa Data Returned Result Codes returned by the CVC.		:Input requires a valid column containing the Melissa Data Returned Result Codes returned by the CVC.

	:For more information on the CVC Results, ~~click here~~		:For more information on the CVC Results, see [[SSIS:CVC:Custom Output Filters\|Custom Output Filters]].
	:~~http~~:~~//www~~.~~melissadata.com/webhelp/ssis/cvc/index.htm#output/customoutputfilters.htm~~

	===Rule===		===Rule===
Line 272:		Line 281:
	;Data Quality Score		;Data Quality Score
	:The Data Quality Score evaluates all possible results combined from Address, Email, Name, Phone and GeoCode in a single column.		:The Data Quality Score evaluates all possible results combined from Address, Email, Name, Phone and GeoCode in a single column.


			==Custom Expression==
			The Custom Expression Algorithm allows you to set up a custom expression of your own design.


			[[File:SSIS_MU_GoldenRecord_ExpressionBuilder.png\|link=]]


			===Expression===
			;Use a pre-built expression
			:To use a pre-built expression, select one from the drop-down list. You may also remove the selected pre-built expression by clicking the self-titled button.

			;Use the specified expression
			:Alternatively, you may create your own expression in this field.
			:*Clicking the text button will check your custom expression for correct syntax.
			:*To save your custom built expression, click the "Save the above expression as a new Pre-Built Expression..." button.

			===Precedence===
			Choose one of the two radio buttons here to designate which result takes precendence over another when comparing two results.
			*The result with the Lowest Value
			*The result with the Highest Value

			===Expression Elements===
			Double-clicking any element in the expression elements section will insert it into the "Use the specified expression:" field.

			For more information, see [[SSIS:MatchUp:Custom Expression:Elements\|Expression Elements]].

			The following elements are available for your use:
			*[[SSIS:MatchUp:Custom Expression:Elements#Columns\|Columns]]
			*[[SSIS:MatchUp:Custom Expression:Elements#Variables\|Variables]]
			*[[SSIS:MatchUp:Custom Expression:Elements#String Functions/Operators\|String Functions/Operators]]
			*[[SSIS:MatchUp:Custom Expression:Elements#Numeric Functions/Operators\|Numeric Functions/Operators]]
			*[[SSIS:MatchUp:Custom Expression:Elements#Boolean Functions/Operators\|Boolean Functions/Operators]]
			*[[SSIS:MatchUp:Custom Expression:Elements#Date/Time Functions/Operators\|Date/Time Functions/Operators]]
			*[[SSIS:MatchUp:Custom Expression:Elements#Misc. Functions/Operators\|Misc. Functions/Operators]]
			*[[SSIS:MatchUp:Custom Expression:Elements#DataType Casts\|DataType Casts]]

			For further information on SSIS Operators, see http://msdn.microsoft.com/en-us/library/ms137538(v=sql.100).aspx.

			For further information on SSIS Functions, see http://msdn.microsoft.com/en-us/library/ms141671(v=sql.100).aspx.


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

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-22T01:50:38Z

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" 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:Golden Record|Golden Record]]
|-
|[[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 mw-collapsed" 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}}

The Golden Record Selection Option is used for selecting the best record amongst a group of duplicate records as your remaining Master Record. This allows for having a single accurate representation of each entity in your data. Thus in a group of duplicate records for example:

{|class="alternate01" cellspacing="0"
!Name
!Address
!Last Update
|-
|John Doe
|123 Main St
|10/25/2003
|-
|John Doe
|123 Main St
|4/16/2012
|-
|John Doe
|123 Main St
|8/6/2008
|}

We can prioritize the selection of the Golden Record by latest update. The selected remaining record will be:

{|class="alternate01" cellspacing="0"
!Name
!Address
!Last Update
|-
|John Doe
|123 Main St
|4/16/2012
|}

The component allows for using pre-defined algorithms that are commonly used to select the Golden Record, or you can also write your own custom expressions for flexible rule generation.

[[File:SSIS_MU_Options.png|link=]]

There are four algorithms that can be modified by clicking the ".." button to the right of its respective algorithm.

Multiple algorithms can be selected to evaluate the Golden Record. The algorithms are ordered from 1-4 in order of priority. Select the rule and click either the up arrow or down arrow to change this order. Golden Record selection will first be evaluated by the first selected algorithm in the list. If a Golden Record cannot be determined using the first selected algorithm, it will automatically cascade down to the second selected algorithm to re-evaluate the ties. The component will continue to cascade through all selected algorithms until a single golden record can be evaluated or until all the selected algorithms have been applied.

The four algorithms are Last Updated, Most Complete, Data Quality Score, and Custom Expression.

==Last Updated==
This algorithm allows for selecting the Golden Record based on the newest or oldest Date/Time.

[[File:SSIS_MU_GoldenRecord_LastUpdated.png|link=]]

;"Last Updated" Column
:Input requires a valid column of type Date/Time (DT_Date).

;Oldest/Newest Date:
:Select whether the Golden Record should be selected based on which one has the Oldest/Newest Date.

:;Example
::For the following data:
::{|class="alternate01" cellspacing="0"
!Name
!Address
!Last Update
|-
|John Doe
|123 Main St
|10/25/2003
|-
|John Doe
|123 Main St
|4/16/2012
|-
|John Doe
|123 Main St
|8/6/2008
|}

::Selecting the Newest Date for the “Last Update” column will yield the following results:

::{|class="alternate01" cellspacing="0"
!Name
!Address
!Last Update
!Results
|-
|John Doe
|123 Main St
|4/16/2012
|MS02
|-
|John Doe
|123 Main St
|8/6/2008
|MS03
|-
|John Doe
|123 Main St
|10/25/2003
|MS03
|}

::The record containing MS02 is the selected Golden Record.

==Most Complete==
This algorithm allows for selecting the Golden Record based on the completeness/length of information.

[[File:SSIS_MU_GoldenRecord_MostComplete.png|link=]]

The options allows for selecting multiple columns for evaluation. In a group of duplicate records, the Golden Record will be selected based on the selected columns has the longest concatenated string.

Example:

For the following data:

{|class="alternate01" cellspacing="0"
!Name
!Address
|-
|Joseph Doe 123 Main St
|-
|Joe Doe 123 Main St
|-
|J. Doe 123 Main St
|}

Selecting the Name and Address columns will yield the following results:

{|class="alternate01" cellspacing="0"
!Name
!Address
!Results
|-
|Joseph Doe 123 Main St MS02
|-
|Joe Doe 123 Main St MS03
|-
|J. Doe 123 Main St MS03
|}

The record containing MS02 is the selected Golden Record.

==Data Quality Score==
This algorithm allows for selecting the Golden Record based on the Quality of Your Data. The Data Quality Score is required to be used in tandem with the Melissa Data Contact Verify Component (CVC).

For more information on the Contact Verify Component, click here: http://www.melissadata.com/webhelp/ssis/cvc/index.htm

[[File:SSIS_MU_GoldenRecord_DataQualityScore.png|link=]]

;Column Containing Result Codes
:Input requires a valid column containing the Melissa Data Returned Result Codes returned by the CVC.

:For more information on the CVC Results, click here
:http://www.melissadata.com/webhelp/ssis/cvc/index.htm#output/customoutputfilters.htm

===Rule===
There are a total of 6 rules that can be utilized.

*Check the box next to the rule(s) that you wish to use.
*Rules are also ordered from 1-6 in order of priority. Select the rule and click either the up arrow or down arrow to change this order.

;Address Quality Score
:The Address Quality Score evaluates your Address Results and selects which one has the best address composition and most deliverable.

;Name Quality Score
:The Name Quality Score evaluates your Name Results and selects which one has the most probable real person’s name.

;Phone Quality Score
:The Phone Quality Score evaluates your Phone Results and selects which one has the most valid 10-digit phone number.

;Email Quality Score
:The Email Quality Score evaluates your Email Results and selects which one has the most correct and deliverable email address.

;GeoCode Quality Score
:The GeoCode Quality Score evaluates your GeoCode Results and selects which one has the highest level of GeoCoding accuracy.

;Data Quality Score
:The Data Quality Score evaluates all possible results combined from Address, Email, Name, Phone and GeoCode in a single column.

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

← Older revision		Revision as of 23:45, 30 December 2016
Line 231:		Line 231:
	Double-clicking any element in the expression elements section will insert it into the "Use the specified expression:" field.		Double-clicking any element in the expression elements section will insert it into the "Use the specified expression:" field.

	For more information, see [[SSIS:MatchUp:Custom Expression:Elements\|Expression Elements]].		For more information, see [[SSIS:MatchUp:Custom Expression Elements\|Custom Expression Elements]].

	The following elements are available for your use:		The following elements are available for your use:
	*[[SSIS:MatchUp:Custom Expression:Elements#Columns\|Columns]]		*[[SSIS:MatchUp:Custom Expression Elements#Columns\|Columns]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Variables\|Variables]]		*[[SSIS:MatchUp:Custom Expression Elements#Variables\|Variables]]
	*[[SSIS:MatchUp:Custom Expression:Elements#String Functions/Operators\|String Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#String Functions/Operators\|String Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Numeric Functions/Operators\|Numeric Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#Numeric Functions/Operators\|Numeric Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Boolean Functions/Operators\|Boolean Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#Boolean Functions/Operators\|Boolean Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Date/Time Functions/Operators\|Date/Time Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#Date/Time Functions/Operators\|Date/Time Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#Misc. Functions/Operators\|Misc. Functions/Operators]]		*[[SSIS:MatchUp:Custom Expression Elements#Misc. Functions/Operators\|Misc. Functions/Operators]]
	*[[SSIS:MatchUp:Custom Expression:Elements#DataType Casts\|DataType Casts]]		*[[SSIS:MatchUp:Custom Expression Elements#DataType Casts\|DataType Casts]]

	For further information on SSIS Operators, see http://msdn.microsoft.com/en-us/library/ms137538(v=sql.100).aspx.		For further information on SSIS Operators, see http://msdn.microsoft.com/en-us/library/ms137538(v=sql.100).aspx.