Dynamic Partitioning In Informatica

Dynamic Partitioning

 

Supported Versions of Informatica:

PowerCenter 9.1 and 9.5

 

Dynamic Partitioning:

If the volume of data grows or you add more CPUs, session run time increases. In dynamic partitioning, Integration Service determines the number of partitions to create at run time based on factors such as source database partitions or the number of nodes in a grid.

 

Types of Dynamic Partitioning:

1.    Based on Source Partitioning - Determines the number of partitions using database partition information. The number of partitions is the maximum of the number of partitions at the source.

2.    Based on number of CPUs - Sets the number of partitions equal to the number of CPUs on the node that prepares the session. If the session is configured to run on a grid, dynamic partitioning sets the number of partitions equal to the number of CPUs on the node that prepares the session multiplied by the number of nodes in the grid.

3.    Based on number of Nodes in Grid - Sets the partitions to the number of nodes in the grid running the session. If you configure this option for sessions that do not run on a grid, the session runs in one partition and logs a message in the session log.

4.    Based on No. of Partitions - Sets the partitions to a number that you define in the Number of Partitions attribute. Use the $DynamicPartitionCount session parameter, or enter a number greater than 1.

 

Description:

Informatica PowerCenter provides enhanced dynamic pipeline partitioning capabilities that when appropriately configured, can increase pipeline parallelism, resulting in greater throughput and a significant performance improvement.

 

Benefits:

 Enhance load performance, resulting in greater throughput and a significant performance improvement.

 

Note: - Dynamic partitioning can be used if the source qualifier not includes a SQL query or source filter.

 

 

Steps to implement the solution:

1.    Open Informatica Administrator Home Page (Admin Console) and check whether partitioning is enabled or not. Partitioning feature should be enabled in license. As show below.

 

2. Create 4 partitions of data at database Level for dynamic Source Partitioning.

3. Workflow developed in Informatica.

4. Valid connection objects assigned for the source.

5. Open workflow, Observe partition navigation pane of the session by edit- >mapping->partition.

 

As shown above, source qualifier transformation’s partition type is selected as Database Partitioning. Same can be verified by clicking on “Edit Partitioned Point”. Partitioned and Non-partitioned transformations are listed accordingly in navigation panel.

6. In “Config Object” tab of the session, observe the “Partitioning Options” section. From here select type of dynamic partitioning.

 

 

 

7. Run the workflow and view target table for result. Observe the source/target statistics as shown below; 2 partitions have been created based on the 2 partitions in the source database.

 Throught Put using Simple task

 

Throught Put using Simple task

 

Note:- Here data at database level is not partitioned so unable to fetch data for partition 2.

8. When the execution completes, open up the session log by selecting on the session from the Workflow Monitor and selecting “Session Log” from the context menu.

9. Observe how the partitions are created in Session Log. You should see 2 partitions created (2 reader threads, 2 writer threads).The source database table is partitioned into 2.

 

Drawbacks:

Dynamic partitioning can’t be used if the source qualifier includes a SQL query or source filter.