Tutorial:Data quality project using Address Check

Getting Started

Data Quality Services uses a knowledge base to compare against a data set you provide to run a data quality project. So you must first set up a knowledge base and then run a data quality project.

Knowledge base management

The knowledge base is what you will compare your data to, so it is important to note that your data cleansing will only be as good as your knowledge base.

Setting up a new knowledge base

1. Select New Knowledge Base.
2. Enter a Name and Description for the New Knowledge Base.
• Ensure that Domain Management is selected.
3. Click Next.
• Wait a few moments for the new knowledge base to be created.

Domain management

A domain is a named data set, and a composite domain is a grouping of domains. For instance, as a domain you can have: Address, City, State, and Zip. Then for a composite domain you group these four domains under Address Record.

Setting up new domains

1. Select the Create a domain icon.
2. Enter a Domain Name and Description for the new domain.
3. Click OK.
4. Make as many new domains as you need.
In order to group the domains you create a composite domain:
5. Click the Create a composite domain icon.
6. Enter a Composite Domain Name and Description.
7. Select which domains you want to add to the composite domain by selecting the domain in the Domains List, then clicking the arrow to move the domain to Domains in Composite Domain.
8. Click OK.
9. Make as many new composite domains as you need.
10. Under each composite domain, select the Reference Data tab.
11. Click Browse and check the check box next to Melissa Data - Address Check.
12. Under the Melissa Data - Address Check section, map the RDS Schema to the Domains you created and placed under this Composite Domain.
• For example: Map AddressLine (M) to Address.
13. Click Add Schema Entry to map additional schema to domains.
14. When you are done mapping the schema, click OK.

Providers settings

With a composite domain selected, you can modify the settings of the Melissa Data - Address Check service. This includes the Auto Correction Threshold, Suggested Candidates, and Min Confidence.

Auto Correction Threshold

This decimal value sets the confidence threshold for records to be automatically corrected by Melissa Data.

Suggested Candidates

This determines the number of possible suggestions you will receive for an invalid record that can be corrected.

Min Confidence

This decimal value sets the minimum confidence required for a record to be considered valid. All other records will be set as invalid and not correctable

1. Once you are satisfied with your Domain Management, click Finish.
• You will then be prompted to publish the Knowledge Base with the latest changes.
2. Click Publish.
3. Click OK.

Data quality projects

Once you have set up a knowledge base you need to make a data quality project to compare your data against the knowledge base.

Setting up a data quality project

1. Select New Data Quality Project.
2. Enter a Name and Description for the new data quality project.
3. Then under the Use Knowledge Base drop-down menu, select the Knowledge Base you created.
• Ensure that Cleansing is selected under Select Activity.
4. Click Next.

Map

You must map your data source to the knowledge base.

1. Select a Data Source.
2. If you select SQL Server:
• Set the Database and Table/View.
3. If you select an Excel File:
• Click Browse... and select the desired excel file.
• Select the Worksheet you will use.
4. For both Data Sources, under Mappings, map the Source Columns to the corresponding Domain.
• To remove a mapping, click the Remove selected column mapping icon to the top-right of the Mappings section.
• You may click the Preview data source... icon to the top-right of the Mappings section
5. Click Next.

Cleanse

1. Click Start to begin cleansing the data.
• This will take some time to begin the upload and then cleanse the data. The Profiler willl show statistics on the data during the operation.
2. When this is finished, click Next.

Manage and View results

In this step you need to manage your data cleansing. Under the selected domain name there are five tabs: Suggested, New, Invalid, Corrected, and Correct.

The validity of records highly depends on what values you chose under Domain Management > Reference Data > Provider Settings.

Suggested

This tab contains the records that are invalid but have suggestions for correction.

New

This tab contains records that are not in the knowledge base and cannot be verified.

Invalid

This tab contains records that could not be verified and are invalid.

Corrected

This tab contains records that had invalid data and were corrected. When you approve records in the Suggested tab they are moved to this tab.

Correct

This tab contains records that have been verified as correct.

Once you have chosen how to proceed with the data cleansing, click Next.

Export

In this step you export your cleansed data into one of three Destination Types: SQL Server, CSV File, Excel File.

• If you are using 64-bit Excel, you cannot export the cleansed data to an Excel file.

1. Enter a Name (or names, depending on your destination type) for your cleansed data.
2. Click Export.
• Once the file download is complete, the progress bar will display 100% complete.
3. Click Close.
4. Click Finish to complete your data cleansing operation.