Local Variables
Use local variables in Aggregator, Expression, and Rank transformations to improve performance. You can reference variables in an expression or use them to temporarily store data.
You might use variables to complete the following tasks:
- •Temporarily store data.
- •Simplify complex expressions.
- •Store values from prior rows.
- •Capture multiple return values from a stored procedure.
- •Compare values.
- •Store the results of an unconnected Lookup transformation.
Temporarily Store Data and Simplify Complex Expressions
Variables increase performance when you enter multiple related expressions in the same transformation. You can define components as variables instead of parsing and validating the same expression components mulitple times in the transformation.
For example, if an Aggregator transformation uses the same filter condition before calculating sums and averages, you can define this condition as a variable, and then reuse the condition in both aggregate calculations.
You can simplify complex expressions. If an Aggregator includes the same calculation in multiple expressions, you can increase performance by creating a variable to store the results of the calculation.
For example, you might create the following expressions to find both the average salary and the total salary with the same data:
AVG( SALARY, ( ( JOB_STATUS = 'Full-time' ) AND (OFFICE_ID = 1000 ) ) )
SUM( SALARY, ( ( JOB_STATUS = 'Full-time' ) AND (OFFICE_ID = 1000 ) ) )
Instead of entering the same arguments for both calculations, you might create a variable port for each condition in this calculation, and then change the expression to use the variables.
The following table shows how to use variables to simplify complex expressions and temporarily store data:
Port | Value |
|---|
V_CONDITION1 | JOB_STATUS = ‘Full-time’ |
V_CONDITION2 | OFFICE_ID = 1000 |
AVG_SALARY | AVG(SALARY, (V_CONDITION1 AND V_CONDITION2) ) |
SUM_SALARY | SUM(SALARY, (V_CONDITION1 AND V_CONDITION2) ) |
Store Values Across Rows
You can configure variables in transformations to store data from source rows. You can use the variables in transformation expressions.
For example, a source file contains the following rows:
California
California
California
Hawaii
Hawaii
New Mexico
New Mexico
New Mexico
Each row contains a state. You need to count the number of rows and return the row count for each state:
California,3
Hawaii ,2
New Mexico,3
You can configure an Aggregator transformation to group the source rows by state and count the number of rows in each group. Configure a variable in the Aggregator transformation to store the row count. Define another variable to store the state name from the previous row.
The Aggregator transformation has the following ports:
Port | Port Type | Expression | Description |
|---|
State | Pass-through | n/a | The name of a state. The source rows are grouped by the state name. The Aggregator transformation returns one row for each state. |
State_Count | Variable | IIF (PREVIOUS_STATE = STATE, STATE_COUNT +1, 1) | The row count for the current State. When the value of the current State column is the same as the Previous_State column, the Integration Service increments State_Count. Otherwise, it resets the State_Count to 1. |
Previous_State | Variable | State | The value of the State column in the previous row. When the Integration Service processes a row, it moves the State value to Previous_State. |
State_Counter | Output | State_Count | The number of rows the Aggregator transformation processed for a state. The Integration Service returns State_Counter once for each state. |
Capture Values from Stored Procedures
Variables provide a way to capture multiple columns of return values from stored procedures.
Guidelines for Configuring Variable Ports
Consider the following factors when you configure variable ports in a transformation:
- •Port order. The Integration Service evaluates ports by dependency. The order of the ports in a transformation must match the order of evaluation: input ports, variable ports, output ports.
- •Datatype. The datatype you choose reflects the return value of the expression you enter.
- •Variable initialization. The Integration Service sets initial values in variable ports, where you can create counters.
Port Order
The Integration Service evaluates the input ports first. The Integration Service evaluates the variable ports next, and the output ports last.
The Integration Service evaluates ports in the following order:
- 1. Input ports. The Integration Service evaluates all input ports first since they do not depend on any other ports. Therefore, you can create input ports in any order. The Integration Service does not order input ports because input ports do not reference other ports.
- 2. Variable ports. Variable ports can reference input ports and variable ports, but not output ports. Because variable ports can reference input ports, the Integration Service evaluates variable ports after input ports. Variables can reference other variables, so the display order for variable ports is the same as the order in which the Integration Service evaluates each variable.
For example, if you calculate the original value of a building and then adjust for depreciation, you might create the original value calculation as a variable port. This variable port needs to appear before the port that adjusts for depreciation.
- 3. Output ports. The Integration Service evaluates output ports last, because output ports can reference input ports and variable ports. The display order for output ports does not matter beause output ports cannot reference other output ports. Be sure output ports display at the bottom of the list of ports.
Data Type
When you configure a port as a variable, you can enter any expression or condition in it. The data type you choose for this port reflects the return value of the expression you enter. If you specify a condition through the variable port, any numeric data type returns the values for TRUE (non-zero) and FALSE (zero).
Variable Initialization
The Integration Service does not set the initial value for variables to NULL.
The Integration Service uses the following guidelines to set initial values for variables:
- •Zero for numeric ports
- •Empty strings for string ports
- •01/01/0001 for Date/Time ports
Therefore, use variables as counters, which need an initial value. For example, you can create a numeric variable with the following expression:
VAR1 + 1
This expression counts the number of rows in the VAR1 port. If the initial value of the variable were set to NULL, the expression would always evaluate to NULL. This is why the initial value is set to zero.