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….
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….
