Essbase Cloud - REST API

REST API is not something new in Oracle EPM world when it was nunvieled and what runs behind most of the Oracle EPM product portfolio and that is the direction what Oracle says and probably you should hear it and start looking in to it

When OAC was released in March 2017 and when I took a look at it three months later, I always wondered if there were REST API's that powers EssCli and I was right. Oracle at Sunday Symposium did mention that and showed a demo of how the REST API's can be used for dynamic data loading and I was thrilled.

I was even thrilled when my good friend Vijay Kurian gave a session on Essbase REST API. You can download his presentation once it is available for everyone from ODTUG. Being a presenter and attendee, we get early access to the presentations and I feel so much previleged about it

If you want to get started right away and want to go and look at the documentation, it's not available yet. This is where it becomes difficult to know what all operations I can perform with REST API and what should be my payload format for my POST requests. What I am going to show in this session is how to go down and take a look at the endpoints and what is the format for the REST endpoints. Suresh T in the blog essbaselabs have shared the REST endpoints and JSON for outline editing. This gives a sneak peak to what all operations (Every operation) you can perform using REST API

Kumar Ramiyer hinted that every click in OAC is a REST API. Considering that, every single operation that you can perform in UI can be customized / extended / you can build your own operational UI.
This is very exciting. But, how do you start and where to start? John Goodwin (inlumi), Jason Jones (Applied olap) and Victor (Oracle) have shared their information about REST API here, here and here. Also, look at Suresh T post in essbaselabs.

If you have past experience with Essbase, you may have heard about C API, VB API na dJava API which extends the existing functionality and gives you the ability to build a custom application using these API without which it would not have been possilbe otherwise.

There are many tools available on how to get started with REST API. Postman is one such tool which can be very helpful. If your organization has restrictions for installing any third-party software, you can use various REST Clients available as add-ons for most of the browser. I personally use RESTClient with firefox Quantum Ver 60.0.2 (64-bit) and works beautifully.

With no documentation available, how will I ever know about my REST endpoints? That's a valid question. There are two ways that you can know. None of them is my discovery

1. You can use the url format 'http://your_essbase_url/essbase/rest/v1/application.wadl'. If you don't know what WADL is, check out this link from Wikipedia. This shared by Vijay Kurian at ODTUG Kscope18 in his presentation
2. You can use Fiddler to track every single action of yours and based on that you can capture and define your REST endpoints. This is cool right?? Thanks to Kumar Ramiyer for hinting this out

I am going to kickstart now with the [2] and show you how you can use fiddler to track the OAC operations and use that to build your own end-end process using your choice of scripting language and REST API

You can install fiddler from this link. Installation is pretty straightforward.
When you open fiddler, it will automatically track the traffic. You can turn that off first by clicking File > Capture traffic

Once you have turned off "Capture Traffic", make sure that you have "stream" and "Decode" on.

Minimize your fiddler and firefox browser, click on "Any Session" and select the firefox browser and enable the traffic as File > Capture Traffic.
Before

After

Somehow, it didn't work. So I have closed all the browsers and opened only chrome. It probably has to do with the default browser setting I believe. If you get it to work with Firefox, let me know.

Open your OAC instance and you would be directed to the usual link. Look at my previous post here where I have shown how to switch from regular UI to JET UI (Modern Interface). If you are all set, you should something like below

Marked at the top block is the REST API link what is tracked with Fiddler. Bottom is the list of applications returned. The link captured is the home screen of the OAC. You might have a doubt that is this really real....

Let's create an application in the UI and see what is tracked in fiddler
Click on "Create Application"

Provide the application name, database name, expand the Advanced options and change if you like to change anything

Once the application is created, you should see that application reflected in the home screen

Below is what is tracked in fiddler

Creation of application is a POST request. Marked in the top is the POST request

URL Link
http:///rest/v1/applications
JSON Payload

{
   "applicationName":"test1",
   "databaseName":"test1",
   "databaseType":"B",
   "allowDuplicates":"false",
   "enableScenario":"false"
}

Isn't it cool? I would say this is awesome.

Now let's try the same using the REST Client in firefox and see if we can create another application
Just like any other RESTClient, you can setup the authentication and any custom header (I have set accept to application/json) and used the same URL that is displayed in the fiddler to show that both are the same. Below is how it would look like in RESTClient in firefox

Scroll down and click on "Response" and you should see the applications

 If I scroll even further, it will show the "test1" application that I have created

Now, let's try and create an application using the same format as what is tracked in the fiddler and see if the application gets created
For application creation, you have to change the method to POST
You have to provide JSON payload in the body section
It should look something like below

you would get "unsupported media type" error if you do not specify "content-type" as "application/json"

If you get a 200 OK response, then the application creation is unsuccessful. You should see the application once you refresh your home screen

Something sounds wierd right. in my JSON payload, I have specified the databaseType as A considering that it would create an ASO similar to the databaseType B for BSO. But, unfortunately it created a BSO cube only
I was curious to know and manually created the application from OAC and looked at the fiddler trace

Strange isn't it. databaseType for BSO is B but for ASO it is ASO. I wish there was a standard code for this

How does we delete an application. For deletion, you would use a DELETE method and use the same URL as above with an addition of your application name
http:///rest/v1/applications/test2
you do not need a JSON payload to delete an application

Don't get confused if the response is 204 No Content as the DELETE will not come back with an output. If you get the response code as "204 No Content", you should be good

Let's refresh the OAC home screen and see

This is just the beginning.


The only question I have now is will it only be REST API or will there be support for Java API. I am not a programmer so I don't know how it works under the covers. Does the REST API hit the JAVA API internally? I do not know

Take a look at essbaselabs as Oracle team is doing brilliant work in sharing information
 I will stop here. You can use the approach I showed here to track any request and let me know if you have a showstopper / I have said something wrong

It took my whole Saturday to write this post. I should have planned it in a better way and have to speed up my writing

In the next post, I am going to talk something outside of essbase by giving an introduction to Jupyter notebooks. There is a reason why I would like to cover and it's a surprise and you have to wait till my next post.

Happy Learning and Sharing!!!