Accessing the Integrator REST API from Macros

The REST API of Jedox Integrator can be addressed from Jedox Web spreadsheet macros (or any PHP environment) using the REST 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.

You can use the in-built method file_get_contents provided by php core to get a response from the Integrator REST API. OLAP credentials or a valid OLAP session ID must be specified in the headers part.

Various methods provided by REST API can be executed on this object. This article includes several use cases for common Integrator tasks.

For exception handling, we recommend wrapping the specific parts of the code in try-catch blocks, and handling thrown exceptions with specific error messages.

First, define the url with the API endpoint for the function for which you want to get a response. When defining a PHP Macro in Jedox Web, the global variable $_JEDOX['ETL_SERVICE_URL'] can be used to define the endpoint. When working in a custom php environment, specify the adress (protocol, interface and port) of Integrator Server instead.

For example, the endpoint for the login method with an existing OLAP session would be constructed as follows:

Copy
$login_url = $_JEDOX['ETL_SERVICE_URL'].'/etlserver/services/v2/login';

All parameters required by the endpoint must be wrapped in the array object as follows:

Copy
$session_data = array('olapSession' => $sid);

Specify the content type (application/json or application/xml) in the header; the method of call (get, post, put, or delete); and encode the parameters using json_encode method of php and pass in the "content" parts of the http object. The http object must be an array, which again is wrapped in an array object.

Copy
$login_options = array(
    'http' => array(
        'header'  => "Content-type: application/json\r\n",
        'method'  => 'PUT',
        'content' => json_encode($session_data)
    )
);

Then create a stream context with the login_options object:

Copy
$login_context  = stream_context_create($login_options);

Now you can use the following code snippet to make a call to the Integrator REST API to get a response:

Copy
$login_response = file_get_contents($login_url, false, $login_context);

For error handling:

Copy
if ($login_response === FALSE) {
    /* Handle error */
    $error = "Error during OLAP session adoption!";
    sep_error("Error", $error);
    return $error;
}

The response is a JSON string and can be decoded using json_decode provided by PHP:

Copy
$login_response = json_decode($login_response);

Finally, check whether the response is valid, and if not, return the error message.

Copy
$is_valid = $login_response->{'valid'};
if (!$is_valid){
    return $login_response->{'errorMessage'};
}

Note that before executing any subsequent calls, you first have to execute the login method once, and set the resulting session as header on the subsequent requests.

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 session ID can be obtained from the login_response object as follows:

Copy
$session_id = $login_response->{'result'};

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:

Copy
$logout_url = $_JEDOX['ETL_SERVICE_URL'].'/etlserver/services/v2/logout';
$logout_options = array(
    'http' => array(
        'header' => array(
            "etlsession: " .$session_id,
            "Content-type: application/json",
        ),
        'method'  => 'POST'
    )
);

The steps for creating a stream context, making the HTTP call, and decoding the JSON response are the same.

Retrieving a list of available Jedox Integrator projects

To retrieve a list of all projects available on the Jedox Integrator server, the /components/locators endpoint is called:

Copy
$get_projects_url = $_JEDOX['ETL_SERVICE_URL']./etlserver/services/v2/logout;
$get_projects_options = array(
    'http' => array(
        'header' => array(
            "etlsession: " .$session_id,
            "Content-type: application/json",
        ),
        'method'  => 'GET'
    )
);

This returns a JSON array which can be iterated over using foreach as follows:

Note, use parameter true with the json_decode method.

Copy
$get_projects_response = json_decode($get_projects_response, true);
foreach($get_projects_response as $key => $values){
    if (is_array($values) || is_object($values)){
        foreach ($values as $value){
            echo $value . ", ";
        }
    }
}

Retrieving a list of jobs in a Jedox Integrator project

The /components/locators endpoint is also used to get a list of all jobs in a specific project. To do this, the "locator" parameter has to be set to the url. For example, to get all jobs from the project sampleBiker, add "sampleBiker.jobs" to the url as:

Copy
$get_jobs_url = $_JEDOX['ETL_SERVICE_URL'].'/etlserver/services/v2/components/sampleBiker.jobs/locators';
$get_jobs_options = array(
    'http' => array(
        'header' => array(
            "etlsession: " .$session_id,
            "Content-type: application/json",
        ),
        'method'  => 'GET'
    )
);

Similar to the method for obtaining a list of projects (described above), the list of jobs is returned as a JSON array, which can be iterated over using foreach.

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.

Executing a Jedox Integrator job

To execute a Jedox Integrator job, the endpoint /components/{locator}/executions/run is needed. The "locator" should be replaced with the locator string; for example, sampleBiker.jobs.default.

Copy
$execute_job_url = $_JEDOX['ETL_SERVICE_URL'].'/etlserver/services/v2/components/sampleBiker.jobs.default/executions/run?waitForTermination=false';
$parameters = array('parameters' => []);
$parameters = json_encode($parameters);
$execute_job_options = array(
    'http' => array(
        'header' => array(
            "etlsession: " .$session_id,
            "Content-type: application/json",
            "accept: application/json"
        ),
        'method'  => 'POST',
        'content' => $parameters
    )
);

The Jedox Integrator server will return immediately after the execution has started. The returned object has to be decoded using json_decode as mentioned above. The key valid is a Boolean value that specifies whether or not an error occurred on job initialization ("false"). The key errorMessage contains the error raised by the Jedox Integrator Server in that case. The most important key 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). If you want to obtain the result with this endpoint, set waitForTermination to true.

Retrieving a list of Jedox Integrator executions

The endpoint /executions 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 time frame, 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'):

Copy
$executions_url = $_JEDOX['ETL_SERVICE_URL'].'/etlserver/services/v2/executions?project=sampleBiker&after=0&before=0&statuses=0&statuses=5';
$executions_options = array(
    'http' => array(
        'header' => array(
            "etlsession: " .$session_id,
            "Content-type: application/json",
            "accept: application/json"
        ),
        'method'  => 'GET'
    )
);

This call would be feasible before executing a Jedox Integrator job, to check if that same job is already running or queued.

Checking the status of a specific execution

The status of a specific execution, identified by its execution ID, can also be queried with the endpoint executions/{executionId}/status. For example, you can check the status of a job with id '5' that was started earlier:

Copy
$execution_status_url = $_JEDOX['ETL_SERVICE_URL'].'/etlserver/services/v2/executions/5/status?waitForTermination=false';
$execution_status_options = array(
    'http' => array(
        'header' => array(
            "etlsession: " .$session_id,
            "Content-type: application/json",
            "accept: application/json"
        ),
        'method'  => 'GET'
    )
);

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 decoded response, "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.