To access Jedox Integrator and, for example, execute Integrator projects from the Macro Engine in Jedox Web spreadsheets, two approaches are possible: 1) two special wrapper functions, which abstract from the SOAP API layer, can be used, or 2) the macro can directly call the SOAP API of Jedox Integrator
1) Wrapper functions for accessing Jedox Integrator
In Jedox Web reports, Integrator jobs can be started via Actions. If you cannot use actions, you can also start Integrator jobs via Macros. The wrapper functions simplify two common tasks: executing an Integrator project and checking the execution status of a project.
To use the wrapper function, the macro code first requires the "integrator" code library. This is done to avoid integration of the code in macros that do not actually use it.
The library is required with this code:
require library('integrator'); It is also possible to use the Macro Engine wrapper functions with the REST-based version of Macro Engine wrapper using the "integrator-rest" library:
require library('integrator-rest');To execute a specific job, the function "integrator_start_job" can be called. In its simplest form, this function expects the name of a project and the name of a job in that project as parameters:
$id = integrator_start_job('sampleBiker','default'); First, the function will check if the job is already running at the moment. If this is the case, it will exit with a message indicating so. If this is not the case, then the function immediately returns the Job ID of the executed job. Additionally, the function offers a set of optional arguments that allow modifying its behavior.
The second available function is called "integrator_set_job_status" and retrieves the status of a job (identified by its execution ID), and sets it to some resource in the spreadsheet, such as a cell or an @variable.
// retrieve the job id from its resource (has to be set beforehand)
$id = retrieve_variable('INTEGRATOR_JOB_ID');
// get the status code and message (array) of the job
$status = integrator_set_job_status($id); The function returns an array with the two fields: "status_code" and "status_message".
Details on both wrapper functions can be found in the overview of the functions and objects exposed to the Macro Engine. This overview can be opened in a browser or downloaded as a zip archive.
2) Accessing the SOAP API from Macros
The SOAP API of Jedox Integrator can be addressed from Jedox Web spreadsheet macros (or any PHP environment) using the SOAP functionality provided by PHP. It can be used to check the current status of Jedox Integrator or execute a Integrator job from a spreadsheet (or PHP script), among other things.
To access Jedox Integrator Server from PHP, you must first construct a SoapClient object that addresses the WSDL of Jedox Integrator. OLAP credentials or a valid OLAP session ID must be specified in the SOAP headers.
Afterwards, various methods provided by Jedox Integrator Server can be executed on this object. Some example calls are described below.
For exception handling, we recommend wrapping the SOAP-specific parts of the code in try-catch blocks, and handling thrown exceptions of both specific SoapFault type and general Exception type.
Construct SoapClient object
A SoapClient object is constructed from the WSDL description of a specific Jedox Integrator server. To construct the adress of the WSDL description in a specific environment, Macro Engine provides the global variable $_JEDOX['ETL_SERVICE_URL']:
$wsdl_url = $_JEDOX['ETL_SERVICE_URL'].'/etlserver/services/ETL-Server?wsdl';
$server = @new SoapClient($wsdl_url, array('exceptions' => true, 'location' => $wsdl_url));The $server resource then provides various methods to be executed on the Jedox Integrator Server. Note that before executing any following calls, you first have to execute the "login" method once, and set the resulting session as header on the $server object. Internally, Jedox Integrator Server will run this login against the Jedox OLAP Server it communicates with.
The login method expects either a user and password, or a valid OLAP session ID (with key "olapSession"). If a valid OLAP session ID is passed, Jedox Integrator Server validates and re-uses this session and does not generate a new session on the OLAP Server.
The (new, or re-used) session ID is contained in the "result" property of the object returned by the "login" method:
// do login attempt on soap object
$login_attempt = $server->login(array('user' => 'admin', 'password' => 'admin'))->return;
$session = $login_attempt->result;
// set the returned session as soap header
$header = new SoapHeader('http://ns.jedox.com/ETL-Server/', 'etlsession', $session);
$server->__setSoapHeaders($header);The "etlsession" header is then set and used for all subsequent requests on the $server object.
If "login" is called specifying a user and password, a new session in OLAP server is created. The session needs to be explicitly closed after all operations are finished, otherwise the session will continue to live for the duration of the OLAP session timeout:
$server->logout(array('etlsession' => $session))->return;Retrieve a list of available Jedox Integrator projects
To retrieve a list of all projects available on the Jedox Integrator Server, the "getNames" method is called without any parameters:
$result = $server->getNames();
// set empty string if no projects exist
if(count($result->return) == 0) {
$project_list = '';
}
$project_list = $result->return;The list will return a one-dimensional array containing the available projects.
Retrieve a list of jobs in a Jedox Integrator project
The "getNames" method is also used to get a list of all jobs in a specific project. To do this, the "locator" parameter has to be set when calling the method:
$project_name = 'sampleBiker';
$result = $server->getNames(array('locator' => $project_name.'.jobs'));
if(count($result->return) == 0) {
$job_list = '';
}
$job_list = $result->return;Similar to the list of projects, the list of jobs is returned as a one-dimensional array.
Other components of an Jedox Integrator project, such as loads, or variables, can be accessed in a similar fashion, simply by changing the ".jobs" suffix of the "locator" argument to the component type that should be returned.
Execute a Jedox Integrator job
To execute a Jedox Integrator job, the aptly named "execute" method is used. This method expects two parameters: a "locator" string that specifies which component on which project is to be executed, and, optionally, a two-dimensional "variables" array that specifies which values to use for variables defined (if any) on the Jedox Integrator component to be executed:
// set the locator to be used for the execution, job with name "default" on project "sampleBiker"
$locator = 'sampleBiker.jobs.default';
// set the variables to be used for the execution
$variables = array(array('name' => 'variable_year', 'value' => 2016), array('name' => 'variable_datatype', 'value' => 'Budget'));
// execute the job
$result = $server->execute(array('locator' => $locator, 'variables' => $variables));
$job_execution = $result->return;The Jedox Integrator Server will return immediately after the execution has started. The returned object has several properties that can be accessed. The property "valid" is a Boolean value that specifies whether or not an error occurred on job initialization ("false"). The property "errorMessage" contains the error raised by the Jedox Integrator Server in that case. The most important property is "id", the execution ID that was set by the Jedox Integrator Server. Using this ID, the status of the execution can then be queried (see below).
Retrieve a list of Jedox Integrator executions
The "getExecutionList" method provides a list of all past, presently running, or queued executions in Jedox Integrator (jobs, loads, and data-preview executions). The list can be filtered using various criteria, such as a project name, a job name, a timeframe, or an execution status. For example, the following call retrieves a list of all executions for the job "default" in the project "sampleBiker" that are currently running (status '0') or queued for future execution (status '5'):
$server->getExecutionList(array('project' => 'sampleBiker', 'type' => 'jobs', 'name' => 'default', 'after' => doubleval(0), 'before' => doubleval(0), 'statuses' => array('0', '5')))->return;This call would be feasible before executing a Jedox Integrator job, to check if that same job is already running or queued.
Check the status of a specific execution
The status of a specific execution, identified by its execution ID, can also be queried. For example, you can check the status of a job that was started earlier:
// check status of the execution that has ID 5555
$result = $server->getExecutionStatus(array('id' => 5555, 'waitForTermination' => false));
$execution_status = $result->return;The "waitForTermination" parameter specifies whether the server should return the status immediately ("false"), or, if the job is running, wait until it is finished and then return its final status ("true"). On the returned object, "statusCode" specifies the numeric status code of the execution; "status" is a textual representation of the status; and "errorMessage" specifies a detailed error message if an error did occur.
Updated June 7, 2024