Blog Update 2-17-2020

While everything in this post still works.  I have recently learned that there is an easier way to execute the dynamic code and return control to your groovy script than creating the shell rule and the variable.

//Create Connection to WFP CUbe
Cube cube = operation.application.getCube("OEP_WFP")


cube.executeCalcScript(scriptBldr.toString())

Basically you create a connection to the cube you want to execute the code against and instead of passing the script to the rule in the job factory you can execute it as a calcscript using the executeCalcScript above.

Original Post

There has been so much great work in blogs and presentations discussing Groovy and how to use it with EPBCS. When I am stuck, I look at Kyle Goodfriend’s Blog, and I am sure there will be some similarities to his code in some of the examples in this post.

Why use Groovy?

Groovy frees us to create solutions that can interact with end-users and the application in ways we could never before with standard business rules. I think some of the best use cases is to be able to execute calculations based on the changed cells instead of running calculations on the whole grid

Dynamic Calc Scripts

Groovy enables us to create Essbase calc scripts and capture from the changed cells not just the POV of the grid but the members on the rows and the columns.

The above form is just a test form that I have built against the financials module and will be the sample form that the rule in this blog will execute on save., the stock accounts are in the row and the periods are in the columns.

Use Case

The goal of this blog is to demonstrate how, in a Groovy rule, we can:

• Execute a calculation only on the changed cells
• Rollup the Financial Cube utilizing the OOB rollup rule
• Transfer the data to reporting cube using a Smart Push with overrides.

Before I worked on this approach, I would use three different rules as part of a rule set, but this technique opens up your implementation toolkit to be able to do so much more…….

Create the Dynamic Calc Script

The other blogs discuss this in more detail,  the approach I take with Groovy is to capture the grid you are working with, identify if there are any changed cells, and then utilize the list of modified cells to write a focused calc script.

The Setup

It is a good idea to define your variables and to capture global objects in the setup so that you can use them easier later on in your rule.

//Get current Data Form
DataGrid curgrid = operation.getGrid()
String curruser =  operation.User.getFullName()
String Rowdim1="Account"
String Coldim1="Period"
//I am trying to get Cell level changes 

In the above code, I am capturing the current grid, the current user, and creating a variable for my row and column dimension of my form. In the rest of my code, I can now reference the grid as the object curgrid and not have to perform a getGrid() when I need to look at the changes the user made.

// Holds the calculation script sent to Essbase
StringBuilder scriptBldr = StringBuilder.newInstance()
//Create POV Dimensions variables to populate the script
String SScenario
String SVersion
String SEntity
String SCurrency
String SPlanElement
String SYear
//Row Lists
String SCurrAcct
String SPeriod
//List of unique rows and cells

def lstallRows =[]
def lstallCells =[]

This part is pretty self-explanatory except I am creating two lists. One will capture the rows that are changed, and the other will catch the row and columns that are modified.

Cube cube =  operation.getCube()
Dimension periodDim = operation.application.getDimension("Period", cube) 

def PeriodRange = periodDim.getEvaluatedMembers("@RELATIVE(YearTotal,0)", cube)
def myPeriodRange="""\"${PeriodRange.join('","')}\""""

Thinking ahead, I only want to execute a calculation on the level 0 members that changed in the web form. This code is evaluating for the current application the level 0 members of YearTotal and putting them in a comma-separated list in the variable myPeriodRange.

//Get edited cells in the grid
operation.grid.dataCellIterator().each {
   if(it.Edited){
   //Get List of row metadata that has changed
lstallRows << it.getMemberName(Rowdim1)
    //Get List of cells  

lstallCells<<['"'+it.getMemberName(Rowdim1)+'"','"'+it.getMemberName(Coldim1)+'"']


   //Set POV DIMENSIONS of that CELL
     
     SScenario = it.getMemberName("Scenario")
     SVersion = it.getMemberName("Version")
     SEntity = it.getMemberName("Entity")
     SCurrency = it.getMemberName("Currency")
     SPlanElement = it.getMemberName("Plan Element")
     SYear = it.getMemberName("Years")

	}
}

This code is evaluating the current grid for any edited cells. It is creating two lists, one with the changed row members and the other with the changed rows and columns. Also, I am setting the POV members based on the last changed cell.

  if(lstallRows.size() == 0){
 //No Changes
	println("No cells edited")
 }
 else{

If there are no cells changed, I will send a comment to the log, and the script will end.

else{
 
 println("Rows edited")
 
 def NoDupsRows=[]
 //Make the list of rows unique
 NoDupsRows=lstallCells.unique()
 String SNoDupsRows = NoDupsRows.toString()
// println("$SNoDupsRows")


//Start the Header of the script


scriptBldr << """
SET UPDATECALC OFF;


SET EMPTYMEMBERSETS ON;


SET AGGMISSG ON;


FIX ("$SScenario","$SVersion", @relative("$SEntity", 0), @RELATIVE( "$SCurrency", 0), "$SPlanElement") 
  FIX ("$SYear") 
"""

I am starting to write the Dynamic Essbase Script.  The first part is my global FIX that will be applied across the calculation. 

//Loop through unique cells
for (item in NoDupsRows) {
 SCurrAcct=item.toString().split(",")[0].trim().replace("[","").replace("]","")
 SPeriod=item.toString().split(",")[1].trim().replace("[","").replace("]","")    //.replace('"','')  Remove DoubleQuotes
  println ("The Cell is $SCurrAcct->$SPeriod")
  

  if(myPeriodRange.contains(SPeriod)){

//Build middle of script
//I would usually use a Template for this. 
		scriptBldr << """
        	$SCurrAcct(	
			$SPeriod =  $SPeriod*1000;	
            )
  			 """
			  }
            
}

In this next part, I am looping through all of the changed cells and breaking them into an account and period variable. Then if the SPeriod is in my level 0 member list, I will write the account block. It is a straightforward example where I am just taking the cell value entered and multiplying it by 1000.

//End of script


scriptBldr << """
ENDFIX 
ENDFIX
"""

After the loop, I write the last part of my calc script and add the closing fix statements.

At this point, the code is ready to send to Essbase to execute. Usually I would have these two lines in my script next.

println scriptBldr // Sends the script to the log

return scriptBldr  //Send Script to Essbase 



The return scriptBldr line executes the Essbase code but because it is a return you cannot perform any additional Groovy code.

In this example, we are not using the return to execute the rule!!!!

Introducing the Job Factory

Remember we want to run this code, and also execute a few other commands.
Instead of running the standard return, I am creating a job in the job factory.

The Job Factory is the ability for you to call a defined Job in the cloud.  I have only tested Rules but there are other types of jobs in the planning cloud.  You can call the job and check on its status all within your groovy script.  It does need to be defined before you call it.  To get around this problem I created a Rule called GroovyShell_Execute and use one variable to pass the dynamic Essbase code into the rule. The job is the Rule but because it is empty and accepts the variable I can make the content inside fully dynamic.

JobFactory jf = operation.application.jobFactory

JobDefinition jobShell1 = jf.job("GroovyShell_Execute", "Rules", ["GroovyCode" : scriptBldr.toString()])
Job job2 = executeJob(jobShell1)  //Job runs as child

The key here is that I am taking the ScriptBldr object and converting it to a String that the RTP can support. Variable and Rule below:

 

 JobDefinition jobDef = jf.job("OFS_Rollup", "Rules", ["YearVar" : SYear,"EntityVar" : SEntity,"ScenarioVar" : SScenario,"VersionVar" : SVersion,,"Currency" : SCurrency])
 Job job = executeJob(jobDef)  //Job runs as child
 // Job job = operation.application.executeJob(jobDef)
// println job.status



//Push the level0 data over to the ASO cube in synch
%Template(name:="Push2Report_T",application:="LDTEST",plantype:="OEP_FS",dtps:=("Entity":=[[SEntity]],"PlanElement":=[[SPlanElement]],"Scenario":=[[SScenario]],"Version":=[[SVersion]],"Currency":=[[SCurrency]],"Year":=[[SYear]]))



}

The next part is pretty simple.  I call the OFS_ROLLUP rule as a job and pass the variables into the runtime prompt. I am also executing a Smart push of the data from the Financial cube into a reporting cube. I am using a template for the Smart push that is defined here:

//Overide Map
Map<String, String> overrideMems = new HashMap<String, String>()

overrideMems.put("Entity", [Entity])
overrideMems.put("Plan Element", [PlanElement])
overrideMems.put("Scenario", [Scenario])
overrideMems.put("Version", [Version])
overrideMems.put("Currency", [Currency])
overrideMems.put("Years", [Year])

println("The Overides are " + overrideMems)

operation.application.getDataMap("FS2REP").createSmartPush().execute(overrideMems,true)

Here is a quick video of this in action

Summary

I know this was a lengthy post. If there are any detailed questions, please post your comments, and I can try to dive into some more detail. 

I think this is a compelling enhancement to Groovy in the Planning suite. What I am excited about:

• Create a template for each plan type and generate Dynamic Essbase code for multiple plan types from one Groovy Rule.  Execute Code in Plan1-> Transfer to Plan2-> Execute Code in Plan2-> Transfer to Reporting Cube 
• From a groovy side, if this were a rule set, I would have to iterate through the grid each time because any variables determined at the row or column level would have to be set in each script.
• When building a custom application, you could create one groovy rule, add it to all of your forms and utilize the form naming convention to determine what rules need to be executed within the groovy script
• All of the other ideas that the community will come up with once they start utilizing this functionality!!