Normalizer Transformation

Normalizer Transformation is Active and Connected transformation. It converts single row data into multiple columns data. It converts de-normalized table into a normalized table. You cannot drag & drop columns to normalizer transformation like the rest of the transformations.

* Normalizer is used to convert rows into columns.
* Normalizer is used in the place of source qualifier while reading mainframe or Cobal Source.

Steps to create Normalizer transformation :-
-----------------------------------------------------------------
- Go to tranformations tab and select normalizer
- Double click normalizer and select the normalizer tab
- Add the occurs based on the requirement

In Normalizer properties tab 'Reset' and 'Restart' are the two options available.

Reset is used to reset the Gk value to the value, that is used before the session.
Restart is used to start Gk sequence from 1 and restart for each session

There 2 important ports available and they are GK (Generated Key) and GCID (Generated Column ID)

GK generates sequence number starting from the value defined in the sequence field.

GCID hold the value of the occurrence field.

Normalizer generates seperate rows based on the occurances we put in the transformation. 

GK is used to identify whether the records belongs to the same original record and allocates key or values like 1 or 2.

GCID is used to give column id's or values to the generated columns or occurances like 1,2,3.

Stored Procedure Transformation

Stored Procedure Transformation is Passive transformation. 
It is both connected and unconnected.
Procedure are stored in database and we call the stored procedure through stored procedure transformation in Informatica.
To perform the calculations like Salary hike, Min and Max salary, we use stored procedure Transfomation.

Examples :-
------------
If an application updates the customer table in ten different places, there can be a single stored procedure and a standard procedure call from the application for this functionality.
If a stored procedure has four SQL statements in the code, then there only needs to be a single call to the database instead of four calls for each individual SQL statement.


Two types of ports :-
---------------------- 
Input : This port is used to get Input parameter.
Output: This port will display the output values as per the procedure defined in database.
Return : This port is used to return values. 


Stored Procedure Type :-
--------------------------
There are 5 different Options available in Stored Procedure Transformation Properties.

- Normal : It will run row by row basis.
- Source Pre Load : It works like property in Source Qualifier as 'Pre Sql'. 
  Before reading the data from the source Source Pre Load would act on              database.
- Source Post Load : It works like property in Source Qualifier as 'Post Sql'. 
   After the load, Source Post Load would act on database. 
- Target Pre Load : It works like property in Source Qualifier as 'Pre Sql'. 
   Before reading the data to the target Target Pre Load would act on                     database.
- Target Post Load : It works like property in Source Qualifier as 'Post Sql'. 
   After the load, Source Post Load would act on database


To create a stored procedure transformation :-

- Go to the transformations tab and click import stored procedure option.
- Connect to the database where the stored procedures is stored and select it.
- Now connect the appropriate ports to input and out put ports.

- Select the connection information 

- Now execute the workflow, we get the expected results.

Union Transformation

Union Transformation is Connected and Active transformation. 
It is used when we have requirement like multiple input groups but only 1 output group. It is used to merge data from multiple sources and load into single target. So this types of transformation is called multi input group transformation.

We cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation

Union transformation is similar to the Union All in Sql. 
So it does not remove duplicates just like the Union All from Sql.

Steps to create a union transformation :-
- Go the mapping designer, create a new mapping or open an existing mapping
- Go to the toolbar-> click on Transformations->Create
- Select the union transformation and enter the name. Now click on Done and then click on OK.
- Go to the Groups Tab and then add a group for each source you want to merge
- Go to the Group Ports Tab and add the ports.

Update Strategy Transformation

Update Strategy Transformation is Connected and Active transformation. It is used to insert, update, delete and reject records from a file or table while loading into the target. Update Strategy flags row type to insert or update or delete as per the requirement.

In order to perform insert, update, delete and reject records we have to flag the rows with certain key words like 

DD_INSERT : Numeric value is 0. Used for flagging the row as Insert.
DD_UPDATE : Numeric value is 1. Used for flagging the row as Update.
DD_DELETE : Numeric value is 2. Used for flagging the row as Delete.
DD_REJECT : Numeric value is 3. Used for flagging the row as Reject.

We have to select the option in session properties 'treat source rows as' Data driven, but this alone will not let you modify rows in the target. Also check the option in Target level properties as 'Update else insert' to apply the Update strategy transformation to flag the rows.


There are three target level properties:-
--------------------------------------------
Update else insert : It property tells, when a row arrives target, if it is a new row it will be inserted, if its a old row it should be updated.
Update as update : It property tells, if row arrives at the target, it has to be update in the target.
Update as insert : It property tells, when a row arrives target and if that row has to be updated then that update behavior should be a insert. 

Also select insert option in target level properties for update as insert and update else insert. Otherwise, session fails thinking that target does not allow inserts, its because these clauses have insert in them.

We cannot load the data into the target without defining primary keys on the target table, when we use update strategy transformation. Session generates error as 'target has no keys specified'.

But we override the target definition by using the option in properties tab of target definition 'Update override'.
We can use option 'Generate SQL', to get update override syntax (or) we can write our own syntax, as per the requirement

Target Override Query is 
'UPDATE TGT_EMP SET NAME=TU.NAME,DNO=TU.DNO, SAL=TU.SAL WHERE EMPNO=TU.EMPNO'. 

Sorter Transformation

Sorter Transformation is Connected and Active transformation. It is use to sort the data either in ascending order or descending order by specifying sort key.
It is used to sort the data from both flat file or relational databases.

Steps to create a sorter transformation :-
- In the mapping designer, create a new mapping or open an existing mapping
- Go the toolbar->Click on Transformation->Create
- Select the Sorter Transformation, enter the name, click on create and then click on Done.
- Select the ports from the upstream transformation and drag them to the sorter transformation. You can also create input ports manually on the ports tab.
- Now edit the transformation by double clicking on the title bar of the transformation.

- Select the ports you want to use as the sort key. For each selected port, specify whether you want the integration service to sort data in ascending or descending order.

Source Qualifier Transformation

Source Qualifier Transformation is Connected and Active transformation. When ever we drag our source to mapping area by default source qualifier will be generated automatically. Its because the sq converts the other data types to informatica compatible or native data types. This source qualifier data types should not be altered at any moment.

Sq can perform many tasks like

- It can join two to more tables from same database by using option from   properties tab 'user defined join'.

- It can filter rows from source by using 'source filter' option from properties   tab. 

- We can sort the data by adding order by clause to 'sql query' option from   properties tab. 

- It can remove duplicate by selecting distinct option from properties tab. 

Creating Source Qualifier Transformation :-

- Click Transformation -> Create

- Select the Source Qualifier transformation.

- Enter a name for the transformation

- Click on create

Sequence Generator Transformation

Sequence Generator Transformation is Connected and Passive Transformation. It is used to generate the sequence numbers of our choice. If we want to set seq no from 1 to 100 or 200 to 300 its completely up to us or as per the requirement.

Steps to create a sequence generator transformation :-
- Go to the mapping designer tab in power center designer.
- Click on the transformation in the toolbar and then on create.
- Click on the transformation in the toolbar and then on create.
- Edit the sequence generator transformation, go to the properties tab and configure the options.

- To generate sequence numbers, connect the NEXTVAL port to the transformations or target in the mapping.

Router Transformation

Router transformation is an active and connected transformation. It is an extension of the filter transformation. Here in the router, we can give multiple filter conditions and pass the records that satisfy the filter conditions and route the rest of the records to the default group, whereas in the filter, we can give a single condition that connects to a single target, and records that do not satisfy the condition will be dropped or filtered out at the mapping level.

Router Transformation has two groups.
Input groups : Here we specify the group name with our choice.
Output groups : Here we specify the  filter condition according to mapping logic

 

* Can't we use multiple filter transformations instead of using router transformation?
When multiple filters are used, each filter transformation reads all records and makes multiple passes over the same data. It will create performance issues.

While Router passes records only a single time and uses it for multiple conditions.

Rank Transformation

Rank Transformation is Active and Connected Transformation. It is used to rank the list of the records in a table. We can rank top and bottom records in a table based on our requirement. If we want to get bottom records we select bottom option and wise versa.

Cache files created:-

----------------------- 

There are 2 different Cache files created with Rank transformation 

- Index Cache : Index Cache stores group values configured in group by ports.

- Data Cache : Data Cache stores ranking information based on group by ports.

Steps to create rank transformation :-
- In the mapping designer, create a new mapping or open an existing mapping
- Go to Toolbar->click Transformation -> Create. Select the Rank transformation
- Enter a name, click on Create and then click on Done
- By default, the rank transformation creates a RANKINDEX port. The RankIndex port is used to store the ranking position of each row in the group
- You can add additional ports to the rank transformation either by selecting and dragging ports from other transformations or by adding the ports manually in the ports tab
- In the ports tab, check the Rank (R) option for the port which you want to do ranking. You can check the Rank (R) option for only one port. Optionally you can create the groups for ranked rows. select the Group By option for the ports that define the groups 

Lookup Transformation

Lookup Transformation is both connected and unconnected.
Lookup Transformation is both active and passive. 

From Informatica PowerCenter Version 9x onwards we can configure the lookup transformation to return all the rows from the lookup table matching the lookup condition.

Choose the LookUp Table from the popup window and select 'Return All Values on Multiple Match'. This property will set the lookup as active lookup transformation.

From the properties tab you can see the 'Lookup Policy on Multiple Match' property is set as 'Use All Values' and it is a read only property and cannot be changed after the transformation is created. This option 'use all values' automatically will be selected when the configure lookup as active.

In normal or passive lookup transformation Lookup policy on multiple match will show 4 options as mention in the image below
By default 'lookup policy on multiple match' selects 'use any value' option, as a result it returns first value. 

Lookup Transformation is used to lookup data in a flat file or a relational database. Lookup is used when we have 2 tables and if we want to lookup data on one table based on other table. Both the table should have common columns and based on that we give condition in lookup transformation where lookup table 1 = lookup table 2. Lookup condition should be on the primary key column.

Lets say there is an EMP1 table in source and TGT_EMP1_LOOKUP table in target. If we perform lookup on DEPT table to get the department name and location with our join condition(deptno) then only the matching rows will be loaded into the target TGT_EMP1_LOOKUP.

Connected Lookup :-

Unconnected Lookup :-

- In unconnected lookup we dont give direct connection, we create a port            in_deptno and select input port.
- We need to select an return port to display the result.
- In Expression transformation, we create a Dname port for output and from the functions select lookup, inorder to select :lkp. expression.
- Add Deptno to give input to :Lkp. expression.
- We cannot put more than 1 return ports in unconnected lookup
   

- Connected lkp returns multiple columns from each and every row where as Unconnected lkp returns only one column from each row

Steps to create  the lookup transformation :-

- Login to the Power center Designer. Open either Transformation Developer tab or Mapping Designer tab.
- Click on the Transformation in the toolbar, and then click on Create.
- Select the lookup transformation and enter a name for the transformation. Click Create
- Now you will get a "Select Lookup Table" dialog box for selecting the lookup source, choosing active or passive option. 
- You can choose one of the below option to import the lookup source definition
Source definition 
Target definition 
Source qualifier
Import a relational source
- In the same dialog box, you have an option to choose active or passive lookup transformation. You can see this option in red circle in the above image. To make the lookup transformation as active, check the option "Return All Values on Multiple Match". Do not check this when creating a passive lookup transformation. If you have created an active lookup transformation, the value of the property "Lookup policy on multiple match" will be "Use All Values". You cannot change an active lookup transformation back to a passive lookup transformation
- Click OK or Click Skip if you want to manually add ports to lookup transformation
- Click OK or Click Skip if you want to manually add ports to lookup transformation
- For unconnected lookup transformation, create a return port for the value you want to return from the lookup.
- Go to the properties and configure the lookup transformation properties
- For dynamic lookup transformation, you have to associate an input port, output port or sequence Id with each lookup port.
- Go the condition tab and add the lookup condition.


Based on cache Lookup is categorized into 3 types :-
- Static cache lookup
- Dynamic cache lookup
- Persistent cache lookup


Static cache :-
---------------
Integration service creates Static Cache by default while creating lookup cache. In Static Cache the Integration Service does not update the cache while it processes the data .In Static Cache when the Lookup condition is true it return value from lookup table else returns Null values .In Static Cache the important thing is that you cannot insert or update the cache.

Here once when the cache is created, integration service always queries the cache instead of lookup table. Here integration service does not update the cache while it process transformation. Data in the cache will stay the same for the entire session.

Dynamic cache :-
-------------------
In Dynamic Cache we can insert or update rows in the cache when we pass the rows. The Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target. The dynamic cache is synchronized with the target.

Here integration service dynamically inserts or updates data in the lookup cache and pass the data to the target.

After selecting the 'dynamic lookup cache' option in properties tab of lookup transformation, additionally we need to check two more options 'synchronize dynamic cache and output old values on update' option. 

We should also select associated ports for lookup updates.
You must associate each lookup port with an input source port or sequence ID, or an expression.

For every insert integration service assigns new lookup row as 1
For every update integration service assigns new lookup row as 2
For every reject integration service assigns new lookup row as 0

Persistent cache :-
--------------------
Persistent cache remains the same, if we want to use a source table again and again and that source doesn't change then we will follow this logic.

- Select persistent option from lookup transformation.

- To reuse cache files across the sessions, specify a cache file name prefix

- With this persistent cache, the cache can be used in another session when we have multiple sessions.

- So as we use persistent cache, the first session will create new cache files.

- Later on, the second session will reuse the existing cache files of the first session.
- The session log shows as refreshing cache files. But for it we need to use the same cache that is used for the first session. The two options we need to select are lookup persistent option and cache file name prefix

We can refresh Persistent Cache by
- 'Re-cache' from source is the option checked from the properties tab.
- You can save the lookup cache files and reuse them the next time.

Non-Persistent Lookup:-
-----------------------------
- By default lookup is non-persistent. 
- Lookup deletes the cache files as soon as the session finishes executing when we don't select the persistent option in lookup transformation. - If the persistent option is not selected, then lookup is considered as non-persistent lookup; here it deletes all the lookup cache files.

Share Lookup cache:-
-----------------------------------------
The shared cache is further divided based on whether it's named or unnamed.
- unnamed cache cannot be used in multiple mapping
- name cache can be used in multiple mappings

Joiner transformation

Joiner transformation is Connected and Active transformation. It is used to join the two tables or more tables from heterogeneous sources or multiple sources.
We join the both the tables by specifying a condition in the joiner transformation. If we want to join 'n' number of tables we connect 'n-1' joiner transformations. By default joiner transformation considers one of the tables as master and other as detail tables.

Cache files created:-

----------------------- 

There are 2 different Cache files created with Joiner transformation 

- Index Cache : Index cache stores all the port values which are participated in the join condition.

- Data Cache : Data cache have stored all ports which are not participated in the join condition.

Steps to create a joiner transformation in Informatica :-
------------------------------------------------------------
- Go to the mapping designer, click on the Transformation->Create.
- Select the joiner transformation, enter a name and click on OK.
- Drag the ports from the first source into the joiner transformation. By default   the designer creates the input/output ports for the source fields in the joiner   transformation as detail fields.
- Now drag the ports from the second source into the joiner transformation. By   default the designer configures the second source ports as master fields.
- Edit the joiner transformation, go the ports tab and check on any box in the M   column to switch the master/detail relationship for the sources.
- Go to the condition tab, click on the Add button to add a condition. You can   add multiple conditions.
- Go to the properties tab and configure the properties of the joiner     transformation.

There are 4 types of joins in joiner transformation :-
- Normal join
- Master outer join
- Detail outer join
- Full outer join

Normal join : It returns only the records that match the join condition given in the joiner transformation. The Normal Join in Informatica is exactly similar to SQL Inner Join.

Master outer join : It returns only the matching rows from the master table and all the rows from detail table. The Master Join in Informatica is exactly similar to SQL Right outer Join.

Detail outer join : It returns only the matching rows from the details table and all the rows from master table.The Detail Join in Informatica is exactly similar to SQL Left outer Join.

Full outer join : It first returns matching rows from both the table and also returns unmatched rows from both the tables.The Full Join in Informatica is exactly similar to SQL Full outer Join.


Joiner is a blocking transformation :-
---------------------------------------
Integration service blocks and unblocks the source data depending upon whether joiner is sorted or unsorted.

Unsorted Joiner : Here integration service first reads master rows by blocking detail rows, after competing caching of master data it unblocks the detail rows and reads the detail rows.
Sorted Joiner : It may not be possible here 

Filter transformation

Filter transformation is connected and active transformation. It is used to filter out the unwanted rows from loading into our target. In order to filter out the rows we have should specify the condition in the filter transformation. If the condition is true rows will be passed to other transformations. If the condition is false rows will not be passed to other transformations.

Steps to create filter transformation :-
- In the mapping designer, open a mapping or create a new mapping.
- Go to the toolbar->click on Transformation->Create->Select the filter    transformation
- Enter a name->Click on create and then click on done.

- You can add ports either by dragging from other transformations or manually creating the ports within the transformation.

Aggregator Transformation

Aggregator Transformation is connected and active transformation used to perform calculations. Aggregate calculations means sums, averages, counts and so on.

Points To Remember :-
---------------------------
If group by is not checked, by default integration service returns last row as a result.

If group by is checked integration service returns all the rows from the table.
If the sorted input is not checked data will not be correct order.

If the sorted input is checked and sorter transformations or order by clause is not used in source qualifier then session would fail with error as 'expecting keys to be descending' 

Group by port and sorter transformation (or) order by clause in source qualifier should be on same port otherwise the session fails for the error as before.


Cache files created:-
-------------------------- 

There are 2 different Cache files created with Aggregator transformation 

- Index Cache : Index Cache stores group values configured in group by ports.

- Data Cache : Data Cache stores calculations based on group by ports.

Steps to create an Aggregator transformation :-

- Go to the Mapping Designer, click on transformation in the toolbar and select create
- Select the Aggregator transformation, enter the name and click create. Then click Done. This will create an aggregator transformation without ports.
- To create ports, you can either drag the ports to the aggregator transformation or create in the ports tab of the aggregator.

Expression Transformation

Its the first and basic transformation used in Informatica power center tool. Expression transformation is connected and Passive transformation used to calculate values on a single row. It is used for concatenating first and last names, conversion of strings to date, used for conditional statements and so on.

Steps to create an expression transformation :-
- In the mapping designer, create a new mapping or open an existing mapping.
- Go to Toolbar->click Transformation -> Create. Select the expression  transformation.
- Enter a name, click on Create and then click on Done.
- You can add ports to expression transformation either by selecting and dragging ports from other transformations or by opening the expression transformation and create ports manually.