Dynamically Changing ETL Calculations Using Informatica Mapping Variable

Johnson Cyriac Oct 16, 2013
|

Informatica SQL Transformation
Quite often we deal with ETL logic, which is very dynamic in nature. Such as a discount calculation which changes every month or a special weekend only logic. There is a lot of practical difficulty in making such frequent ETL change into production environment. Best option to deal with this dynamic scenario is parametrization. In this article let discuss how we can make the ETL calculations dynamic.

Business Use Case

Lets start our discuss with the help of a real life use case.

The sales department wants to build a monthly sales fact table. The fact table need to be refreshed after the month end closure. Sales commission is one of the fact table data element, its calculation is dynamic in nature. It is a factor of sales or sales revenue or net sales.

Sales Commission calculation can be :
      1. Sales Commission = Sales * 18 / 100
      2. Sales Commission = Sales Revenue * 20 /  100  
      3. Sales Commission = Net Sales * 20 / 100
Note : The expression calculation can be as complex as the business requirement demands.

The calculation need to be used by the month end ETL will be decided by the Sales Manager before the month ETL load.

Mapping Configuration

Now we understand the use case, lets build the mapping logic.  

Here we will be building the dynamic sales commission calculation logic with the help of a mapping variable. The changing expression for the calculation will be passed into the mapping using a session parameter file.

Step 1 : As the first step, Create a mapping variable $$EXP_SALES_COMM and set the isExpVar property TRUE as shown in below image.
Dynamically Changing Calculations Using Informatica Mapping Parameter Note : Precision for the mapping variable should be big enough to hold the whole expression.

Step 2 : In an expression transformation, create an output port and provide the mapping variable as the expression.  Below shown is the screenshot of expression transformation.
Dynamically Changing Calculations Using Informatica Mapping Parameter Note : All the ports used in the expression $$EXP_SALES_COMM should be available as an input or input/output port in the expression transformation.

Workflow Configuration

In the workflow configuration, we will create the parameter file with the expression for Sales Commission and set up in the session.

Step 1 : Create the session parameter file with the expression for Sales Commission calculation with the below details.
[s_m_LOAD_SALES_FACT]
$$EXP_SALES_COMM=SALES_REVENUE*20/100
Step 2 : Set the parameter in the session properties as shown below.
Dynamically Changing Calculations Using Informatica Mapping Parameter With that we are done with  the configuration. You can update the expression in the parameter file when ever a change is required in the sales commission calculation.  This clearly eliminate the need of a ETL code change.

Hope you enjoyed this article, please leave us a comment or feedback if you have any, we are happy to hear from you.




About US Contact US Advertise Guest Post Terms and Conditions Privacy Policy Disclaimer

© 2012-2013 Data Intelligence Solution, All Rights Reserved
The contents in this site is copyrighted to Data intelligence Solution and may not be reproduced on other websites.