Table of Contents

Search

  1. Preface
  2. Using the Designer
  3. Working with Sources
  4. Working with Flat Files
  5. Working with Targets
  6. Mappings
  7. Mapplets
  8. Mapping Parameters and Variables
  9. Working with User-Defined Functions
  10. Using the Debugger
  11. Viewing Data Lineage
  12. Comparing Objects
  13. Managing Business Components
  14. Creating Cubes and Dimensions
  15. Using the Mapping Wizards
  16. Datatype Reference
  17. Configure the Web Browser

Variable Datatype and Aggregation Type

Variable Datatype and Aggregation Type

When you declare a mapping variable in a mapping, you need to configure the datatype and aggregation type for the variable.
The datatype you choose for a mapping variable allows the Integration Service to pick an appropriate default value for the mapping variable. The default is used as the start value of a mapping variable when there is no value defined for a variable in the parameter file, in the repository, and there is no user defined initial value.
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
  • Max
  • Min
You can configure a mapping variable for a Count aggregation type when it is an Integer or Small Integer. You can configure mapping variables of any datatype for Max or Min aggregation types.
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.
For multiple target load order groups, the mapping variable value in a target load order group depends on the variable aggregation type and the variable value in the previous target load order group. After every target load order group runs, the Integration Service calculates the mapping variable values to use in the next target load order group based on the variable aggregation type.
For example, a session contains two target load order groups. You have set Max as the aggregation type of the mapping variable.
When the first target load order group runs, you have set the following different values using the SetVariable function for the mapping variable, $$MAPVAR:
  1. SetVariable($$MAPVAR,20)
  2. SetVariable($$MAPVAR,10)
  3. SetVariable($$MAPVAR,60)
  4. SetVariable($$MAPVAR,30)
At the end of the first target load order group run, the Integration Service calculates the Max of all the four values of $$MAPVAR. As the Max of the four values is 60, so the Integration Service uses 60 as the initial value of the mapping variable, $$MAPVAR, in the next target load order group.


Updated July 02, 2020