Database Scripts in Jedox

Database scripts for 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 into Jedox Web; for example, scripts can be created for databases or parts of the database, in which case they will represent the object's content (similar to a “dump” in relational databases). Furthermore, scripts can be executed from the Designer and Modeler 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

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

Generating scripts

Scripts can also be generated by the Jedox In-Memory DB (OLAP). Generation of database scripts is also supported by the Java API for OLAP Server and thus can be used from Groovy Jobs in Jedox Integrator projects.

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 types the script should contain:

Notes:

• The OLAP database script engine has been extended to support Lists.
• There is a default limit on script's file size for editing; you will receive an error message if you try to edit a script file that is larger than the set limit.

You will also be asked for an output location. You can either download the created script file to the local file system or to 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.

Script content

Scripts are made up of one or more commands. Each command consists of the command name and a list of parameters for the command. The command parameter list is enclosed by brackets; command parameters are separated by semicolons. By default, commands are specified in uppercase, but both, commands and clauses, are case-insensitive.

Comments can be added in a script by adding a hash character as the first character in a line. Empty lines can be added in scripts and will be ignored during script execution. Line breaks (for example as parts of the value of an OLAP string cell) are supported. Strings (for example, an element name) are enclosed by double quotes. If a string itself contains a double quote character, it has to be escaped by an additional double quote.

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

VERSION(1;SCRIPT;7803)

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

VARIABLE_DECLARE

After the version command, variables used in the script can be declared. Declared variables are used during later script execution to generate automatically a dialog which prompts the user to enter the values of these variables. Every VARIABLE_DECLARE command consists of the variable name, a default value which is used in “silent” script executions, and a description. This description is shown in the input dialog during execution as well.

VARIABLE_DEFINE

While variable declaration is used for defining which variables are definable later by a user, within the script, variables also can be defined with the command VARIABLE_DEFINE. Before a variable can be used, it has to be either declared or defined. THE VARIABLE_DEFINE command can be used at any point in a script but must be used before the variable is used for the first time in another command. In declaration and definition, the variable name is simply a string. When actually using a variable after it has been declared/defined, the variable's name must be prefixed with the $ character.

It can make sense to use variables, which are defined but not declared, in cases, in which a user should be able to change the value via UI dialog, but still should be able to parametrize some values, which are used in many script commands, in a single place in the script. The VARIABLE_DEFINE command also allows concatenating strings. This makes it possible 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, the user always will be queried for a database to run the script on, and the script will start with the provided database. If the script declares a variable called “system.database”, the DB name provided by the user is passed to that variable. Meanwhile, when executing a script in Modeler, the database to run the script on is actually inferred from where the user called the context menu command “Run script...”. Therefore, the command “DATABASE_SELECT” is not mandatory in cases when scripts are executed by context menu commands of the Modeler.

Individual script commands can be extended with clauses for handling existing database content. The script author can control whether a command should fail when its target object doesn’t exist (ERROR_IF_NOT_EXISTS), whether it should fail if an object, that should 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 rolled back.

NO_ERROR

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

Executing scripts

Scripts can be executed either from Designer or with the context menu command of a database element in the Modeler. To execute a script in Designer, right-click the file and select “Run script”.

Note: 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 on, and select Run script.... If the script has any declared variables, then a popup is shown to the user, asking for input values for the variables, before it is executed. After clicking OK in the input dialog, the script is executed. If there are no declared variables, the script is executed right away. Please be aware that changes made by script execution cannot be undone after the execution is finished.

Script commands are executed with the context of the user who runs the script, and the user’s access rights are checked during script execution. If the user has insufficient rights for execution of a specific command in a script, that command will fail with an error, and as mentioned above, all other changes, made by previous commands in that script, are rolled back. Note that any commands from a script will not trigger Supervision Server workers (e.g. a cube worker). Cell value changes made by a script will however be logged in the audit log of a cube, if auditing is enabled for that cube.

Scripts are not as optimized for mass data load as the Jedox Integrator. For small data sets, the performance of scripts is sufficient, albeit slower than the Integrator. For mass data load, the Integrator is preferable. As an example, a load of ca. 1 million cube values takes ca. 20 seconds on a Windows Server with normal hardware resources. Load of the same amount of values into the same cube via script takes ca. 80 seconds.

For information on generating and executing database scripts in Jedox Integrator with a Groovy job, see Executing Scripts in Groovy Jobs.

Script size

By default, the maximum allowed size for editing a database script in Jedox Web is 20MB. 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, 2022