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

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

 

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.

We cannot use JOINER transformation to combine two data sources and get all the orders form a customer just because of the fact that, we need to use greater than (>) operator to get all the records and only equal (=) operator is supported in JOINER.

So we can create the mapping with the Active LookUp Transformation to over come the limitation.

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 ideas .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, cache can be used in another session, when we have mutiple sessions.

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

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

We can refresh Persistent Cache by
- 'Re-cache' from source is the option checked from 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 finished executing when    we don't select persistent option in lookup transformation.- If persistent option is not select, then lookup is considered as non-persistent      lookup, here it delete all the lookup cache files.

Share Lookup cache :-
-----------------------------------------
Share cache is furture divided based on whether its name or unnamed.
- unnamed cache cannot be used in muliple mapping
- name cache can be used in multiple mappings