Informatica PowerCenter Constraint Based Load Ordering

Johnson Cyriac Jan 20, 2013

Informatica PowerCenter Constraint Based Loading
Constraint based loading technique is available in Informatica PowerCenter since couple of its prior versions. This PowerCenter feature will let you load multiple tables in a single session, which is having database level primary key - foreign key constraint or parent - child relation. In this article let's see what is needed to set up a session for constraint based loading.

What is Constraint Based Loading

In the Workflow Manager, you can specify constraint-based loading for a session. When you select this option, the Integration Service orders the target load on a row-by-row basis. For every row, the Integration Service loads the row first to the primary key table, then to any foreign key tables.

What is Needed to Setup Constraint Based Loading

There are couple of rules for setting up Constraint-based loading in a particular session and lets see those in detail.
      1. Key Relationships.
      2. Active Source.
      3. Target Connection Groups.
      4. Treat Rows as Insert.

1. Key Relationships.

When target tables have no key relationships, the Integration Service does not perform constraint-based loading. Similarly, when target tables have circular key relationships, the Integration Service reverts to a normal load. It reverts to a normal load.

When the target definition is imported into the Target Designer, PowerCenter will detect the Key Relationship defined on the database.  You can manually create the relationship with in the Target Designer  as well.
Informatica PowerCenter Target Designer

2. Active Source.

Both the parent and child table need to be mapped from a single active source such as Source Qualifier, Aggregator, Normalizer etc. When target tables receive rows from different active sources, the Integration Service reverts to normal loading for those tables.

In the below mapping, both targets are getting rows from the active transformation Normalizer 
Informatica PowerCenter Mapping

3. Target Connection Groups.

The Integration Service enforces constraint-based loading for targets in the same target connection group. If the tables with the primary key-foreign key relationship are in different target connection groups, the Integration Service cannot enforce constraint-based loading when you run the workflow.

To verify that all targets are in the same target connection group, complete the following tasks.
  • All targets are in the same target load order group and receive data from the same active source.
  • Use the default partition properties and do not add partitions or partition points.
  • Define the same target type for all targets in the session properties.
  • Define the same database connection name for all targets in the session properties.
  • Choose normal mode for the target load type for all targets in the session properties.
    Below Image shows the target are in the same load order group.  You can verify the load order from the mapping designer.
    Target load order
    Other required properties for the Constraint Based loading is highlighted in the below image. We can set these properties in the session level from workflow manager.
    Informatica PowerCenter Session Properties

    4. Treat Rows as Insert.

    Use constraint-based loading when the session option Treat Source Rows As is set to Insert. You might get inconsistent data if you select a different Treat Source Rows As option and you configure the session for constraint-based loading.

    Set this property in the session level as shown in below image.
    Informatica PowerCenter session properties

    How to Setup Constraint Based Loading

    Now we now the requirements for setting up a session for constraint based target loading, Lets see how we configure the session.

    To enable constraint-based loading, you need to set the "Constraint bases load ordering" property as shown in below image.
    Informatica PowerCenter Constraint Based Loading
    That is all we need to setup a session for constraint based target loading.

    If you go through the session log file, you can see the details of the constraint based Load Ordering as in below image.


    Hope you enjoyed this article and will be helpful in your live projects. Please feel free to leave a comment or question below, we are more than happy to help.





    About US Contact US Advertise Guest Post Terms and Conditions Privacy Policy Disclaimer

    © 2012-2013 Data Intelligence Solution, All Rights Reserved
    The contents in this site is copyrighted to Data intelligence Solution and may not be reproduced on other websites.