Understand Informatica PowerCenter Mapping Designer

Johnson Cyriac Aug 22, 2012

Informatica Mapping Designer
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.Mapping Components
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.
    Informatica PowerCenter 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:Informatica PowerCenter source Qualifier
  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’.Informatica PowerCenter source Qualifier
  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. Informatica PowerCenter source Qualifier
  3. Enter the name and click on OK.
    Informatica PowerCenter source Qualifier

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.
      Informatica Expression 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.
Informatica Expression Transformation

V. Create Expression Formula

  1. Click the down arrow in the expression column of the NAME port. Informatica Expression Transformation
  2. Delete the text NAME in the Formula field in the Expression Editor. Informatica Expression
  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. Informatica Expression
  5. Create the expression as shown below: Informatica Expression
  6. Click on Validate. This will parse the expression. Informatica Expression Validate
  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:
    Informatica Expression Transformation

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. Informatica Expression Auto Link
    3. Click on Apply Now and OK.
  3. Notice the links between expression transformation and target table. Informatica Mapping
  4. The mapping is now complete and should look like the figure shown below: Informatica Mapping
  5. Select MAPPINGS | VALIDATE to validate the mapping. Informatica 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.





About US Contact US Advertise Guest Post Terms and Conditions Privacy Policy Disclaimer

© 2012-2013 Data Intelligence Solution, All Rights Reserved
The contents in this site is copyrighted to Data intelligence Solution and may not be reproduced on other websites.