Mapping Parameters and variables:

In this post I would like to share some useful information regarding mapping parameters and variables.

Mapping Parameters and variables enables us to make our code reusable.

first declare and assign value before starting the session

Use mapping parameters and variables in a mapping to incrementally extract data.

Before going into details let us recall what are Parameters and Variables.

Parameters:

1.represents a constant value that you can define before running a session. A mapping parameter retains the same value throughout the entire session.

2.When you use a mapping parameter, you declare and use the parameter in a mapping or mapplet. Then define the value of the parameter in a parameter file. The Integration Service evaluates all references to the parameter to that value.

Variables:

1.Unlike a mapping parameter, a mapping variable represents a value that can change through the session. The Integration Service saves the value of a mapping variable to the repository at the end of each successful session run and uses that value the next time you run the session.

2.To override the saved value, define the start value of the variable in a parameter file or assign a value in the pre-session variable assignment in the session properties.

Using Mapping Parameters and Variables:

1.You can create mapping parameters in Mapping or Mapplet Designer. Once created appears in the variables part of the expression editor.

2.When you use mapping parameters and variables in a Source Qualifier transformation, the Designer expands them before passing the query to the source database for validation.

3.When you create a reusable transformation in the Transformation Developer, use any mapping parameter or variable.

4.If the parameter or variable is not defined in the mapplet or mapping, or if it is used incorrectly in the reusable transformation, the Designer logs an error when you validate the mapplet or mapping.

5.When the Designer validates a mapping variable in a reusable transformation, it treats the variable as an Integer datatype.

6.You cannot use mapping parameters and variables interchangeably between a mapplet and a mapping.

7.When you enter mapping parameters and variables of a string data type in a Source Qualifier transformation, use a string identifier appropriate for the source database

8.When you enter string parameters or variables using the PowerCenter transformation language, do not use additional quotes.

Source Qualifier transformation to extract rows for a particular state

STATE = ‘$$State’

Filter transformation to filter rows for a particular state:

STATE = $$State

Using Date-time Parameters and Variables

1.When you use a date-time parameter or variable in the Source Qualifier transformation, you might need to change the date format to the format used in the source.

2.You can also use mapping parameters in transformation overrides in the session properties in the Workflow Manager. You can override properties such as a filter or user-defined join in a Source Qualifier transformation.

Mapping Variables Values:

The Integration Service holds two different values for a mapping variable during a session run:

• Start value of a mapping variable
• Current value of a mapping variable

To use the current value of a mapping variable within the mapping or in another transformation, create the following expression with the SETVARIABLE function:

SETVARIABLE($$MAPVAR,NULL)

Start Value:

The Integration Service looks for the start value in the following order:

1.Value in parameter file 
2.Value in pre-session variable assignment

3.Value saved in the repository

4.Initial value 

5.Data type default value

Current Value:

1.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

2.Unlike the start value of a mapping variable,the current value can change as the Integration Service evaluates the current value of a variable as each row passes through the mapping.

Variable Functions:

SetMaxVariable. Sets the variable to the maximum value . It ignores rows marked for

update, delete, or reject.

SetMinVariable. Sets the variable to the minimum value . It ignores rows marked for

update, delete, or reject.

SetCountVariable. Increments the variable value by one. In other words, 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.

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. To use the SetVariable function with a mapping variable, the aggregation type of the mapping variable must be set to Max or Min. The SetVariable function ignores rows marked for delete or reject.

Note:

1.Use variable functions only once for each mapping variable in a pipeline.

2.When you declare a mapping variable for a mapplet and use the mapplet multiple times within the same mapping, the same mapping variable value is shared across all mapplet instances.

Tips:

1.Mapping parameter and variable values in mapplets must be preceded by the mapplet name in the parameter file, as follows:

mappletname.parameter=value

mappletname.variable=value

2.You cannot use variable functions in the Rank or Aggregator transformation. Use a different transformation for variable functions.

3.To use the current value of a mapping variable within the mapping or in another transformation, create the following expression with the SETVARIABLE function:

SETVARIABLE($$MAPVAR,NULL).

Hope you enjoyed it.

Please leave your comments.