Constraint Based Loading and Target Load Order

When you have a Parent – Child relationships (Primary Key, Foreign Key) defined in the target database, loading data in target requires special consideration.

 Either you have to load data into the target in different sessions or you have to ensure that database constraints are honored before loading data into the target.

The example given here shows how to use Constraint Based Loading and Target Load Order while loading

We will look at target load order in this post and constraint based loading in the subsequent one.

Target Load Order -->

If you have multiple SQ connected to muliple targets and if you want to decide the order in which the targets are loaded , we go with target load order .

We can decide the target load order based on the SOurce qualifier transformations preset in the mapping 

Target Load Order Group:

A target load order group is the collection of source qualifiers, transformations and targets linked in a mapping. The integration service reads the target load order group concurrently and it processes the target load order group sequentially. The following figure shows the two target load order groups in a single mapping:

Use of Target Load Order:

Target load order will be useful when the data of one target depends on the data of another target. For example, the employees table data depends on the departments data because of the primary-key and foreign-key relationship. So, the departments table should be loaded first and then the employees table. Target load order is useful when you want to maintain referential integrity when inserting, deleting or updating tables that have the primary key and foreign key constraints.

Target Load Order Setting:

You can set the target load order or plan in the mapping designer. Follow the below steps to configure the target load order:

1. Login to the powercenter designer and create a mapping that contains multiple target load order groups.
2. Click on the Mappings in the toolbar and then on Target Load Plan. The following dialog box will pop up listing all the source qualifier transformations in the mapping and the targets that receive data from each source qualifier.

3. Select a source qualifier from the list.
4. Click the Up and Down buttons to move the source qualifier within the load order.
5. Repeat steps 3 and 4 for other source qualifiers you want to reorder.
6. Click OK