Variables

The variables tool enables incremental loading, so that only new rows in the data source will be appended when scheduling a data model.

Note: this feature is available only with an Enterprise edition license.

A variable can be configured on the following nodes:

• Table node
• Calculated Column
• SQL Query
• Python
• R
• Filter node

Configure a Variable

Step 1

To add a variable, click the Variables button in the ribbon (either before or after adding nodes to the source node). From the Variables panel, click the variable to open the following pop-up:

Name: name the variable

Data Type: select the relevant data type

Initialize From: choose whether to initialize from a saved (constant) value or a database query

Current Value: the row number 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. Once the ETL has been run, this value will automatically be updated to reflect the value (number of rows) from the last execution.

Preview Value: this allows you to preview a specified amount of columns. It can be left at 0.

Step 2

Click the relevant node, and from the Properties panel, open the Set Variable Values window. To add the variable, click the plus sign to open the pop-up window below:

Variable: select the required variable

Aggregation: select the aggregation type for the given variable

Columns: select the column ID that the variable will be applied to

Step 3

To set the variable values, connect your target node to the ETL. From the target's Properties panel, open the Target Tables window. Find and select the table containing the variable. Change the Writing Type to Append.

Step 4

After the data flow and/ or data model has been run, you can access the ETL's Progress panel from the job spooler, and see the status of the variables:

Variable Injection into Scripts

You can inject a variable into scripts when working with the following nodes:

• SQL Query
• Python
• R

To inject the variable into the script, add @ and the variable name. For example:

SELECT * FROM [products p] where [p.ProductKey] > @MaxProductKey

Preview Variables

By default, Pyramid automatically creates a variable called IsPreview (pictured below) with a preview value of 1. This variable can be injected into a script to change the preview behavior.

Using the following expression, you can stop the preview from loading. This is useful if you don't want to see a preview for every change you make, which can be time consuming.

select * from data where @IsPreview = 0

By using the above code, the IsPreview variable is injected into the script; because the actual value of the variable is 1, not 0, no preview is returned, as pictured below.

Variable Injection into Calculated Columns

To inject the variable into the calculated column, either:

• Write your script in the Calculated Column script window. Insert the variable into your script, with the syntax @variablename. For example, @MaxProductKey.
• Write your script in the PQL Editor. Click Variables in the Columns panel, and select the required variable from the Elements panel to add it to the script.

Variable Injection into Filter Nodes

To inject the variable into a filter node, under Filter Value in the filter node's Properties panels, select Variable. Then from the next drop down, select the required variable.