Constraint-Based Loading

Version 9

    Purpose

    Demonstrate the usage of constraint based loading.

    Usage

     

    The Integration Service can load data to targets based on primary key-foreign key constraints and active sources in the session mapping. Using one mapping and a session, load two tables that have a parent / child relationship with an enforced primary key / foreign key relationship.

    Overview

     

    In a mapping that uses Constraint Based Load Ordering, PowerCenter’s Integration Service orders the target load on a row-by-row basis. For every row

    generated by an active source, PowerCenter’s Integration Service loads the corresponding transformed row first to the primary key table (the "parent" table), then to any foreign key tables (the "child" tables). Constraint-based loading depends on the following requirements:

     

    • Active source: Related target tables must have the same active source.
    • Key relationships: Target tables must have key relationships.
    • Target connection groups: Targets must be in one target connection group.
    • Treat rows as insert: Use this option when you insert into the target. You cannot use updates with constraint-based loading.

    Challenge Addressed

     

    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’s "Constraint Based Load Ordering" functionality, which  allows developers to read the source once and populate parent and child  tables in a single process.

    Download

    • PowreCenter 9.6.x XML Mapping (See Attachments section below)
    • PowerCenter 9.1.0 XML Mapping (See Attachments section below)
    • PowerCenter 8.6.1 XML Mapping (See Attachments section below)

     

    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, PowerCenter’s Integration Service 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, PowerCenter’s Integration Service 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 PowerCenter’s Integration Service performs constraint-based load ordering: loading the primary key table first, then the foreign key table.

     

    When target tables have no key relationships PowerCenter’s Integration Service does not perform constraint-based loading. Similarly, when target tables have circular key relationships, PowerCenter’s Integration Service 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. PowerCenter’s Integration Service cannot enforce constraint-based loading for these tables. It reverts to a normal load.

    Example

     

    constraints based loading.png

     

    The key concepts illustrated in this mapping template can be found in the router transformation (rtr_NEW_MANUFACTURERS_ALL_ITEMS) and the two target objects Items and Manufacturers.

     

    The rtr_NEW_MANUFACTURERS_ALL_ITEMS is the last single active source of data for the two targets. The targets Items and Manufacturers have a Primary Key –Foreign Key relationship. A manufacturer ID must exist in the Manufacturers table before a row containing the manufacturer ID, can be loaded into the ITEMS table.

     

    Using a normal load for this mapping would result in a constraint error, as the PowerCenter Integration Service would attempt to load the tables in any order. In this example, this may result in attempt to load a row into the ITEMS table that does not have a corresponding manufacturer in the Manufacturers table.

    When you enable constraint-based loading, the Integration Service orders the target load on a row-by-row basis.

     

    To enable constraint-based loading:

    • In the session that will use constraint-based load ordering, set 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 service displays a warning.
    • While in the session, select Config Object tab. In the Advance settings, select Constraint Based Load Ordering.
    • Click OK.

     

    Note:

    • The Mappings were originally built using PowerCenter Designer. If you are using PowerCenter Express (PCX), all the mappings cannot be imported as PCX includes only the Informatica Developer tool and not PowerCenter Designer tool.  For example, Informatica Developer doesn’t support mappings that use “Sequence Generator” transformation.
    • The objects are based on “UTF-8” codepage. If you have changed you codepage, XML may need to be edited.