Oracle Integration Cloud Service for EPM II

This post continues on from an earlier one, which can be found here. From a blank canvas, we can just drag and drop the REST connection from the Invokes palette on the right. On the wizard that opens up, you get the option to specify which REST command you would like to execute. To get substitution variables from PBCS, Oracle tells us what the relative URI needs to look like. In the screenshot below, I’ve added the “REST” invoke action from the palette to the canvas. I’ve also added the desired action to the step.

We need to add parameters to this action. Again, the PBCS REST documentation tells us which parameters are available for this “GET” action.

We can add these to our REST invoke set up as well.

Now, we need to set up the response. The response back from PBCS is going to be in JSON format.

We can add in-line JSON to allow OIC to understand how the response is structured.

The inline JSON would look like this, for substitution variables.

Before we add the write FTP invoke step, we need to be able to loop through the responses that come back from the REST execution. For this, we can use a simple “For Each” loop function from the palette.

If we drag and drop this step into our flow, a wizard pops up. We need to now add the repeating element into the wizard. The “items” response can be used as the basis for the loop. If we think about it, this makes sense, as there will be multiple variables potentially, in an application.

We now also need to make sure we add an action for our loop to execute. This will be our write step, to the FTP invoke.

We can then specify the action we would like to perform.

The next part was a bit tricky to figure out. We need to define the layout of the target file. This is essentially what I think the “define schema” step does.

Thankfully, we can do this with a simple CSV file. I created a template which mimics what I would like the extract to look like.

Based on the file, OIC understands the desired structure of the output:

As you can see, this is how OIC internalizes the file structure.

The next step is to map the JSON responses from the loop, to the file.

At this point, this is what our integration looks like.

I am going to wind up this post, as it seems to be getting a bit too long. In part 3, we will go over adding the finishing touches to the integration, executing the integration, and monitoring the results.

EDIT: Parts 1 and 3 can be found here and here.


About Vijay Kurian

Known as the Clem Fandango of EPM consulting, Vijay Kurian has been developing enterprise solutions for companies for the last 12 years (increment years if reading post-2015). Having worked with Essbase, Planning, DRM and other assorted technologies during that time, he’s made the frankly, average decision, to write about them. He is, surprisingly, an Oracle ACE Associate. He hopes to contribute frequently to US Weekly, People and Sensible Chuckle magazines on improving reporting solutions, creating master data management systems and zzz…

Leave a Reply

Your email address will not be published. Required fields are marked *