Lab 2. Creating a Mapping

Lab 2

Creating a Mapping

Lab at a Glance 2

Objectives 2

Duration 2

Exercises 3

Exercise 1: Create a Mapping 3

Lab at a Glance

The exercises in this lab are designed to familiarize the student with the process of creating a mapping. The student will also learn to navigate within the Mapping Designer tool.

Objectives

After completing the lab, the student will be able to:

q  Open the Mapping Designer tool.

q  Create a new mapping.

q  Create an expression transformation.

q  Link sources, transformations, and targets within a mapping.

q  Modify and add ports to an expression transformation.

q  Validate a mapping.

The completed mapping should look like this:

Duration

This lab should take approximately 30 minutes.

Exercises

Exercise 1: Create a Mapping

In this exercise, the student will create a mapping that represents the data flow between the EMPLOYEE source and the ODS_EMPLOYEE target.

A mapping represents the dataflow between sources and targets. The instructions defined in the mapping tell the Informatica Server how to read, transform and write the data.

Step 1. Open the Mapping Designer tool.
/ q  In the Navigator window, select the Studentxx folder (where xx is your assigned student number).
q  Select Tools | Mapping Designer, or click the Mapping Designer button in the toolbar.
Step 2. Create a new mapping.
q  Select Mappings | Create.
q  The Mapping Name dialog box appears.
q  Enter m_ODS_EMPLOYEE_xx for the New Mapping name (where xx is the assigned student number):
q  Click the OK button.
Step 3. Add source and target definitions.
/ q  In the Navigator window, expand the Sources and odbc_source nodes and locate the EMPLOYEE source definition:
q  Drag-and-drop the EMPLOYEE source definition to the far left side of the workspace.
q  In the Navigator window, expand the Targets node and locate the ODS_EMPLOYEE target definition:
q  Drag-and-drop the ODS_EMPLOYEE target definition to the far right side of the workspace.
The workspace should look as follows:
Note that an object called SQ_EMPLOYEE is automatically added to the mapping above. This transformation object is called the Source Qualifier and is required with all relational and flat file sources. It was created automatically when an instance of the EMPLOYEE source definition was added to the mapping.
Step 4. Rename and examine the source qualifier transformation.
Note that the Data type field indicates the PowerCenter data type for the field, or port. The Informatica Server translates native data types to PowerCenter data types before processing. When the information is written to a target, it is translated to the defined database or flat file data type. / For purposes of this lab, the source qualifier will be renamed to follow Informatica Velocity best practices.
q  Edit SQ_EMPLOYEE by double-clicking on the header.
The Edit Transformations dialog box appears.
q  Click the Rename button.
q  Enter sq_EMPLOYEE in the Transformation Name field.
q  Click OK.
q  Click Apply.
q  Click on the Ports tab to view the port names, datatypes, precisions and scales:
q  Click the OK button to close the dialog box.
Step 5. Create an expression transformation.
Before the target is loaded, the FIRST_NAME and LAST_NAME ports for each row must be concatenated. This concatenation can be done in an Expression transformation.
If the toolbar is not visible, activate it by selecting Tools | Customize, then select the Transformations checkbox / Before the target is loaded, the FIRST_NAME and LAST_NAME ports for each row must be concatenated. This concatenation can be done in an Expression transformation.
q  Locate the Transformation toolbar.
The default location is at the top of the Designer.
q  Click on the vertical bar at the beginning of the toolbar and drag the toolbar to the center of the Designer workspace.
An alternative is to select Transformations – Create from the menu and then choose Expression as the transformation type. In this method, you name the transformation at the time you create it.
In order to give yourself more room to work, you may wish to toggle off the navigator window. You can do this by selecting View – Navigator from the menu or by clicking the “x” in the upper-right corner of the navigator. / q  Click OK.
q  Click on the Expression Transformation button in the toolbar.
q  Move the pointer in the workspace to the right of the sq_EMPLOYEE source qualifier. In the workspace, the cursor appears as crosshairs .
q  Left-click the mouse.
An Expression transformation is created.
Step 6. Link the Source Qualifier to the Expression transformation.
Link Columns toggles linking on/off. You must still link the columns, as in the next step. / q  Select Layout | Link Columns, or click the Link Columns button in the toolbar.
q  In the sq_EMPLOYEE source qualifier, hold down the Ctrl key and select the following ports:
§  EMPLOYEE_ID
§  FIRST_NAME
§  LAST_NAME
§  DATE_HIRED
§  TYPE_CODE
q  Drag the ports to an empty line of the expression transformation object and release the mouse button.
Step 7. Rename the Expression transformation.
/ q  Double-click on the header of the Expression transformation.
q  Click the Rename button.
q  Rename the transformation to exp_ODS_EMPLOYEE.
Step 8. Modify/add ports to the Expression transformation.
/ q  Select the Ports tab.
q  Disable the output ports for FIRST_NAME and LAST_NAME by removing the checkmark in the O (output) column. This will define the port as input only.
q  Click on the LAST_NAME column.
q  Click on the Add button to add a new port.
q  Name the new port NAME_out.
q  Verify the data type is string and increase the precision to 51.
q  Disable the input port for NAME_out by removing the checkmark in the I (input) column. This will define the port as output only:
Step 9. Create an expression formula.
Using the || string operator instead of CONCAT improves Informatica Server performance in processing the formula.
Note that a formula can also be typed in manually, if the names of the functions and ports are known. / q  Click in the Expression column of the NAME_out port and notice the arrow that appears at the far right:
q  Click on the arrow.
The Expression Editor dialog box appears.
q  Delete the text, NAME_out, in the Formula field.
q  Select the Ports tab on the left-hand side of the Expression Editor dialog box.
q  Double-click on the port FIRST_NAME. Note that it is added to the Formula field.
q  Click on the double-pipe button in the Operator keypad. The concatenation operator is added to the formula.
q  Click on the single-quote button.
q  Press the spacebar once and click the single-quote button again. This will cause a space to be placed after the FIRST_NAME in the formula.
q  Click on the double-pipe button again.
q  Double-click on LAST_NAME. The concatenation formula is complete, and should look like this:
FIRST_NAME||''||LAST_NAME
q  Click OK.
Upon successful parsing of the formula, the Expression parsed successfully message will appear.
q  Click OK.
q  Click OK to close the Edit Transformation dialog box.
q  Save the repository.
Step 10. Link the target definition.
/ q  Click on the following ports in exp_ODS_EMPLOYEE and drag them onto ODS_EMPLOYEE to link the ports:
§  EMPLOYEE_ID à EMPLOYEE_ID
§  NAME_out à NAME
§  DATE_HIRED à DATE_HIRED
§  TYPE_CODE à TYPE_CODE
The mapping is now complete. Right-click in the workspace and select Arrange All – the mapping should look like the following:
Step 11. Validate the mapping.
q  Save the repository.
Every time a repository is saved, mapping validation checks are performed.
q  Click on the Save tab in the Output window.
q  Expand the Output window and scroll up until the validation test details are visible:
q  Verify that there are no errors.
Step 12. Correct any errors
If the validation results are “INVALID”:
q  Locate the last date/time stamp (when the repository was saved).
q  Locate the first error.
Note that there may be more than one error – it is best to find and correct the first error, as it may have caused further errors.
q  Correct the error and save the repository, or select Mappings | Validate.
q  Check the results in the Output window’s Validate tab:
q  Save the repository.

PowerCenter 7 Basics – Lab Guide 2 - 11