Lab 2

Creating a Mapping
Lab at a Glance ..............................................................2
Objectives........................................................................2
Duration...........................................................................2
Exercises........................................................................3
Exercise 1: Create a Mapping ..........................................3

PowerCenter 9.x Level I Developer Lab Guide

Lab 2. Creating a Mapping

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:
Open the Mapping Designer tool.
Create a new mapping.
Create an expression transformation.
Link sources, transformations, and targets within a
mapping.
Modify and add ports to an expression transformation.
Validate a mapping.
The completed mapping should look like this:

Duration
This lab should take approximately 30 minutes.

2-2 PowerCenter 9.x Level I Developer Lab Guide

 Lab 2. Creating a Mapping

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.

In the Navigator window, select the Studentxx folder

(where xx is your assigned student number).
Select Tools | Mapping Designer, or click the Mapping
Designer button in the toolbar.
Step 2. Create a new mapping.

Select Mappings | Create.

The Mapping Name dialog box appears.
Enter m_ODS_EMPLOYEE_xx for the New Mapping name
(where xx is the assigned student number):

Click the OK button.

PowerCenter 9.x Level I Developer Lab Guide 2-3

Lab 2. Creating a Mapping

Step 3. Add source and target definitions.

In the Navigator window, expand the Sources and

odbc_source nodes and locate the EMPLOYEE source
definition:

Drag-and-drop the EMPLOYEE source definition to the far

left side of the workspace.
In the Navigator window, expand the Targets node and
locate the ODS_EMPLOYEE target definition:

Drag-and-drop the ODS_EMPLOYEE target definition to the

far right side of the workspace.
The workspace should look as follows:

2-4 PowerCenter 9.x Level I Developer Lab Guide

 Lab 2. Creating a Mapping

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.
For purposes of this lab, the source qualifier will be
renamed to follow Informatica Velocity best practices.
Edit SQ_EMPLOYEE by double-clicking on the header.
The Edit Transformations dialog box appears.
Click the Rename button.
Enter sq_EMPLOYEE in the Transformation Name field.
Click OK.
Click A pply .
Click on the Ports tab to view the port names, datatypes,
precisions and scales:

Note that the Data ty pe field indicates the

Pow erCenter data ty pe for the field, or
port. The Informatica Serv er translates
nativ e data ty pes to Pow erCenter data
ty pes before processing. When the
information is w ritten to a target, it is
translated to the defined database or flat
file data ty pe.

PowerCenter 9.x Level I Developer Lab Guide 2-5

Lab 2. Creating a Mapping

Click the OK button to close the dialog box.

Step 5. Create an expression transformation.
Before the target is loaded, the Before the target is loaded, the FIRST_NAME and LAST_NAME
FIRST_NAME and LAST_NAME ports for ports for each row must be concatenated. This concatenation can be
each row must be concatenated. This done in an Expression transformation.
concatenation can be done in an
Ex pression transformation.
Locate the Transformation toolbar.
If the toolbar is not v isible, activ ate it by
selecting Tools | Customize , then
select the Transformations checkbox
The default location is at the top of the Designer.

Click on the vertical bar at the beginning of the toolbar

and drag the toolbar to the center of the Designer
workspace.
An alternativ e is to select
Transformations Create from Click on the Expression Transformation button in the
the menu and then choose Expression
as the transformation ty pe. In this toolbar.
method, y ou name the transformation at
the time y ou create it.

In order to giv e y ourself more room to Move the pointer in the workspace to the right of the
w ork, you may w is h to toggle off the
nav igator w indow . You can do this by sq_EMPLOYEE source qualifier. In the workspace, the
selecting View Navigator from the
menu or by clicking the x in the upper- cursor appears as crosshairs .
right corner of the nav igator.
Left-click the mouse.
An Expression transformation is created.

2-6 PowerCenter 9.x Level I Developer Lab Guide

 Lab 2. Creating a Mapping

Step 6. Link the Source Qualifier to the Expression

transformation.

Select Layout | Link Columns , or click the Link Columns

Link Co lumns toggles linking on/off. button in the toolbar.
You must still link the columns, as in the
nex t step. In the sq_EMPLOYEE source qualifier, hold down the Ctrl
key and select the following ports:
EMPLOYEE_ID

FIRST_NA ME

LA ST_NA ME

DA TE_HIRED

TYPE_CODE

Drag the ports to an empty line of the expression

transformation object and release the mouse button.
Step 7. Rename the Expression transformation.

Double-click on the header of the Expression

transformation.
Click the Rename button.
Rename the transformation to exp_ODS_EMPLOYEE.
Step 8. Modify/add ports to the Expression
transformation.

Select the Ports tab.

Disable the output ports for FIRST_NA ME and LA ST_NA ME
by removing the checkmark in the O (output) column.
This will define the port as input only.
Click on the LAST_NAME column.
Click on the A dd button to add a new port.
Name the new port NAME_out.
Verify the data type is string and increase the precision to
51.
Disable the input port for NA ME_out by removing the
checkmark in the I (input) column. This will define the
port as output only:

PowerCenter 9.x Level I Developer Lab Guide 2-7

Lab 2. Creating a Mapping

Step 9. Create an expression formula.

Click in the Expression column of the NA ME_out port and

notice the arrow that appears at the far right:

Click on the arrow.

The Expression Editor dialog box appears.
Delete the text, NAME_out, in the Formula field.
Select the Ports tab on the left-hand side of the
Expression Editor dialog box.

Ports
tab

Double-click on the port FIRST_NA ME. Note that it is

added to the Formula field.

2-8 PowerCenter 9.x Level I Developer Lab Guide

 Lab 2. Creating a Mapping

Using the || string operator instead of

Click on the double-pipe button in the Operator
CONCAT im prov es Informatica Serv er keypad. The concatenation operator is added to the
performance in processing the formula. formula.

Click on the single-quote button.

Press the spacebar once and click the single-quote button
again. This will cause a space to be placed after the
FIRST_NA ME in the formula.

Click on the double-pipe button again.

Double-click on LA ST_NA ME. The concatenation formula
Note that a formula can also be ty ped in
manually, if the names of the functions
is complete, and should look like this:
and ports are know n.
FIRST_NAME||''||LAST_NAME
Click Validate .
Upon successful parsing of the formula, the Expression
parsed successfully message will appear.

Click OK.
Click OK to close the Edit Transformation dialog box.
Save the repository.
Step 10. Link the target definition.

Click on the following ports in exp_ODS_EMPLOYEE and

drag them onto ODS_EMPLOYEE to link the ports:
EMPLOYEE_ID EMPLOYEE_ID
NA ME_out NA ME
DA TE_HIRED DA TE_HIRED

TYPE_CODE TYPE_CODE

The mapping is now complete. Right-click in the

workspace and select A rrange A ll the mapping should
look like the following:

PowerCenter 9.x Level I Developer Lab Guide 2-9

Lab 2. Creating a Mapping

Step 11. Validate the mapping.

Save the repository.

Every time a repository is saved, mapping validation
checks are performed.
Click on the Save tab in the Output window.
Expand the Output window and scroll up until the
validation test details are visible:

Verify that there are no errors.

Step 12. Correct any errors

If the validation results are INVALID:

Locate the last date/time stamp (when the repository was

saved).
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.
Correct the error and save the repository, or select

2 - 10 PowerCenter 9.x Level I Developer Lab Guide

 Lab 2. Creating a Mapping

Mappings | Validate.

Check the results in the Output windows Validate tab:

Save the repository.

PowerCenter 9.x Level I Developer Lab Guide 2 - 11