Before you can configure Data Cleanser, you must add it to the Toolbox, and drag the Data Cleanser component to the Business Intelligence Development Studio design surface.

To open the Data Cleanser configuration interface, right-click the Data Cleanser component in the design surface, and click Edit:

To configure Data Cleanser, you need to:

1. Select the columns you want to cleanse
2. Add cleansing actions you want to apply to the columns

You can then test the cleansing actions by specifying sample strings.

1. Select columns to cleanse

In the Select data pane, Data Cleanser displays columns from a connected data flow source in the Available Columns list:

You can select multiple columns (use CTRL or SHIFT) and then choose to group them (Select as Group), enabling you to apply identical cleansing actions to all columns in the group.

To separate groups back into individual columns, highlight the columns or groups in the Columns to Cleanse list and click Ungroup.

To combine groups and columns into a new group, highlight the columns or groups in the Columns to Cleanse list (use CTRL or SHIFT) and click Group.

2. Add cleansing actions

In the Apply cleansing actions pane, select the group or column you want to cleanse in the Columns to Cleanse list, and choose which of the following actions you want to apply:

• Convert Currency
• Find and Replace
• Normalize Letter Case
• Normalize Date
• Trim Strings

For more information on these actions, see: Configuring cleansing actions.

To associate a cleansing action with the selected column or group, click Add:

The action is added as a tab to the list below:

You can then continue to add further cleansing actions to the selected column or group.

Note that:

• Data Cleanser performs the actions in the order they appear in the list.
  To change the order the actions are performed, drag the action tab to a new position in the list.
• You can add the same action type more than once to the list.
  For example, you may want to remove non-alphanumeric characters, but do not want to remove the white space until after other actions have been performed.

Testing the cleansing actions

After you have set up your actions, you can preview the results on sample data in the Test cleansing actions pane:

As you add cleansing actions, the sample strings in the Output column of the table update in real-time, enabling you to view the effect of the actions.

If you want to test the actions on strings that are more representative of the real data that you want to change, type new values directly into the Sample input column.