Pushdown optimization technique in informatica PowerCenter
This article will help you to understand Pushdown Optimization technique to enhance Informatica ETL performance. We will see how to implement Pushdown optimization and its limitation.
Author: Dhandhaliya Dhiraj
Informatica ETL Developer | SQL Developer | IICS Developer | IDQ Developer
Email | Linkedin Profile
What is Pushdown Optimization?
Pushdown Optimization is use to increase data processing performance extremely. Always processing data on database level is faster than processing data in Informatica level. Pushdown optimization working on same concept.
Pushdown Optimization is a concept of push Informatica transformation logic on source or target when they are database table. Here whatever transformation logic can be convert in SQL query those can be push down on database side (execute on database).
Type of Pushdown Optimization
There are 3 type of Pushdown optimization available as mentioned below.
1. Source-side Pushdown optimization
2. Target-side Pushdown optimization
3. Full Pushdown optimization
Let us take below mapping example to understand deference between types.
Mapping contained objects like
ITEMS – Source (database)
SQ_ITEMS – Source Qualifier transformation
Agg_Price – Aggregator transformation
RNK_Prices – Rank transformation
EXP_Prices_Rankes – Expression Transformation
ITEM_Totals – Target (database)
1. Source-side pushdown optimization
When we run a session configured for source-side Pushdown optimization, the Integration Service analyses the mapping from the source to the target direction and generate SQL SELECT statement as per the mapping logic. This generated statement will execute on source database and fetch the records and process further to insert/update/delete in target database.
If mapping pipeline having a transformation that logic could not able to convert in SQL then Integration service execute current prepared SQL statement to execute on source database and captured data then processes the remaining transformation.
In our example, RANK transformation logic could not able to convert into SQL(due to limitation), so when we use Source-side Pushdown optimization, Informatica integration service generate SQL SELECT statement for Source, Source Qualifier and Aggregation transformation. This statement will execute on source database and send captured data to rank transformation through pipeline.
2. Target-side Pushdown optimization
When we run a session configured for target-side Pushdown optimization, the Integration Service analyzes the mapping from the target to the source and generate SQL INSERT or UPDATE or DELETE statement as per the mapping logic. This generated statement will execute on Target database, fetch the records, and process further to pipeline.
In our example, RANK transformation logic could not able to convert into SQL, so when we use Target-side Pushdown optimization, Informatica integration service generate SQL INSERT or UPDATE or DELETE statement for Target and expression transformation. Therefore, the data which coming from RANK transformation will feed to prepared SQL statement.
3. Full Pushdown optimization
To use full Pushdown optimization, the source and target databases must be in the same relational database management system. In full Pushdown optimization, Integration service try to push as possible as transformation logic to source or target.
In our example, the Rank transformation cannot be push to the source or target database. If you configure the session for full Pushdown optimization, the Integration Service pushes the Source Qualifier transformation and the Aggregator transformation to the source, processes the Rank transformation, and pushes the Expression transformation and target to the target database. The Integration Service does not fail the session if it can push only part of the transformation logic to the database.
How to implement Pushdown optimization?
To implement Pushdown optimization, we need set session level properties as shown in screenshot. (From IICS)
You can select value “No” for other option where choose “Yes” as per your requirement.
We have below 5 option for session property “Pushdown Optimization”.
1. None. The task processes all transformation logic for the task.
2. To Source. The task pushes as much of the transformation logic to the source database as possible.
3. To Target. The task pushes as much of the transformation logic to the target database as possible.
4. Full. The task pushes as much of the transformation logic to the source and target databases as possible. The task processes any transformation logic that it cannot push to a database. Applicable when the source and target are the same databases.
5. $$PushdownConfig. The task uses the Pushdown optimization type specified in the user-defined parameter file for the task.
In Informatica Powercenter we can also able to see SQL Logic as see in the picture, while implementing Pushdown optimization along with error, if any.
Pushdown Optimization Limitation
1. Informatica Integration(IS) service can push SQL logic only for below transformations.
--Aggregator --Sequence Generator
--Expression --Sorter
--Filter --Source Qualifier
--Joiner --Target
--Lookup --Union
--Router --Update Strategy
2. Integration service transfer the transformation logic at database level, so we cannot able to find rejected rows.
3. We cannot able to use variable port in Expression transformation.