Pivot Data Using Informatica

You can use the Normalizer Transformation to generate rows out of columns. But to pivot values in rows into columns you would have to use Aggregator Transformation.  Basically, pivot can be done in two ways

Pivot columns in a single row into multiple rows
 

Source

AddressID	Name	Address1	Address2
1	Rachel Geller	address1	address2
2	Joey Smith	address1	address2

Target

AddressID	Name	Address
1	Rachel Geller	address1
1	Rachel Geller	address2
2	Joey Smith	address1
2	Joey Smith	address2

In the above scenario, the column data is converted to rows. To normalize the data, we use normalizer transformation.  Go to Normalizer Transformation, to see, how to implement the above scenario.

Pivot data from multiple rows into columns in a single row
 

Source

AddressID	Name	Address
1	Rachel Geller	address1
1	Rachel Geller	address2
2	Joey Smith	address1
2	Joey Smith	address2

Target

AddressID	Name	Address1	Address2
1	Rachel Geller	address1	address2
2	Joey Smith	address1	address2

In this scenario, the row data is converted to column. This can achieved by using an aggregator transformation.

Source -> AggregatorTransformation -> Target

In the aggregator Transformation, group by AddressID and Name.

Create two Output Ports o_Address1 and o_Address2.

Set the Expression as

o_Address1: First(Address)

o_Address2: Last(Address)

*First or last functions will pick first or last of the incoming rows if there are duplicates.

If the source has variable number of rows like

CUSTOMER_ID, CUST_NAME, COMMENT

1,Rachel Geller,aaa

1,Rachel Geller,bbb

2,Joey Smith,ccc

2,Joey Smith,ddd

2,Joey Smith,eee

3,Ivar Smith,fff

I would like to Pivot, example, four comments from my data.

CUSTOMER_ID	CUST_NAME	COMMENT1	COMMENT2	COMMENT3	COMMENT4
1	Rachel Geller	aaa	bbb	Null	Null
2	Joey Smith	ccc	ddd	eee	Null
3	Ivar Smith	Fff	Null	Null	Null

In this scenario, we use expression and aggregator transformation.

Source -> Expression -> Aggregator -> Target

In the expression create 3 variable and one output port:

v_Check_New_Cust_ID: IIF(CUSTOMER_ID=v_last_CUST_ID,'Y','N')

v_Count: IIF(v_Check_New_CUST_ID='N',1,v_Count+1)

v_last_CUST_ID: CUSTOMER_ID

o_count: v_count

In the aggregator group by CUSTOMER_ID and create 4 output address ports:

o_COMMENT1: MAX(COMMENT,o_count=1)

o_COMMENT2: MAX(COMMENT,o_count=2)

o_COMMENT3: MAX(COMMENT,o_count=3)

o_COMMENT4: MAX(COMMENT,o_count=4)

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.

Informatica Powercenter Express - Aggregator Transformation

Aggregator transformation is used to perform aggregate calculations, such as averages and sums. The Data Integration Service performs aggregate calculations as it reads and stores data group and row data in an aggregate cache.

The transformation language has the following aggregate functions

 AVG

COUNT

FIRST

LAST

MAX

MEDIAN

MIN

PERCENTILE

STDDEV

SUM

VARIANCE

An Aggregator transformation has the following port types

Input Receives data from upstream transformations.

Output Provides the return value of an expression.

Pass-Through Passes data unchanged.

Variable Used for local variables.

Group by Indicates how to create groups. When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.

Advanced properties for an Aggregator transformations

Cache Directory

Local directory where the Data Integration Service creates the index cache files and data cache files. If you have enabled incremental aggregation, the Data Integration Service creates a backup of the files each time you run the mapping. The cache directory must contain enough disk space for two sets of the files.

Data Cache Size

Data cache size for the transformation.

Index Cache Size

Index cache size for the transformation.

Sorted Input

Select this option only if the mapping passes sorted data to the Aggregator transformation.

Tracing Level  
Amount of detail that appears in the log for this transformation. You can choose terse, normal, verbose initialization, or verbose data. Default is normal.

Tips to Improve performance while using Aggregator Transformation

• Use sorted input to decrease the use of aggregate caches. Sorted input reduces the amount of data cached during mapping run and improves performance. Use this option with the Sorter transformation to pass sorted data to the Aggregator transformation.
• Limit the number of connected input/output or output ports to reduce the amount of data the Aggregator transformation stores in the data cache.
• If you use a Filter transformation in the mapping, place the transformation before the Aggregator transformation to reduce unnecessary aggregation.

Scenario For Aggregator Transformation

Calculate total salaries paid to employees in each department

*In case of any questions, feel free to leave comments on this page and I would get back as soon as I can.