Running Data Load Rule through Rest API and Groovy using Substitution Variables

Hello 👋👋,

How many of you know that, we don't need to go into data management to run data load rule or check the status of the Process?

Yes, we can do it through Business rules. By attaching the Rule to form through Action Menus, the User on right click can rule the data load Rules.

(This example is not for File based integration) *

Here is the solution:

The Important Parameters for Data load Rule are Start period, End Period, Source Filters.

Yes, we can obtain the above parameters through substitution variables or Run time Prompts.

Step1:

Create the Substitution Variables for Year, Period and Source Filter Dimesnions (which need to change)

I have created 4 Substitution Variables at Cube Level, 

CurrYearBudget = FY22

CurrPeriod = BegBalance

Current_Version = Current Version

Previous_Version = V1

Here I am running the Rule for single Period, if you wish to run for multiple period or years create two more Substitution Variables for the period and year.

Step 2:

Creating web Service provider for Substitution Variables

Go to Tools -> Connections -> Create-> Other Web Service Provider

Connection Name: Sub

URL: http://localhost:9000/HyperionPlanning/rest/{Rest Api Version}/applications/{Application Name}/plantypes/{Cube Name}/substitutionvariables

In the above URL replace {} information with your own information 

Example: http://localhost:9000/HyperionPlanning/rest/v3/applications/Vision/plantypes/Plan1/substitutionvariables

In Show Advanced Option,

Select Header and give Name as Content-Type & Value as application/json

User: Identity Domain ID.Admin User Name

Password: Admin Password

Finally, we can now Save and Close.

Step 3:

Creating web Service provider for Data Management

Go to Tools -> Connections -> Create-> Other Web Service Provider

Connection Name: DM

URL: http://localhost:9000/aif/rest/{DM Rest API Version}/jobs

In the above URL replace {} information with your own information 

Example: http://localhost:9000/aif/rest/V1/jobs

In Show Advanced Option,

Select Header and give Name as Content-Type & Value as application/json

User: Identity Domain ID.Admin User Name

Password: Admin Password

Finally, we can now Save and Close.

Step 4:

Navigate -> Rules->Create a new Groovy Business Rule

Copy and Paste the below Rule:

See the Description of the code in lines added with '//' as prefix.

// This Below code is used to Bring Substitution Variable information from CurrYearBudget variable

HttpResponse jsonYear = operation.application.getConnection("Sub").get("CurrYearBudget")

.asString()

def Year_RTP = new JSONObject(jsonYear.body).getString("value")

println("Response is  $Year_RTP ")

// This Below code is used to Bring Substitution Variable information from CurrPeriod variable

HttpResponse jsonPeriod = operation.application.getConnection("Sub").get("CurrPeriod")

.asString()

def Period_RTP = new JSONObject(jsonPeriod.body).getString("value")

println("Response is  $Period_RTP")

// This Below code is used to Bring Substitution Variable information from Previous_Version variable

HttpResponse jsonPreVer = operation.application.getConnection("Sub").get("Previous_Version")

.asString()

def Pre_Ver_RTP = new JSONObject(jsonPreVer.body).getString("value")

println("Response is  $Pre_Ver_RTP")

// This Below code is used to Bring Substitution Variable information from Current_Version variable

HttpResponse jsonCurrVer = operation.application.getConnection("Sub").get("Current_Version")

.asString()

def Curr_Ver_RTP = new JSONObject(jsonCurrVer.body).getString("value")

println("Response is  $Curr_Ver_RTP")

//This below code is used to concatenate Year_RTP and Period_RTP variables 

def periodName_RTP = "{"+Period_RTP+"-"+Year_RTP[2..3]+"}" + "{"+Period_RTP+"-"+Year_RTP[2..3]+"}"

println("Response is $periodName_RTP")

//This below code is used to concatenateCurr_Ver_RTP and Pre_Ver_RTP variables 

def Ver_RTP = '"'+Curr_Ver_RTP+'"' + ',' + '"'+Pre_Ver_RTP+'"'

println("Response is $Ver_RTP")

//This below code is used to Run Data load rule

//Change the jobName, importMode & exportMode as per your requirment

def body = new JSONObject()

.put("jobType","INTEGRATION")

.put("jobName","Test")

.put("periodName",periodName_RTP)

.put("importMode","REPLACE")

.put("exportMode","REPLACE")

.put("sourceFilters",["Version":Ver_RTP])

.toString()

HttpResponse jsonResponse = operation.application.getConnection("DM").post()

.header("Content-Type","application/json")

.body(body)

.asString()

println("Response is $jsonResponse.body")

//This Below code is used to Bring Status of Data load rule from Process Details 

boolean DLRStatus = awaitCompletion(jsonResponse, "DM", "The Data Load Rule Status is")

println("DLRStatus is $DLRStatus")

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")

ReadContext ctx = JsonPath.parse(jsonResponse.body)

int status = ctx.read('$.status')

println("status is before loop $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")

println("status is after loop $status")

      if(status != 0){ 

        throwVetoException("Error occured in Rule processing") 

      }

return status == 0

}

int getJobStatus(String connectionName, String jobId) {

     HttpResponse<String> pingResponse = operation.application.getConnection(connectionName).get(jobId).asString()

     return JsonPath.parse(pingResponse.body).read('$.status')

}