Variables are used to store static or dynamic values that can then be passed into tables for incremental loading, and injected into scripts, filters, and calculated columns in the Data Flow. They can also be passed into containers, processes, and dynamic expressions in the Master Flow.
The Variables panel is where you can create, edit, and delete variables.
Note: this feature is available only with an Enterprise edition license.
- Click here to learn more about Variables.
Access the Variables Panel
To create, edit, or delete a variable, click the Variables button in the ribbon to open the Variables panel:
The Variables panel lists all variables that have been added to the data flow. Pyramid automatically adds the 'IsPreview' variable; a simple script can be added to an SQL node to prevent the Preview panel from loading a preview of the data.
- Click here to learn how to prevent the Preview from loading.
Create a Variable
To create a new variable, click the blue plus sign at the bottom of the list (blue arrow below). This will open the Variable Settings dialog where you can configure the new variable (see below).
Edit a Variable
Click the relevant variable listing to open its Variable Settings dialog. Edit the settings as required and click Apply to confirm your changes.
Delete a Variable
Find the relevant variable in the list; click its red delete button.
Variable Settings
Configure a new variable or edit an existing one from the Variable Settings dialog.
- Initialize From: choose whether to initialize from a saved value or a database query.
- Saved Value: this is a static value that exists in the column to be used for incremental loading.
- Database Query: the variable value will be dynamic, based on a given SQL script and resolved at runtime.
- Name: name the variable.
- Data Type: select the relevant data type; this should correspond to the data type of the 'Current Value'. For instance, if the current value is a date, the data type should be set to Date (as in the image above). If the current value contains a string, the Data Type should be set to 'String' (see image below).
- List: enable this option to initialize from and preview a list of values. Enter each value on a separate line (blue highlight below).
- Current Value: the value from which to start updating rows in the table. If the ETL has not yet been run with the variable, this value should be 0 so that a full load will run. Once the ETL has been run, this value will automatically be updated to reflect the value from the last execution.
- Preview Value: this allows you to preview a specified amount of rows. It can be left at 0 if you don't want to see a preview.
Data Types
Select one of the following data types:
- Integer: whole numbers between -2^31 (-2,147,483,648) and 2^31 (2,147,483,647). This data type is a 32-bit representation of an integer and takes up to 4 bytes of storage.
- BigInteger: whole numbers between -2^63 (-9,223,372,036,854,775,808) and 2^63 (9,223,372,036,854,775,807). A BigInteger is the 64-bit representation of an integer and takes up to 8 bytes of storage.
- Double: numbers with a decimal point.
- String: use for text values.
- Date: date values that include the date only (not time).
- DateTime: values that include both date and time.
List Variables
Variables can be used to store a single value, or a list of values. To store a list of values in the variable, enable 'List' and enter the list values in the Preview Value field. List variables are especially useful in the Master Flow, where they are used to create For Each Loops.
