Constraint Based Loading in Informatica

Constraint based loading is used to load the data first into the parent table (It has Primary key) and later into the child table (It has Foreign Key). When the constraint based load ordering option is checked, the integration service order the target load order on a row by row basis. 
The following requirements can be needed to set up Constraint based loading
  • Active source
  • Key Relationships
  • Target Connection groups
  • Treat rows as insert 
Active source
Constraint-based load ordering is only implemented in a session for target tables that receive rows from the same active source. When target tables receive records from different active sources, the Informatica Server reverts to normal loading for those tables.
     For example, a mapping contains three distinct data streams: the first two both contain a source, Source Qualifier, and target. Since these two targets receive data from different active sources, the Informatica Server reverts to normal loading for both targets. The third data stream contains a source, Normalizer, and two targets. Since these two targets share a single active source (the Normalizer), the Informatica Server performs constraint-based load ordering: loading the primary key table first, then the foreign key table.
Key Relationships
When target tables have no key relationships, the Informatica Server does not perform constraint-based loading. Similarly, when target tables have circular key relationships, the Informatica Server reverts to a normal load. For example, you have one target containing a primary key and a foreign key related to the primary key in a second target. The second target also contains a foreign key that references the primary key in the first target. The Informatica Server cannot enforce constraint-based loading for these tables. It reverts to a normal load.
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.
Treat rows as insert 
Use constraint-based load ordering only when the session option Treat Rows As is set to "Insert." When you select a different Treat Rows As option and you configure the session for constraint-based loading, the Workflow Manager displays a warning. A session can be configured for constraint-based load ordering by selecting the "Constraint-based load ordering" check box on the Configuration Parameter window of the Session Properties sheet. The Configuration Parameter window is accessed by selecting the button "Advanced Options" from the General Tab of the session properties window.
 The Constraint based load ordering attribute applies only to insert operations. Change data normally contains a mixture of insert, update, and delete operations. PowerCenter provides a custom property to enable complete constraint-based loading. 
             When you enable complete constraint-based loading, change data is loaded to targets in the same Transaction Control Unit (TCU) by using the row ID assigned to the data by the CDC Reader. As a result, data is applied to the targets in the same order in which it was applied to the sources. The following message will be issued in the session log to indicate that this support is enabled: WRT_8417 Complete Constraint-Based Load Ordering is enabled.To enable complete constraint-based loading, specify FullCBLOSupport=Yes in the Custom Properties attribute on the Config Object tab. This property can also be set in the PowerCenter Integration Service, which makes it applicable to all workflows and sessions that use that the PowerCenter Integration Service. 

If you use complete constraint-based loading, your mapping must not contain active transformations which change the row ID generated by the CDC Reader. The following transformations change the row Id value: 
  • Aggregator Transformation
  • Custom, configured as an active transformation 
  • Joiner Transformation
  • Normalizer Transformation
  • Rank Transformation
  • Sorter Transformation
Create and implement Constraint based loading 
  • Create source and target tables 
  • Make sure the foreign key relationship exists between both target tables.
  • If not exists create the relationship between the tables. 
  • Go to the mapping designer and drag both source and target tables
  • Connect both source and targets.
  • Go to the work flow Manger and create workflow and task
  • Edit the task-> go to the configuring object tab and check the option constraint based loading option. 
  • Start the workflow. 

Powered by Blogger.