Friday, 29 January 2016

Essbase Web Services Part 3

Back again to continue the series looking at the Essbase Web Services and in this part I will focus on the operations that are available in the Administration module.

If you are have not done already I would recommend reading through the previous two parts.

Part 1 – Introduction to Essbase Web Services, how to get up and running, pitfalls, bugs and examples.

Part 2 – In-depth look at the Datasource service module and examples for each of the operations.

I should be able to fly through the administration module as it is pretty self-explanatory and there only four operations.

The operations are:
  • Ping
  • Start
  • Stop
  • Perform
I think you probably can guess what most of them are going to do.

Likr in the previous parts the examples I am going to be using Boomerang which is a great free SOAP and REST client available for Chrome though there are plenty of other free clients available for the majority of browsers, I will also provide an example script in Powershell to try and demonstrate how easy it is to work with the Web Services.

The WSDL URL for the Administration service is:


Once the WSDL URL has been added to the client the following operations will be returned:

Let us start with the “ping” operation which the documentation provides the following information:

  • Input: URI (Essbase application or cube)
  • Output: Packet round trip time in milliseconds
So basically the request will require the Essbase application or cube information and the response will be the time to ping in milliseconds.

The required input information for the request is exactly the same as you will have seen in the previous post.

In the following example I am going to ping the Sample Basic database on an Essbase Server that is on another environment, in fact it is running on a previous version so as long as the server running APS can connect the Essbase Server it should be ok.

You should be familiar with the format to the request and I am hoping that is makes perfect sense now.

The response returns a value of -1 which means the application or database are not running.

Using the same PS script template I used in my previous examples it didn’t take long to replicate the above request.

I reuse the XML template and change the operation to ping, then update the input elements in the SOAP XML request.

Invoke the request and store the result and ping value from the SOAP response, if the ping value is -1 then the application is not started otherwise output the value in milliseconds.

Ok, so the database is not started so we can move on to the next operation to start it up.

  • Input: URI (Essbase application or cube)
  • Output: Void
The input is exactly the same and only the operation name has changed.

If the response does not generate an error then the assumption is the application/database started up without any problems.

To demonstrate an error I stopped Essbase and ran the same request and this time detailed info on the reason why the request failed is returned.

In the PS script all that was required after the ping was to change the operation and invoke the request again.

I have not displayed any error trapping but it doesn't take much effort to start catching and handling errors.

Now that the application is running I can test the ping again.

Next operation is to stop an Essbase application or database.

  • Input: URI (Essbase application or cube)
  • Output: Void
Once again the format is no different and only the operation name changes in the request, in the following example I will stop the Sample application.

Just like with the start operation if the response does not contain errors then the application/database will have stopped.

On to the final operation which is perform and this executes a MaxL statement.

  • Input: MaxL statement
  • Output: Two-dimensional string array of MaxL result set
It couldn't be simpler as the only input requirement is a MaxL statement.

The response should contain exactly the same information as if you ran statement from a MaxL session.

The good thing here is that because the response is in an XML format it can be easily manipulated and stored, this is usually a pain when you use the MaxL as the output is not the nicest and you end up having to parse an output file.

Converting this into a script does not require much more effort than before.

The results are stored in arrays so it is just a case of looping through them to view or store the results.

The numeric values seem to be returned as a decimal but they can easily be converted to an integer.

In the last part I highlighted a bug when trying to create an ASO application with the create operation, as a workaround you could use the perform operation with a create application MaxL statement.

Again with this statement if there is no errors it should have been successful.

You can always use the get operation to check the application exists.

Alternatively you could ping the application or execute another perform MaxL statement to verify the application exists.

So this is great you can run MaxL statements through http then format the results without requiring the MaxL client, yes this is true but this is Oracle so there is massive drawback that the developers didn't seem to think about.

If you look at the input to the perform operation you will see it does not take any server information so how does it know what Essbase server to run the MaxL against, well after testing I found that it is hardcoded to run against localhost which means Essbase would have to be running on the same server as APS.

This is the error response that is returned if Essbase is not running on the same server as APS.


In my example the perform operation is trying to connect to Essbase on the same server as where APS is deployed and that server does not have Essbase installed on it.

Maybe the developers thought Essbase is only ever deployed in that way which is definitely not the case.

It is very frustrating as it would only require a small amount of changes in the code so it could accept a server name but to be honest I can’t see that happening.

Just to prove the point I made a few changes to add in the server name into the input request.

The server name is then passed in and replaces the hardcoded localhost

So there you go it is now possible to run MaxL against any accessible Essbase server, if you need help Oracle let me know :)

Well that is the administration module covered and in the next part I will look at the data and metadata query module.


Ning Shi said...

Thanks for your sharing

Patrick Bayard said...

Dear John,
First of all a big thank for sharing with us all these years. I wouldn't be where I am today without you.

On your Essbase Web Services side, I have successfully set up all your examples, but am stuck on the perform() side : how did you add the server info? Here is what you mentioned "Just to prove the point I made a few changes to add in the server name into the input request".

Could you kindly point me to the right direction? I just cannot make my request point to the Essbase server.

A big thank again,