Sunday, 19 August 2018

Essbase REST API - Part 4

On to Part 4 of this series looking at the Essbase REST API, which is currently only available in OAC. Just to recap, in the first part I provided an overview of the REST API, the second part was focused on application and database monitoring such as applications/database and properties, starting, stopping and deleting. In the last part I concentrated on management type tasks like managing substitution variables, filters and access permissions.

In this post I am going to cover scripts, listing, creating and editing. The examples will be based on calculation scripts, but the concept is the same for the other available types of scripts like MDX, Maxl and Report scripts. I will also look at running scripts through jobs and monitoring the status.

As usual I will stick with the same style examples using a mixture of a REST client and PowerShell, the choice is yours when it comes to scripting so pick the one that you feel most comfortable with.

You should be aware now that the URL structure to work with the REST API is:

https://<oac_essbase_instance>/rest/v1/{path}

In the UI, the different type of scripts can be viewed at the database level.


To retrieve a list of calc scripts the URL format is:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/scripts

Just like with the other REST resources you can add the parameter “links=none” to suppress the links in the JSON that is returned.

With a GET request against the following URL:

https://<oac_essbase_instance>/essbase/rest/v1/applications/Sample/databases/Basic/scripts?links=none

A list of available calc scripts for the Sample Basic database are returned in JSON format.


This matches what is displayed in the UI. If the “links=none” parameter is removed, then the links to the different resources are returned.


To view the content of a calc script, a GET request is made to the URL format of:

https://<oac_essbase_instance>/essbase/rest/v1/applications/<appname>/databases/<dbname>/scripts/<scriptname>/content

Let us take the “CalcAll” script in the Sample Basic application.


A GET request to

https://<oac_essbase_instance>/essbase/rest/v1/applications/Sample/databases/Basic/scripts/CalcAll/content


will return the contents in JSON format, the response will include “\n” for any new lines in the script.


To edit a calc script, the content of the script is required in JSON format in the body of the request, the PUT method is required with the URL format.

https://<oac_essbase_instance>/essbase/rest/v1/applications/<appname>/databases/<dbname>/scripts/<scriptname>

This time I am going to have a PowerShell script that reads in the following file:


Basically, it is the same calc all script with an additional SET command.

Once the script is read, it is converted to JSON and the REST request is made.


With a GET request, the content of the script can be outputted, and it now includes the changes.


Creating a new calc script is very similar, a POST method request is made to the URL format:

https://<oac_essbase_instance>/essbase/rest/v1/applications/<appname>/databases/<dbname>/scripts

The body of the request must include the name of the script and the content in JSON.


The following example creates a new script with one line of content.


In the UI the new script is available.


To delete a script the DELETE method is used against the URL format.

https://<oac_essbase_instance>/essbase/rest/v1/applications/<appname>/databases/<dbname>/scripts/<scriptname>

So that covers managing scripts, now on to running them, which is done through Jobs.


The following jobs can be run from the UI.


The list on the left is the available jobs in the user managed version of OAC and the right is autonomous OAC, the difference is that autonomous OAC does not include the ability to run MaxL and Groovy scripts.

As I have mentioned before, the majority of what you can do in the UI can also be achieved with REST.

To run a calculation script in the UI, you just select “Run Calculation” from the list of jobs.


To run jobs with the REST API, a POST method request to the following URL format is required.

https://<oac_essbase_instance>/essbase/rest/v1/jobs

The body of the request includes the application, database, script name and job type.


Some of the other job types are maxl, mdxScript, groovy, dataload and dimbuild.

An example of the response from running a script is:


The response includes the current status of the job and a URL where you can keep checking the status.

https://<oac_essbase_instance>/essbase/rest/v1/jobs/<jobID>

Checking a job returns a similar response.


This is the equivalent of what would be displayed in the UI.


To replicate the list of jobs displayed in the UI with a script is an easy task.


As you can see, the start and end times are returned in Unix time format, these can be converted to a readable format with a simple function which I provided an example of in the second part of this series.


To run a job using a script can once again be achieved with very little code, the following example runs a calc script which creates a level0 export using the DATAEXPORT command.


You can either construct the URL to view job information with the job ID, or alternatively extract the URL from the response.


Now the status of the job can be repeatably checked until the status changes from “In progress”.


To run other job types only changes to the body of the request are required.

For example, to run an MDX script:


A data load:


A dimension build:


To clear all data from a database:


Anyway, back to the calc script I ran earlier, the data export in the script produces a level0 extract file named "level0.txt", this can be viewed in the UI.


With the REST API, a list of files can be returned by making a GET request to the URL format:

https://<oac_essbase_instance>/essbase/rest/v1/files/applications/<appname>/<dbname>

To be able to list the files there is an additional header parameter required in the request, which is “Accept=application/json”

The following script returns all the calc script and text files in the Sample Basic database directory.


To download the file, the name of the file is required in the URL, the accept header parameter is not required.


The text file will then be available in the location specified in the script.


Another nice feature in OAC Essbase is the ability to view an audit trail of data either through the UI or Smart View.

To be able to do this a configuration setting “AUDITTRAIL” is required to be added at application level.


You will not be surprised to know that configuration settings can be added using the REST API with a post to the URL format:

https://<oac_essbase_instance>/essbase/rest/v1/files/applications/<appname>/configurations

The body should include the configuration setting name and value.


A http status code of 204 will be returned if the operation was successful.

If the application is started it will need restarting for the configuration to be applied, I went through stopping and starting applications in part 2 of this series.

Once the setting is in place, any data changes can be viewed in the UI at database level for the user logged in.


To return the audit data with the REST API then a GET method request can be made to the following URL format:

https://<oac_essbase_instance>/essbase/rest/v1/applications/<appname>/databases/<dbname>/audittrail/data


This will return the data in text format


To return the data in JSON format the accept header is required with “application/json”.


It doesn’t require much to replicate this functionality using scripting, the next example downloads the audit data to a CSV file.


The file can then be viewed in say Excel, the time will require updating to a readable format which can either be done in the script or Excel.


I am going to leave it here for this post, until next time….

Sunday, 22 July 2018

Essbase REST API - Part 3

Moving on to part 3 of the series looking at the Essbase REST API which is currently only available in OAC. As I mentioned previously, most of the Essbase web UI is built on top of REST so there is there is a hell of a lot you can do with it, this also means it would be an endless task trying to cover everything, so I am going to pick some of the common management type tasks where adding in automation can assist further.

Historically carrying out everyday tasks would be done with MaxL, this is definitely still the case and there is nothing wrong with that. The problem I have always found with MaxL is that it can be restrictive, the output is not great, and it doesn’t interact so well with other scripting languages. This is where the REST API can come to the rescue and the good news is that it can be integrated with most scripting languages.

I am going to assume you have read at least the first part of this series, so you understand what the Essbase REST API is all about. I will stick with the same style examples using a mixture of a REST client and PowerShell, the beauty is you can pick whichever scripting language you feel most comfortable with, the example scripts I show are just there to give you an idea of what can be achieved with simplicity.

Let’s start off with a common task of managing substitution variables.

In the OAC Essbase UI sub vars can be managed through the variables tab.


To return the variables using the REST API is extremely easy.

Remember the URL structure to access the REST APIs will be:

https://<oac_essbase_instance>/rest/v1/{path}

To return all the sub vars at an application level then the format is:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/variables

and database level:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/variables

For example, using a REST client with GET request with the following URL will return all the subs vars for the Sample application, I am also suppressing the links for display purposes.


This will return the following variables in JSON format.


If you want to focus on a single variable then the name of the variable is included in the URL.



This time I have included the links which give you an indication of how to edit and delete sub vars.


Using a PUT method request, it is possible to update a variable, the body of the request requires the variable name and new value in JSON format. In the following example I am going to update the “currMonth” variable from “Jul” to “Aug”


The response shows the variable has been updated.


A quick check in the Essbase UI proves it has been updated.


A DELETE request to delete a variable at application level would be

https://<oac_essbase_instance>/rest/v1/applications/<appname>/variables/<subvarname>

or database level

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/variables/<subvarname>


A 204 status code will be returned if the deletion was successful.

Back in the Essbase UI, the variable has been removed.


To create a new variable then the URL format is similar, a POST request should be made and the body of the request should include the variable name and value in JSON format.


The response returns the newly created variable details.


In the Essbase UI the new variable is available.


In terms of scripting it is simple to manage sub vars, I am starting out with the same base script that I have used in the previous parts of this series.

The user and password are base64 encoded so they can be added to the header of the request.

The REST base URL and Essbase application are defined and are then combined to create the URL of the resource.

A request is made to return all the variables at the application level and the response is outputted to the console window.


To update a variable it is a similar script, this time the sub var name and new value are defined, these are then converted to JSON and added to the body of the request.

A PUT request is made and the response with the updated variable value is outputted to the console window.


To create a new variable, the only difference from the previous script would be POST request instead of a PUT.


To delete, again very simple with not much variation required.


So as you can see, it is not a difficult task to manage sub vars.

Moving on to another common maintenance task, which is managing filters.

To retrieve all the filters against an Essbase database, the following URL format is required:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/filters

An example using a GET request and suppressing links:


The response in JSON format will contain the names of all the filters assigned to the database.


To return a single filter the format is:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/filters/<filtername>

This time I am not supressing the links, the response provides you with the URLs and methods to manage the filters.


If I look at the same filter in the Essbase UI, the member specification and access level are displayed.


To return the same information using the REST API then a GET request is required with the URL format:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/filters/<filtername>/rows


The response as usual is in JSON format and is split by row definition like in the UI.


To update a filter definition a PUT request is required:


The body of the request should include all the required filter rows, any that are not included will be removed, basically it acts in the same way as a replace.

In the follow example I am going to only include one row which will mean the other existing rows will be removed.


A check back in the UI confirms the filter now only contains one row, alternatively I could have used the REST API to confirm this.


To delete a filter the URL format is the same except a DELETE request is required.


I have two filters created and say I wanted to delete the filter named “MTL-Filter”


A DELETE request would be made including the filter name in the URL


If the deletion was successful a 204 status will be returned.

In the UI you can see the filter is no longer available.


To create a new filter and define access rows then the format for the URL is the same as retrieving all the filters.

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/filters

A POST method is required, and the body of the request should include (in JSON format) the name of the new filter and the access definition rows.


In the UI you can see the filter and the access definition have been created.


In reality you wouldn’t be using a REST client to manage filters and you probably would want it automated using a script.

In the following example I have a text file and the filename is the name I want to create.


I did the delete the filter first as it is the same name as the one that already exists.

The content of the file contains the access rows.


The script defines variables for the Essbase application, database name and filename.

The filter name is extracted from the filename.

The REST URL is built from the variables.

The contents of the CSV file are read and then converted to JSON to be used in the body of the REST request.

A POST request is made to create the filter and definition.


Back in the Essbase UI you can see the filter is there with the access rows from the file.


If you have ever worked with MaxL then hopefully you agree it much simpler to manage filters using the REST API.

So, what about assigning permissions to a filter, well once again you can use the REST API to achieve this.

Before assigning the filter permissions, let us take a step back and first provide access to the Essbase application.

Currently there is only one user with access to the application.


To update permissions for an application then the REST URL format is:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/permissions/<user_group_name>

A PUT request is required, and the body of the request should include the user/group and role.


If successful, a 204 status code will be returned.

In the UI, the user has been granted with access to the Essbase application.


Alternatively, the REST API could return all the permission information for the specified application.


Back to the filter that was created earlier, now the user has been granted access to the application they can be assigned permission to the filter.

In the UI they would usually done in the permissions tab for the filter.


To achieve this with the REST API then a POST request should be made to the URL format:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/filters/<filtername>/permissions

The body of the request should include the user id or group you want to assign permissions to.


This can be simply achieved using scripting, I don’t feel like I need to explain as the concept is pretty much the same as the previous example scripts.


In the UI, the user has been assigned permissions to the filter.


To delete permissions then a DELETE request should be to URL format of:

https://<oac_essbase_instance>/rest/v1/applications/<appname>/databases/<dbname>/filters/<filtername>/permissions/<user_group_name>


Right, I am going to leave it there for this post. In the next part I will go through managing calculation scripts and running and monitoring jobs.