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:

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

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:

Ping()

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

Start()

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

Stop()

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

Perform()

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

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.