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
- Filter node
Configure a Variable
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.
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
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.
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
To inject the variable into the script, add @ and the variable name. For example:
SELECT * FROM [products p] where [p.ProductKey] > @MaxProductKey
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.