When data in a source table is frequently updated, it is necessary to capture the updated information in the data warehouse. However, due to data volumes and load window considerations, it is desirable to process only those records that have been updated, rather than re-reading the entire source into a mapping. Usually there is an update timestamp column in the source table. This column can be used to filter the source records for reading the updated rows. This filter condition has to change every time the mapping is run to extract only the records that were updated since the last run. This mapping template illustrates a method of using the mapping variable functionality to dynamically build the filter every time the mapping run. Implementation Guidelines:

• The mapping in this template uses a mapping variable to track and store the maximum value of update timestamp every time the session runs. Once a mapping variable has been declared for a mapping, it can be called by mapping logic at runtime. 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 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 is run.

Note:The download is a zip file which contains the exported mapping in the XML format. After extracting the XML file it can be imported into the PowerCenter Designer client tool. You can download this listing as part of the Bundle : Data Warehouse.