Job execution can be parameterized with variables. For example, you may want to import data for particular years independent of each other, such as all data for the year 2018 and then all monthly data for the year 2018. 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.
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.
Now you can enter a description and a default value for the new variable.
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.
Selecting the Password checkbox encrypts the Default Value, which can be useful when the value is a password or other sensitive value.
If the origin is set to DefaultValue, then the variable is always initialized with the Default Value.
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.
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.
|Setting is Global||Setting is in the current Model||Setting is in another Model|
|Integrator project is Global||Scope = “”||N/A||Scope = “com.jedox.model.profitandloss” (example)|
|Integrator project is in a Model||Scope = “Global”||Scope = “”||Scope = “com.jedox.model.profitandloss” (example)|
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.
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.
Starting with 2019.1, 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.
// 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();
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
As of Jedox 2019.2, 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
You can also set the value of a variable for a Jedox Integrator job that has been started via a web service call or a batch file. The command line would then look like this:
Variables can be changed in a variety of ways according to an order of precedence, as outlined below:
- Variables set in the surrounding job of a sub-job.
- SOAP interface of the Jedox Integrator with
• from Jedox Web via PHP script in the Macro Engine.
• Context variables in the command line client with option
- Fixed values of variables set in a External Job definition.
- Fixed values of variables set in a Standard Job definition.
- Initial values of Settings or Groovy origin in the variable definition of the Jedox Integrator project.
- Default values in the variable definition of the Jedox Integrator project.
The Flowgraph displays where a specific variable is used in a project:
Note: the use of variables to reference other components has some limitations:
- The default values of the variable shave to be set so that the project is valid, i.e., they must reference existing components.
- The flow graph is shown based on default variable values only. Other dependencies during runtime are not visualized.
- Value help on columns for the Target input field is not fully functional for variable values.