Planning REST API

If you have had any involvement with Oracle PBCS then you will know there is a utility called EPM automate that allows to remotely connect to a PBCS instance and run a whole host of tasks such as run rules, refresh the application, import and export metadata.

The utility is built on top of a set of REST (stands for Representational State Transfer) APIs using Java and is run from command line, the REST APIs can also be accessed outside of the utility using a variety of ways such as a web browsers or pretty much any programming language.

REST is not a new technology and there is lots of information out there on the internet if you want to read up and gain some knowledge.

Here is a brief description from the documentation:

REST describes any simple interface that transmits data over a standardized interface (such as HTTP) without an additional messaging layer, such as SOAP. 

REST provides a set of design rules for creating stateless services that are viewed as resources, or sources of specific information, and can be identified by their unique URIs. 

RESTful web services are services that are built according to REST principles and, as such, are designed to work well on the Web. Typically, RESTful web services are built on the HTTP protocol and implement operations that map to the common HTTP methods, such as GET, POST, PUT, and DELETE to retrieve, create, update, and delete resources, respectively.

As the majority of planning customers will either be on-premise or using alternative cloud provider I was interested to see if that REST APIs have actually made it to 11.1.2.4 and if so what is available.

Before I start I will give the usual disclaimer and point out that the REST API is currently only documented for PBCS so I am not sure what you are about to read is supported yet which means  this is all for educational purposes :)

The URL structure to access planning REST resources is:

http(s)://server:port/HyperionPlanning/rest/{api_version}/{path}

To test whether the API is there in 11.1.2.4 there is a REST resource which uses a GET method and will return information about which REST API versions are available and supported.

The resource is accessed through /HyperionPlanning/rest and should return a response in JSON format, it may be worth having a read up on JSON if it means nothing to you.

So let’s just try and access the resource in a standard browser.

Nice, a response has been returned so the API is available in some form in 11.1.2.4.

The JSON response may not be so easy to read but this is not a problem as there are a variety of REST client addons available for browsers.

For the examples I am going to use the RESTClient addon for Firefox which is extremely simple to use.

Enter the URL for the resource and the JSON response will be generated in a readable format.

The response parameters that are returned are defined as:

Deciphering the response indicates for the latest active version the URL to use for the resources will be:

http://server:port/HyperionPlanning/rest/11.1.2.4

If you are using PBCS it is currently

http://server:port/HyperionPlanning/rest/v3

Let us look at another available resource to return a list of available applications.

This time the response is an error message and this is because I am trying to access a resource that requires authentication.

The majority of resources require HTTP basic authentication credentials to be supplied in the header which is no problem using the RESTClient

This will add the authentication credentials to header for all requests.

After adding the authentication header the response now returns the applications.

I think the response is clear enough to understand which applications are available, the dpEnabled parameter indicates whether the application supports decision packages which are part of the different planning modules such as public sector.

If you are using PBCS then the authentication is a little different which the documentation does not specify, maybe I will post an update soon.

Anyway, I thought I would show how to simple it is write a bit of code to work with the REST APIs, for my examples I have chosen PowerShell just because nowadays it is pretty much available on every client Windows machine and there is an IDE available so it will be easy to test, you can work with the REST APIs in most programming languages so pick the one that you are most comfortable with.

The first part of the script handles creating an encoded password for the HTTP header, once you have an encoded password this could be read from a file so no need to keep generating it.

Starting in Powershell V3 there is a included cmdlet called Invoke-RestMethod which sends http(s) requests to RESTful services and is extremely simple to use.

So with a small amount of code the list of available planning applications can displayed or stored.

If you are going to use the Planning REST API the main activity will be around the executing of Jobs.

The supported job types:

• RULES 
• RULESET 
• PLAN_TYPE_MAP 
• IMPORT_DATA 
• EXPORT_DATA 
• EXPORT_METADATA 
• IMPORT_METADATA 
• CUBE_REFRESH 

There is a resource available to retrieve all the jobs that are available in the application using the following format:

/HyperionPlanning/rest/{api_version}/applications/{application_name}/jobdefinitions

This will return each job type and job name that is available in the requested application

If you only want to return certain job types then you can use a query parameter in JSON format:

q={“JobType”:”JobName”}

Running a job once again has its own resource which this time uses the POST method:

/HyperionPlanning/rest/{api_version}/applications/{application_name}/jobs

In the body of the request you post the Job Type and the Job Name

This is where 11.1.2.4 planning and PBCS seem to differ as in the PBCS version the body of the request accepts JSON but this doesn't seem to work in 11.1.2.4.

I will go through examples of using this resource starting with refreshing the application.

In PBCS you can use the format of:

{“JobType”:”JobType”,”jobName”:”JobName”}

In order to be able to execute jobs in 11.1.2.4 I used the following:

JobType=JobType&JobName=JobName

Once submitted this generated the following response:

The response parameters that are returned are defined as:

As you can see the status and descriptive status indicate the job is still running, to check the status of a job then there is another GET resource available using the following format:

/HyperionPlanning/rest/{api_version}/applications/{application_name}/jobs/{jobID}

This time the response confirms the refresh job was successfully completed.

If you look at the job console in planning you will also see the job information.

To achieve this using PowerShell is once again pretty simple.

The only difference from the earlier PowerShell example is this time it includes as body to the request.

As the Job ID has been stored it is easy to check the progress of the job.

To run a rule then the request is pretty much the same with only the job type and job name changing.

This is fine if the rules does not contain any runtime prompts, I couldn’t get it to work in 11.1.2.4 when it does and I spent far long trying to get it to work.

I believe the format should be:

No matter what I tried I kept getting the following response:

The logs contained:

[APP: PLANNING#11.1.2.0] [SRC_CLASS: com.hyperion.planning.jobs.HspBRJob] [SRC_METHOD: executeJob] Failed to run job: RestRule[[
java.lang.NullPointerException
at com.hyperion.planning.calcmgr.cmdlnlauncher.HspCalcMgrCmdLineLauncher.launchRule(HspCalcMgrCmdLineLauncher.java:220)
at com.hyperion.planning.jobs.HspBRJob.executeJob(HspBRJob.java:62)

I am not sure whether it actually works in 11.1.2.4 but if I do find out it does then I will update this post.

It certainly works in PBCS but like I said earlier that accepts JSON in the following format:

{
    "JobType": "JobType",
    "jobName": "JobName",
    "parameters": {
        "VariableName": "VariableValue"
    }
}

I did find some interesting hidden resources that are not documented.

Please note the following is not available in PBCS and could be removed from on-premise in the future.

The first one returns the POV, Columns, Rows and data in a planning form.

The resource uses the GET method and can be accessed through:

/HyperionPlanning/rest/{api_version}/applications/{application_name}/dataexport/{form_name}

Let us take this form as an example.

Submit the request.

This generates as response of:

Pretty cool the form and data have been returned in JSON, again this can be easily done in a scripting language.

There is another resource which is even more fascinating that allows MDX to be run against planning and produces a similar response to the last example.

The resource uses the POST method and can be accessed through:

/HyperionPlanning/rest/{api_version}/applications/{application_name}/dataexport/plantypes/{plantypename}

In the body of the request the MDX can be passed.

The above MDX query should bring back data for a Smart List member and a text member.

Interesting the text member data is actually returned as the text string and not just a value, the Smart List data only returns the numeric value though.

To show how easy it is retrieve the data using a bit of scripting.

It certainly opens up lots of opportunities when you have easy access to the data.

There is also an additional query parameter that can be added which will also return attributes of the data cells in the response.

I think I am going to leave it there for today as putting this blog post together has certainly been a mammoth task and has taken up a lot of my time.

I am sure there will be future posts on this subject but in the meantime if you would like to know anything more just get in touch.