Tables in the same dimension are frequently linked by a primary key / foreign key relationship, which requires that a record exist in the "parent" table before a related record can exist in a "child" table. Often these tables are populated by the same data source. One method of populating parent / child tables is to set up a separate mapping and session for each one. However, that requires two reads of the source. This mapping template illustrates PowerCenter Constraint Based Load Ordering functionality, which allows developers to read the source once and populate parent and child tables in a single process.In a mapping that uses Constraint Based Load Ordering, the Informatica server orders the target load on a row-by-row basis. For every row generated by an active source, the Informatica Server loads the corresponding transformed row first to the primary key table (the "parent" table), then to any foreign key tables (the "child" tables). An active source is the last active transformation in a data stream pipeline. The following transformations can be an active source within a mapping:

• Source Qualifier
• Normalizer (COBOL or flat file)
• Advanced External Procedure
• Aggregator
• Joiner
• Rank
• Filter
• Router
• Mapplet, if it contains one of the above transformations

Implementation Guidelines:

• 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.
• 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.