1. Data Management
  2. Data Quality Studio
  3. General setup

Set up duplicate check

You can use duplicate check rules to check if data doesn't already exist in D365 F&SCM.

Before you can set up a duplicate check rule, set up the duplicate check to be applied.

You can apply these types of duplicate checks:

  • Basic matching: Checks which combination of table fields has duplicate values. So, the combination of field values must be unique in the table.
  • Fuzzy matching: Checks for duplicates applying fuzzy logic. A fuzzy duplicate check compares values from several fields. Based on the comparison, a duplicate score is calculated.


Data quality administrator Data quality administrator The data quality administrator (DQSDataQualityAdministrator) can set up and maintain: Data quality policies Data quality studio parameters Data quality studio general setup Start Start Which type of  duplicate check  do you need? Which type of  duplicate check  do you need? Set up duplicate check - Basic matching Set up duplicate check - Basic matching Use a basic duplicate check to ensure that no (almost) similar records exist in a table. To set up a duplicate check rule you can use a duplicate check of type Basic matching. If the desired Basic matching duplicate check does not exist, set up a new Basic matching duplicate check. For each Basic matching duplicate check, define which combination of table fields is checked on duplicate values. So, the combination of field values must be unique in the table. Only one record can have this combination of field values. Procedure 1. Click Data quality management. 2. Click Duplicate checks. 3. Click New. 4. In the Duplicate check name field, type a value. 5. In the Duplicate check type field, select 'Basic matching'. 6. In the Table name field, enter or select a value. 7. Sub-task: Select the fields to be checked on duplicate values. 8. In the Fields section, click Add. 9. In the Available list, find and select the desired fields. 10. Click -> (Add) to add the fields to the Selected list. 11. Click OK. 12. Sub-task: Select the fields that can be merged for a duplicate record. 13. In the Merge fields section, click Add. 14. On the dialog, in the list, find and select the desired fields. 15. Click Add (->). 16. Click OK. 17. Sub-task: Activate the duplicate check. 18. If the duplicate check setup is finished, you can make it available for selection on the duplicate check rules. Select Yes in the Active field. Note: If active, you cannot edit the duplicate check. If active and used in an active data quality policy version, you cannot make the duplicate check inactive. 19. Close the page. New or changed  dynamic query  needed? New or changed  dynamic query  needed? Set up dynamic queries

Set up dynamic queries

Use a dynamic query to find one or more records in the database. The found records are the input for further processing.

You can use a dynamic query on:

  • An enrichment rule of type Data query.
  • An action rule of type Data query.
  • A condition that is applied to a validation rule, duplicate check rule, or action rule.
  • A configurable lookup of type Dynamic query. You can apply the configurable lookup on a validation rule of type Configurable lookup.

To define a query range, you can use a function.

 Phonetic search  needed in fuzzy  duplicate check? Phonetic search  needed in fuzzy  duplicate check? Set up phonetic search rules Set up phonetic search rules On duplicate checks of type 'Fuzzy matching', you can apply phonetic search algorithms. You can use a phonetic search algorithm to check on duplicate names that sound similar, for example, John and Jon. To apply a phonetic search algorithm to a duplicate check, set up a phonetic search rule and link it to a field in a duplicate check. The supported phonetic search algorithm is Metaphone. You can apply these versions of the Metaphone algorithm: Double metaphone Metaphone 3 Advanced setup You are advised to start applying phonetic search with the basic setup, that is with the selected Phonetic search algorithm. Based on testing and experience, you can finetune the phonetic search rule setup by defining: A maximum length: The maximum number of characters for a phonetic search key. The shorter a phonetic search key is, the fuzzier the duplicate check result is. Words to be ignored: You can define the words for which you do not want to create a phonetic search key. Procedure 1. Go to Data quality studio > Setup > Phonetic search rules. 2. Click New. 3. In the Phonetic search rule field, type a value. 4. In the Phonetic search algorithm field, select an option. 5. Sub-task: Set phonetic search rule parameters. 6. Expand the Parameters section. 7. In the Phonetic key maximum length field, enter a number. 8. Sub-task: Define words for which no phonetic search key is desired. 9. Expand the Ignored words section. 10. Click Add line. 11. In the Word field, type a value. Set up duplicate check - Fuzzy matching Set up duplicate check - Fuzzy matching Use a fuzzy duplicate check to ensure that no (almost) similar records exist in a table. To set up a fuzzy duplicate check rule, use a duplicate check of type Fuzzy matching. If the desired Fuzzy matching duplicate check does not exist, set up a new Fuzzy matching duplicate check. A Fuzzy matching duplicate check checks for duplicates applying fuzzy logic. A Fuzzy matching duplicate check compares, for a selected record, several field values with the values of the same fields of other records. Based on the comparison, a duplicate score is calculated. On the duplicate check, you define: Which dynamic query is used. The dynamic query defines the records that are checked for duplicate values and the fields that can be checked for duplicate values. In the dynamic query, the first defined table must be the main table, on which you want to check for duplicates. This table must be the same table that you define in the duplicate check header, in the Table name field. A form can use several related tables. In this case, in the dynamic query, use data entities for the next table records. For each of the data entity table records, define the applicable parent. Use the data entities to select the fields that you want to check for duplicates. The fields which values are checked for duplicates. You only can use fields that are defined in the dynamic query. For each field, the weightage. The weightage expresses the importance of a duplicate value. Express the weightage in a number (with or without decimals). Define the weightage number in such a way that the importance is expressed compared to the other fields. If you do not define a weightage for a field, the field value is not checked for duplicates. The threshold that is used to indicate if a record is a possible duplicate. The threshold is expressed in a percentage. Only if the calculated duplicate score for a record is equal to or higher than the threshold, a record is marked as possible duplicate. The duplicate score is calculated in this way: [Weightage sum of fields with duplicates] / [Total weightage sum] * 100% Example: Duplicate check on CustTable Threshold: 50% Table name Datasource name Field Field label Weightage CustTable CustTable AccountNum Customer account   CustCustomerV3Entity CustCustomerV3Entity AddressStreet Street 1 CustCustomerV3Entity CustCustomerV3Entity AddressZipCode ZIP/postal code 1 CustCustomerV3Entity CustCustomerV3Entity OrganizationName Organization name 6 CustCustomerV3Entity CustCustomerV3Entity PrimaryContactEmail Primary email 3 CustCustomerV3Entity CustCustomerV3Entity PrimaryContactPhone Primary phone 3 Calculation examples: Duplicate values exist in the Primary email field and in the Primary phone field. The duplicate score is: 6 / 14 * 100 = 42,86. The record is not reported as possible duplicate. Duplicate values exist in the Organization name field and the ZIP/postal code field. The duplicate score is: 7 / 14 * 100 = 50. The record is reported as possible duplicate. Procedure 1. Click Data quality management. 2. Click Duplicate checks. 3. Click New. 4. In the Duplicate check name field, type a value. 5. In the Duplicate check type field, select 'Fuzzy matching'. 6. In the Query field, enter or select a value. 7. In the Threshold % field, enter a number. 8. Sub-task: Select the fields to be checked on duplicate values. 9. In the Fields section, click Add. 10. In the Select fields dialog, in the available grid, select the desired fields. 11. Click Add. 12. Click OK. 13. Select the Use phonetic search check box. 14. In the Phonetic search rule field, enter or select a value. 15. The weightage expresses the importance of a duplicate value. Express the weightage in a number (with or without decimals). Define the weightage number in such a way that the importance is expressed compared to the other fields. In the Weightage field, enter a number. Note: If you do not define a weightage for a field, the field value is not checked for duplicates. 16. Select the Hide in results check box. 17. Sub-task: Select the fields that can be merged for a duplicate record. 18. In the Merge fields section, click Add. 19. On the dialog, in the list, find and select the desired fields. 20. Click Add (->). 21. Click OK. 22. Sub-task: Activate duplicate check. 23. If the duplicate check setup is finished, you can make it available for selection on the duplicate check rules. Select Yes in the Active field. Note: If active, you cannot edit the duplicate check. If active and used in an active data quality policy version, you cannot make the duplicate check inactive. 24. Close the page. Phonetic search  set up in fuzzy  duplicate check? Phonetic search  set up in fuzzy  duplicate check? Synchronize phonetic search keys Synchronize phonetic search keys A fuzzy duplicate check with phonetic search rules applied, uses phonetic search keys to search for possible duplicate values. For better performance of duplicate checks, the phonetic search keys for existing data must be generated and synchronized regularly. The synchronized phonetic search keys are stored in the DQSPhoneticKey table. To have up-to-date phonetic search keys, you are advised to synchronize the phonetic search keys several times per day. Regular synchronization of phonetic search keys is required due to: New or changed data on which fuzzy duplicate checks are done. New or changed fuzzy duplicate checks phonetic search setup. On synchronization, phonetic search keys are created for: Active fuzzy duplicate checks. Only for the table fields with phonetic search setup. The phonetic search keys are created considering the setup of the applicable phonetic search rule: Phonetic search algorithm. Maximum phonetic search key length. Words to be ignored. Procedure 1. Click Data quality management. 2. Click Duplicate checks. 3. In the list, find and select the desired active duplicate check with at least one table field to which phonetic search is applied. 4. Click Synchronize phonetic search keys. 5. In the Duplicate check name field, type a value. 6. Select No in the Force recreate field. 7. Sub-task: Set up recurring batch processing. 8. Expand the Run in the background section. 9. Select Yes in the Batch processing field and fill in the other fields as desired. 10. Click Recurrence and fill in the fields as desired. 11. Click OK. 12. Click OK. Notes In case of a Data Entry Workflow integration, when you create or change a record, and phonetic search is applicable, the phonetic search keys are created automatically. End End Basic  matching Fuzzy  matching Yes No Yes No Yes No

Activities

Name Responsible Description

Set up duplicate check - Basic matching

Data quality administrator

Use a basic duplicate check to ensure that no (almost) similar records exist in a table.

To set up a duplicate check rule you can use a duplicate check of type Basic matching. If the desired Basic matching duplicate check does not exist, set up a new Basic matching duplicate check.

For each Basic matching duplicate check, define which combination of table fields is checked on duplicate values. So, the combination of field values must be unique in the table. Only one record can have this combination of field values.

Set up dynamic queries

Data quality administrator

Use a dynamic query to find one or more records in the database. The found records are the input for further processing.

You can use a dynamic query on:

  • An enrichment rule of type Data query.
  • An action rule of type Data query.
  • A condition that is applied to a validation rule, duplicate check rule, or action rule.
  • A configurable lookup of type Dynamic query. You can apply the configurable lookup on a validation rule of type Configurable lookup.

To define a query range, you can use a function.

Set up phonetic search rules

Data quality administrator

On duplicate checks of type 'Fuzzy matching', you can apply phonetic search algorithms. You can use a phonetic search algorithm to check on duplicate names that sound similar, for example, John and Jon.

To apply a phonetic search algorithm to a duplicate check, set up a phonetic search rule and link it to a field in a duplicate check.

The supported phonetic search algorithm is Metaphone. You can apply these versions of the Metaphone algorithm:

  • Double metaphone
  • Metaphone 3

Advanced setup

You are advised to start applying phonetic search with the basic setup, that is with the selected Phonetic search algorithm.

Based on testing and experience, you can finetune the phonetic search rule setup by defining:

  • A maximum length: The maximum number of characters for a phonetic search key. The shorter a phonetic search key is, the fuzzier the duplicate check result is.
  • Words to be ignored: You can define the words for which you do not want to create a phonetic search key.

Set up duplicate check - Fuzzy matching

Data quality administrator

Use a fuzzy duplicate check to ensure that no (almost) similar records exist in a table.

To set up a fuzzy duplicate check rule, use a duplicate check of type Fuzzy matching. If the desired Fuzzy matching duplicate check does not exist, set up a new Fuzzy matching duplicate check.

A Fuzzy matching duplicate check checks for duplicates applying fuzzy logic. A Fuzzy matching duplicate check compares, for a selected record, several field values with the values of the same fields of other records. Based on the comparison, a duplicate score is calculated.

On the duplicate check, you define:

  • Which dynamic query is used. The dynamic query defines the records that are checked for duplicate values and the fields that can be checked for duplicate values.
    In the dynamic query, the first defined table must be the main table, on which you want to check for duplicates. This table must be the same table that you define in the duplicate check header, in the Table name field.
    A form can use several related tables. In this case, in the dynamic query, use data entities for the next table records. For each of the data entity table records, define the applicable parent. Use the data entities to select the fields that you want to check for duplicates.
  • The fields which values are checked for duplicates. You only can use fields that are defined in the dynamic query.
  • For each field, the weightage. The weightage expresses the importance of a duplicate value. Express the weightage in a number (with or without decimals). Define the weightage number in such a way that the importance is expressed compared to the other fields. If you do not define a weightage for a field, the field value is not checked for duplicates.
  • The threshold that is used to indicate if a record is a possible duplicate. The threshold is expressed in a percentage. Only if the calculated duplicate score for a record is equal to or higher than the threshold, a record is marked as possible duplicate.
    The duplicate score is calculated in this way: [Weightage sum of fields with duplicates] / [Total weightage sum] * 100%

Example:

Duplicate check on CustTable

Threshold: 50%

Table name Datasource name Field Field label Weightage
CustTable CustTable AccountNum Customer account  
CustCustomerV3Entity CustCustomerV3Entity AddressStreet Street 1
CustCustomerV3Entity CustCustomerV3Entity AddressZipCode ZIP/postal code 1
CustCustomerV3Entity CustCustomerV3Entity OrganizationName Organization name 6
CustCustomerV3Entity CustCustomerV3Entity PrimaryContactEmail Primary email 3
CustCustomerV3Entity CustCustomerV3Entity PrimaryContactPhone Primary phone 3

Calculation examples:

  • Duplicate values exist in the Primary email field and in the Primary phone field. The duplicate score is: 6 / 14 * 100 = 42,86. The record is not reported as possible duplicate.
  • Duplicate values exist in the Organization name field and the ZIP/postal code field. The duplicate score is: 7 / 14 * 100 = 50. The record is reported as possible duplicate.

Synchronize phonetic search keys

Data quality administrator

A fuzzy duplicate check with phonetic search rules applied, uses phonetic search keys to search for possible duplicate values. For better performance of duplicate checks, the phonetic search keys for existing data must be generated and synchronized regularly. The synchronized phonetic search keys are stored in the DQSPhoneticKey table.

To have up-to-date phonetic search keys, you are advised to synchronize the phonetic search keys several times per day. Regular synchronization of phonetic search keys is required due to:

  • New or changed data on which fuzzy duplicate checks are done.
  • New or changed fuzzy duplicate checks phonetic search setup.

On synchronization, phonetic search keys are created for:

  • Active fuzzy duplicate checks.
  • Only for the table fields with phonetic search setup.

The phonetic search keys are created considering the setup of the applicable phonetic search rule:

  • Phonetic search algorithm.
  • Maximum phonetic search key length.
  • Words to be ignored.

Provide feedback