Chapter 39

Using the DQS Cleansing Transform

In the previous lesson, you created a DQS knowledge base and cleansed some data, all interactively. As you improve the quality, domain coverage, and capability of your knowledge base, it will be able to correct a larger and larger percentage of values from new incoming data. As this occurs, you will benefit from automating as much of the cleansing as possible. You may want to have values that are correct or corrected with high confidence levels to move directly into the destination. Then you can review and fix only the remaining values. This capability exists with SSIS as the DQS Cleansing Transform. Additionally, you can correct and approve or reject the remaining values using the DQS Client. There is a truly intelligent cooperation between the Cleansing Task and the DQS Client.

The Cleansing Task accepts a Data Flow as input, cleanses the data using the knowledge base of your choice, adds output meta data, and passes the Data Flow forward. A commonly used Data Flow for the Cleansing Transform is shown in Figure 39-1.

Figure 39-1

When you double-click the DQS Cleansing Task, its editor pops up, and contains only three tabs: Connection Manager, Mapping, and Advanced.

The Connection Manager tab is where you choose the connection manager that points to your DQS server. After you choose the data quality connection manager, you can select which of the knowledge bases on that server should be used by the task. At the bottom of the window is the Configure error output check box. By default, the task will fail if there is an error, but you can choose to redirect the error rows.

In the Mapping tab, you choose which columns from the input should be cleansed and associate each of those input columns with its corresponding domain. The standard output columns for each input are Source, Output, Status, Confidence, and Reason. The default column names are supplied, but you can change these names if you want. Figure 39-2 shows the Mapping tab.

Figure 39-2

While each column has a status associated with it, there is also a status associated with the entire row. This enables you to use either or both statuses. The row status tells you the worst column status, which should indicate whether the data steward should trust the row or review the values in the row. For instance, if all the column statuses are Correct, the row status should be Correct. If all column statuses are Correct, except for one which is Corrected, the row status is Corrected. Though you are free to do whatever you want with these row statuses, most examples indicate Correct and Corrected as trusted values, which are sent forward without additional scrutiny. Any other row status can require individual examination, correction and approval, or rejection.

The Advanced tab shown in Figure 39-3 contains a list of additional options that you can set.

Figure 39-3

• Standardize output—When selected, your output will be formatted using the “Format Output To” domain properties chosen for the domain. Formatting properties for domains depend on the data type of the domain:

• Confidence—When chosen, this value is provided for each source column. The confidence score between 0 and 100 indicates the level of trust that the DQS Server or relevant Reference Data Source has in the accuracy of the suggested value. Higher values indicate a higher level of confidence.
• Reason—When chosen, this value is provided for each source column. This column explains the output column value. The reason “Domain Value” indicates a valid value found in the domain. “New Value” indicates the value is not marked as error or invalid in the domain, nor does it exist as a valid value. “New Value” also includes low confidence suggestions. Other examples of reasons are “Unknown,” “Invalid,” and “Corrected to leading value.”
• Appended Data—If your domain uses a reference data source, the reference can return additional information about the domain. For instance, an address validation reference data source can provide additional longitude/latitude information.
• Appended Data Schema—Automatically selected when Appended Data is selected, this adds the schema definition you would use to interpret the information provided in the appended data column.

With the Cleansing Transform configured, you need to route the output to complete the package. You will use a Conditional Split Task to route the rows into two Data Flows: one Data Flow that will continue and be automatically added to the data warehouse, and another Data Flow that requires manual review and decisions about the data quality. Using the row status as the source, values of Correct and Corrected are routed to a “ready to use” flow. Any other value in the row status causes the rows to be routed to the “items for review” Data Flow.

Every time your package runs, a data cleansing project is created. This means the data steward can use the Client tool to interactively review the data from the SSIS cleansing in the same way that was done during knowledge discovery. You can use the DQS Client tool to open the data quality project and review, correct, approve, or reject values.

After the DQS project review is complete, you can import the new information into the knowledge base, continuously improving the knowledge base. To bring the project information into the knowledge base, open the knowledge base in the Client tool and choose Domain Management. In the Domain Values tab, choose the Import Values icon, as shown in Figure 39-4.

Figure 39-4

Try It

In this Try It, you create an SSIS package that uses the DQS Cleansing Transform. After the data is cleansed, you split the output into two Data Flows, one with acceptable values and another that requires further inspection. After you complete this lesson, you will be able to use SSIS to automatically clean your data.

You can download the completed Lesson39.dtsx and sample files for this lesson from www.wrox.com. You should copy these files to C:ProjectsSSISPersonalTrainer directory.

Lesson Requirements

You must have elevated permissions to complete this lesson. The minimum permission is membership in the dqs_kb_editor security role in the DQS_Main database. To create the knowledge base that will be used for cleansing, import the Lesson39KB.dqs file.

Hints

• Import Lesson39KB.dqs, which you downloaded from the www.wrox.com site to create the knowledge base.
• Use the DQS Cleansing Transform to cleanse the data.
• Use the Conditional Split Transform to partition the output and direct it to two Data Flows.

Step-by-Step

Figure 39-5

Figure 39-6

Figure 39-7