Is there a Essbase Web services - Part 1

I read about these amazing blogs in December 2015 by John Goodwin Part 1 Part 2 Part 3 & Part 4 (yes i really doubt any one can write such a detailed information about this were there is very little information from the product document)

His blog really motivated me to start exploring about this hidden gem (you will agree with once you understand the potential this service is going to offer)

What is this Essbase Web Service:

From the Oracle doc, http://docs.oracle.com/cd/E57185_01/epm.1112/aps_admin.pdf

Note: The WSDL URLs for Essbase Web Services are disabled by default. Before using Web Services, enable WSDL using the Oracle Enterprise Manager user interface. See the Oracle Enterprise Manager documentation.

For enabling the WSDL for the Essbase Web Servcies,

• Login to you Oracle Enterprise Manager http://WeblogicAdminServer:7001/em
• User Id - epm_admin 
• Go to APS -> Web Services 

• Select the 3 Web Services and enable them for the WSDL to "True"

Note: Even after setting the WSDL enabled to True, i had the issue in bring the web-services up.

I did get error message stating that 

Note: If you encounter this issue 

kindly check the solution here 

Once you are all set,Now you should be able to access the 3 web Services 

• You can also run a quick test from the EM,

• I selected the operation "getTypes" (as no inputs or login details needed)

• And it ran successfully

The URL for accessing the 3 services are,

AdminServicePortType -http://epm11124:19000/essbase-webservices/DatasourceService?wsdl

DatasourceServicePortType - http://epm11124:13080/essbase-webservices/DatasourceService?wsdl

QueryServicePortType - http://epm11124:13080/essbase-webservices/AdminService?wsdl

Really great, that we have come to this point were we have enabled and the url for the Essbsae web services are working.

Let's explore the sample java programs given along with the Oracle EPM 11.1.2.4 installation.

Note: The sample java programs will be available only if you enabled the "Provider Services Java Web Application Samples"

All the Sample Java programs are found under the directory,

Before getting into compiling the sample programs, lets go back to the product document which specifies 

Deploying Web Services

Oracle recommends deploying Web Services in secure mode with Oracle Web Services Manager

(OWSM). 

Using in OWSM mode

Note: You must also configure the Provider Services deployment in Oracle WebLogic Server to

support OWSM. 

To configure Web Services to support OWSM:

1. Stop Provider Services.
2. Navigate to the following directory: EPM_ORACLE_HOME/common/EssbaseJavaAPI/11.1.2.0/bin
3. Open essbase.properties.
4. Set essbase.webservices.disable.owsm to false.
5. Save and close essbase.properties.
6. Restart Provider Services.

Using OWSM is at-least complicated to me, but you can use it in OWSM mode too ( i got that covered of installing and deploying a service in  OWSM in this blog but that is for DRM. Yet the process remains the same)

Using in non-OWSM mode

If you are convinced in using the web services in non OWSM mode, then perform the following steps.

To use Web Services in non-OWSM mode, set the essbase.webservices.disable.owsm
property in essbase.properties to true.

Client applications should send the non-OWSM
or native Essbase user credentials using a SOAP message header in ClientMessageHandler
similar to the following:
SOAPElement el = header.addHeaderElement(envelope.createName("Parameters", "", "http://
context.webservices.epm.oracle/"));
SOAPElement el0 = el.addChildElement(envelope.createName("UserName", "",
"http://context.webservices.epm.oracle/"));
el0.setValue(s_userName);
SOAPElement el1 = el.addChildElement(envelope.createName("Password", "",
"http://context.webservices.epm.oracle/"));
el1.setValue(s_password);
SOAPElement el2 = el.addChildElement(envelope.createName("IsToken", "",
"http://context.webservices.epm.oracle/"));
el2.setValue(Boolean.toString(b_isPwdToken));
SOAPElement el3 = el.addChildElement(envelope.createName("UserNameAs", "",
"http://context.webservices.epm.oracle/"));
el3.setValue(userNameAs);



You will also need to modify your Oracle WebLogic Server deployment to use Web Services in
non-OWSM mode. by disabling the use.owsm.security=false in ws.build.properties file is located under,

Compiling the Sample Java Programs:

Glad we made to the point were we can run the Sample programs :-), lets get them compiled and run them to check what they offer out of box.

All the Sample Java Programs are under,

Some instruction for compiling and running them were found here in the buil.xml file 

Note: Make sure you have the path set for the session before compiling the java programs.

Adding the web-servcies to the OHS (Web Server)

The web-services can be added to the OHS, so we can access them via the internet and also we don't have to expose the ports to everyone else, to avoid the security breach.

Add the following lines to the file mod_wl_ohs,

A restart of the Web server is needed once the entries are added, then you should be able to access the web-services via the internet via the port 19000

Finally running the sample programs

Output of the Admin Program

Output of the Metadata Program

Output of the TestEssbaseNoSecurity Program

Output of the DataQuery Program

Buildfile: C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\aps\ws-samples\build.xml

execute:

     [echo] Started: Execution of WebService Example DataQuery

     [java] 

     [java] Data Query - MDX execution Sample 

     [java] *****************

     [java] Inserting native-security headers for native Essbase... 

     [java] MDX QUERY: SELECT CrossJoin({[New York]}, [Measures].CHILDREN) on columns, CrossJoin({[Jan], [Feb]}, [Scenario].CHILDREN) on rows, {[Caffeinated_True], [Caffeinated_False]} on axis(2) from Sample.Basic

     [java] Outbound message

     [java] Inbound message

     [java] Product Product Product Product Product Product

     [java] Caffeinated_True Caffeinated_True Caffeinated_False Caffeinated_False Caffeinated_False Caffeinated_False

     [java] New York New York New York New York New York New York

     [java] Profit Inventory Ratios Profit Inventory Ratios

     [java] Jan Actual #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

     [java] Jan Budget #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

     [java] Jan Variance #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

     [java] Jan Variance % #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

     [java] Feb Actual #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

     [java] Feb Budget #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

     [java] Feb Variance #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

     [java] Feb Variance % #MISSING #MISSING #MISSING #MISSING #MISSING #MISSING

     [java] 

     [java] Data Query - Grid Sample 

     [java] *****************

     [java] Inserting native-security headers for native Essbase... 

     [java] ------- Use Case1: Performing a DEFAULT RETRIEVE on epm11124/Sample/Basic -------

     [java] 

     [java] Outbound message

     [java] Inbound message

     [java] Measures Product Market Scenario

     [java] Year #MISSING

     [java] 

     [java] ------- UseCase1 Ended -------

     [java] 

     [java] ------- Use Case2: Performing a custom grid RETRIEVE on epm11124/Sample/Basic --- 

     [java] 

     [java] Outbound message

     [java] Inbound message

     [java] Measures Product Market Scenario

     [java] Year #MISSING

     [java] Qtr1 #MISSING

     [java] Jan #MISSING

     [java] Feb #MISSING

     [java] Mar #MISSING

     [java] 

     [java] ------- UseCase2 Ended -------

     [java] 

     [java] ------- Use Case3: Performing a ZOOM-IN on epm11124/Sample/Basic --- 

     [java] 

     [java] ~~ RANGE (1,0) to (1,0) ~~

     [java] Outbound message

     [java] Inbound message

     [java] Measures Product Market Scenario

     [java] Qtr1 #MISSING

     [java] Qtr2 #MISSING

     [java] Qtr3 #MISSING

     [java] Qtr4 #MISSING

     [java] Qtr1 #MISSING

     [java] Jan #MISSING

     [java] Feb #MISSING

     [java] Mar #MISSING

     [java] 

     [java] ------- UseCase3 Ended -------

     [java] 

     [java] ------- Use Case4: Performing a ZOOM-OUT on epm11124/Sample/Basic --- 

     [java] 

     [java] ~~ RANGE (2,0) to (2,0) ~~

     [java] Outbound message

     [java] Inbound message

     [java] Measures Product Market Scenario

     [java] Year #MISSING

     [java] 

     [java] ------- UseCase4 Ended -------

     [java] 

     [java] ------- Use Case5: Performing a REMOVE-ONLY on epm11124/Sample/Basic --- 

     [java] 

     [java] ~~ RANGE (4,0) to (4,0) ~~

     [java] Outbound message

     [java] Inbound message

     [java] Measures Product Market Scenario

     [java] Year #MISSING

     [java] 

     [java] ------- UseCase5 Ended -------

     [java] 

     [java] ------- Use Case6: Performing a KEEP-ONLY on epm11124/Sample/Basic --- 

     [java] 

     [java] ~~ RANGE (5,0) to (5,0) ~~

     [java] Outbound message

     [java] Inbound message

     [java] Measures Product Market Scenario

     [java] Year #MISSING

     [java] 

     [java] ------- UseCase6 Ended -------

     [java] 

     [java] ------- Use Case7: Performing an UPDATE on epm11124/Sample/Basic --- 

     [java] 

     [java] Outbound message

     [java] Inbound message

     [java] Sales 100-10 New York Actual

     [java] Year 100.0

     [java] 

     [java] ------- UseCase7 Ended -------

     [java] 

     [java] ------- Use Case8: Performing an PIVOT on epm11124/Sample/Basic --- 

     [java] 

     [java] Outbound message

     [java] Inbound message

     [java] 100-10 New York Actual

     [java] Sales Year #MISSING

     [java] 

     [java] ------- UseCase8 Ended -------

     [java] 

     [java] ------- Use Case9: Performing an PIVOT to destination cell location on epm11124/Sample/Basic --- 

     [java] 

     [java] Outbound message

     [java] Inbound message

     [java] New York Actual

     [java] Sales 100-10 Year #MISSING

     [java] 

     [java] ------- UseCase9 Ended -------

BUILD SUCCESSFUL

Total time: 9 seconds

Output of the DataSource Program

Buildfile: C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\aps\ws-samples\build.xml

execute:

     [echo] Started: Execution of WebService Example DataSource

     [java] ********* Datasource Sample (BEGIN) *********

     [java] Inserting native-security headers for native Essbase... 

     [java] 

     [java] ---- GET THE DIFFERENT TYPES OF DATASOURCE ---

     [java] Outbound message

     [java] Inbound message

     [java] Type : 0 - SERVER

     [java] Type : 1 - APPLICATION

     [java] Type : 2 - CUBE

     [java] Type : 3 - OUTLINE

     [java] Type : 4 - DIMENSION

     [java] 

     [java] ---------------- GET SERVER  -----------------

     [java] Outbound message

     [java] Inbound message

     [java] Server=epm11124, Version=11.1.2, Locale=English_UnitedStates.Latin1@Binary, ActiveApp/Db=, IsUnicodeType=true

     [java] 

     [java] ------------- UPDATE SERVER  -------------

     [java] 

     [java] ------------- LIST APPLICATIONS ---------------

     [java] Outbound message

     [java] Inbound message

     [java] epm11124/Demo

     [java] epm11124/Sample

     [java] epm11124/Sample_U

     [java] epm11124/Samppart

     [java] epm11124/Sampeast

     [java] epm11124/ASOsamp

     [java] 

     [java] ------------- GET APPLICATION ----------------

     [java] Outbound message

     [java] Inbound message

     [java] Fetched AppName: Sample, Type: NON_UNICODE, StorageType: MULTIDIMENSIONAL, State: LOADED

     [java] 

     [java] ------------- CREATE APPLICATION -------------

     [java] Outbound message

     [java] Inbound message

     [java] Created AppName: Sample1, Type: NON_UNICODE, StorageType: MULTIDIMENSIONAL, State: LOADED

     [java] 

     [java] ------------- UPDATE APPLICATION -------------

     [java] Description of 'Sample1' (Before UPDATE): 

     [java] Outbound message

     [java] Inbound message

     [java] Description of 'Sample1' (After UPDATE): Updated this app at Wed Sep 07 17:17:16 IST 2016

     [java] 

     [java] ------------- RENAME APPLICATION -------------

     [java] Outbound message

     [java] Inbound message

     [java] Renamed application 'Sample1' to 'Sample1R'

     [java] 

     [java] ------------- DELETE APPLICATION -------------

     [java] Deleting AppName: Sample1R

     [java] Outbound message

     [java] Inbound message

     [java] Successfully deleted AppName: Sample1R

     [java] 

     [java] ---------------- LIST CUBES ------------------

     [java] Outbound message

     [java] Inbound message

     [java] epm11124/Sample/Basic

     [java] epm11124/Sample/Xchgrate

     [java] epm11124/Sample/Interntl

     [java] 

     [java] ----------------- GET CUBE -------------------

     [java] Outbound message

     [java] Inbound message

     [java] Fetched DbName: epm11124/Sample/Basic, Type:NORMAL, CubeAccess:null, State: LOADED

     [java] 

     [java] ---------------- CREATE CUBE -----------------

     [java] Outbound message

     [java] Inbound message

     [java] Created Db: epm11124/Sample/Basic1, Type:NORMAL, CubeAccess:null, State: LOADED

     [java] 

     [java] ---------------- UPDATE CUBE -----------------

     [java] Description of 'Basic1' (Before UPDATE): 

     [java] Outbound message

     [java] Inbound message

     [java] Description of 'Basic1' (After UPDATE): Updated this db at Wed Sep 07 17:17:27 IST 2016

     [java] 

     [java] ---------------- RENAME CUBE -----------------

     [java] Outbound message

     [java] Inbound message

     [java] Renamed cube 'Basic1' to 'Basic1_R'

     [java] 

     [java] ---------------- DELETE CUBE -----------------

     [java] Deleting DbName: Basic1

     [java] Outbound message

     [java] Inbound message

     [java] Successfully deleted DbName: Basic1

     [java] 

     [java] ********* Datasource Sample (END) ***********

BUILD SUCCESSFUL

Total time: 27 seconds

I have shown all/most of the sample program outputs to express the capability/operation that can be performed using the webservices. Of course we can edit them as we want and perform the needed operations.  

If you are not much of a Java fan, then you could think about using windows Powershell, i will post a customized Powershell program using the Essbase web-services in the next post.

Stay tuned, this is quite interesting!!!

Again my big thanks to John Goodwin, who helped me in understanding and get this working. Thank you Sir!!!