PowerCenter - Mapping (Parameter|Variable)

Card Puncher Data Processing

About

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.

Determintation

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

Powercenter Pre Session Variable Assignment

  • Initial value saved in the repository (only for a variable)

Powercenter Persistent Values

  • Initial value in the mapping or mapplet
  • PowerCenter defaut value
Data Type Value
String Empty String
Number 0
DateTime 1/1/1753 A.D. or 1/1/1 when the Integration Service is configured for compatibility with 4.0

Example

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.

Type of variable and scope

You cannot use mapping parameters and variables interchangeably between a mapplet and a mapping. Mapping parameters and variables declared for a mapping cannot be used within a mapplet. Similarly, you cannot use a mapping parameter or variable declared for a mapplet in a mapping.

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.

Mapping Parameter Variable

Parameter

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

The Integration Service handles parameters just like any other port identifiers.

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.

To reuse the same mapping with other parameters, you can enter a new value for the parameter in the parameter file and run the session. Or, you can create another parameter file and start the session with it using pmcmd.

When you want to use the same value for a mapping parameter each time you run the session, use the same parameter file for each session run.

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.

Variable

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:

Powercenter Persistent Values

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.

Powercenter 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)

Creation

Click :

  • In the Mapping Designer, Mappings > Parameters and Variables.
  • In the Mapplet Designer, Mapplet > Parameters and Variables.
Field Description Note
Name Parameter name. Name parameters $ParameterName. The syntax for the parameter name must be $$ followed by any alphanumeric or underscore characters.
Type Variable or parameter
Datatype Datatype Use any datatype except Binary or Raw
Precision or Scale Precision and scale
Aggregation Aggregation type of a variable (not used for a parameter) Determines the type of calculation you can perform with the variable. See aggregation
IsExprVar Determines how the Integration Service expands the parameter in an expression string. If true, the Integration Service expands the parameter before parsing the expression. If false, the Integration Service expands the parameter after parsing the expression. Default is false. If you set this field to true, you must set the parameter datatype to String, or the Integration Service fails the session.
Initial Value Initial value Use any of the following formats for initial values for Date/Time parameters:
- MM/DD/RR
- MM/DD/RR HH24:MI:SS
- MM/DD/YYYY
- MM/DD/YYYY HH24:MI:SS.US
Description Description associated

Aggregation

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.

Variable Functions

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:

Fonctions Description
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:

Utilization (PowerCenter transformation language)

Once created in a mapping or mapplet, mapping parameters and variables can be use:

  • in a Source Qualifier transformation (they appear on the Variables tab in the SQL Editor)
  • in any other transformation (they appear on the Variables tab of the Expression Editor)
  • or in a reusable transformation.

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

Transformation

Source qualifier

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.

Reusable transformation

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.

Data Type

String

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

DateTime

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

System Session Variable (Built-in)

Powercenter Built In Variable

Support

Session Log

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 [16] for mapping parameter:[mplt_SIL_InsertRowInRunTable.$$DATASOURCE_NUM_ID].
DIRECTOR> VAR_27028 Use override value [21484569] 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:

mappletname.parameter=value
mappletname.variable=value

Variable functions not found in the Rank or Aggregator transformation.

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





Discover More
Card Puncher Data Processing
PowerCenter - Is the variable an expression ? (IsExprVar)

IsExprVar is a propertie thay you must set when you define a mapping parameter or variable. It determines how the Integration Service expands (replaces) them in an expression string when you run a session....
Powercenter Parameter File
PowerCenter - Parameter File (prm)

A parameter file is a way to hold the initial value of Mapping parameter or variable. You can set its location into the session attributes. # or ; for comments Mapping parameter and variable...



Share this page:
Follow us:
Task Runner