Skip to main content

mSQL - matching output SP's

Lauren Wheeler
  • Updated

 

msp_OutputMatchingPairs

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string.

 

After running msp_ FindMatches, this will output all matching record pairs to the ‘matching_pairs’ table, ordered by the Score column.

 

Setting

Description

outputSettings->matchingPairsTable

Specifies the name of the matching pairs table that will be produced.

outputSettings->matchesTable

Specifies that name of the matches table that will be required to generate the matching pairs table.

dataSources

Specifies the datasource containing the specification for the source data that will be combined with the matches table to produce the matching_pairs table.

 

Matching_pairs table

 

Column

Description

Score

Match Score for the matching pair.

ID_1

Reference ID of the first record in the matching pair.

ID_2

Reference ID of the second record in the matching pair.

MatchRef

 

 

Note that the matching_pairs table also contains the source fields for each record mapped in the datasource within the configuration file, allowing you to view the actual data that has matched.


msp_OutputMatchingGroups

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string.

After running msp_ GroupMatches, this will output all groups of matching records to the ‘matching_groups’ table, ordered by the MatchRef column.

 

Setting

Description

outputSettings->matchingGroupsTable

Specifies the name of the matching groups table that will be produced.

outputSettings->groupedMatchesTable

Specifies that name of the matches_grouped table that will be required to generate the matching_groups table.

dataSources

Specifies the datasource containing the specification for the source data that will be combined with the matches_grouped table to produce the matching_pairs table.

 

Matching_groups table

 

Column

Description

MatchRef

Reference ID for the matching group

ID

ID of record

 

Note that the matching_groups table also contains the source fields for each record mapped in the datasource within the configuration file, allowing you to view the actual data that has matched.

 


msp_OutputDuplicates

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string.

After running msp_GroupMatches, this will output all the non-master duplicate records (i.e. all records where the unique ref is different from the MatchRef) that are to be removed from the source table(s).

 

Setting

Description

outputSettings->duplicatesTable

Specifies the name of the duplicates table that will be produced.

outputSettings->groupedMatchesTable

Specifies that name of the matches_grouped table that will be required to generate the matching_groups table.

dataSources

Specifies the datasource containing the specification for the source data that will be combined with the matches_grouped table to produce the duplicates table.

 

Duplicates table

This table contains the non-master duplicate records following the matching process. The structure of the table is determined by the fields mapped in the datasource (i.e. it contains an ID field, but also the fields that you have mapped in the datasource).

 

msp_OutputDedupedTable

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string.

 

This effectively produces the opposite of msp_OutputDuplicates. All records from source table(s) are output, except for the identified non-master duplicate records.

 

Setting

Description

outputSettings->dedupedTable

Specifies the name of the deduped table that will be produced.

outputSettings->groupedMatchesTable

Specifies that name of the matches_grouped table that will be required to generate the matching_groups table.

dataSources

Specifies the datasource containing the specification for the source data that will be combined with the matches_grouped table to produce the deduped table.

 

Deduped table

This table contains only the master records following the matching process. The structure of the table is determined by the fields mapped in the datasource (i.e. it contains an ID field, but also the fields that you have mapped in the datasource).

 

msp_TagMatchingResultsWithGroupLevel

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string
  • Level – can be Individual, Family, Household, Business or Custom.

 

This procedure renames any of the grouping related tables produced by the preceding four procedures and the msp_GroupMatches Procedure. Each table is renamed by giving it a suffix matching the value passed in the level parameter.

 

Setting

Description

outputSettings->duplicates

Name of the duplicates table to be renamed.

outputSettings->dedupedTable

Name of the deduped table to be renamed.

outputSettings->matchingGroupsTable

Name of the matching_groups table to be renamed.

outputSettings->groupedMatchesTable

Specifies that name of the matches_grouped table to be renamed.

dataSources

Specifies the datasource containing the connection string to the database.

 


 

Related articles