Integrator Variables

Job execution can be parametrized with variables. For example, you may want to import data for particular years independent of each other, such as all data for the year 2023 and then all monthly data for the year 2023. Without the variable concept, you would have to create extracts, transforms, loads, and jobs separately for each year, or edit these manually each time. With the variable concept, however, only an extract that supports the year as a variable is necessary. To implement the Jedox Integrator process for a particular year, the variable for the job is changed, or a separate job is defined.

Variables can be used anywhere during the modeling, such as in the SQL statement of a relational extract or in the filter of a cube extract.

Defining variables

To create a new variable, look in the Navigation pane under the project name, right-click on Variables, and select New (see screenshot below). You will be prompted to give the variable a name. Click OK.

The variable is defined for the project globally with a name and a default value. The variable will be replaced with its current value during the execution of the job.

Variables are initialized as defined by their origin (DefaultValue, Setting, or Groovy) or the Default value, described below.

Selecting the Password checkbox encrypts the Default Value, which can be useful when the value is a password or other sensitive value.

Note: variables that have "Password" set in their definition can only be used in encrypted input fields, e.g. the password fields in various connections, and therefore cannot be set in a Groovy Job or function.

DefaultValue origin

If the origin is set to DefaultValue, then the variable is always initialized with the default value set in the field above.

Settings origin

Variables can be initialized by a value from the Jedox Web settings.

The Setting field specifies the folders and key of the setting, separated with a dot, e.g. tasks.smtp.user.

The settings are organized into scopes. A setting could be in the Global scope, or it could be in a particular Model. The Scope field specifies the location of the setting. It could be empty, Global, or the fully qualified name of a Model (e.g. com.jedox.model.profitandloss). The table below shows the possible combinations.

The Default Value field can be left blank, or it can be populated with a fallback value that will be used if the Setting is not found.

When entering the FQN of a model, the namespace is automatically assigned. If you would like to manually enter this information, you can include it as a prefix (e.g. MyNamespace_com.jedox.model.profitandloss).

Note: variables that do not have "password" set in their definition cannot be used in encrypted input fields, and must have as origin only non-encrypted settings.

Groovy origin

This option uses a Groovy script to initialize the variable. This option could be used to retrieve the current date, for example.

Selecting Groovy opens a script editor, as shown below:

The Default Value field can be left blank, or it can be populated with a fallback value that will be used if the script returns no result.

Groovy Origin variables can reference values from a Global Connection, such as Jedox Cube or relational connection. This is useful if you need to retrieve stored connection variables, such as tokens, when you connect via REST.

Note: you cannot pass an Integrator variable to a Groovy scripted variable.

Example:

// This groovy call returns the values from the config cube
// Change the following variables. These cannot be dynamically taken from other variables

Conn = "localhost_static";
DB = "Biker";
Cub = "P_L";
String [] path = ["Variance", "All Years", "Year", "501 Omega Group", "Gross Profit"];

IConnection con = OLAP.getGlobalConnection(Conn);
IDatabase db = con.getDatabaseByName(DB);
ICube cube = db.getCubeByName(Cub);
IElement [] elPath = cube.getCellPath(path);
ICell cell = cube.getCell(elPath);
Object value = cell.getValue();

return value;

You can test the call by running the following Groovy job:

To test, you can run the following Groovy job (replace 'test' with the name of your variable)

String g = API.getProperty('test');

LOG.info("Server Variable: " + g);

Show value button

You can inspect the current value of a variable by clicking Show value in the toolbar, as shown below. This option is useful for variables sourced from the Settings or a Groovy script.

Changing variables in Integrator processes

Variables can be changed in a variety of ways according to an order of precedence, as outlined below:

1. Individual values that are set in Jedox Integrator Script Job or Function (type Groovy or JavaScript) with API.setProperty method, or via loop sources in TableLoop Transform and Loop Job. For details, see Integrator Scripting API.
2. External calls of Jedox Integrator, e.g.:
   • from the Integrator UI using "Run with options".
   • from Jedox Web Actions.
   • from the OData Hub: visit the current Jedox OData REST API Documentation and navigate to Jobs -> RunJobWithVariables.
   • from Jedox Web Wrapper functions for accessing Jedox Integrator.
   
3. Fixed values of variables set in a job definition of type Standard, Parallel or External. The variable values apply for all sub-jobs and loads.
4. Initial values of Settings or Groovy origin in the variable definition of the Jedox Integrator project.
5. Default values in the variable definition of the Jedox Integrator project.

An example of how variables are used in the Jedox Integrator process can be found in the sample Jedox Integrator projects "sampleVariables" and "sampleLoopJob".

Variable tracking

The Flowgraph displays where a specific variable is used in a project:

After selecting the variable “_SourceRef”, the flow graph displays all modules that use this variable.

Limitations

Using variable names in component references, e.g. the connection in an extract or the data source in a load, should be avoided. Though the real component name is resolved during the execution runtime according to the current variable value, there are some inconveniences: the displayed flow graph and the dependent components only use the variable's default value. In ComboBoxes, source columns will not work correctly if the referenced source is specified with a variable.

Updated March 28, 2024