java transformation example

Informatica Technology Network http://technet.informatica.com

© 2009 Informatica Corporation. All Rights Reserved. 1

Implementing FOR loop in Informatica PowerCenter

Applies to:

Informatica PowerCenter

Summary

This article briefs about implementing FOR loop in Informatica using Java Transformation.

Author Bio

Author(s): Sukumar Balasubramanian

Company: CIBC

Created on: November 17, 2009

Sukumar Balasubramanian is an experienced Informatica ETL Consultant working with CIBC, Canada. He has good exposure to Data Integration/Data Warehousing Projects. He is also a key contributor in informatica-l group of ittoolbox .This is his first technical article in TechNet.




Table of Contents

INTRODUCTION ................................................................................................................................................ 3

REQUIREMENT ................................................................................................................................................. 3

SOURCE ......................................................................................................................................................... 3

TARGET .......................................................................................................................................................... 3

SOLUTION OVERVIEW ..................................................................................................................................... 4

SOLUTION STEPS ............................................................................................................................................. 4

SOURCE ......................................................................................................................................................... 4

EXPRESSION TRANSFORMATION .............................................................................................................. 4

JAVA TRANSFORMATION ............................................................................................................................ 5

TARGET .......................................................................................................................................................... 6

OUTPUT ......................................................................................................................................................... 6

ADVANTAGES ................................................................................................................................................... 7

DISCLAIMER AND LIABILITY NOTICE ............................................................................................................. 8




INTRODUCTION

This article briefs about implementing FOR loop in Informatica using Java Transformation.

REQUIREMENT

Consider that we are receiving a ‘|’ delimited flat file that contains the sales person’s tour information.

SOURCE

Salesperson Name | Tour from Date | Tour to Date | Place

Sukumar | 20091117 | 20091119 | Chennai

Ram | 20091117 | 20091119 | Bangalore

Sukumar | 20091120 | 20091122 | Bangalore

Ram | 20091120 | 20091122 | Chennai

Our requirement is to generate Tour Table TARGET that contains the following

TARGET

Salesperson Name Date Place

Sukumar 20091117 Chennai



Sukumar 20091120 Bangalore



Ram 20091117 Bangalore



Ram 20091120 Chennai






SOLUTION OVERVIEW

To achieve the above requirement we will be using the Java Transformation available in Informatica PowerCenter.

SOLUTION STEPS

SOURCE

Delimited Flat File TourInfo.txt

Salesperson Name | Tour from Date | Tour to Date | Place

Sukumar | 20091117 | 20091119 | Chennai

Ram | 20091117 | 20091119 | Bangalore

Sukumar | 20091120 | 20091122 | Bangalore

Ram | 20091120 | 20091122 | Chennai

EXPRESSION TRANSFORMATION

Port Name Data Type Type Expression

Description String(255) I

Salesperson Name String(50) O Use the substring, instr function to retrieve the sales person name using the | delimiter.

FromDate String(20) V Use the substring, instr function to retrieve the ’From Date’ using the | delimiter.

ToDate String(20) V Use the substring, instr function to retrieve the ‘To Date’ name using the | delimiter.

Place String(20) O Use the substring, instr function to retrieve the ‘Place’ using the | delimiter.

FromDate_out Date time O To_date(FromDate,YYYYMMDD)

ToDate_out Date time O To_date(ToDate,YYYYMMDD)

Expression transformation is used for retrieving the values from the flat file source and assigns values to individual ports.




JAVA TRANSFORMATION

• Drag the java transformation into the Mapping.

• Create the following ports.

Port Name Data Type Type

Salesperson Name String(50) I/O

FromDate String(20) I

ToDate String(20) I

Place String(20) I/O

FromDate_out Date time O

ToDate_out Date time O

TourDate DateTime O

Link the FromDate_out, toDate_out from the Expression Transformation to the From Date and To Date ports of the Java transformation.

• Under the Java code tab; Import Package tab place the below code: Import java.util.*; Import java.text.*;

This is to import relevant java packages.

• Under "On InputRow " tab place the following code

DateFormat formatter ;

formatter = new SimpleDateFormat("dd-MMM-yy");

FromDate_out =formatter.format(FromDate);

ToDate_out =formatter.format(ToDate);

Calendar startCal = Calendar.getInstance ( ) ;

Calendar endCal = Calendar.getInstance ( ) ;

Date date_start=new Date(FromDate.longValue());

startCal.setTime ( date_start) ;

Date date_end=new Date(ToDate.longValue());

endCal.setTime ( date_end) ;

for ( Calendar c = startCal; c.compareTo ( endCal ) <= 0; c.add (Calendar.DAY_OF_WEEK, 1 ) )

{




TourDate= formatter.format(c.getTime ());

generateRow();

}

• The above code snippet uses the java calendar function.

• You can see for loop construction based on the from date and the to date,

• We need to link the salesperson name, Tour Date and Place ports from this java transformation to the target.

TARGET

Relational Table TOUR

Description DataType

Sales Person name String

Tour Date Date time

Place String

OUTPUT

Source:

Sukumar | 20091117 | 20091119 | Chennai

Target:

Java transformation takes the ‘from date’ and ‘to date’ as input and a FOR loop is constructed.

For each input row based on the ‘from date’ and ‘to date’ java transformation will generate multiple rows.

In our case for a single row in source we will be generating 3 rows in target.

Salesperson Name Date Place







ADVANTAGES

• Java transformation can be used to generate multiple rows based on a condition.

• Normalizer can generate rows based on the ‘Occurs’ clause where we will specify a static number but if you want to generate multiple rows based on a dynamic value then go for java transformation.




DISCLAIMER AND LIABILITY NOTICE

Informatica offers no guarantees and assumes no responsibility or liability of any type with respect to the content of this software asset, including any liability resulting from incompatibility between the content within this asset and the materials and services offered by Informatica. You agree that you will not hold, or seek to hold, Informatica responsible or liable with respect to the content of this software asset.

java transformation example

Documents