Constraint Based Loading in Informatica

Constraint based load ordering is used to load the data first in to a parent table and then in to the child tables. You can specify the constraint based load ordering option in the Config Object tab of the session. When the constraint based load ordering option is checked, the integration service order the target load order on a row by row basis.

For every row generated by the active source, the integration service first loads the row into the primary key table and then to the foreign key tables. The constraint based loading is helpful to normalize the data from a denormalized source data.

• The constraint based load ordering option applies for only insert operations.
• You cannot update or delete the rows using the constraint base load ordering.
• You have to define the primary key and foreign key relationships for the targets in the warehouse or target designer.
• The target tables must be in the same Target connection group.

Complete Constraint based load ordering

There is a work around to do updates and deletes using the constraint based load ordering. The informatica powercenter provides an option called complete constraint-based loading for inserts, updates and deletes in the target tables. To enable complete constraint based loading, specify FullCBLOSupport=Yes in the Custom Properties attribute on the Config Object tab of session. This is shown in the below image.

When you enable complete constraint based loading, the change data (inserts, updates and deletes) is loaded in the same transaction control unit by using the row ID assigned to the data by the CDC reader. As a result the data is applied to the target in the same order in which it was applied to the sources.

You can also set this property in the integration service, which makes it applicable for all the sessions and workflows. When you use complete constraint based load ordering, mapping should not contain active transformations which change the row ID generated by the CDC reader.

The following transformations can change the row ID value

• Aggregator Transformation
• Custom Transformation configured as an active
• Joiner Transformation
• Normalizer Transformation
• Rank Transformation
• Sorter Transformation

Mapping Implementation of constraint based load ordering

As an example, consider the following source table with data to be loaded into the target tables using the custom transformation.

Table Name: EMP_DEPT

Create table emp_dept
(
 dept_id   number,
 dept_name varchar2(30),
 emp_id    number,
 emp_name  varchar2(30)
);

dept_id dept_name emp_id emp_name
---------------------------------
10      Finance   1      Mark
10      Finance   2      Henry
20      Hr        3      Christy
20      Hr        4      Tailor

The target tables should contain the below data.

Target Table 1: Dept

Create table dept
(
 dept_id   number primary key,
 dept_name varchar2(30)
);

dept_id dept_name
-----------------
10      Finance
20      Hr

Target Table 2: Emp

create table emp
(
 dept_id  number,
 emp_id   number,
 emp_name varchar2(30),
 foreign key (dept_id) references dept(dept_id)
);

dept_id emp_id emp_name
---------------------------------
10      1      Mark
10      2      Henry
20      3      Christy
20      4      Tailor

Follow the below steps for creating the mapping using constraint based load ordering option.

• Create the source and target tables in the oracle database
• Go to the mapping designer, source analyzer and import the source definition from the oracle database.
• Now go to the warehouse designer or target designer and import the target definitions from the oracle database.
• Make sure that the foreign key relationship exists between the dept and emp targets. Otherwise create the relationship as shown in the below images.

• Now create a new mapping. Drag the source and targets into the mapping.
• Connect the appropriate ports of source qualifier transformation to the target definition as shown in the below image.

• Go to the workflow manager tool, create a new workflow and then session.
• Go to the Config object tab of session and check the option of constraint based load ordering.
• Go to the mapping tab and enter the connections for source and targets.
• Save the mapping and run the workflow.