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