Friday, October 13, 2017

Explore Planning REST API - Oracle PBCS with Groovy flavor !!!

After the introduction to the REST API & Groovy lets explore the Planning REST API in this post.

1. Get REST API version for Planning

Returns information about which versions are available and supported. Multiple versions might be supported simultaneously by Oracle Hyperion Planning.

This is already covered but lets see how to get the apiVersion details using Groovy.

Output of the program

2. Listing all the Jobs created in the instance

Lets create a Groovy program used to retrieve job definitions for types of jobs and the name along with how many jobs are created in the application

The below program will call the REST API 
/HyperionPlanning/rest/{api_version}/applications/{application}/ jobdefinitions to get the following,

# Number of jobs created in the Application
# Name of the and the Type (Cube Refresh/Export Data/Import Data/Export Metadata/Import Metadata/Rules/Ruleset/Plan Type Map)

 Output of the program


Lets have a Groovy program to launch business rule created in the Calculation Manager 
executeJob("RULES", "ClearGLData1" , null);

Output from the Groovy Console

Output from the Job Console

Note: I had difficulties in executing a rule with a space in the rule name.The error is shown below when there is a space in the rule name.

If there is space in the rule name specify that as shown below to fix the issue.

executeJob("RULES", 'Clear GL Data' , null);


launches business ruleset. Ruleset with no runtime prompts with the default values will be supported
executeJob("RULESET", 'Run RuleSet' , null);

Output from the Groovy Console

Output from the Job Console


Copies data from a block storage database to an aggregate storage database or from
a block storage to another block storage based on the settings specified in a Oracle

Hyperion Planning job of type plan type map.
executeJob("PLAN_TYPE_MAP", "MapChannels" , null);

Output from the command prompt after executing the Groovy program

Output from the Jobs console for the same program


 Exports metadata into a file using the settings specified in a Oracle Hyperion Planning job of type export metadata. The file containing the exported metadata is stored in the Oracle Planning and Budgeting Cloud repository.

Lets have a Groovy program to call the job that exports metadata job and display the output.

Calling the function with the needed parameters
executeJob("EXPORT_METADATA", "ExpYear", "");

Output of the job

Checking the status of the job from the job console


Same function used to run the job to export data from the PBCS application
executeJob("EXPORT_DATA", "ExpData", "");

Output from the Grrovy Console:

Output from the Job Console from PBCS Service Instance
Note: If you notice the name of the data export file is same as the job name even if we specify a different name


Refreshes the Oracle Hyperion Planning application cube. Typically, you refresh the
cube after importing metadata into the application.

Create a job for refreshing the job and call the job via the groovy as shown below
executeJob("CUBE_REFRESH", "RefAppl", null);

Output of the program from Groovy Console

Output of the same job via the Job console


Enables you to clear specific data within input and reporting cubes. NOTE: The Clear Cube job deletes the data you specify within input and reporting cubes, but it does not delete the application definition in the application's relational tables. 

Calling the job ClearEmpBl for clearing all the empty blocks from the Plan1 from Groovy
executeJob("CLEAR_CUBE", "ClearEmpBl", null);

Output from the Groovy Console

Output from the Job Console


Adds a new member to the application outline in the specified dimension and plan type and under the specified parent member.

Make sure you have enable for Dynamic Children is done and the application is refreshed before executing the groovy program.
The Groovy function to add a member under the member "TD"
Groovy Output

Added Member "ViaGroovy"in the Entity Hierarchy under the parent "TD"

11. Get Applications

Returns a list of applications to which the specified user is provisioned
Groovy Function to fetch the application details

Output for the Groovy Program

12. Getting and Setting Substitution Variables for Planning

You can get and set substitution variables at the plan level and application level using a set of REST resources

Variables defined at the applicaiton level

Groovy function to list all the variable defined at the application level

Output of the Program

Variable for the plan Type "Plan1" 

Groovy function to list variables for the Plan1 database

Output of the Program

To get the value for a particular variable
Let me end this topic related to the Planning REST API with hell lot of pictures :-), in the next series will explore the Migration and Data Management.


  1. Nice tutorial. The Oracle Hyperion DRM tutorial was help ful for me. Keep Sharing Tutorials.

  2. Hello, could you post the string payload of when you run the business rules with parameters. I can't get it to work with parameters but the following payload does work for me



  3. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog on. Oracle Soa Online course