Friday, 22 January 2016

Essbase Web Services Part 2

I have finally got round to writing up the next part in this series of posts looking at Essbase Web Services.

In the first part I provided an overview of the Web Services, how to get up and running, some pitfalls and bugs and then a few examples.

If you have not read the first part then I recommend you do as the assumption will be that you are familiar with the Web Services.

As I explained in the first part there are three modules that make up the Web Services, these are:
  • Datasource
  • Administration
  • Data and Metadata Query
Today I am going to focus on the operations available in the “Datasource” module which I did touch upon in the last post.

In 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 am also going to provide some examples using PowerShell like I did in my previous posts about the Planning REST API, I have only selected Powershell because it is easily accessible if you are running a Windows OS and I am sure many consultants and administrators who have experience in batch scripting have also played around with it a little.

If I was to pick Java and started posting lots of code I am sure it would end up baffling more and do more harm than good than displaying some small PS scripts, I have no affiliation to it and in the end it is your choice which language you feel most comfortable with and want to access the Web Services with, the good thing is you should be able to interact with the services using any language.

Right as you know this blog is not about waffle so let’s get down to seeing what can be achieved with the Datasource module.

The WSDL URL for the Datasource service is:

http(s)://:/essbase-webservices/DatasourceService?wsdl

Once the WSDL URL has been added to the client the operations available will be returned.


In the last part I did provide some examples on the “getTypes” and “list” operations but I will quickly go over them again.

The “getTypes” operation does not require any input values in the SOAP request.


The response will be a list of types that can be used in the other operations.


The response also gives an indication to what is meant by "uri" and "urid" which is used throughout the different operations.

To achieve the same results using PS only requires a few lines of code:


In the script I used the “New-WebServiceProxy” cmdlet which I did in my posts about the Planning REST API, the cmdlet is great for RESTful Web Services but as you are about to find out not so good with the Essbase Web Services which are SOAP based.

Moving on to the “List” operation which the documentation only provides the following information:
  • List()
  • Input: UriType, URI
  • Output: BaseObject[]
To be honest the documentation is up there with the worst out there and assumes you are a mind reader and are using the Java examples.

The operation lists out Essbase applications or databases for a specified Essbase server and I did provide examples for both in my last post.

The SOAP request only requires a few inputs to define whether it is going to list applications or databases which is entered into arg0, the remaining details go into arg1 and you will notice “urid” and “type” which the values relate to what was returned from the “getTypes” operation.


Sending the request provides a response returning all the Essbase applications for the specified Essbase Server


To list out Essbase databases only requires a slight change to the request which you can see in more detail in the last blog post.

Back to the same PS example but this time try using the list operation.


The problem is that for operations that require authentication they also need a custom SOAP header, unfortunately using the “New-WebServiceProxy” cmdlet does not allow custom headers which is another reason why I prefer RESTful Web Services as they much simpler to work with.

Now there are a number of ways around this but I thought that the simplest method to demo would be to have an XML file with the correct SOAP format for the request but leave the values which I will populate in the PS script, this way you can see how the SOAP request is working in the same way as using browser client.

I started with the following XML template file which forms the SOAP request for the list operation.


The script then reads in the XML file and populates the elements with application and server details.

It uses the “Invoke-WebRequest” cmdlet to send the SOAP request to the Essbase Datasource Web Service and then processes the SOAP response which is in XML format.


So with not many lines of code it is possible to list all the Essbase applications, ok they are not in the alphabetical order but that is easy to fix if needed.

You may well be saying well I can easily do that with Maxl which is true but you need to install the Essbase client then you need to have the correct Essbase ports open and the problem with MaxL it is not a fully blown scripting language and there is no concept of storing and processing responses without parsing output logs and involving another language.

Don’t get me wrong MaxL is great but at the same time it can be limiting so there are alternatives out there and if you have the Essbase Web Services enabled and http access you are on your way.

By expanding the script slightly all the Essbase applications and databases can be returned.


Let us move on to the next operation which is “Get”, here is the full Oracle documentation about it:
  • Get()
  • Input: URI
  • Output: BaseObject
This operation will return properties on either an Essbase server, application or database.

An example of the SOAP request to return Essbase server properties is:


Hopefully you are starting to see that the input requirements for the request are quite similar to previous examples so it is pretty quick and simple to start returning more Essbase information.


At an Essbase server level the response includes version (not exact version), locale and if Unicode is enabled.

To convert this into a PS example I pretty much used the same script as previously, I did modify the SOAP XML template file as there is only one set of arguments required and I updated the operation name this is because the template can be used across different operations so I might as well just change the operation name in the script.


It is not essential but I thought I would try and only have as few as possible XML templates which could be used across all the operations in each of the modules, you could do away with the XML file altogether and just build it up in a script if that was preferred.


The script reads in the SOAP XML file and replaces “changeme” to the current operation which is “get”.

The rest is similar to the previous script but this time returns and formats the Essbase server properties.

To return Essbase application properties once again only needs a quick update to the request.


You should be noticing a theme for updating the request from server to application or database.

So in this example it will return the properties for everybody’s favourite sample application.


You shouldn't need the properties that are returned explaining and can match them up to what you see in EAS if you want to be certain.

The script follows the same concept as before and can easily return and format the application properties.


To return an Essbase database properties follows the same principal and I am convinced you understand the format by now so I am only going to show the response from the SOAP request.


As you can see there is a large amount of properties that are returned for an Essbase database and should cover most of the properties you would want to find out about.

Using the same script all the properties can be displayed or stored.


As some of the properties are at a further child level in the SOAP response they can be accessed like:


This level of information could be useful if you are reviewing or monitoring applications and the results could then be presented into a report or a mail sent if thresholds have been exceeded.

Time to move on to the next operation and that is “Create”, the documentation provides:
  • Create()
  • Input: BaseObject
  • Output: BaseObject
I think we all know that “Create” is going to be used to create Essbase applications or databases.

The SOAP request should be understandable from the previous requests.


There are properties that can be set in the request so they applied when creating the application, the properties and values are the same that are returned when using the “Get” operation.

The “storageType” is obviously required  as when creating an application it defines if it will be BSO or ASO, the value for BSO can be “MULTIDIMENSIONAL” or “DEFAULT”


Be sure to set some of the properties otherwise where it is true/false value it will default as false.

Application creation was confirmed by a quick check in EAS.


I did hit a bug when creating an ASO application.


Even though the application type was set to a valid value the application created kept failing with an invalid application type error.


I double checked using the Java examples and got the same error.

I did finally find where the issue is and it is definitely a bug, behind the scenes the Web Services use the Essbase Java API and to create an application it executes a method called createApplication.

To create an ASO application a value of 4 has to be passed in as the storage type otherwise it will fail, the Web Services are passing in the value of 1, easy to fix but requires an underlying code change so out of my hands 

There is a possible workaround which I will go through in the next part.

I don’t feel like I need to provide the PS script to create an application because with the above SOAP request and the previous examples I think you would be able to easily achieve it.

Quickly moving on to next operation which is “Update” and the amazing documentation has the following information:
  • Update()
  • Input: URI, Base Object (application or cube)
  • Output: BaseObject
Basically using the “Update” operation allows you to change most of the properties for an application or database that were returned using the “Get” operation.

The example I want to show is to update the application description and set the property to allow users to start the application which I did not set when I created the application.


The SOAP request should not throw up any surprises.


The description should update to “WS Update” and setting loadable to true show allow users to start up the application.


The response will return all the properties again and you can see that they have been updated.

This is definitely confirmed by looking at the application properties in EAS.


Using previous examples the PS script was slightly modified to incorporate the update operation and change the description again.


The same principals will be applied if you want to update any database properties.

Next let us take a look at the “Rename” operation and here is the full documentation:
  • Rename()
  • Input: URI, String(newName)
  • Output: None
No awards to guessing it renames an application or a database.

I will quickly show the SOAP request which by now you should understand what it is going to do.


If the response is not an error then it should have renamed the application.


Checking EAS reveals that the application has been successfully renamed.


On to the final operation which is “Delete” and to be consistent here is the official documentation
  • Delete()
  • Input: URI
  • Output: None
The SOAP request only requires the server, application or cube information as inputs.


Like with the rename operation if the response does not contains errors then application or database will be deleted.


So that completes the operations and hopefully it is clear what can be done with the "Datasource" module putting in relatively little effort, though saying that it has taken me a considerable amount of effort to get to this stage :)

In the next part I will look at what operations are available in the Essbase Web Services administration module.

No comments: