Skip to main content

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!!!

Comments

Popular posts from this blog

PBCS/EPBCS - ASO exclude shared & Dynamic

As you all are aware that Oracle releases patches to EPM cloud every month (EDMCS is released every 2 months) and the patches are applied on first-week of Friday in Dev and third-week of Friday in Prod I did a post long back about a challenge that I have faced in on-premise and how I have addressed that. New functions were released in Nov-2018 release of PBCS. Below is an excerpt from the readiness document. You can find the document here New Aggregate Storage Functions in Calculation Manager The following Aggregate Storage functions have been added to Calculation Manager. These functions filter the shared and dynamic members from the Point of View (POV). The functions can be used in the POV of an Aggregate Storage custom calculation or allocation. @FilterDynamic(Dimension Name, Member Name) removes all dynamic members from the list of members @FilterShared(Dimension Name, Member Name) removes all shared members from the list of members @FilterSharedAndDynamic(Dimensio

EPM Cloud Tips & Tricks - #1

The first EPM cloud product was released in 2014 and it's been six years till date. I was recently part of an FCCS implementation project. I know what you might be thinking. Coming from completely essbase and planning background and been working on it for almost 12 years and doing an FCCS project? Well, it turned out that way and it was a change for me too than being in my comfort zone and took it as a challenge.  I have been very busy for over the past one year and I didn't really had a chance or time to get back to my blogging and sharing my knowledge. The project finally went live and I am going to share my bit of learnings. Some of it you might alread know The first tip is going to be an easy one and those who have worked in FDMEE / Data Management in the cloud, you might already know it. But, this is very important when it comes to FCCS as zeroes are valid from Balance Sheet standpoint Data Management by default doesn't load zeroes. Below is a excerpt from the document

EPM Cloud (PBCS/EPBCS/FCCS) - Report Bursting & Reports scheduling

The first product of Oracle EPM cloud was launched in 2014 and it's been 5 years and over the course of these 5 years, Oracle has every EPM product in cloud to what is available in on-premise. With so many products under the EPM belt each with its own functionality, there is a real need to have a single unified reporting tool that can handle all your reporting requirements across all your EPM products at one place. EPRCS (Enterprise Performance Reporting Cloud Service) is the Oracle direction to address all the reporting needs for any organization of any size. If your team is responsible for the management, narrative and external reporting with the ability to author, collaborate and a solid approval process, you definitely have to consider implementing EPRCS at your organization EPRCS can connect to your EPM Cloud products, Essbase cloud and also to your Fusion Applications Essbase app. It addresses all your financial, management, narrative and disclosure reporting. I am not goi