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 which are executed against a specific OLAP database. They can be parametrized through use of variables. Support for script execution is built into Jedox Web in version 7; for example, scripts can be created for databases or parts of database, in which case they will represent the object’s content (similar to a “dump” in relational databases). Furthermore, scripts can be executed from within the Report Designer in Jedox Web, or in the Modeler. During the deployment of Jedox Models, scripts are used to generate and modify database contents.
Scripts can be created (that is, written) manually, or – for existing databases, dimensions or cubes – by the Jedox OLAP Server. To create a new, empty script in Jedox Web, click in Report Designer the button “New” and select “New Script”. In this initial state, the script is basically just an empty text file.
To generate a script which represents the content of an existing database object, right-click that object in the Modeler tree, and select “Create script…”.
Depending on the chosen object, you will be asked which content types the script should contain. For example, on a dimension, you can optionally include elements, attributes, subsets, and access right definitions. After defining these options, you will be queried for an output location. You can choose to either download the created script file, or to save it in a folder of the Report Designer. Script files have the file name extension .jds (Jedox Database Script).
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. For details, please check the Java API documentation.
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 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:
This states the used syntax version is “1”. The second and third parameter are optional and only for internal use (they declare a script type and the OLAP Server version used for script generation).
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.
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 variabled 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 to concatenate 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.
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 script from Report 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.
Scripts can be executed either from Report Designer, or with the context menu command of a database element in the Modeler. To execute a script in Report Designer, right-click the file, and select “Run script”. Note that, when executed in Report 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.