02_lab_creating a simple mapping

7/30/2019 02_Lab_Creating a Simple Mapping

1/14

BANGALORE TECHNICAL

TRAININGS

[email protected]

+917411642061

Lab 2

Creating a Mapping

Lab at a Glance................................................................2

Objectives..............................................................2Duration.................................................................2

Exercises.........................................................................3

Exercise 1: Create a Mapping................................3

PowerCenter 9.x Level I Developer Lab Guide

[email protected] 2 - 1
mailto:[email protected]:[email protected]


2/14

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 alsolearn 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


3/14


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.

Enterm_ODS_EMPLOYEE_xx for the New Mapping name

(where xx is the assigned student number):

Click the OKbutton.

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


4/14


Step 3. Add source and target definitions.



5/14


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 andlocate the ODS_EMPLOYEEtarget definition:

Drag-and-drop the ODS_EMPLOYEE target definition to the

far right side of the workspace.

The workspace should look as follows:



6/14


Step 4. Rename and examine the source qualifiertransformation.

Note that the Data type field indicates thePowerCenter data type for the field, orport. The Informatica Server translatesnative data types to PowerCenter datatypes before processing. When theinformation is written to a target, it istranslated to the defined database or flatfile data type.

For purposes of this lab, the source qualifier will be

renamed to follow Informatica Velocity best practices.

Edit SQ_EMPLOYEEby double-clicking on the header.The Edit Transformations dialog box appears.

Click the Rename button.

Entersq_EMPLOYEE in the Transformation Name field.

ClickOK.

ClickApply.

Click on the Ports tab to view the port names, datatypes,precisions and scales:

Click the OKbutton to close the dialog box.

Step 5. Create an expression transformation.

Before the target is loaded, theFIRST_NAME and LAST_NAME ports foreach row must be concatenated. Thisconcatenation can be done in anExpression transformation.

If the toolbar is not visible, activate it by

selecting Tools | Customize, thenselect 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.

Locate the Transformation toolbar.

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 Designerworkspace.

An alternative is to selectTransformations Create fromthe menu and then choose Expression

Click on the Expression Transformation button in the

toolbar.



7/14


as the transformation type. In thismethod, you name the transformation atthe time you create it.

In order to give yourself more room to

work, you may wish to toggle off thenavigator window. You can do this byselecting View Navigator from themenu or by clicking the x in the upper-right corner of the navigator.

Move the pointer in the workspace to the right of the

sq_EMPLOYEE source qualifier. In the workspace, thecursor appears as crosshairs .

Left-click the mouse.

An Expression transformation is created.



8/14


Step 6. Link the Source Qualifier to the Expressiontransformation.



9/14


Link Columns toggles linking on/off.You must still link the columns, as in thenext step.

Select Layout | Link Columns, or click the Link Columns

button in the toolbar.

In the sq_EMPLOYEEsource qualifier, hold down the Ctrlkey and select the following ports:

EMPLOYEE_ID

FIRST_NAME

LAST_NAME

DATE_HIRED

TYPE_CODE

Drag the ports to an empty line of the expressiontransformation object and release the mouse button.



10/14


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 Expressiontransformation.

Select the Ports tab.

Disable the output ports forFIRST_NAME and LAST_NAME

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 Add 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 forNAME_out by removing thecheckmark in the I (input) column. This will define the

port as output only:

Step 9. Create an expression formula.

Click in the Expression column of the NAME_outport andnotice the arrow that appears at the far right:



11/14


Using the || string operator instead ofCONCAT improves Informatica Server

performance in processing the formula.

Note that a formula can also be typed inmanually, if the names of the functionsand ports are known.

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 theExpression Editor dialog box.

Portstab

Double-click on the port FIRST_NAME. Note that it is

added to the Formula field.

Click on the double-pipe button in the Operator

keypad. The concatenation operator is added to theformula.

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_NAME in the formula.

Click on the double-pipe button again.

Double-click on LAST_NAME. The concatenation formulais complete, and should look like this:

FIRST_NAME||''||LAST_NAME

ClickValidate.

Upon successful parsing of the formula, the Expressionparsed successfully message will appear.

ClickOK.



12/14


ClickOK 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 anddrag 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 shouldlook like the following:

Step 11. Validate the mapping.


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:



13/14


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 tofind and correct the first error, as it may have caused

further errors.

Correct the error and save the repository, or selectMappings | Validate.

Check the results in the Output windows Validate tab:



14/14



02_lab_creating a simple mapping

Documents