Using On-Prem Planning REST API to Import Metadata

I finally found some time to blog after KScope16 :-)

KScope is always an inspiring event for me. I actually enjoy attending other sessions than FDMEE ones. And this is what I did!

It's being some time since I was curious about REST (this is what I wish to do but not possible with little one running all the time and trying to destroy whatever he finds...)
For that reason I tried to attend REST sessions at KScope, even in the DB track!

I have to highlight the one delivered by Jake Turrell. A very interesting session about using REST API in Groovy to import metadata/data from external sources into Planning. That was a good session, clear, concise and very well presented.

This post is not about introducing REST which is I think is very well covered in the other blogs. This post is just to provide some idea about how we can invoke REST API methods from FDMEE in order to communicate with other parties either as a source or as a target.

However, I found this descriptive image answering to someone asking about REST Vs. SOAP. I could not skip this in today's post :-)

The Requirement
Going back to what gave me the idea for this post, all started some time ago when I had a chat with GJG about a requirement where REST could be a good solution to build a custom process that allowed importing Planning metadata from FDMEE.

Which was the requirement? We had a customer using FDM Classic to load new accounts and entities into Planning before data was loaded. In that way they didn't get errors when target members didn't exist in the application. In other words, no partial loads in Planning.

Let's have a look at old solution BTW:

As you can see above, FDM Classic was launching an ODI scenario which was actually performing some operations and finally loading new metadata into Planning. We could have also used OLU (Outline Load Utility) but there were many security restrictions and they liked ODI as other processes were also in place (if you are wondering if they had full ODI license, yes they did)

So time for migration, time for FDMEE. Keep same solution?

• High level? YES
• Technical solution? NO

Why keeping high level solution? seamless metadata import, robust error capturing, transparent for end-user, etc.
End-users are interested only in seeing the gold fishes along workflow. My data is imported, my data is mapped, my data is valid, my data is in Planning. That's all.

Looking at the technical solution, do we really need ODI to load metadata from a flat file? Even if they had full ODI license, we agreed to remove that piece of the cake. ODI was not being used as expected so no reason to keep it.

Was OLU a good replacement? Maybe, maybe not. Couple of points that didn't make me happy at all:

• FDMEE and Planning servers were different so Planning utilities had to be executed remotely or installed in the FDMEE server.
• Not a clean solution in terms of error capturing so hard to integrate with FDMEE as I wished

John was showing in his blog how to use REST and how REST API is also available for on-prem Planning. It was not clear at that moment if that was supported but we got confirmation from Oracle so green light! Thanks John for laying the first stone.

Decision made, time to play!

- Francisco: Hello REST, my name is Francisco and I want to use you from FDMEE. 
- REST: Hello Francisco, we are going to have fun for the next couple of days. 

REST API for On-Prem Planning
I'm not going to reinvent the wheel so visiting John's post about Planing REST API is a must to understand this post.
Already read it? Then you are probably now familiar with Planning REST API and how to use it. I was.

As always, I recommend to start from high level approach and keep diving into lowest levels until you finally have the technical solution. And that is what I did, start from the high level solution design:

The diagram above does not show all details and look simple at this stage.
When you start developing the orange boxes then several questions comes up:

How do I invoke a REST API method to execute a Planning job?
How do I check Job status?
How do I read response from REST API?
How do I parse the response to get job id, result, etc.?
...

If we focus on today's topic, we could add some light by defining the process as:

1) Invoke REST API method to run a Planning job to import metadata
2) Read and parse JSON response to get job details
3) Invoke REST API method to check status of job 1) (until it is completed)
4) If job succeeded, invoke REST API method to run a Planning job to refresh the cubes
5) Read and parse JSON response to get job details
6) Invoke REST API method to check status of job 4) (until it is completed)

Basically a game of invoking & reading response. So let's play that game.

Planning Jobs in the SUI
New to the SUI (Simplified User Interface)? Have a look, this is how cloud services interface looks like. The good thing is that SUI is also available in on-prem (Planning so far). You can feel like in the Cloud :-)

It took me some time to get used to how things are done now but once you get it, it's easy to remember.

I just need to create one job for my testing. A job to import new entities into Planning from a CSV file. The idea is to have a metadata file in the Planning Inbox/Outbox folder so the job can import it.

Metadata file is simple:

Playing around with REST Web Client
Now that we have created our job it's time to play with REST. This is essential to get familiar with it. I wouldn't start coding if I don't understand how it works.

For that purpose, I'm also using the RESTClient for Firefox, easy to use and very helpful.

Invoking REST API method to run a Job
API methods for PBCS accepts JSON body with parameters but on-prem does not so far. You can easily create the following request and see the error returned;

A workaround is to pass the body as part of the POST URL:

As we can see, the JSON response contains the job status and job id (132). This last value will be used to invoke the GET method that returns the job status:

The new base entity FDMEE_Entity_REST has been created in Planning: 

Using FDMEE as the REST Client
John showed how to invoke REST API from PowerShell scripts, Jake did the same from Groovy and now it's time for Jython.

I first need to highlight that we are restricted to Jython 2.5.1 in current FDMEE release (11.1.2.4.200). Luckily, from Python 2.6 onward we had several enhancements. As you can imagine some of them were related to HTTP requests and JSON.

Unluckily we have to stick to 2.5.1 so we can't use any of these enhancements. The sample code below shows specific method using native urllib2 module and Jyson, a third party JSON codec for Jython 2.5.1.

Steps to run a Planning Job
1) Import necessary libraries/modules

2) Build URL

3) Build Body (parameters as a encoded dictionary)

4) Add Basic Authentication Header

5) Invoke REST API Method (open URL)

6) Read and parse the JSON response

 7) Invoke REST API method to check Job status

I have logged all output so you can see details like JSON Response.

If you want to see more details about job execution you can navigate to Import and Export Status in SUI:

Refreshing the cube follows similar approach so I let you play with that.

This is all for today. I hope you found this post useful and got the basis to build your own solution.
I must say that with FDMEE now providing Hybrid integration, REST is a very good approach to make both Cloud and On-prem interact and will help you to meet most of your requirements.

You just need to be creative, REST will do the rest.

Take care!