Tutorial:Data quality project using Address Check
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
- Select New Knowledge Base.
- Enter a Name and Description for the New Knowledge Base.
- Ensure that Domain Management is selected.
- Wait a few moments for the new knowledge base to be created.
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
- Select the Create a domain icon.
- Enter a Domain Name and Description for the new domain.
- Click OK.
- Make as many new domains as you need. In order to group the domains you create a composite domain:
- Click the Create a composite domain icon.
- Enter a Composite Domain Name and Description.
- 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.
- Click OK.
- Make as many new composite domains as you need.
- Under each composite domain, select the Reference Data tab.
- Click Browse and check the check box next to Melissa Data - Address Check.
- 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.
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.
This determines the number of possible suggestions you will receive for an invalid record that can be corrected.
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
- Once you are satisfied with your Domain Management, click Finish.
- You will then be prompted to publish the Knowledge Base with the latest changes.
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
- Select New Data Quality Project.
- Enter a Name and Description for the new data quality project.
- Then under the Use Knowledge Base drop-down menu, select the Knowledge Base you created.
- Ensure that Cleansing is selected under Select Activity.
You must map your data source to the knowledge base.
- Select a Data Source.
- If you select SQL Server:
- Set the Database and Table/View.
- Click Browse... and select the desired excel file.
- Select the Worksheet you will use.
- 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
- 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.
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.
This tab contains the records that are invalid but have suggestions for correction.
This tab contains records that are not in the knowledge base and cannot be verified.
This tab contains records that could not be verified and are invalid.
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.
This tab contains records that have been verified as correct.
Once you have chosen how to proceed with the data cleansing, click Next.
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.
- Enter a Name (or names, depending on your destination type) for your cleansed data.
- Click Export.
- Once the file download is complete, the progress bar will display 100% complete.