Understand Informatica PowerCenter Mapping Designer

  Level1 Training

A mapping is a set of source and target definitions linked by transformation objects that define the rules for data transformation. Mappings represent the data flow between sources and targets. When the Integration Service runs a session, it uses the instructions configured in the mapping to read, transform, and write data.

Every mapping must contain the following components:

1. Source definition : Describes the characteristics of a source table or file.
2. Transformation : Modifies data before writing it to targets. Use different transformation objects to perform different functions.
3. Target definition : Defines the target table or file.
4. Links : Connect sources, targets, and transformations so the Integration Service can move the data as it transforms it.

Lets understand more about the tool by developing our first mapping and lets do the following.

• Understand the Designer’s Mapping Designer tool
• Create a mapping using
  • A Source definition
  • Source Qualifier and Expression Transformation
  • A Target definition
  • Edit Transformations
    • Add new ports in transformations
    • Add formulas in the Expression transformation
  • Validate a mapping
  • Understand Designer’s Output window

Here in our exercise  we are going to build a mapping to create a relational target table that contains the Name as a concatenation of First Name and Last Name.

I. Source Qualifier Transformation

1. Select TOOLS | MAPPING DESIGNER, or click on the icon. The workbook changes to the Mapping Designer.
   
2. Select MAPPINGS | CREATE, or click the Create Mapping icon from the Mapping toolbar.
3. Enter M_Employee_Name_x for the new mapping name.
4. Click on OK.
5. Drag the source from the navigator to the Mapping Designer. Designer creates the Source Qualifier by default, and connects it to the source as shown below:
6. If the Source Qualifier transformation does not appear by default, Select TOOLS | OPTIONS and click on the FORMAT tab. Ensure automatic creation by checking the ‘Create Source Qualifiers When Opening Sources checkbox’.
7. Drag the source again into the workspace. The Source Qualifier transformation automatically appears and is connected to the source. Delete the previously dragged source from the workspace by pressing the delete key.
   Note: Use the automatic Source Qualifier creation when you want to create one Source Qualifier for each source in your mapping and disable the automatic creation when you want to join data from different sources.

II. Edit the Source Qualifier Transformation

1. Use either of the methods to invoke the Edit Transformations box.
   1. Right click on the Source Qualifier and select Edit…
   2. Double click on the Source Qualifier transformation.
2. Click on the Transformation tab and click on Rename to rename the transformation.
3. Enter the name and click on OK.
   

III. Create the Expression Transformation and link to the Source Qualifier

1. To create the Expression Transformation, use either of following methods:
   1. Click on the expression transformation icon in the transformation toolbar. Drag the pointer, which now appears as crosshairs , into the Workspace window to the right of the Source Qualifier transformation
   2. Select TRANSFORMATION | CREATE.
   3. In the Create Transformation Dialog box, select the type of transformation from the pull-down list. Enter a name for the transformation.
      
   4. Click on the Create button.
   5. Click on the Done button.
      Note: The expression transformation appears in normal mode.
2. Link the ports from the SQ_Employees_x to the new expression transformation using any of the methods
1. Select LAYOUT | LINK COLUMNS.
2. Select the Link Columns icon in the toolbar   Hint: Hold down the Ctrl or Shift key and select the ports in the SQ_Employees_x. Drag them to an empty line on the expression transformation. When the mouse is released, not only will the port names (including data types, precision and scale) will be copied from SQ_Employees_x, but links connecting the ports between the two transformations will also be created.

IV. Edit Expression Transformation

1. Double click on the header of the Expression transformation to enter Edit mode.
2. Click the Rename button under the Transformation tab.
3. Rename the transformation as Exp_Employee_Name_x.
   Note: If the transformation was created via the TRANSFORMATION | CREATE menu instead of the transformation toolbar, the object will have a name.
4. Click on the Ports tab.
5. Disable the output ports for FIRSTNAME and LASTNAME by removing the checkmark in the ‘O’ (output) column – this will define the port as input only.
6. Select the FIRSTNAME port and click on the icon to add a new port and rename it to Name. This will cause the new port to be positioned immediately after FIRSTNAME.
7. Verify whether the data type is string and increase the precision to 51.
8. Disable the input port for NAME by removing the checkmark in the ‘I’ (input) column- this will define the port as output only.
9. The Edit Transformations box should look as shown below.

V. Create Expression Formula

1. Click the down arrow in the expression column of the NAME port.
2. Delete the text NAME in the Formula field in the Expression Editor.
3. Select the Ports tab as shown in the figure below.
4. Double-click on the port LASTNAME and note its presence in the Formula field. Based on which port is selected, the port details appear under the ‘Instance Name:’ section of the ports tab.
5. Create the expression as shown below:
6. Click on Validate. This will parse the expression.
7. Click on OK if the expression is successfully parsed.
8. Click OK to close the Edit Transformation dialog box.
9. The finished transformation will look like the following:
   

VI. Link Expression Transformation and Target

1. Drag the target definition from Navigator Window into the workspace.
2. Link columns of the Expression Transformation to the target definition:
   1. Select LAYOUT | AUTOLINK.
   2. In the Autolink dialog box, select the From Transformation and To Transformation.
   3. Click on Apply Now and OK.
3. Notice the links between expression transformation and target table.
4. The mapping is now complete and should look like the figure shown below:
5. Select MAPPINGS | VALIDATE to validate the mapping.
6. Select REPOSITORY | SAVE or press Ctrl-S to save the changes to the repository.
   Note : Every time a repository save is executed, a series of validation checks are performed on what has been changed.
7. View the results of the Validation by locating Save tab of the Output window, at the bottom of Designer. Note: If the Validation results show ‘INVALID’, locate the last time stamp when the save repository was executed and scan for the first error. The series of validation checks will display all of the errors. Rectify the errors and validate the mappings again, until the mapping is valid.

Video Tutorial.

In this demonstration video tutorial we will see how the mapping is created.

Hope you enjoy this tutorial, Please let us know if you have any difficulties in trying out these exercise.

Share with your friends