Constraint Based Loading in Informatica

  • Updated
  • Posted in Programming
  • 4 mins read


Constraint based mostly load ordering is used to load the info first in to a mother or father desk after which in to the kid tables. You can specify the constraint based mostly load ordering choice in the Config Object tab of the session. When the constraint based mostly load ordering choice is checked, the combination service order the goal load order on a row by row foundation. For each row generated by the energetic supply, the combination service first masses the row into the first key desk after which to the international key tables. The constraint based mostly loading is useful to normalize the info from a denormalized supply information.

  • The constraint based mostly load ordering choice applies for less than insert operations.
  • You can’t replace or delete the rows utilizing the constraint base load ordering.
  • You need to outline the first key and international key relationships for the targets in the warehouse or goal designer.
  • The goal tables have to be in the identical Target connection group.

Complete Constraint based mostly load ordering

There is a piece round to do updates and deletes utilizing the constraint based mostly load ordering. The informatica powercenter gives an choice known as full constraint-based loading for inserts, updates and deletes in the goal tables. To allow full constraint based mostly loading, specify FullCBLOSupport=Yes in the Custom Properties attribute on the Config Object tab of session. This is proven in the under picture.

When you allow full constraint based mostly loading, the change information (inserts, updates and deletes) is loaded in the identical transaction management unit by utilizing the row ID assigned to the info by the CDC reader. As a outcome the info is utilized to the goal in the identical order in which it was utilized to the sources. You can even set this property in the combination service, which makes it relevant for all of the classes and workflows. When you employ full constraint based mostly load ordering, mapping shouldn’t include energetic transformations which change the row ID generated by the CDC reader.

The following transformations can change the row ID worth

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

Mapping Implementation of constraint based mostly load ordering

As an instance, think about the next supply desk with information to be loaded into the goal tables utilizing the customized transformation.

Table Name: EMP_DEPT

Create desk emp_dept
(
dept_id quantity,
dept_name varchar2(30),
emp_id quantity,
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 goal tables ought to include the under information.

Target Table 1: Dept

Create desk dept
(
dept_id quantity main key,
dept_name varchar2(30)
);

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

Target Table 2: Emp

create desk emp
(
dept_id quantity,
emp_id quantity,
emp_name varchar2(30),
international 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 under steps for creating the mapping utilizing constraint based mostly load ordering choice.

  • Create the supply and goal tables in the oracle database
  • Go to the mapping designer, supply analyzer and import the supply definition from the oracle database.
  • Now go to the warehouse designer or goal designer and import the goal definitions from the oracle database.
  • Make certain that the international key relationship exists between the dept and emp targets. Otherwise create the connection as proven in the under photographs.
  • Now create a brand new mapping. Drag the supply and targets into the mapping.
  • Connect the suitable ports of supply qualifier transformation to the goal definition as proven in the under picture.
  • Go to the workflow supervisor device, create a brand new workflow after which session.
  • Go to the Config object tab of session and verify the choice of constraint based mostly load ordering.
  • Go to the mapping tab and enter the connections for supply and targets.
  • Save the mapping and run the workflow.

Leave a Reply