Task 2. Create a Mapping to Standardize Data
In this task, you create a mapping and configure the mapping to use data objects and a Standardizer transformation.
To create a mapping to standardize data, complete the following steps:
1. Create a mapping.
2. Add source and target data objects to the mapping.
3. Add a Standardizer transformation to the mapping.
4. Configure the Standardizer transformation to standardize common address terms to consistent formats.
Step 1. Create a Mapping
In this step, you create and name the mapping.
1. In the Object Explorer view, select your tutorial project.
2. Click File > New > Mapping.
The New Mapping window opens.
3. In the Name field, enter StandardizerMapping.
4. Click Finish.
The mapping opens in the editor.
Step 2. Add Data Objects to the Mapping
In this step, you add the All_Customers data object and the All_Customers_Stdz_tgt data object to the mapping.
1. In the Object Explorer view, browse to the data objects in your tutorial project.
2. Select the All_Customers data object and drag it to the editor.
The Add Physical Data Object to Mapping window opens.
3. Verify that Read is selected and click OK.
The data object appears in the editor.
4. In the Object Explorer view, browse to the data objects in your tutorial project.
5. Select the All_Customers_Stdz_tgt data object and drag it to the editor.
The Add Physical Data Object to Mapping window opens.
6. Select Write and click OK.
The data object appears in the editor.
7. Select all ports in the All_Customers data object. Drag the ports to the CustomerID port in the All_Customers_Stdz_tgt data object.
Tip: Hold down the Shift key to select multiple ports. You might need to scroll down the list of ports to select all of them.
The ports of the All_Customers data object connect to corresponding ports in the All_Customers_Stdz_tgt data object.
Step 3. Add a Standardizer Transformation to the Mapping
In this step, you add a Standardizer transformation to standardize strings in the address data.
1. Select the editor that contains the StandardizerMapping mapping.
2. In the Transformation palette, select the Standardizer transformation.
3. Click the editor.
A Standardizer transformation named NewStandardizer appears in the mapping.
4. To rename the Standardizer transformation, double-click the title bar of the transformation and type AddressStandardizer.
5. Select the Address1 port in the All_Customers data object, and drag the port to the Input group of the Standardizer transformation.
A port named Address1 appears in the input group. The port connects to the Address1 port in the All_Customers data object.
Note: You add an output port to the transformation when you configure a standardization strategy.
Step 4. Configure the Standardizer Transformation
In this step, you configure the Standardizer transformation to standardize address terms in the source data.
Note: You will define five standardization operations in this task. Each operation replaces a string in the input column with a new string.
1. Select the editor that contains the StandardizerMapping mapping.
2. Click the Standardizer transformation.
3. Click Window > Show View > Properties.
4. In the Properties view, select Strategies.
5. Click New. The New Strategy wizard displays.
6. Click the selection arrow in the Inputs column, and choose the Address1 input port.
The Outputs field shows Address1 as the output port.
7. Select the character space and comma delimiters [\s] and [,]. Optionally, select the options to remove trailing spaces.
8. Click Next.
9. Select the Replace custom strings operation, and click Next.
10. Under Properties, click New.
11. Edit the Custom Strings and Replace Withfields so that they contain the first pair of strings from the following table:
Custom Strings | Replace With |
|---|
STREET | ST. |
BOULEVARD | BLVD. |
AVENUE | AVE. |
DRIVE | DR. |
PARK | PK. |
12. Repeat steps 9 through 12 to define standardization operations for all strings in the table.
13. Drag the Address1 output port to the Address1 port in the All_Customers_Stdz_tgt data object.
14. Click File > Save to save the mapping.