Database Scripts in Jedox

Database scripts in Jedox offer a lightweight yet powerful way to create and modify database contents in a simple, scripted approach. Scripts are comprised of commands that are executed against a specific OLAP database. They can be parametrized through use of variables. Support for script execution is built in Jedox Web. For example, scripts can be created for databases or database objects, representing the object's content (similar to a “dump” in relational databases). Furthermore, scripts can be executed from the Designer, Modeler, and Integrator components. During the deployment of Jedox Models, scripts are used to generate and modify database contents.

Specific documentation for OLAP database script commands is available here: Open in Browser / Download as zip-archive.

Generating scripts

From Designer

Go to the Designer start page and click New → New script to manually create a script from scratch. In this initial state, the script is just an empty text file.

From Modeler

To generate a script that represents the content of an existing database, go to the Modeler start page and click on the dots icon. Select "Create database script".

To generate a script that represents a database object, such as a dimension or cube, expand the navigation tree in Modeler and right-click on the object.

Depending on the chosen object (database, cube, or dimension), you will be asked which content type the script should contain.

You will also be asked for an output location. You can either download the created script file to the local file system or save it in Designer. Script files have the file name extension jds (Jedox Database Script) and are represented by an icon in the Designer start page.

Notes:

• The OLAP database script engine has been extended to support Lists.
• There is a default limit on script's file size for editing. Editing a script file larger than the set limit size will result in an error.

From Integrator

In Jedox Integrator you can create a new database script by using the JDS load type "create", which allows you to effectively define the models' structure by choosing which rules and objects to include.

Executing scripts

To execute a script in Designer, right-click the file and select “Run script”. Note that when executed in Designer, scripts are always executed against the internal OLAP server of that Jedox Web instance.

To execute a script in Modeler, right-click the database or database element you want to run the script and select "Run script". If the script has any declared variables, a pop-up window will appear, requesting you to enter the input values for the variables before it is executed. After clicking OK in the input dialog, the script will be executed. If there are no declared variables, the script will be executed immediately. Note that changes made by running the script cannot be undone once the execution is finished.

To execute an existing script in Integrator, use the JDS load type "run".

Script commands are executed in the context of the user executing the script, and the user’s access rights are checked during script execution. If the user has insufficient rights to execute a specific command in a script, that command will fail with an error, and any other changes made by previous commands in that script will be reverted. Commands from a script will not trigger the Supervision Server workers (e.g. a cube worker). However, cell value changes made by a script will be logged in the audit log of a cube if auditing is enabled for that cube.

Scripts are not optimized for large data loads compared to the Jedox Integrator. For small data sets, the performance of scripts is sufficient, although slower than the Integrator. Therefore, it is recommended to use the Integrator for the load of large data sets. For example, loading about 1 million cube values on a Windows server with normal hardware resources takes about 20 seconds. Loading the same set of values into the same cube by script takes about 80 seconds.

Generation of database scripts is supported by the Java API for OLAP Server and thus can also be used from Groovy Jobs. For information on generating and executing database scripts with a Groovy job, see Executing Scripts in Groovy Jobs.

Script content

Scripts consist of one or more commands. Each command consists of the command name and a list of parameters for the command. The list of command parameters is enclosed by square brackets. Command parameters are separated by semicolons. By default, commands are written in uppercase, and both commands and clauses are not case-sensitive.

Comments can be included in a script by adding a hash character as the first character in a line. Empty lines can be included in scripts and will be ignored during script execution. Line breaks are supported (e.g. as part of the value of an OLAP string cell). Strings are quoted with double quotation marks (e.g. an element name). If a string itself contains a double quotation mark, a further double quotation mark must be added.

Every database script must begin with a specific command called VERSION. This command tells the OLAP Server that a script is being executed and which version of the script syntax is used. In generated scripts, you will find a first line like this:

VERSION(1;SCRIPT;7803)

This means that the used syntax version is “1”. The second and third parameters are optional and for internal use only (they declare a script type and the OLAP Server version used for scripting).

VARIABLE_DECLARE

After the version command, variables used in the script can be declared. Declared variables are used during later script execution to automatically generate a dialog asking you to enter the values of these variables. Each VARIABLE_DECLARE command consists of the variable name, a default value used in “silent” script executions, and a description. This description is also displayed within the input dialog during execution.

VARIABLE_DEFINE

While the variable declaration is used to specify which variables can be declared within the script, variables can also be defined using the VARIABLE_DEFINE command. Before a variable can be used, it must be either declared or defined. The VARIABLE_DEFINE command can be used anywhere in a script, but it must be invoked before the variable is used for the first time within another command. In declaration and definition, the variable name is simply a string. When using a variable after it has been declared/defined, the variable's name must be prefixed with the $ character.

It may be useful to use defined but undeclared variables in cases where a user should be able to change the value via a UI dialog, but still be able to parameterize some values used in many script commands within a single place in the script. The VARIABLE_DEFINE command allows concatenation of strings. This allows to create a new variable as a combination of an existing variable and a concatenated static string. The concatenation character is a plus sign.

DATABASE_SELECT

Another important command is DATABASE_SELECT. This command selects a database on which all further commands of the script will be processed (unless there is another DATABASE_SELECT command). When executing a script from Designer, you will be asked to specify the database on which to run the script. If the script declares a variable called “system.database”, the specified database name is passed to that variable. When executing a script in Modeler, the database on which the script is to be executed is retrieved from the "Run Script..." context menu command that you invoked. Therefore, the "DATABASE_SELECT" command is not mandatory when scripts are executed using Modeler context menu commands.

Individual script commands can be extended with clauses for handling existing database content. The script author can control whether a command should fail if its target object doesn’t exist (ERROR_IF_NOT_EXISTS), whether it should fail if an object to be created already exists (ERROR_IF_EXISTS), or whether such errors should be suppressed. If an error occurs during execution of a script, the execution is stopped, and all changes made by this script are reverted.

NO_ERROR

When the NO_ERROR clause is used for non-matching character count arguments, the "best effort" result is returned (see examples below).

Script size

By default, the maximum allowed size for editing a database script in Jedox Web is 20 MB. To increase this default value, create the following key in Administration → Settings:

For more information about adding a new key, see Jedox Web Settings.

Updated September 27, 2024