Re-Runnability for ETL Processes Which Uses Mapping Variables
Informatica PowerCenter mapping variable can be effectively used to implement change data capture logic. The mapping variable is stored in the repository and its value is set to the new value only when the session execution is successful. This property makes the restartability of the ETL process easy. Since the last success point - variable value, is stored in repository, we cannot go back and reprocess an already processed data set.  In this article lets see how we can handle overriding the mapping variable value.

A Bit of Theory Before Implementation

Before we look into the implementation, lets us understand the theory behind the implementation.

A mapping variable can take its starting value from
    1. Parameter file
    2. Pre-session variable assignment
    3. Value saved in the repository
    4. Initial value
    5. Default Value
The Integration Service looks for the start value in the order mentioned above. So we can override the mapping variable value stored in the repository using a parameter file.

Override Mapping Variable Using Parameter File

We can configure a workflow or session to use a parameter file by giving the parameter file details at the workflow or session level as shown in below image.
Re-Runnability for ETL Processes Which Uses Mapping Variablesimage
The parameter file will have the name of the mapping variable and the overriding value for the mapping variable as shown below. 

                    [s_Load_CUST_DIM]
                    $$M_LST_RUN_TS=01-01-2013 00:00:00

Note : Remove mapping variable value from the parameter file to use the value saved in the repository. The parameter file will look like 

                    [s_Load_CUST_DIM]
                    $$M_LST_RUN_TS=

Design Advantages

  • No code changes required in a production environment when there is a need to override the repository persisted mapping variable value.
  • Easy to set the starting value of the mapping variable for the initial run in production environment.
  • Can be used to set the mapping variable, if the persisted value is lost during the production releases. 

Addition Options to Reset Mapping Variables

There are additional option to reset the mapping variable value, but do not have the flexibility to go to a desired value like we discussed above. Lets see how we do this from workflow manager.

Just like shown in below image, you get the option to view the persisted mapping variable value.
Re-Runnability for ETL Processes Which Uses Mapping Variables
Here you have the option to reset the persisted value like in below image. Once you reset, the values goes back to the initial value or will get the default value based on the variable data type.
Re-Runnability for ETL Processes Which Uses Mapping Variables
Hope you guys enjoyed this. Leave us your comment and feedback, we are happy to help you here.

Share with your friends

Readers Comments