Update

I have added loops to check the job status for the creation of the attachment and the import of the transactions. Here is an example of the code in groovy

def LOOP_STATUS = new JsonSlurper().parseText(jsonResponse.body) as Map
println "$RESTSTAGE Status is $LOOP_STATUS.status"
while(LOOP_STATUS.status != "COMPLETED") {
jsonResponse = operation.application.getConnection("EDMCS").get("/jobRuns/$jobid/result")
.header("Content-Type", "application/json").asString()
LOOP_STATUS = new JsonSlurper().parseText(jsonResponse.body) as Map
println "$RESTSTAGE Status is $LOOP_STATUS.status"
}

You want to make sure you uptime the Job Id and check its status.

Original Start

This last year I have been lucky to work with EDMCS. I am impressed with its functionality and the different approach to metadata management. My current client does metadata changes in EDMCS during the month and then sends the changes to their EPBCS applications, on-premises Essbase, and relational data sources on a monthly schedule. They load actuals to EPBCS twice a day and sometimes there are new cost centers that are not caught during the monthly EDMCS maintenance. To limit the number of database refreshes in the EPBCS applications, we have a parent member defined that allows dynamic members, and then on the next load, the cost center will be there for the data load.

Here is an example of the member we have defined in planning:

There are several methods to accomplish this in EPBCS. The method I used was with a Groovy rule and it is detailed below. The goal is to add the member to planning without executing a database refresh.

The EDMCS Problem

This was great for planning, but I had two maintenance points. I needed to dynamically create the members that the planning administrator generated in EDMCS so that they can be part of the maintenance cycle and not have to be created in two places.

I had attempted this before but I was not successful because before December 2020 you had to have an excel file created to generate a request in EDMCS. With the December update, the REST API includes an ability to generate an attachment from JSON. This is a game-changer and allows EDMCS to be extended into many possible workflows. This post has a good amount of detail in it so here is a quick video of the code in action to frame the use case.

Add Members on the Fly in Planning

Below is a groovy script that is executed within EPBCS. Here is the full script but let’s break it down below.

/*RTPS:  {rtp_NewCC} */
%Script(name:="GROOVYFUNCTIONS")
// The rule will let you add new entity members on the fly.  It will check if the member name already exists.  The Members should be placed in EDMCS for integrity
String curruser =  operation.User.getFullName()
println "The User running the rule is $curruser"
def newmembers2edmcs=[]
def newmembers=[]
//Create list based on the CSV values
newmembers=rtps.rtp_NewCC.toString().tokenize(',')
println newmembers
//Create Connection to Cube
Cube lookupCube = operation.application.getCube("CUBE1")
//Establist Entity Dim
Dimension entityDim = operation.application.getDimension("Entity", lookupCube)
//Parent member
def ParentMember = entityDim.getMember("CC_Rejections", lookupCube)
//Define Variables for New member process
Map newMembermap
String newmember
//For each member in list
newmembers.each{
newmember=it
//If the member does not exist
if(!entityDim.hasMember(newmember)){
//Create new member as child map to parent
newMembermap= ParentMember.newChildAsMap(newmember)
//Make the new member alias null
newMembermap["Alias: Default"] = ""
//Save Member to outline
Member NewMember = entityDim.saveMember(newMembermap, DynamicChildStrategy.DYNAMIC_IF_AVAILABLE)
println ("The member $newmember added succesfully")
newmembers2edmcs<<it.replace('.','')
}else{
println "Member $newmember already exists."
}
}
if(newmembers2edmcs.size()>0){
%Template(name:="ADD_CC_2_EDMCS",dtps:=("NEWEDMCSMEMBERS":=[[newmembers2edmcs.toString().replace('[','').replace(']','')]],"VIEW_NAME":=[["MYVIEW"]],"VIEWPOINT":=[["CC_BLOG_2"]],"EDMCS PARENT":=[["CC_Rejections"]],"EDMCS NodeType":=[["MCC_BLOG"]]))
}

Add Members Opening

/*RTPS:  {rtp_NewCC} */
%Script(name:="GROOVYFUNCTIONS")
// The rule will let you add new entity members on the fly.  It will check if the member name already exists.  The Members should be placed in EDMCS for integrity
String curruser =  operation.User.getFullName()
println "The User running the rule is $curruser"
def newmembers2edmcs=[]
def newmembers=[]
//Create list based on the CSV values
newmembers=rtps.rtp_NewCC.toString().tokenize(',')
println newmembers

At the beginning of the script, I am calling a TEXT RTP and importing a log function that I built to simplify the REST logging. The RTP is then converted into a list by utilizing the tokenize function.

Create a connection to the Cube

The following part of the code creates a connection to the Planning cube and generates a map for the parent member that I want the members to be added to.

//Create Connection to Cube
Cube lookupCube = operation.application.getCube("CUBE1")
//Establist Entity Dim
Dimension entityDim = operation.application.getDimension("Entity", lookupCube)
//Parent member
def ParentMember = entityDim.getMember("CC_Rejections", lookupCube)
//Define Variables for New member process
Map newMembermap
String newmember

Loop through the new members

In this part of the code I am looping through the new member list and:

• Checking that the member does not exist in the application
• if it does not then create a new member map adding the child member to the parent member newMembermap= ParentMember.newChildAsMap(newmember)
• Setting the default alias to blank
• Saving that new member to EPBCS Member NewMember = entityDim.saveMember(newMembermap, DynamicChildStrategy.DYNAMIC_IF_AVAILABLE)
• Adding that member to a list that will be passed to EDMCS. I am removing any periods in the member names because in EDMCS the Node name does not have any periods but is added based on rules for extracts.

//For each member in list
newmembers.each{
newmember=it
//If the member does not exist
if(!entityDim.hasMember(newmember)){
//Create new member as child map to parent
newMembermap= ParentMember.newChildAsMap(newmember)
//Make the new member alias null
newMembermap["Alias: Default"] = ""
//Save Member to outline
Member NewMember = entityDim.saveMember(newMembermap, DynamicChildStrategy.DYNAMIC_IF_AVAILABLE)
println ("The member $newmember added succesfully")
newmembers2edmcs<<it.replace('.','')
}else{
println "Member $newmember already exists."
}
}

If the member already exists I write that out to the log. println “Member $newmember already exists.”

Pass the parameters to an EDMCS Template

if(newmembers2edmcs.size()>0){
%Template(name:="ADD_CC_2_EDMCS",dtps:=("NEWEDMCSMEMBERS":=[[newmembers2edmcs.toString().replace('[','').replace(']','')]],"VIEW_NAME":=[["MYVIEW"]],"VIEWPOINT":=[["CC_BLOG_2"]],"EDMCS PARENT":=[["CC_Rejections"]],"EDMCS NodeType":=[["MCC_BLOG"]]))
}

This code will test the size of the list and if it is greater than 0 it will call the EDMCS template

Lets look at some of the Design Time prompts that are being called:

• “NEWEDMCSMEMBERS”:=[[newmembers2edmcs.toString().replace(‘[‘,”).replace(‘]’,”)]] This is the list of new members but I am converting it to a string and removing any brackets. I am sure that there is a more elegant way of doing this.
• “VIEW_NAME”:=[[“MYVIEW”]] This is the name of the view in EDMCS that you want to create the request against.
• “VIEWPOINT”:=[[“CC_BLOG_2”]] We are defining the view or the tab within EDMCS. If your viewpoint has a label defined use the label. The attachment that is created will use the label.

• “EDMCS PARENT”:=[[“CC_Rejections”]] This is the parent I want to add the new members to in EDMCS
• “EDMCS NodeType”:=[[“MCC_BLOG”]] This is the node type that the new members will be created in.

Generate the request in EDMCS

To utilize this functionality you first need to create a connection in EPBCS to call the API of EDMCS. This is done in Connections.

I suggest utilizing the Other Web Service provider and defining your connection like below to your EDMCS environment.

URL= https:server/epm/rest/v1

User=domain.user

The Opening

In the opening of the script I am taking the DTP of new members and converting them to a list. Here I am also creating a String builder to create the JSON to pass into EDMCS.

def newmembersedmcs=[]
//Create list based on the CSV values
newmembersedmcs=[NEWEDMCSMEMBERS].toString().tokenize(',')
String RESTSTAGE
// Holds the calculation script to send to EDMCS to build the object body
StringBuilder scriptBldr = StringBuilder.newInstance()

Earlier in the post I mentioned a function to help with REST logging. I am going to start using it soon so I wanted to display the details of it.

The function accepts a string defining the stage of the script and the http response from the server.

//create a function to report on the status
def checkRESTStatus(String stage,HttpResponse<String> response)
{
if(!(200..299).contains(response.status)){
         throwVetoException("EDMCS $stage status Error occured: $response.statusText")
}else{
println "EDMCS $stage status is $response.statusText"
}
}

Get the EDMCS Views

To create the request you need to get a list of all of the views available in EDMCS. They will each have a unique id that will be used in the remainder of the code.

The code is creating a jsonResponse variable and getting the EDMCS connection and then adding the uri “views”.

Basically it is creating a URL request https:server/epm/rest/v1/views and storing the response in the jsonResponse object. Then I am calling my status function and passing that to the log.

RESTSTAGE = "GET VIEWS"
//Get Views From EDMCS
 HttpResponse<String> jsonResponse = operation.application.getConnection("EDMCS").get("/views")
 .header("Content-Type", "application/json").asString()
checkRESTStatus(RESTSTAGE,jsonResponse)

Create a Map of the EDMCS Views

Next, I am going to take the response from EDMCS, parse it, and generate a map that I can look up the view name and get the corresponding id

def EDMCS_VIEWS = new JsonSlurper().parseText(jsonResponse.body) as Map
def baseurl =operation.application.getConnection("EDMCS").getUrl()
String requestid
String viewid
Date now = new Date()
String myid
String myname
def viewmap = [:]
EDMCS_VIEWS.items.each{
			myid = it['id']
			myname = it['name']
			viewmap.put(myname,myid)
}
viewid=viewmap[[VIEW_NAME]]

Here are the highlights:

• Create a map from the response from EDMCS def EDMCS_VIEWS = new JsonSlurper().parseText(jsonResponse.body) as Map
• Iterate through the EDMCS items EDMCS_VIEWS.items.each{
• Get each view id myid = it[‘id’]
• get each view name myname = it[‘name’]
• add them to a new map viewmap.put(myname,myid)
• Retrieve the viewid from the map based on the view name dtp viewid=viewmap[[VIEW_NAME]]

Generate an EDMCS Request

Now we need to create a request in EDMCS. This is accomplished by generating a POST request with a body to EDMCS. I am reusing the jsonResponse object from the earlier section but sending over a new URL. The key component here is in the body. Here I am building a URL to pass to the system using the base and the view id that I retrieved from the last part of the script. I am also adding the current EPBCS user and the time so that it will be displayed in the request in EDMCS.

RESTSTAGE = "GENERATE REQUEST"
//Generate Request in EDMCS for View
	jsonResponse = operation.application.getConnection("EDMCS").post("/requests")
 .header("Content-Type", "application/json")
 .body(json(["viewUri" : "$baseurl/views/$viewid", "title": "EPBCS USER:$curruser TIME:$now" ])).asString()
checkRESTStatus(RESTSTAGE,jsonResponse)
def REQUEST_CREATE = new JsonSlurper().parseText(jsonResponse.body) as Map
requestid = REQUEST_CREATE.id

Once the request is generated I am parsing the response again as a map and storing the request id in the requestid variable.

Generate the JSON for the attachment

In this next section I am creating a JSON object using a scriptbuilder. I know there are probably better ways to do this but I thought this worked well and it made sense to me.

• Generate the header of the list. This is universal across the whole JSON object. I called the spreadsheet that EDMCS will create called EDMCS_Groovy.xlsx but you can call it any excel file.
  • scriptBldr << “”” { “fileName”: “EPBCS_GROOVY.xlsx”, “items”: [“””
• In the next part, I am getting the size of the list to use later. def listsize = newmembersedmcs.size()
• Then I iterate through the list and build the items for the spreadsheet. I am using the eachWithIndex to get a count of the current item I am working on

newmembersedmcs.eachWithIndex{it,index->

• This next section will build out the item details for each item in the list. The key fields here are:
  • VIEWPOINT – the tab of the spreadsheet
  • Name – this is the current member that is iterating in the list and is the node name in EDMCS
  • Parent – the Parent in EDMCS
  • Node Type and Parent Node Type – The node that the members will be created in
  • Action Code – Add the member but really this can be what you need

This part of the code checks if the current member is the last one in the list. If it is not the last one it will add a comma to the JSON if not it will ignore it.

if(index < listsize -1){ scriptBldr << “”” , “””}

//Generate Request Atachment
scriptBldr << """
  {
  "fileName": "EPBCS_GROOVY.xlsx",
  "items": [
   
"""
def listsize = newmembersedmcs.size()
//println listsize
newmembersedmcs.eachWithIndex{it,index->
//println index
scriptBldr << """
{
  "viewpoint": [VIEWPOINT],
      "data": [
        {
          "header": "Name",
          "value": "$it"
        },
        {
          "header": "Parent",
          "value": [EDMCS PARENT]
        },
        {
          "header": "Node Type",
          "value": [EDMCS NodeType]
        },
        {
          "header": "Parent Node Type",
          "value": [EDMCS NodeType]
        },
        {
          "header": "Action Code",
          "value": "ADD"
        }
      ]
    }
"""
if(index < listsize -1){
scriptBldr << """
,
"""
}
 
}
scriptBldr << """
    ]
}
"""

This last part just adds the footer of the JSON script

scriptBldr << “”” ] }”””

Here is what the JSON looks like for the request seen in the video.

{
  "fileName": "EPBCS_GROOVY.xlsx",
  "items": [
   
{
  "viewpoint": "CC_BLOG_2",
      "data": [
        {
          "header": "Name",
          "value": "AB1234"
        },
        {
          "header": "Parent",
          "value": "CC_Rejections"
        },
        {
          "header": "Node Type",
          "value": "MCC_BLOG"
        },
        {
          "header": "Parent Node Type",
          "value": "MCC_BLOG"
        },
        {
          "header": "Action Code",
          "value": "ADD"
        }
      ]
    }
,
{
  "viewpoint": "CC_BLOG_2",
      "data": [
        {
          "header": "Name",
          "value": " CA6574"
        },
        {
          "header": "Parent",
          "value": "CC_Rejections"
        },
        {
          "header": "Node Type",
          "value": "MCC_BLOG"
        },
        {
          "header": "Parent Node Type",
          "value": "MCC_BLOG"
        },
        {
          "header": "Action Code",
          "value": "ADD"
        }
      ]
    }
,
{
  "viewpoint": "CC_BLOG_2",
      "data": [
        {
          "header": "Name",
          "value": " 8799"
        },
        {
          "header": "Parent",
          "value": "CC_Rejections"
        },
        {
          "header": "Node Type",
          "value": "MCC_BLOG"
        },
        {
          "header": "Parent Node Type",
          "value": "MCC_BLOG"
        },
        {
          "header": "Action Code",
          "value": "ADD"
        }
      ]
    }
    ]
}

Create an attachment in EDMCS

Here is the new API call that was released in December. Seems like it took a while to get here but its worth it 🙂

RESTSTAGE="GENERATE ATTACHMENT"
println scriptBldr
  jsonResponse = operation.application.getConnection("EDMCS").post("/requests/$requestid/attachments/generate")
 .header("Content-Type", "application/json")
 .body(scriptBldr.toString()).asString()
 
checkRESTStatus(RESTSTAGE,jsonResponse)
def ATTACHMENT_CREATE = new JsonSlurper().parseText(jsonResponse.body) as Map
//println ATTACHMENT_CREATE.links['href']
def jobid = ATTACHMENT_CREATE.links['href'].toString().split('/').last().replace("]","")

I am reusing that jsonResponse object again and sending a new post request with the requestid in the url. Also notice that the scriptbuilder object is the body just cast as a string.

I need to get the job id for the next part of the script. Currently this is only stored in a URL within the response object.

def jobid = ATTACHMENT_CREATE.links[‘href’].toString().split(‘/’).last().replace(“]”,””)

In the above code I am parsing the response from the server and getting the last part of the url to store the job id.

Here is an example of the response url https://server:443/epm/rest/v1/jobRuns/a9004e3f-749b-4036-85d2-aa5753c43b9b

if successful this will create a spreadsheet on the request.

Here is an example of the spreadsheet that was generated for this request.

Import the Spreadsheet

You now have to import the items from the generated sheet into your request. If you don’t your request will just be empty with an attachment and not do anything once its submitted.

This has to be done in two phases. In the first part you need to query EDMCS and get the job details for the attachment you just created.

RESTSTAGE = "GET JOB DETAILS"
// Get Job details
 jsonResponse = operation.application.getConnection("EDMCS").get("/jobRuns/$jobid/result")
 .header("Content-Type", "application/json").asString()
checkRESTStatus(RESTSTAGE,jsonResponse)
def JOBRUN_STATUS = new JsonSlurper().parseText(jsonResponse.body) as Map
def attachmentid= JOBRUN_STATUS.result['attachmentId'].toString()

I am using the job id in the url to get the details and then parse the response to get an attachment id.

Once I have the attachment id I can then import the items into the request.

RESTSTAGE = "IMPORT_TRANSACTIONS"
//Generate Request in EDMCS for View
jsonResponse = operation.application.getConnection("EDMCS").post("/requests/$requestid/import")
 .header("Content-Type", "application/json")
 .body(json(["attachmentUri" : "$baseurl/requests/$requestid/attachments/$attachmentid", "sheetNames": [[VIEWPOINT]]])).asString()
checkRESTStatus(RESTSTAGE,jsonResponse)
def ATTACHMENT_IMPORT = new JsonSlurper().parseText(jsonResponse.body) as Map

The url on the post request is pretty simple. the trick here is to build the proper body using the correct attachment id and the sheet name you want to import.

Once this is compete the request should now be populated with the items from your spreadsheet.

Submit the Request

This last part takes the now complete request and submits it to EDMCS to follow the workflow defined in the tool.

// Submit request to edmcs
RESTSTAGE = "SUBMIT REQUEST"
jsonResponse = operation.application.getConnection("EDMCS").post("/requests/$requestid/transitions")
 .header("Content-Type", "application/json")
 .body(json("action": "SUBMIT")).asString()
checkRESTStatus(RESTSTAGE,jsonResponse)

After some of the more complex examples this is fairly simple. I am utilizing the request id in the url and changing the status to submit.

Complete Code

def newmembersedmcs=[]
//Create list based on the CSV values
newmembersedmcs=[NEWEDMCSMEMBERS].toString().tokenize(',')
String RESTSTAGE
// Holds the calculation script to send to EDMCS to build the object body
StringBuilder scriptBldr = StringBuilder.newInstance()
RESTSTAGE = "GET VIEWS"
//Get Views From EDMCS
 HttpResponse<String> jsonResponse = operation.application.getConnection("EDMCS").get("/views")
 .header("Content-Type", "application/json").asString()
checkRESTStatus(RESTSTAGE,jsonResponse)
def EDMCS_VIEWS = new JsonSlurper().parseText(jsonResponse.body) as Map
def baseurl =operation.application.getConnection("EDMCS").getUrl()
String requestid
String viewid
Date now = new Date()
String myid
String myname
def viewmap = [:]
EDMCS_VIEWS.items.each{
			myid = it['id']
			myname = it['name']
			viewmap.put(myname,myid)
}
viewid=viewmap[[VIEW_NAME]]
RESTSTAGE = "GENERATE REQUEST"
//Generate Request in EDMCS for View
	jsonResponse = operation.application.getConnection("EDMCS").post("/requests")
 .header("Content-Type", "application/json")
 .body(json(["viewUri" : "$baseurl/views/$viewid", "title": "EPBCS USER:$curruser TIME:$now" ])).asString()
checkRESTStatus(RESTSTAGE,jsonResponse)
def REQUEST_CREATE = new JsonSlurper().parseText(jsonResponse.body) as Map
requestid = REQUEST_CREATE.id
//Generate Request Atachment
scriptBldr << """
  {
  "fileName": "EPBCS_GROOVY.xlsx",
  "items": [
   
"""
def listsize = newmembersedmcs.size()
//println listsize
newmembersedmcs.eachWithIndex{it,index->
//println index
scriptBldr << """
{
  "viewpoint": [VIEWPOINT],
      "data": [
        {
          "header": "Name",
          "value": "$it"
        },
        {
          "header": "Parent",
          "value": [EDMCS PARENT]
        },
        {
          "header": "Node Type",
          "value": [EDMCS NodeType]
        },
        {
          "header": "Parent Node Type",
          "value": [EDMCS NodeType]
        },
        {
          "header": "Action Code",
          "value": "ADD"
        }
      ]
    }
"""
if(index < listsize -1){
scriptBldr << """
,
"""
}
 
}
scriptBldr << """
    ]
}
"""
RESTSTAGE="GENERATE ATTACHMENT"
println scriptBldr
  jsonResponse = operation.application.getConnection("EDMCS").post("/requests/$requestid/attachments/generate")
 .header("Content-Type", "application/json")
 .body(scriptBldr.toString()).asString()
 
checkRESTStatus(RESTSTAGE,jsonResponse)
def ATTACHMENT_CREATE = new JsonSlurper().parseText(jsonResponse.body) as Map
//println ATTACHMENT_CREATE.links['href']
def jobid = ATTACHMENT_CREATE.links['href'].toString().split('/').last().replace("]","")
RESTSTAGE = "GET JOB DETAILS"
// Get Job details
 jsonResponse = operation.application.getConnection("EDMCS").get("/jobRuns/$jobid/result")
 .header("Content-Type", "application/json").asString()
checkRESTStatus(RESTSTAGE,jsonResponse)
def JOBRUN_STATUS = new JsonSlurper().parseText(jsonResponse.body) as Map
def attachmentid= JOBRUN_STATUS.result['attachmentId'].toString()
RESTSTAGE = "IMPORT_TRANSACTIONS"
//Generate Request in EDMCS for View
jsonResponse = operation.application.getConnection("EDMCS").post("/requests/$requestid/import")
 .header("Content-Type", "application/json")
 .body(json(["attachmentUri" : "$baseurl/requests/$requestid/attachments/$attachmentid", "sheetNames": [[VIEWPOINT]]])).asString()
checkRESTStatus(RESTSTAGE,jsonResponse)
def ATTACHMENT_IMPORT = new JsonSlurper().parseText(jsonResponse.body) as Map
// Submit request to edmcs
RESTSTAGE = "SUBMIT REQUEST"
jsonResponse = operation.application.getConnection("EDMCS").post("/requests/$requestid/transitions")
 .header("Content-Type", "application/json")
 .body(json("action": "SUBMIT")).asString()
checkRESTStatus(RESTSTAGE,jsonResponse)

Conclusion

This was a fun project to put together. Ideally, I would want the source to feed into EDMCS but we are not there yet and this will greatly improve the metadata workflow of this particular client. Others can utilize this example and integrate EDMCS with other tools that can utilize REST to send requests to create awesome workflows! I hope this helps you out in your implementations and have a wonderful New Year!!