Dynamic Caching

Version 6

    Purpose

    Slowly Changing Dimensions Using Effective To and From Dating

     

    Usage

    Type 2 Slowly changing dimension. This dimension will keep a full history of changes in the target by date range.

     

    Overview

    The mapping reads the incoming source and compares the source records to the warehouse table active records. Flags are set to determine if the incoming record is a new record or if it already exists in the warehouse. If the record exists in the warehouse, then the fields from the source record and the warehouse record are compared to see if there are any differences. If differences exist, then a new record is added and the old record is end-dated. If no changes exist between the two records, then the existing warehouse record remains as-is.

     

    dynamic caching.png

     

    Challenge Addressed

    In a typical PowerCenter mapping for a  dimension that keeps an entire history, the Informatica server must read  the new dimension file from the source system and compare it to the  existing records in the dimension table of the warehouse. Flagging a  record as new or as a changed record needs to be addressed by comparing  the incoming record to the records in the warehouse. If a record is new  it needs to be added to the warehouse dimension table. If a record is  unchanged, no updates or inserts should occur. If a record is changed,  the existing record needs to be effectively end-dated and a new record  containing the changes should be inserted.

     

    Pros

     

    • The entire history of a dimension remains intact in the warehouse. So, ‘Point-in-Time’ reports are available in the warehouse.
    • Can be used with relational and file-based sources.

    Cons

     

    • Large  dimension tables will require enough lookup cache to handle the data  returned from the warehouse or a partitioning strategy or non-cached  lookup may be required.
    • Large dimension tables will require  extra time for the database to perform the updates that end-date a  record. Inserts will also be affected if numerous indexes exist on the  warehouse table. Archiving the old records may be a requirement for  performance.

     

    Download

     

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

    Implementation Guidelines

     

    The source file is a comma delimited flat file which contains Customer dimension attributes. One transformation that occurs is deriving the First Name and Last Name from the Full Name attribute. The main functionality of this mapping is to perform a comparison of incoming data to existing data by using a Dynamically Cached lookup and using the built in flag ‘NewLookupRow’ to determine the appropriate load strategy. The Dynamic Cache lookup has a filter to only cache ‘Active’ records based on EFFECT_TO_DATE.

     

    Please refer to the mapping’s metadata descriptions for more information on this mapping template’s functionality.

     

    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.