Thursday, December 16, 2010

Informatica Transformations1

Aggregator Transformation

Aggregator transformation is an Active and Connected transformation.

This transformation is useful to perform calculations such as averages and sums (mainly to perform calculations on multiple rows or groups).

For example, to calculate total of daily sales or to calculate average of monthly or yearly sales. Aggregate functions such as AVG, FIRST, COUNT, PERCENTILE, MAX, SUM etc. can be used in aggregate transformation.

Expression Transformation

Expression transformation is a Passive and Connected transformation.

This can be used to calculate values in a single row before writing to the target.

For example, to calculate discount of each product

or to concatenate first and last names

or to convert date to a string field.


Filter Transformation

Filter transformation is an Active and Connected transformation.

This can be used to filter rows in a mapping that do not meet the condition.

For example,

To know all the employees who are working in Department 10 or

To find out the products that falls between the rate category $500 and $1000.


Rank Transformation

Rank transformation is an Active and Connected transformation.

It is used to select the top or bottom rank of data.

For example,

To select top 10 Regions where the sales volume was very high

or

To select 10 lowest priced products.

Router Transformation

Router is an Active and Connected transformation. It is similar to filter transformation.

The only difference is, filter transformation drops the data that do not meet the condition whereas router has an option to capture the data that do not meet the condition. It is useful to test multiple conditions.

It has input, output and default groups.

For example, if we want to filter data like where State=Michigan, State=California, State=New York and all other States. It’s easy to route data to different tables.


Sorter Transformation

Sorter transformation is a Connected and an Active transformation.

It allows to sort data either in ascending or descending order according to a specified field.

Also used to configure for case-sensitive sorting, and specify whether the output rows should be distinct.


Sequence Generator Transformation

Sequence Generator transformation is a Passive and Connected transformation. It is used to create unique primary key values or cycle through a sequential range of numbers or to replace missing keys.


It has two output ports to connect transformations. By default it has two fields CURRVAL and NEXTVAL(You cannot add ports to this transformation).


NEXTVAL port generates a sequence of numbers by connecting it to a transformation or target. CURRVAL is the NEXTVAL value plus one or NEXTVAL plus the Increment By value.

Normalizer Transformation


Normalizer Transformation is an Active and Connected transformation.

It is used mainly with COBOL sources where most of the time data is stored in de-normalized format.

Also, Normalizer transformation can be used to create multiple rows from a single row of data.

1 comment:

  1. In computing, a data warehouse or enterprise data warehouse (DW, DWH, or EDW) is a database used for reporting and data analysis. survival warehouse review

    ReplyDelete