Thursday 31 December 2015

Essbase Web Services Part 1

This is one of those blogs that has been sat in my to-do folder for an age and I have just never got round to dedicating some time to research and write up.

With the ongoing rise in cloud offerings I feel Web Services will start to play a bigger part in integrating segregated cloud instances and put a potential link between the on-premise and cloud world, you can already see this with the EPM Automate utility which is an integral part of PBCS and is built on top of the Planning REST API, soon there will additional functionality in the API to integrate with the Business Intelligence Cloud Service.

In the near future FDMEE will include hybrid functionality to pass metadata or data between on-premise and cloud applications and will no doubt be using Web Services in some form, there is also going to be DRM integration which is built on top of the already available Web Services.

Most of the core EPM products do have Web Services available which are using either REST or SOAP methods and recently I covered the Planning REST API which you can read about here and here, I thought I would continue the theme and start to look at what is currently available in other areas starting with Essbase Web Services.

Essbase Web Services were introduced in 11.1.2.2 and to be honest since they were made available I have not read or heard much about them so my assumption is they are not being widely used.

There are probably a number of different reasons why this is and here are some ideas I have:
  • Did not know they existed
  • Not got around to looking at them
  • Not sure what they can offer
  • Had difficulty setting them up
  • Documentation was poor so didn't bother.
  • See no added value in using them.
Hopefully I can address some of those reasons if you feel you can relate to them, I definitely can relate to the Essbase WS documentation being very poor and when you can compare it to the documentation for the Planning REST API you realise how little effort has been put into it.

I am going to split this into two parts and in this first part I will cover get up and running with the Web Services and go through any pitfalls or bugs, in the second part I will go through some of the functionality with examples.

This is going to be based on 11.1.2.4 but it should be similar in 2.2 and 2.3 because the Web Service have not really changed since they were first released.

I am going to try and keep it simple as the intention is just to give a taste of what is available and then let you decide if you want to progress.

The 11.1.2.2 Provider Services new features document provided the following information:

Essbase Web Services

Web services are self-contained, modular applications that one can describe, publish, locate, and invoke over a network. Web services allow access to regular applications using a Web browser. Web services use XML to code and decode data, and SOAP (Simple Object Access Protocol) to transport it. Web services are defined using WSDL (Web Service Description Language).

Essbase Web Services will expose Essbase user and administrative functionality in a services-oriented (SOA) environment via Provider Services, and allow Essbase to be easily integrated with both Oracle and third-party applications

Essbase Web Services support access to and administration of Essbase applications and cubes. Essbase Web Services include the following modules:
  • Datasource
  • Administration
  • Data and Metadata Query
So basically the Web Services are using SOAP which is not my preferred method as I am much more of a fan of REST due to the ease of use and nowadays REST seems to be the more popular choice, within EPM you can see the swing towards REST with Planning, DRM has a new mobile functionality using REST and HPCM has switched in 11.1.2.4 from SOAP to REST. When the Essbase Cloud Service arrives my assumption is that it will have a new REST API but we will have to wait and see.

The Essbase Web Services are split into three modules and each are defined by their own WSDL which provide different operations, if you read the documentation you will not really find out about what the operations do.

There are examples that are available which need to be installed and are Java based, If you are going to use Java then I am not completely convinced to why you would use the Web Services over using the Essbase Java API via Provider Services but anyway to install the samples you need to run the EPM Installer.


Under the Essbase component there is an option which is not selected by default to install the Web Services samples.


This will create a new directory under the APS product directory named ws-samples.

The Java samples can be found in a client directory.


Before you can start using the samples or write your own Java code to access the Web Services you need to compile the client stubs, the client stub describes the associated Web Services resources as well as the remote procedure call that the Web Service executes.

To generate and build the client stubs then you will first need to edit the "ws-build.properties" file to match your environment.


The wsdl.base.url will be the server and port where Provider Services is running.

I recommend setting the owsm security property to false unless you want to use OWSM, unless you want to secure the web service then I wouldn’t bother with OWSM and I am going to steer clear of it as it requires a far bit of configuration and won’t apply to many, if there is enough interest I will cover it in the future but I will be surprised if there is.

Once the properties file has been completed the client can be compiled using Apache Ant, this is usually available in the following location within an EPM installation.


Ant is not usually part of the path so will need to be called using the full path or add the ant bin directory to the path.

To compile the following command can be run:

ant -f buld.xml clean compile

The build.xml is located in the ws-samples directory and contains all the tasks to carry out.

I set the following variables before running:

set WSSAMP=E:\Oracle\Middleware\EPMSystem11R1\products\Essbase\aps\ws-samples

set JAVA_HOME=E:\Oracle\Middleware\jdk160_35


set PATH=E:\Oracle\Middleware\modules\org.apache.ant_1.7.1\bin;%JAVA_HOME%\bin;%PATH%;


ant -f %WSSAMP%\build.xml clean compile



On to the first issue, as I mentioned there are three WSDL files which describe the functionality of the web service, these should be accessible through the following URLs:

http(s)://<server>:<port>/essbase-webservices/DatasourceService?wsdl
http(s)://<server>:<port>/essbase-webservices/AdminService?wsdl
http(s)://<server>:<port>/essbase-webservices/QueryService?wsdl

From 11.1.2.3.502 the WSDL URLs for the Essbase Web Services have been disabled by default, if you try and access them directly in a browser you should see:


To enable them you will need to start up the WebLogic admin server and log into Enterprise Manager through:

http://<adminserver>:7001/em


Select the Provider Services WebLogic managed server and then Web Services

Select the “Oracle Infrastructure Web Services” tab, under the Web Service Name click “AdminServicePortType” Endpoint Name and then the configuration tab.


The WSDL parameter will be set to false.

Also set WSDL to true for the following Endpoint names “DatasourceServicePortType” and “QueryServicePortType


Once set restart the provider services web application.

The WSDL URLs should now be accessible.


So now I can try and compile again.


This time it was successful and all the java files have now been compiled into classes.


You can now go and take them and start coding in Java to access the Essbase Web Services.

It is also possible to compile and run the sample programs directly from command line using Ant.

To do this you this you can edit ws-build.properties and set the sample.program property value and then execute:

ant execute

or you can specify name of sample program using:

ant execute -Dsample.program=<Sample Program>

In the following example I execute the Admin sample program which starts the Essbase Sample application, pings the application and then stops it.


I am going to leave it there for the Java side of things and let you go and experiment to see if you want to continue down that line.

I want to concentrate on the SOAP side of things as it should provide more of an understanding of what is going on with the Web Services.

Depending on what method you decide to use you may encounter a bug with the "DatasourceService" WSDL which has been around since 11.1.2.2 and still has not been fixed.

If you intend on using SOAP UI then you will certainly hit it.


When trying to load and import the WSDL you will encounter the following error:


This is because the "CubeAccess" element has a default value which makes no sense.

If you take a look at the WSDL you will see the following values for "CubeAccess":


These values do make sense but the default value is set as “DBALL


I have no idea what DBALL is meant to mean but it is not valid and is causing the error.

One workaround is save the WSDL locally and edit it but that is not always a viable option so another way is to edit it in within the web application files.

If you open or extract aps.ear, using 7zip is an easy way to open or extract the files.


Then the same for ess_ws.war


You should find the DatasourceService.wsdl


Edit the file and remove default=”DBALL”


Once edited place the file back in the war and then restart the Provider Services web application, you may need to clear the web application tmp directory.


If you view the WDSL URL the default value should be removed.

Alternatively you can go straight to the tmp directory and update the file in:

<MIDDLEWARE_HOME>\user_projects\domains\EPMSystem\servers\AnalyticProviderServices0\tmp\servers\AnalyticProviderServices0\tmp\_WL_user\APS_11.1.2.0\e52nr8\war\WEB-INF\wsdl


Restart Provider Services and the changes should be picked up, be mindful though if you patch, redeploy then these changes may be lost.

The WSDL should be valid and can imported.


As I said though this issue depends on what software you are using to access the web service, personally I have only encountered it with SOAP UI so it is probably best to see if you hit the issue before making any changes.

There are a number of different ways to test or access the Web Service, you can use the standard functionality within Enterprise Manager.


There are also quite a few browser plugin or applications that are available for browsers, I find that Chrome application Boomerang is a great and easy way to access SOAP and REST Web Services.

Basically for SOAP you just add the WDSL and the operations are automatically generated.


In the above example I had added a new request to the list operation which is part of the DataSourceService module.

The SOAP request is then populated in the PAYLOAD tab.

Now we have got to the point where test out the Web Services but when you refer to the documentation it doesn’t make life any easier.

This what the documentation has to say about the list operation:

List()
Input: UriType, URI
Output: BaseObject[]

It probably means absolutely nothing to you. if you have a look at the SOAP request above then you can see there are two input arguments and one containing urid and type.

To understand what the available values to set for urid and type you can use the operation “getTypes


The “getTypes” operation does not take any inputs so can be executed without any changes.


The response reveals some of the types that can be used in the “List” operation.

So going back to the List operation I have populated the arguments with the following inputs


So for argument 1 I have set the type as server and the name as the Essbase server.

For argument 0 I have put what I was to list and that is application.

This means in theory I should be able to return all the Essbase applications for the Essbase server – superchargedepm

I did have to make a slight modification from the original SOAP request that was automatically generated, I removed “typ” namespaces because if I didn't the response would return the following error.


Ok, the format is correct let me run the SOAP request again.


We are now running an operation which requires authentication which makes sense as we shouldn’t be able to list Essbase applications without being authenticated.

There is also the added complication that by default OWSM is enabled by default when using the Web Services.

To change this there needs to be an update to the essbase properties file that the Provider Services web application uses.

There are a number of different versions of this properties file and the correct one to update is:

<MIDDLEWARE_HOME>\EPMSystem11R1\common\EssbaseJavaAPI\11.1.2.0\bin\essbase.properties



Add or uncomment

essbase.webservices.disable.owsm=true

Set the value to true which will disable OWSM.

Nice to see to that the properties file has 11.1.2.2.500 even though it is 11.1.2.4 :)

Once updated restart Provider Services.

Right, I will try the same “List” operation again with no credentials


Well at least it is progression as the error message is now informing that OWSM is not being used but header authentication is required.

The SOAP header requires the following to be added:

<Parameters xmlns="http://context.webservices.epm.oracle/">
<UserName>admin</UserName>
<Password>password</Password>
</Parameters>

This should now pass the credentials to the Web Service and authenticate.

With the header updated the SOAP request looks like:


Now running the SOAP request looks much more promising.


In the SOAP XML response each Essbase application is returned for the Essbase Server – superchargedepm

The request can be easily updated to now return a list of databases for a defined application.


In the above example I have requested that all the databases for the Sample application are returned.


Once you understand the required format for each operation it is pretty easy to start returning information from Essbase.

As the response is returned as XML then it should be relatively simple to process depending on what mechanism you are using to access the Web Services.

In all the examples I have used I have been accessing Provider Services directly and this is not always possible due to firewalls and network restrictions, this can mean the only accessible route is through the http web server such as OHS

To be able to access the Essbase Web Services through OHS then the plug-in configuration file requires the web service information adding.


Once added and OHS is restarted the WSDL URLs should be accessible through the web server.


I am going to leave it here for today as hopefully there should be enough information to be able to get up and running and in the next part I will look into more detail of the operations that are available for each of the three modules.

Friday 18 December 2015

Simplified planning issue when offloading SSL

Recently I have seen an issue in 11.1.2.4 with simplified forms where SSL is being either terminated at the load balancer or OHS.

If you are on 11.1.2.4 and are not using any flavour SSL or have implemented full SSL then you will not come across this problem.

As the simplified interface is the way forward for planning and over time the standard interface will disappear then there is a possibility you may run into this issue so I thought I would write it up.

So let me go through an example of the problem first where SSL is being terminated at OHS, so basically you will be accessing planning over https via OHS and then OHS proxies to the planning WebLogic managed server over http.

You should not see any issues with functionality until you try to use a simplified form.


In the above example I have autosave enabled in the form settings though it is exactly the same if you click the save button, at first it looks like after entering data the data is saved but if you refresh you will notice that it has not.


Also if you try to expand or collapse members then nothing will happen.

After delving into this issue a little you start to unravel what is happening.


When saving a form or data in a cell planning attempts to post the data back to a planning servlet but as you can see it is trying to post the data back over http and not https.

Tracing the issue further you can locate the JavaScript function where the post is happening.


The script is using XMLHttpRequest which provides client side functionality for transferring data between a client and a server without having to a full page refresh so speeds up data entry in a form.

The culprit here is the URL held in the variable "enterDataServlet" which is used in many of the JavaScript functions in a simplified form.


The URL is using http instead of https and it is possible to track down how the URL is generated in a JavaServer Page (JSP)


The Java method getScheme()  returns whether the request was made over http or https, this method is well known to cause issues when SSL is being offloaded because the request to planning is being proxied over http so that is what is being returned instead of https.

As the post if being sent back over http instead of https it fails and no data is submitted.

Luckily when SSL is being terminated at OHS there is a quick and simple fix to overcome this issue.

Fire up the WebLogic admin server and log into the admin console, for each managed server there is a configuration setting called "WebLogic Plugin-Enabled"


By default this setting is not enabled, once enabled it changes the way the header information is handled through the web server plug-in and the proxied request from OHS should be returned as https instead of http.

After making the change and restarting planning it will be possible to submit data in a simplified form.


Refresh form after saving.


This time in the JavaScript code https is being correctly generated for the URL.


If SSL is being terminated at the load balancer then there is more to it than just enabling the WebLogic Plug-In setting.

The flow now will be:

Load Balancer (https) > OHS (http) > WebLogic (http)

Here is the same example when SSL is being terminated at the load balancer and the "WebLogic Plug-In" setting has been enabled.


Refresh form after saving.


The same issue that the data is not being submitted and looking at the script confirms it is using http instead of https.


Also as the load balancer is using the default https port the URL that is being generated is now set as port 80.

To resolve this issue we need to be able to proxy the SSL information from the load balancer to OHS and then to WebLogic.

The first step to do this is to add a request header to the load balancer configuration


If you are using an F5 load balancer then you can read how to add the header here

The problem now is that OHS will remove the header information that is being sent from the load balancer so it will not reach WebLogic and the same issue will occur.

To get around this there is a parameter available in the OHS WebLogic Plug-In configuration file called WLProxySSLPassThrough which by default is set to OFF.


This can be set globally or as I am doing setting it only for Planning.

Time to test again.

Refresh form after saving.


Good news the data is being submitted back to planning correctly.


In the JavaScript code the URL is being correctly formed using https and 443 as the port.

If you ever hit this problem in the future now you know the solution to fix it.