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

OAC - Switch Classic to Modern - Trick

Back to back posts today. But, I am excited and wanted to share this cool hack....Not sure if anyone has figured it out....

I did a blogpost on OAC new / modern interface and few features that I really liked. You can find my blogpost here. A good friend (became friends at Kscope18) Vijay Kurian posted in his blog at theunlockedcube about the Essbase Cloud features Part I and Part II and he has shown how to switch from classic interface to modern interface

For some reason it didn't work for me in the pormotional OAC instance that I was using. However, I was able to switch based on what I poseted in my previous blogpost

In general If you want to switch from Classic to Modern, follow the below screenshot

However, for some reason that didn't work for me and I didn't knew why

What I did might sound pretty silly but now I have the whole new Modern Interface that i can use without having to rely on the Deep Dive environment that I was using earlier. (That's Oracle environment and I don;t know till when that env will be live)

The OAC classic link will be https:///essbase/ui/ as shown in below screenshot

To get to new interface replace essbase/ui with essbase/jet.

https:///essbase/jet/ will be your new modern interface

 Isn't it cool...It's awesome!!!

OAC - New Interface

At ODTUG Kscope18, Oracle has revealed new features and groundbreaking updates that are going to come in the next release in OAC. Have you ever wondered what it's going to be

I was so excited that I kicked-off the promotional offer and fired up my OAC instance. But, I was surprised and felt sad after looking at the instance that is in public cloud and the instance that is going to come in the next release. Not sure if I am missing anything here which will enable me to access the newer interface / newer instance

The new interface looks sleek and looks a lot better than the previous interface. I am using the instance as parts of Hands-On lab at ODTUG Kscope18. Not sure till when this environment will be available. I will update this blog and make it more comparitive with what is available right now in public cloud

New JET UI. I don't know if that is the right way to call it but that's what it says when you fire up the OAC instance new interface

Less clutter and way better. When you click on actions at a database level, you would notice two new items. "Inspect" and "Analyze"
"Inspect" gives information about your database. The most important being the audit trail, statistics, partitions, filters and many more

What I really liked is the ability to execute the MDX scripts. With the ability to perform calculations using MDX INSERT and export the data using MDX EXPORT, it really makes sense to execute the MDX script.

"Analyze" on the other hand can be considered as smartview lite. You would have understood it by looking at the error message that popped up below.

This is not a fully functional SmartView and can be considered as a lite version to do some basic testing and also the ability to execute MDX scripts. If you are on Mac and doesn't want to install SmartView, this could be one way. However, it was announced at Kscope18 that office 365 will be supported for SmartView. This is really a very good news.

 If you look at Jobs, the type of jobs that you can execute has been expanded. You have the ability to run MDX script, Groovy script (this is new), ability to build and clear aggregations. Take a look at the list below

I have to explore more on "Run Groovy" and where that would be applicable. I believe this is the first time Groovy has been added to Essbase. Till now, Groovy has only been permitted to Planning cloud

Now let's look at files. Files could be anything from outline (otl), calc scripts (csc), report scripts, maxl scripts, mdx scripts, grrovy scripts, text files (csv, txt)
I believe some of the extension of these file depends on the type of application that you are trying to build.

There are two ways that you can upload the files to an OAC.

• You can upload it at an APP/DB level by navigating yourself from "Inspect" > Click "Files" tab > "Upload files" and select the files that you would want to upload
• Another way is to click on "Files" at the top and upload the files from there. I would prefer this was as this gives more control of where I can upload the files to. You would notice that there is a folder called "Gallery" and you can probably upload templates for a reference and use them later

Note of Caution
Remember that these features are not live yet and it may change from the time I am writing this to actually when it will be available for you to patch up to be able to see all these neat features

Now, let's take a look at one example. Below is the main screen. We will focus only on the applications for now

Navigate to applications folder and navigate down till the database level. "Upload Files" will let you upload files. When you selct the files, wait for it to show as per the below screen and then proceed further. It will come up with X mark if the format is not supported. Only the supported format gets uploaded. I am still trying to figure out the format for groovy scripts

You would see below screen after the files are uploaded

Have you noticed the file type? Maxl, MDX, Groovy. Isn't it amazing!!! I really love it...

Things I felt would have been better

• There is no way to cancel. Once you click "Close", the files get uploaded
• If the file already exist, it will show X mark and doesn't upload
• If you want to delete any files, you have to do it manually. There is no way that you can select multiple files and delete

I wish all the above would change eventually go away

In the next blog posts, I will try to migrate the application I used for my kscope18 presentation in to OAC and see how things would work out. I am going to look at more use cases of MDX and how they can be used to become a standard langague in OAC

OAC - Finally Up and Running

I had few hiccups while setting up my OAC instance. Learned a lot from these mistakes and would always recommend to read the document first and understand the pre-requisites before you get your OAC up and running

There are various blog posts that talk about how you can get started with OAC. The blogs that helped me to get started are Blog 1 (blog post at Rittman Mead and this post also has information about how to setup the https for your OAC instance) and Blog 2 (This particular blog helped me with the access rules that need to be enabled to get things to work)

Apart from these, the two things where I was stuck and probably would help others if you face the same issue

• Even though I have created DBaaS and have started the service, my OAC still pop-up with the message that the pre-requisites are not met. All I did was to open up the ports (http, https, dbconsole, dblistener) and post that I was able to create the OAC instance. From your

From your Dashboard, do as shown in the below screenshot

Click on "Open Service Console" and perform the step as shown in the below screenshot

Click on "Access Rules" and ensure that all those marked are enabled as per the below screenshot

Once this is done, you should be able to launch the "Create Analytics Service"

• While creating the OAC instance, remember to use the rest endpoint URL for storage directly copied from the accounts section. The format would be {REST endpoint URL}/{container}. I have named my container "beyondessbase". So, my storage container format would be {REST endpoint URL}/beyondessbase. Remember that the user id and password would be your cloud account.

You can get the REST endpoint of your storage container navigating as below

Click on the hamburger icon and click on "Dashboard". You would see all your created instances below

Click on "Open Service Console" and you would land to as below

Click on the "Accounts" and you would get the REST endpoint. This is the rest URL that you should use followed by the container. in my case, it's the {REST endpoint}/beyondessbase1. Though I have two containers, I was using beyondessbase1 for my OAC instance

I also had another issue of using http for my OAC instance which I don't like it. Becky has helped with a link to a post on how to fix that which can be found at the link blog post mentioned before as Blog 1

After all that is done. I am finally up and running with OAC. I will shut down the instances, for now, to use it tomorrow. Not sure what challenges I will encounter once I fire up my OAC instance tomorrow. Till then, Goodbye for now...

Remember to select the type of OAC that you would like to have. It can be Standard, Data Lake or Enterprise Edition

Happy Learning!!!

Achievements...Opportunities...2018

Sometimes there would be a phase in your life, where you have to break all your limits and have to have confidence in your ability to move forward

I took the decision in 2018 that I will break and go beyond my limits and I am surprised by the results. Here are a few achievements that I have

1. Applied at AIDP (Artificial Intelligence Development Programme) at Genpact. a 6-months program to jumpstart the career in AI. Unfortunately, I couldn't get selected in the first round. Would be applying again at the end of this year
2. Mentored students at SmartCity Hackathon at Vardhaman College in the Semi-Final stage which is organized by Telangana Government. I would like to thank and give a special mention and shout-out to Madhu Vadlamani for inviting me and the HackMania Team
3. I will be speaking at ODTUG Kscope18. One of the biggest and premier developer conference that attracts Oracle experts around the world organized and conducted by ODTUG
4. My submission to UKOUG Application cloud conference 2018 was rejected. However, I got an opportunity to publish an article at UKOUG / Oracle Scene Magazine. A great opportunity for me and I will be working on this
5. I am in the semifinal stage of the Dat Incubator Fellowship Program. Free 8-week intensive program for Master / Ph.D. students who want to enter into the industry. I applied on the sole basis that I have a good amount of experience with Bachelors Degree. There are two more rounds which involve a project to showcase my programming and domain skills followed by a technical interview. I am really excited towards it
6. I have submitted an abstract to ODevCYatra organized by AIOUG (All India Oracle User Group). If selected, I will be presenting the session in Hyderabad on 10th of July 2018
7. Submitted an abstract to Oracle Open World 2018. Selected abstracts will be announced in June and if I get selected, I will be speaking at the conference. Oracle OpenWorld is happening in San Fransisco, CA from Oct 21st - Oct 25th
8. Currently in the ideating phase of two products with Meenakshi, Madhu Vadlamani, and Vijay

Too much to focus on but can't let the focus go away from the family

Oracle MOOC

You might have heard what MOOC is. If you Don't, Its called Massive Open Online Courses. With platforms like KhanAcademy, Stanford Online, Coursera, edX, Udacity, Udemy becoming mainstream and providing valuable education to the needy for those who cannot afford a university education. A platform like these have provided various courses in many categories
Following the same lines, Oracle Developer Community has launched few MOOC's and the one that is very interesting to me is the Oracle Intelligent Bot Service. I am pretty packed up right now and would probably take some time for me to take a deep dive into it

If you are interested, below are the resources that can help you started

Oracle MOOC in Intelligent Bots: course link

Get started with chatbots: http://www.ateam-oracle.com/getting-started-with-chatbots/
Chatbot podcast: Part 1 | Part 2
A Primer: Oracle Intelligent bots: PDF Link
Develop and Deploy your first bot: PDF Link

Oracle developer blog on chatbots: https://blogs.oracle.com/developers/chatbots

I have few ideas and projects which I will be working on to build my portfolio on how these technologies can bring value-add to EPM World

Keep checking back for more information

Kscope18 - Here I Come!!!

I got to know about ODTUG from 2009 and have been following it since then. Every year Kscope happens, I assure myself that someday I will present but T didn't had the confidence in myself to dare and submit an abstract. What If it won't get selected

I have decided to myself that this year, I will overcome that fear in myself and work on four things that will enhance and improve my career

1. Submit an abstract to Kscope18. I have submitted two abstracts and both of them got selected and I thank ODTUG selection committee from the bottom of my heart to give an opportunity to be present. I will try my best to do justice for the opportunity that you have given to me
2. Continue blogging. I took a break for two years when my wife became pregnant in 2015 to take care of her and followed by taking care of my wife and daughter. The first year was really tough handling the kid by ourselves and decided to dedicate completely to my family
3. Contribute enough to become capable of Oracle Ace Associate
4. Become knowledgeable in Machine Learning (There's so much to it and I would like to start with Machine Learning)

Being able to get selected for ODTUG Kscope18 is one feat as this is going to be my first Kscope. But, being the first person from Genpact is the one which I will cherish for a lifetime. This has been well received by senior management at Genpact and I feel really happy and proud about it. This wouldn't have been possible without ODTUG and the selection committee. I thank them from the bottom of my heart as they have made this a memorable experience to me.

What will I be presenting?

An alternate approach to ASO Calcs - MDX Scripts

This session focus on how you can use MDX queries for calculations. Procedural calcs have been for a very long time and have become the de-facto standard for calculations in ASO. The limitations have been addressed using a mix of MDX member formulas and Procedural calcs

With 11.1.2.4.010 patch, it has become very easy to format the data export from MDX queires. Gary Crisci has done a session at kscope17 titled " I've got an MDX script for that". In this session, I will talk about how you can use this feature and can build calculations using MDX queries. I will also talk about few paragidms / best practices on how you would do in BSO and how you can do the same in ASO

Intro to machine Learning using Oracle Data Viz

This one was a surprise to me as well. I wanted to get out of my comfort zone and wanted to present something on which I have started learning during my free time. It's very difficult to keep up with office, family, EPM cloud and Machine Learning. This session focuses on how you can kick start yourself with Machine Learning with Oracle DVD. This session will be about Machine Learning, the Machine Learning process from model building to prediction and steps involved in it and a live demo using Oracle DVD.

Sneak peak!!! Oracle DVD Machine Learning capability uses python. So, if you are familiar / unfamiliar with python, the Machine Learning process and approach will fairly be similar to how you do in Python / any other programming language

See you all at Kscope18!!!