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.
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.
- Click here to learn about SQL vs static variables.