Loading Incremental Updates

Version 5

    Purpose

    Demonstrate loading incremental changes/updates using a mapping variable.

    Usage

    Use a mapping variable to keep track of incremental changes from source data.

    Overview

    There are a few different methods of processing incremental changes that exist in a source table. This mapping template illustrates a method of using a mapping variable to process only those records that have changed since the last time the mapping was run.

    In addition to this method, PowerExchange’s Change Data Capture (CDC) option can be utilized to capture database change streams as soon as they occur, and be delivered to multiple targets in real time, without intermediate queues or staging tables.

     

    loading incremental changes.png


    Challenge Addressed

    When source data is frequently updated,  it may be necessary to capture the updated information in the data  warehouse as well. However, due to data volumes and load window  considerations, it is often desirable to process only those records that  have been updated, rather than re-processing the entire source data  into a mapping.

     

    Download

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

    Pros

    Process fewer records by eliminating static unchanged records from the data flow.

    Cons

    It relies on the existence of a reliable update timestamp in the source table.

     

    Implementation Guidelines

    Mapping variables add flexibility to mappings. Once a mapping variable has been declared for a mapping, it can be called by mapping logic at runtime. Unlike mapping parameters, the value of a mapping variable can change throughout the session. When a session begins, it takes the current value of the mapping variable from the repository (unless an included parameter file overrides the value) and brings it into the mapping. When the session ends, it saves the new value of the mapping variable back to the repository, to be used the next time the session runs. Refer to the PowerCenter Designer Guide for more information on using mapping variables.

    The mapping in this template uses a mapping variable called $$INCREMENT_TS. This variable is used in two places within the mapping:

    • In the source qualifier transformation sq_CUSTOMER_MASTER, the mapping variable is referenced in the ‘SQL Override’ for the WHERE clause to only bring in records that have an UPDATE_TS greater than $$INCREMENT_TS.
    • In the expression transformation exp_SET_INCREMENT_TS, the mapping variable is set by the port ‘v_SET_INCREMENT_TS’ to the current SYSDATE coming from the source.

    In this example, when the variable $$INCREMENT_TS was declared, it was given an initial value of ‘2000-01-01.’ The first time a session that implements this mapping runs, the value of $$INCREMENT_TS will be ‘2000-01-01.’ At runtime, the Informatica server will translate the WHERE clause in the ‘SQL Override’ statement from:

    WHERE
    UPDATE_TS > '$$INCREMENT_TS'

    To:

    WHERE
    UPDATE_TS > '2000-01-01'

     

    Thus, the first time the mapping runs it will pull all records for which UPDATE_TS is greater than January 1, 2000. Also, note that the ‘SQL Override’ queries the database for the value of SYSDATE, and inputs that value into the mapping through the ‘UPDATE_TS’ port overriding the “true” value of UPDATE_TS (since this value is not needed within the mapping). This brings the current date from the source system into the mapping. This value will be used in the subsequent expression transformation (i.e. exp_SET_INCREMENT_TS).

     

    The expression transformation exp_SET_INCREMENT_TS sets a new value for $$INCREMENT_TS. To do this, it uses the SetVariable() function to set the value of $$INCREMENT_TS to the value stored in the input port ‘in_CURRENT_TIMESTAMP’, which is actually the value of SYSDATE from the source system. When the session associated with this mapping runs to completion, this new value for $$INCREMENT_TS will be stored in the repository. As a result, the next time the mapping is run, only those records that have an UPDATE_TS greater than the SYSDATE for the previous session’s run will be processed.

     

    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.