What are Transformations in Informatica

Informatica Transformations are repository objects that can read, modify or pass data on to the target structures defined, such as tables, files, or any other required targets. You can describe a transformation as a collection of rules.  These rules determine the data flow and how you load the data to aim. Informatica Power Center provides multiple transformations, which each serve a specific functionality.

Transformations in Informatica

In Informatica, transformations are the objects that build, change or transfer data to the specified target structures (tables, files or any other destination). In Informatica’s transformation, the aim is to change the source data as per target system requirement. This also ensures the consistency of the loaded data onto the target.

Informatica provides various transformations for performing similar functionalities. Tax calculation based on source data, data cleaning operation, etc., for example. In transformations, we connect the ports for transmitting data to it, and the transformation returns the output to the ports.

Classification of Transformations in Informatica

You can classify Transformation into two categories, one based on connectivity and the other based on the number of rows changed.  First, let us look at the connectivity in transformation.

Types of transformation based on connectivity

●        Linked Transformations

●        Unlinked Transformations

In Informatica, the transformations related to other transformations during mappings.

Source qualifier transformation of Source table EMP. For example, use a filter transformation to filter a dept’s employees.

Those transformations unrelated to any other transformations are transformations unconnected.

Their functionality is naming them within certain transformations, such as Transformation of Language. Such modifications are not part of the system.

The related transformations are preferred when transformation is called or is required to return a value for each input row. For example, the transformation returning town name for the zip codes in every row.

Unconnected transformations are useful if their functionality is needed only periodically or on the basis of certain conditions. For example, if tax value isn't available, measure the tax information.

Now let's continue to look at the transformations one by one.

INFORMATICA Transformations & Transformation of Filters

Types of transformations dependent on no-row transition

●        Fast Conversions

●        Passive Conversions

Effective Transformations are the ones that change the rows of data and the number of input rows passed to them. For instance, if a transformation receives ten rows as input and returns fifteen rows as output, it is an active transformation. During the active transformation even the data in the row is updated.

Passive transformations are the ones that do not change the number of rows data. In passive transformations the number of input and output rows remains the same, only row-level data is changed.

No new rows are created, or existing rows are dropped, during the passive transformation.

The List of Informatica Transformations follows

●        Transformation of Root Parameters

●        Aggregator Processing

●        Router Storage

●        Transformation into a joiner

●        Level Change

●        Series transformation generator

●        Transformation of Transaction Power

●        Lookup and Migration Usable

●        Normalizer Application

●        Performance Tuning to Transform

●        Outside Transformation

●        Transforming speech

Aggregator transformation

The transformation of aggregators is an Active and Connected transformation. The transformation of Informatica is useful for calculations such as averages and percentages (mainly for calculations performed on several rows or groups). For example, to calculate the total number of daily sales, or to calculate the monthly or annual average sales. In aggregate transformation abstract functions such as Average, FIRST, COUNT, Percentage, MAX, Total, etc. may be used.

Lookup Transformation

The Lookup transformation is the Informatica transformation which is most common and commonly used. The lookup transformation can be used as a Connected or Unconnected transformation which combines it as an Active or Passive transformation based on the user's requirement. It is mainly used to look up the details from a source, source qualifier, or target to get the relevant data required. You may also scan for a 'flat file,' 'link table,' 'view' or 'synonym.' In a mapping one can use multiple lookup transformations.

The lookup transformation is generated using the following ports type(Logical points for information transfer.

●        Gate of Entry (I)

●        Port output (O)

●        Ports Lookup (L)

●        Return Port (R) (With Unconnected Lookup only)

Differences between Conversion of the Connected and Un Connected Lookup:

The differences between Connected lookup and unconnected lookup transformations are as follows.

●        Connected lookup receives the input values directly from the mapping pipeline, while UnConnected lookup receives values from another transformation from the lookup expression. A mapping in Informatica may be viewed as a pipeline for Source, Transformations, and Goals linked together.

●        Connected lookup returns multiple columns from the same line as multiple return ports, while UnConnected lookup only has one return port and returns one column from each line. For example, if we use a connected lookup on an employee database for a specific department Id as a parameter, we can get all the information related to that department 's employees such as their Names, Employee ID number , Address, etc. while with an Unconnected lookup we can get only one employee attribute such as their Name or Employee ID number or any user-specified attribute.

●        Linked lookup caches all columns for the lookup, while UnConnected lookup caches only the conditions for the lookup performance and lookup.

●        Connected lookup supports default values specified by the user while UnConnected lookup does not support values defined by the user. For example, you can set the default value of certain columns to NULL in the lookup expressions if you want to adjust the values of a certain column to NULL after the lookup. Nevertheless, in case of UnConnected lookup this function is not necessary.

What is filter transformation?

Filter Transformation is an active transformation, as the number of records shifts.

Using the transformation filter you can filter the records depending on the state of the filter. Transformation of filters is an active transformation as it changes record no.

For example, you can put filter transformation in the mapping with the filter condition deptno=10 to load the employee records having deptno only equal to 10. So only those records that have deptno = 10 will be passed through filter transformation, other records will be dropped to rest.

Filter Transformation

Step 1

You need to build a mapping with source EMP.  Then you can map the destination "EMP TARGET."

Step 2

Then in the diagram

Choose Menu on Conversion

Choose Choice Build

Step 3

Then build the Transition Fenster

From the list pick Transformation Filter

Enter the name "fltr deptno 10" for transformation purposes.

Choose Choice Build

Step 4

Create the filter transformation, select "Done" button in the transformation window

Step 5

In the diagram

Drag and drop all columns of Source Qualifiers to the filter transformation

Link columns to target table from filter transformation

Step 6

Double-click the filter to open its properties and then

●        Choose the Assets menu

●        Tap the Editor for Condition Filter

Step 7                                          

Then in the expression editor for the filter state

Enter condition of the filter-deptno=10

Pick Press OK

Now you will see the filter condition again in the edit transformation window in the Properties tab, click the OK button.

Now, after creating the session and workflow, save the mapping and execute it. The records with deptno=10 will only be loaded in the target table.

In this way you can use filter transformation to filter the source information.

Conclusion

I hope you reach a conclusion about Transformations in Informatica. You can learn more from Informatica online training.