When you use a mapping parameter or variable in a mapping, first you declare the mapping parameter or variable for use in each mapplet or mapping. Then, you define a value for the mapping parameter or variable before you run the session.
You can create mapping parameters and variables in the Mapping Designer or Mapplet Designer.
A parameter is constant whereas a variable can change during the data flow.
The Integration Service looks for the value in the following order:
- Value in parameter file
- Value in pre-session variable assignment
- Initial value saved in the repository (only for a variable)
- Initial value in the mapping or mapplet
- PowerCenter defaut value
|DateTime||1/1/1753 A.D. or 1/1/1 when the Integration Service is configured for compatibility with 4.0|
Incremental extraction of data
Use mapping parameters and variables in a mapping to incrementally extract data. Use mapping parameters or variables in the source filter of a Source Qualifier transformation to determine the beginning timestamp and end timestamp for incrementally extracting data.
For example, you can create a user-defined mapping variable $LastUpdateDateTime that saves the timestamp of the last row the Integration Service read in the previous session. Use $LastUpdateDateTime for the beginning timestamp and the built-in variable $$SessStartTime for the end timestamp in the source filter. Use the following filter to incrementally extract data based on the SALES.sales_datetime column in the source:
SALES.sales_datetime > TO_DATE (‘$$LastUpdateDateTime’) AND SALES.sales_datetime < TO_DATE (‘$$$SessStartTime’)
Data Extraction of one customer
For example, you want to use the same session to extract transaction records for each of the customers individually. Instead of creating a separate mapping for each customer account, you can create a mapping parameter to represent a single customer account. Then use the parameter in a source filter to extract only data for that customer account. Before running the session, you enter the value of the parameter in the parameter file.
To reuse the same mapping to extract records for other customer accounts, you can enter a new value for the parameter in the parameter file and run the session. Or, you can create a parameter file for each customer account and start the session with a different parameter file each time using pmcmd.
Monthly gross earnings
You might use a mapping parameter instead of a database lookup. For example, you want to perform calculations using monthly gross earnings. Instead of using a Lookup transformation to connect to a database table for that information, you can create a gross earnings mapping parameter and update its value in the parameter file each month to reflect current earnings.
Different databases, same table
You might also use a mapping parameter in conjunction with a session parameter to reuse a mapping and session. For example, you have transactional data from different states stored in the same table in different databases, and you want to perform the same calculations on all data, while changing the state sales tax accordingly. Instead of creating a separate mapping and session for each state, you can create one mapping with a sales tax mapping parameter and a session using a source database connection session parameter.
Mapping parameter between sessions
When you want to change the value of a mapping parameter between sessions you can perform one of the following tasks:
- Update/Edit the parameter file between sessions.
- Create a different parameter file and configure the session to use the new file.
- Remove the parameter file from the session properties. The Integration Service uses the parameter value in the pre-session variable assignment. If there is no pre-session variable assignment, the Integration Service uses the configured initial value of the parameter in the mapping.
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.
When you use a mapping variable, you declare the variable in the mapping or mapplet, and then use a variable function in the mapping to set the value of the variable.
To change the value of a variable from session to session, use a variable function.
At the beginning of a session, the Integration Service evaluates references to a variable to determine the start value.
At the end of a successful session, the Integration Service saves the final value of the variable to the repository. The next time you run the session, the Integration Service evaluates references to the variable to the saved value.
The Integration Service does not save the final value of a mapping variable to the repository when any of the following conditions are true:
- The session fails to complete.
- The session is configured for a test load.
- The session is a debug session.
- The session runs in debug mode and is configured to discard session output.
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.
- clear all saved values for the session in the Workflow Manager.
The Integration Service holds two different values for a mapping variable during a session run:
- Start value (the value of the variable at the start of the session)
- Current value (the value of the variable as the session progresses)
The Integration Service uses the aggregate type of a mapping variable to determine the final current value of the mapping variable. When you have a pipeline with multiple partitions, the Integration Service combines the variable value from each partition and saves the final current variable value into the repository.
You can create a variable with the following aggregation types:
- Count. If you want to use the mapping variable to count number of rows read from source and when it is an Integer or Small Integer data type.
- Max. If you want to use the mapping variable to determine a maximum value from a group of values.
- Min. If you want to use the mapping variable to determine a minimum value from a group of values.
To keep the variable value consistent throughout the session run, the Designer limits the variable functions you use with a variable based on aggregation type.
For example, use the SetMaxVariable function for a variable with a Max aggregation type, but not with a variable with a Min aggregation type.
The following table describes the available variable functions and the aggregation types and datatypes you use with each function:
|Variable Function||Valid Aggregation Types||Valid Datatype|
|SetVariable||Max or Min||All transformation datatypes except binary datatype.|
|SetMaxVariable||Max only||All transformation datatypes except binary datatype.|
|SetMinVariable||Min only||All transformation datatypes except binary datatype.|
|SetCountVariable||Count only||Integer and small integer datatypes only.|
When you use a mapping variable, you have to determine how to set the value of the mapping variable. Use a variable function to set a variable value.
Variable functions determine how the Integration Service calculates the current value of a mapping variable in a pipeline. Use variable functions in an expression to set the value of a mapping variable for the next session run.
The transformation language provides the following variable functions to use in a mapping:
|SetMaxVariable||Sets the variable to the maximum value of a group of values. It ignores rows marked for update, delete, or reject. To use the SetMaxVariable with a mapping variable, the aggregation type of the mapping variable must be 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. To use the SetMinVariable with a mapping variable, the aggregation type of the mapping variable must be set to Min.|
|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. To use the SetCountVariable with a mapping variable, the aggregation type of the mapping variable must be 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. 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|
Use variable functions only once for each mapping variable in a pipeline. The order in which the Integration Service encounters variable functions and processes them in the mapping may not be the same for every session run. This may cause inconsistent results when you use the same variable function multiple times in a mapping.
You can use a variable function in any of the following transformations:
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. This allows the source database to validate the query.
When using mapping variables in a Source Qualifier transformation follow these rules:
- Enclose string variables in string identifiers, such as single quotation marks, to indicate the variable is a string.
- When necessary, change the format of the datetime variable to match the format in the source. The Integration Service converts dates from the PowerCenter default date format to the default date format of the source system.
In any others transformations
In other transformations in a mapplet or mapping, mapping parameter or variables appear in the Expression Editor. When you write expressions that use mapping variables, you do not need string identifiers for string variables.
When you create a reusable transformation in the Transformation Developer, use any mapping parameter or variable. Since a reusable transformation is not contained within any mapplet or mapping, the Designer validates the usage of any mapping parameter or variable in the expressions of reusable transformation. When you use the reusable transformation in a mapplet or mapping, the Designer validates the expression again. 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.
When the Designer validates a mapping variable in a reusable transformation, it treats the variable as an Integer datatype.
When you enter string parameters or variables using the PowerCenter transformation language, do not use additional quotes. The Integration Service recognizes mapping parameter and variable naming syntax in the PowerCenter transformation language.
For example, you might use a parameter named $State in the filter for a Source Qualifier transformation to extract rows for a particular state:
STATE = ‘$$State’
During the session, the Integration Service replaces the parameter with a string. If $State is defined as MD in the parameter file, the Integration Service replaces the parameter as follows:
STATE = ‘MD’
You can perform a similar filter in the Filter transformation using the PowerCenter transformation language as follows:
STATE = $$State
If you enclose the parameter in single quotes in the Filter transformation, the Integration Service reads it as the string literal “$State” instead of replacing the parameter with “MD”.
System Session Variable (Built-in)
See the session log to determine what start value the Integration Service uses for each variable.
DIRECTOR> VAR_27028 Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP]. DIRECTOR> VAR_27028 Use override value  for mapping parameter:[mplt_SIL_InsertRowInRunTable.$$DATASOURCE_NUM_ID]. DIRECTOR> VAR_27028 Use override value  for mapping parameter:[MPLT_GET_ETL_PROC_WID.$$ETL_PROC_WID].
Values in parameter file not used
In the parameter file, I configured values for parameters in a mapplet, but they are not being used in the session. Mapping parameter and variable values in mapplets must be preceded by the mapplet name in the parameter file, as follows: