Overview

Introduction

The Create a Data Quality Project to Clean Data module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Working with Data Quality Services to Clean Data
  2. Using DQS Cleansing Transformation
  3. Performing Data Match using Data Quality Services

Exercise 1 - Working with Data Quality Services to Clean Data

In this exercise, you will learn the following in Microsoft SQL Server 2012:

  • Creating a knowledge base to cleanse data using knowledge discovery
  • Editing the knowledge base domain
  • Creating an SSIS project to clean dirty data in a SQL Server data source using the DQS Cleansing transformation
  • Creating a DQS project to cleanse data in a SQL Server data source

Exercise 2 - Using DQS Cleansing Transformation

You can use DQS Cleansing transformation in your SSIS package for correcting data to maintain data integrity. This transformation is a knowledge driven data quality service. To use this transformation, you need to first create a knowledge base applicable to the data in the data sources that needs to be cleaned. Once the required knowledge base is created, you can configure the DQS Cleansing transformation from within the Integration Services to use the created Knowledge Base for correcting the incorrect data in the connected data source.

In this task, you will create an SSIS package to extract data from a source table, correct any incorrect data in the table, and load the corresponding corrected and correct entries into the respective destination tables. You will use an OLE DB Source adapter to read data from a source table in the AdventureWorks2012 database table. You will then use the DQS Cleansing transformation and connect it with the OLE DB Source adapter. You will then configure the DQS Cleansing transformation to connect with the Knowledge Base EmpDepartmentInformation to enable it to correct the incorrect data, if any in the connected data source.

Exercise 3 - Performing Data Match using Data Quality Services

In this exercise, you will learn the following in Microsoft SQL Server 2012:

  • Create a knowledge base to perform matching activity and use the knowledge base in a Data Quality project

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.