Mapping parameters and variables represent values in mappings and mapplets.
When we use a mapping parameter or variable in a mapping, first we declare the mapping parameter or variable for use in each mapplet or mapping. Then, we define a value for the mapping parameter or variable before we run the session.
- A mapping parameter represents a constant value that we can define before running a session.
- A mapping parameter retains the same value throughout the entire session.
Example: When we want to extract records of a particular month during ETL process, we will create a Mapping Parameter of data type and use it in query to compare it with the timestamp field in SQL override.
- After we create a parameter, it appears in the Expression Editor.
- We can then use the parameter in any expression in the mapplet or mapping.
- We can also use parameters in a source qualifier filter, user-defined join, or extract override, and in the Expression Editor of reusable transformations.
- Unlike mapping parameters, mapping variables are values that can change between sessions.
- The Integration Service saves the latest value of a mapping variable to the repository at the end of each successful session.
- We can override a saved value with the parameter file.
- We can also clear all saved values for the session in the Workflow Manager.
We might use a mapping variable to perform an incremental read of the source. For example, we have a source table containing time stamped transactions and we want to evaluate the transactions on a daily basis. Instead of manually entering a session override to filter source data each time we run the session, we can create a mapping variable, $$IncludeDateTime. In the source qualifier, create a filter to read only rows whose transaction date equals $$IncludeDateTime, such as:
TIMESTAMP = $$IncludeDateTime
In the mapping, use a variable function to set the variable value to increment one day each time the session runs. If we set the initial value of $$IncludeDateTime to 8/1/2018, the first time the Integration Service runs the session, it reads only rows dated 8/1/2018. During the session, the Integration Service sets $$IncludeDateTime to 8/2/2018. It saves 8/2/2018 to the repository at the end of the session. The next time it runs the session, it reads only rows from August 2, 2018.
Used in following transformations:
- Update Strategy
Initial and Default Value:
When we declare a mapping parameter or variable in a mapping or a mapplet, we can enter an initial value. When the Integration Service needs an initial value, and we did not declare an initial value for the parameter or variable, the Integration Service uses a default value based on the data type of the parameter or variable.
Data ->Default Value
String ->Empty String
Date time ->1/1/1
Variable Values: Start value and current value of a mapping variable
The start value is the value of the variable at the start of the session. The Integration Service looks for the start value in the following order:
- Value in parameter file
- Value saved in the repository
- Initial value
- Default value
The current value is the value of the variable as the session progresses. When a session starts, the current value of a variable is the same as the start value. The final current value for a variable is saved to the repository at the end of a successful session. When a session fails to complete, the Integration Service does not update the value of the variable in the repository.
Note: If a variable function is not used to calculate the current value of a mapping variable, the start value of the variable is saved to the repository.
Variable Data type and Aggregation Type When we declare a mapping variable in a mapping, we need to configure the Data type and aggregation type for the variable. The IS uses the aggregate type of a Mapping variable to determine the final current value of the mapping variable.
Aggregation types are:
- Count: Integer and small integer data types are valid only.
- Max: All transformation data types except binary data type are valid.
- Min: All transformation data types except binary data type are valid.
Variable functions determine how the Integration Service calculates the current value of a mapping variable in a pipeline.
SetMaxVariable: Sets the variable to the maximum value of a group of values. It ignores rows marked for update, delete, or reject. Aggregation type set to Max.
SetMinVariable: Sets the variable to the minimum value of a group of values. It ignores rows marked for update, delete, or reject. Aggregation type set to Min.
SetCountVariable: Increments the variable value by one. It adds one to the variable value when a row is marked for insertion, and subtracts one when the row is Marked for deletion. It ignores rows marked for update or reject. Aggregation type set to Count.
SetVariable: Sets the variable to the configured value. At the end of a session, it compares the final current value of the variable to the start value of the variable. Based on the aggregate type of the variable, it saves a final value to the repository.
Creating Mapping Parameters and Variables
- Open the folder where we want to create parameter or variable.
- In the Mapping Designer, click Mappings > Parameters and Variables. -or- In the Mapplet Designer, click Mapplet > Parameters and Variables.
- Click the add button.
- Enter name. Do not remove $$ from name.
- Select Type and Data type. Select Aggregation type for mapping variables.
- Give Initial Value. Click ok.
Example: Use of Mapping of Mapping Parameters and Variables
- EMP will be source table.
- Create a target table MP_MV_EXAMPLE having columns: EMPNO, ENAME, DEPTNO, TOTAL_SAL, MAX_VAR, MIN_VAR, COUNT_VAR and SET_VAR.
- TOTAL_SAL = SAL+ COMM + $$BONUS (Bonus is mapping parameter that changes every month)
- SET_VAR: We will be added one month to the HIREDATE of every employee.
- Create shortcuts as necessary.
- Open folder where we want to create the mapping.
- Click Tools -> Mapping Designer.
- Click Mapping-> Create-> Give name. Ex: m_mp_mv_example
- Drag EMP and target table.
- Transformation -> Create -> Select Expression for list -> Create –> Done.
- Drag EMPNO, ENAME, HIREDATE, SAL, COMM and DEPTNO to Expression.
- Create Parameter $$Bonus and Give initial value as 200.
- Create variable $$var_max of MAX aggregation type and initial value 1500.
- Create variable $$var_min of MIN aggregation type and initial value 1500.
- Create variable $$var_count of COUNT aggregation type and initial value 0. COUNT is visible when datatype is INT or SMALLINT.
- Create variable $$var_set of MAX aggregation type.
12. Create 5 output ports out_ TOTAL_SAL, out_MAX_VAR, out_MIN_VAR,
out_COUNT_VAR and out_SET_VAR.
13. Open expression editor for TOTAL_SAL. Do the same as we did earlier for SAL+ COMM. To add $$BONUS to it, select variable tab and select the parameter from mapping parameter. SAL + COMM + $$Bonus
14. Open Expression editor for out_max_var.
15. Select the variable function SETMAXVARIABLE from left side pane. Select
$$var_max from variable tab and SAL from ports tab as shown below.SETMAXVARIABLE($$var_max,SAL)
17. Open Expression editor for out_min_var and write the following expression:
SETMINVARIABLE($$var_min,SAL). Validate the expression.
18. Open Expression editor for out_count_var and write the following expression:
SETCOUNTVARIABLE($$var_count). Validate the expression.
19. Open Expression editor for out_set_var and write the following expression:
20. Click OK. Expression Transformation below:
21. Link all ports from expression to target and Validate Mapping and Save it.
22. See mapping picture on next page.