Dynamically Changing ETL Calculations Using Informatica Mapping Variable
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.
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 :
The calculation need to be used by the month end ETL will be decided by the Sales Manager before the month ETL load.
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.
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.
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.
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 :
- Sales Commission = Sales * 18 / 100
- Sales Commission = Sales Revenue * 20 / 100
- Sales Commission = Net Sales * 20 / 100
Note : The expression calculation can be as complex as the business requirement demands.
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.
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.
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.
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.