In addition to the web-based Integrator component of Jedox Web, Integrator projects can also be run from the command line by using the CLI tool EtlClient. This tool is located in the directory …\tomcat\client. To open it, use the command etlclient.bat (Windows) or etlclient.sh (Linux).
As of Jedox 6.0, authentication with an OLAP Server user is required for communication with the Jedox Integrator Server. Valid login credentials must be provided for each call of the CLI; the user can be set with option -u and password with option -pw. The IP address of Jedox Integrator is set with option -s (default value for the server is “http://localhost:7775” ).
For example, to start the Jedox Integrator project example sampleBiker on a locally installed Jedox Integrator, enter the following at the command line level:
|To add the project to the server:
etlclient –a \samples\sampleRelational -u admin -pw admin
|To run the standard job:
etlclient -p sampleRelational -u admin -pw admin
Instead of providing login credentials with each CLI call, you can user server profiles. Server profiles are created with option -spc. To use all options for a defined profile, you can use option -sp.
etlclient.bat -spc MyProfile -s localhost -u admin -pw admin
etlclient.bat -sp MyProfile -a samples\sampleBiker
etlclient.bat -sp MyProfile -p sampleBiker
All profiles are stored in the local file .\client\config\profiles.xml. If no profile is given, the profile with name “default” is used. In this case, you can omit the option -sp.
etlclient.bat -spc default -s localhost -u admin -pw admin
etlclient.bat -p sampleBiker
Note: the Integrator component of Jedox Web and the command line client work with the same Jedox Integrator. This means that jobs that were created with the Jedox Integrator web client can also be run from the command line client.
Command Line Client Parameters
Pressing the ‘-h’ key will call up a list of the valid parameters. See Command Line Client Parameters for descriptions of the parameters.
Executing a Jedox Integrator Project
To execute an Jedox Integrator project, the Jedox Integrator project definition must be entered using option -p. A load and job defined in the Jedox Integrator project can then be executed. Two loads or jobs can be executed simultaneously, but only if a new job containing the desired jobs or loads has been created. If no job is determined, the job named “default” is executed.
etlclient –p sampleBiker
etlclient -p sampleBiker -j Masterdata
etlclient -p sampleBiker -l Years
Note: in all examples, it is assumed that login credentials are provided in the default server profile.
Before executing loads or jobs, it is advisable to display on the screen the data sources taken as a basis. Any data source, regardless of whether it is an extract or a transformation, can be shown. For tree-based data sources, the display is shown in the “full hierarchy” format, without attributes and levels. The number of display lines can be determined.
etlclient -p sampleBiker -d Products
etlclient –p sampleBiker -d Products -n 20
The test data can also be written to a file with the “>” shell option.
etlclient -p samples\sampleBiker -d OrdersData_Trans> testfile.txt
The job execution can be defined more accurately using context variables. Even if a job has already been assigned variables, the variables shown at the command line level are used. Spaces must be put in quotation marks (” “) in order to be used as variable values.
-c <Variable1>=<Value1> <Variable2>=Value2> …. or
-c <Variable1>=”<Value1>” <Variable2>=”Value2>” ….
etlclient -p sampleVariables -j SalesMonth -c Year=2006 Month=Dec
etlclient –p sampleVariables -j SalesMonth -c Year=”2006” Month=”Dec”
If a job or load is executed from the command line level, the execution protocol is displayed directly on the command line. It contains information, warnings, and error messages that appear during the Jedox Integrator process. The level of detail during logging can be entered with the log level on the command line using option –ll. Possible values are OFF, FATAL, ERROR, WARN, INFO, DEBUG, and ALL.
The following final messages exist for an Jedox Integrator process:
- “Completed successfully”
- “Completed with Warnings”
- “Completed with Errors”
The log display can be written to a file with the “>” shell option.
etlclient -p samples\sampleBiker –j Initdata > InitdataLog.txt
For Windows, the following special characters must be double quoted if they are used as values for any command line option: & \ < > ^ | <space>. For some characters, an additional prefix is required: ! : ^! and ” : “”
etlclient -p “My Project &” -j “Go^!”
Executing commands with the Jedox Integrator command line tool will return an “Exit code” that can be used later within the batch script:
|10||Execution ended with Status “Successful”|
|20||Execution ended with Status “Warnings”|
|30||Execution ended with Status “Errors”|
|40||Execution ended with Status “Failed”|
|50||Execution ended with Status “Stopped”|
|60||Execution ended with Status “Aborted”|
|0||No error occurred (other commands than Execution)|
|-1||Jedox Integrator was not reachable|
|-2||Error on Client or Server side (e.g. Project/Job not found, Project not valid)|
Note: executions start with Jobs (-j) or Loads (-l), Data preview (-d), and Test (-t).
call etlclient.bat -s localhost: 7775 -p sampleBiker -j default
rem print the status code of the execution (in this case it will be 10 which means successful)
echo Exit Code is %ERRORLEVEL%
Generate a project documentation
It is possible to generate additional info messages indicating the progress of a job or a data preview. This is helpful for the analysis of long-running Jedox Integrator jobs with high data volume.
For one or several components of the Jedox Integrator job (extract, transform, or load), a log block size can be indicated as a command line parameter. Each time the component has processed a block, an info message will appear.
Notation: -c #<componentName>.logBlockSize=<n>
-p samples\sampleBiker -j Cubedata -c #sampleBiker.extracts.Orderlines.logBlockSize=500 #sampleBiker.loads.OrdersCube.logBlockSize=2000
This will lead to these additional log messages:
INFO : 1 rows processed from extract Orderlines
INFO : 500 rows processed from extract Orderlines
INFO : 1000 rows processed from extract Orderlines
INFO : 2000 rows processed from load OrdersCube