Calling an internal REST API using Groovy

Introduction

This 15-minute tutorial shows you how to call a Data Management REST API to execute a data load rule that will load the latest product volumes for the user’s entity into Oracle EPM Cloud Planning.

Background

The Groovy EPM object model provides a way to call internal (cross-POD or other Oracle Cloud Services) and external REST APIs.

In this example, you use the Product Volume form. The Product Volume form captures the volume for various products in monthly periods. You create and execute a Groovy rule to call an internal Data Management REST API to load product volumes in the form.

Prerequisites

Cloud EPM Hands-on Tutorials may require you to import a snapshot into your Cloud EPM Enterprise Service instance. Before you can import a tutorial snapshot, you must request another Cloud EPM Enterprise Service instance or remove your current application and business process. The tutorial snapshot will not import over your existing application or business process, nor will it automatically replace or restore the application or business process you are currently working with.

Before starting this tutorial, you must:

Have Service Administrator access to a Cloud EPM Enterprise Service instance.
Upload and import this snapshot into your Planning instance. If you've previously uploaded the snapshot for another Groovy tutorial, you can continue using the same snapshot.

Note:

If you run into migration errors importing the snapshot, re-run the migration excluding the HSS-Shared Services component, as well as the Security and User Preferences artifacts in the Core component. For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

Tip:

The scripts you need for this tutorial are linked as text files within each section.

Setting up the Planning environment

Setting user variables in Planning

First, you set the required user variables to work with the Product Volume form.

From the Home page, click Tools, and then select User Preferences.

Select the User Variables tab.
Enter the following variable definitions:

User Variable	Member
Currency	USD
Entity	Sales US
Reporting Currency	USD
Scenario	OEP_Plan
Version	OEP_Working

Click Save. At the confirmation message, click OK, then return to the Home page.

Uploading a data file with product volumes

Upload a product volumes data file to the Data Management inbox (which is different than the Application inbox).

Right-click the link for the ProductVolumes.csv data file and save it to your local drive.
From the Home page, navigate to Data Exchange (under Application). Select the Product Volumes integration.

Select File Options.

Select File Browser.

Double-click Inbox.

Upload the ProductVolumes.csv data file from your local drive to the Inbox folder.

Click Cancel to close the File Browser dialog. Click Cancel to close the File Import - File Options dialog. Click Cancel to close the Edit Integration: ProductVolumes dialog.

Editing the Data Management connection

Edit the Data Management connection.

Navigate to Connections (under Tools), and select the DM connection. Enter an administrator-level user and password, then save and close the connection.

Calling an internal REST API to load product volumes

In this section, you implement a Groovy script to call an internal REST API to load product volumes.

Navigate to Rules (under Create and Manage) to open Calculation Manager and create a rule named Groovy Load Product Volumes in the OEP_FS cube.

In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.

Copy this script and paste it into the editor:

// Load product volumes from DM to Planning
HttpResponse<String> jsonResponse = operation.application.getConnection("DM").post()
.body(json(["jobType":"DATARULE", "jobName":"ProductVolumes", "startPeriod":"Jan-17", "endPeriod":"Dec-17", 
"importMode":"REPLACE", "exportMode":"STORE_DATA", "fileName":"inbox/ProductVolumes.csv"]))
.asString(); 
boolean pushDataToPlanning = awaitCompletion(jsonResponse, "DM", 
"Push Product Volumes to Planning") 

if(pushDataToPlanning) {
    // Capture the periods and products whose volumes were loaded
    Set<String> editedMembers = []
    operation.grid.dataCellIterator().each { DataCell cell ->
    	editedMembers << cell.periodName << cell.getMemberName("Product", MemberNameType.ESSBASE_NAME)
    }
}

// Wait for DM job to be completed
def awaitCompletion(HttpResponse<String> jsonResponse, 
String connectionName, String operation) {  
    final int IN_PROGRESS = -1
    if (!(200..299).contains(jsonResponse.status))
    throwVetoException("Error occured: $jsonResponse.statusText")
    
    // Parse the JSON response to get the status of the operation. 
    Keep polling the DM server until the operation completes.
    ReadContext ctx = JsonPath.parse(jsonResponse.body)
    int status = ctx.read('$.status') 
    for(long delay = 50; status == IN_PROGRESS; delay = Math.min(1000, delay * 2)) {
        sleep(delay)
        status = getJobStatus(connectionName, (String)ctx.read('$.jobId'))
    }
    
    println("$operation ${status == 0 ? "successful" : "failed"}.\n")
    return status == 0
}

// Poll the DM server to get the job status
int getJobStatus(String connectionName, String jobId) { 
    HttpResponse<String> pingResponse = operation.application.getConnection(connectionName).get("/" + jobId).asString()
    return JsonPath.parse(pingResponse.body).read('$.status')

}

Get the DM connection. This connection object is a communication link between the Groovy script and the Data Management REST API resource. Execute the HTTP POST on this resource to trigger a data load rule based on the start period and end period. This rule loads the product volumes to Planning.

Wait for the Data Management rule execution to complete.

Parse the JSON response from the REST API to get the status of the operation. Keep polling the Data Management server until the operation completes.

Poll the Data Management server to get the latest job status for the specified job ID by executing the HTTP GET on this resource.

On the toolbar, click (Save) to save the script, then click (Validate and Deploy) to validate and deploy the script. Click OK when prompted. Close Calculation Manager.

Adding the rule to a form

In this section, you add your Groovy script rule to the Product Volume form, to be run after saving.

Navigate to Forms (under Create and Manage) and select the Product Volume form.

Edit the form. Add the Groovy Load Product Volumes rule and set it to Run After Save. Save the form and close the Form Manager window.

Testing the rule

In this section, you test your Groovy rule by editing data in the Product Volume form.

From the home page, click Data. Open the Product Volume form.V
Go to Actions Menu and select Business Rules. Select Groovy Load Product Volumes.

After the successful execution of the rule, the volumes for the products are updated.

Product volumes are updated in the Product Volume form.