Metadata loop using Groovy scripting in EPM Enterprise Cloud 8

When I wrote the blog post on using Metadata loop in Calc Manager Pete Nitschke commented on it. His comment was related to dynamic dimensions. If you are using a Metadata loop on an always-changing dimension like Entity/Account), then you’ll have to redeploy the rule after a metadata change.

Bummer!!!

I thought that was the case with Essbase rules, after a quick chat with Sree Menon from Oracle, he did inform that Pete is correct (I never doubted you, Pete 🙂 ).

When you deploy the Calc Manager rule, it pushes the rule to Planning repository, with the available members. Now when the hierarchy (on which the metadata loop runs) changes it won’t be able to find those new members.

On-prem customers, you are out of luck, EPBCS/PBCS + Modules/EPM Enterprise cloud customers rejoice 🙂 there is groovy to save the day.

You can use Groovy to mimic the same feature, and the beauty of that is Groovy rules are dynamic!!!
I did create the script on a planning app and changed the dimension names to replicate Sample Basic.

Here is how you can create a groovy script.
Use the script Editor -> Change the script type to “Groovy script”

If you are going to create a groovy script to do an intelligent calc (calculate only changed blocks) then you need the following lines.

// Grab current form data grid
DataGrid curDataGrid = operation.grid
// Get current cube
Application currApp = operation.application
// Get current cube
Cube currCube = operation.cube
// Grab members of the Page 
def povmbrs = curDataGrid.pov

Line 2 is getting the current grid(webform).

Line 5 is getting the current application that you are on. Similarly, line 8 is getting the current cube.

Line 11 we are getting the POV/Page information.

The approach we are taking with this script is to build a calculation script on the fly and pass it to Essbase for execution.
We are going to create a StringBuilder and add some SET commands.

// Construct a string builder to build the Calc Script
def scriptBldr = new StringBuilder()
// Create SET commands for Calc script
scriptBldr << """SET AGGMISSG ON;
SET EMPTYMEMBERSETS ON;
SET NOTICE LOW;
SET UPDATECALC OFF;
SET CACHE HIGH;
SET LOCKBLOCK HIGH;
"""

Next step is to get the members present in Page and POV (page is also POV for groovy).

// Grab members of the Page
def curMrkt=povmbrs.find {it.dimName =='Market'}.essbaseMbrName
// Grab members of the POV
def curScen=povmbrs.find {it.dimName =='Scenario'}.essbaseMbrName

We are finding out whether the dimension name is Market. If it is Market dimension, we are getting the Essbase member name of the member in the current grid. A similar approach is taken to find the Scenario member name.

In this particular form, we have the Period dimension in columns, and it is time to find out which of them are present in the form. You can calculate all period members if needed. The following is a demonstration of how to get members of a dimension from columns.

// Create lists all the members Period dimensions (columns on form)
def colmbrs = curDataGrid.columns
// create an empty list
def perColMbrs= []
colmbrs.each{ cols->
	cols.each{
      if(it.dimName =='Period'){
        perColMbrs << it.essbaseMbrName
      }
	}
}

curDataGrid.columns returns a list of list, now we need to get the member name, and that is the reason why you have to loop through it twice and generate a list with Essbase member names.

Once the list is generated and if you need to remove dynamic calc members, you can use the following script to do so.

// remove dynamic members from year
currApp.getDimension("Period").getEvaluatedMembers('Descendants("Period")',currCube).each{
	if (it.toMap().find{it.key == "Data Storage ($currCube.name)"}.Value == "dynamic calc"){
    	perColMbrs -= it.getName(MemberNameType.ESSBASE_NAME)
    }
}

getEvaluatedMembers uses Planning expression (the functions that are present in Form member selection), so you’ll have to write your own code if you need something like @REMOVE.

Member properties (it uses OutlineLoad utility format) are represented as a map, and we are checking whether the storage property of the current plan type is dynamic calc. If it is dynamic calc, we are removing that from our previous list of column members.

We got Page, POV, and column members, its time to find out the row members. As mentioned earlier, groovy can detect which cell was updated. However, from an Essbase standpoint, a cell level calculation is not that efficient we must use a block and calculate the block at once.

// Create a hashsets and map to get  unique row member combination from edited cells
def uniqueRowSet = [] as HashSet
// Create iterator of edited cells
curDataGrid.dataCellIterator({DataCell cell -> cell.edited}).each{
	def prdMbr=it.getMemberName('Product')
    //If more sparse members are there in rows add them here
    uniqueRowSet.add("\"$prdMbr\"")
}

How are we going to achieve that in groovy? That is an excellent question.
We can create a HashSet (which will not allow duplicates) and add all the sparse members that are changed to it. Since I’ve one member, I’m using the following format “\”$prdMbr\“”. If you got more sparse members in rows, then follow the following syntax.
“\”mbr1\”,\”mbr2\””

We got all the members we need to create the script, let’s start creating the calc script.

scriptBldr << """
FIX("$curMrkt")
	FIX("${perColMbrs.unique(false).join('","')}")
    	FIX("$curScen")
"""
uniqueRowSet.each{
	scriptBldr << """    		FIX($it)"""
}

If member names are repeated in the period list, then we are getting the unique names only. To prevent the update to the original list unique(false) is used.

Now that we got all the FIXes in place, it’s time to

Now that we got all the FIXes in place, it’s time to do something more interesting.
You can create a groovy script template. I did write about it here almost two years back. What I did miss in that post is how to add a pseudo step to get all the DTPs and make the DTPs as “Prompt it?”.
When you create a DTP in Groovy Template, check the “Prompt it” check box. (Mandatory is not needed)

Launch the template wizard designer and add a step, you don’t need to select any DTPs.

Here is my template.

Drag the template to the main rule and add ENDFIXes.

%Template(name:="TMPL_Groovy_MetaLoop",application:="Sample",plantype:="Basic",dtps:=("scriptBldr":=[[scriptBldr]],"currApp":=[[currApp]],"currCube":=[[currCube]]))
scriptBldr << """
			ENDFIX
		ENDFIX
	ENDFIX
ENDFIX
"""

Here is the full script in all it’s glory.

// Grab current form data grid
DataGrid curDataGrid = operation.grid
// Get current cube
Application currApp = operation.application
// Get current cube
Cube currCube = operation.cube
// Grab the current Page/POV
def povmbrs = curDataGrid.pov
// Construct a string builder to build the Calc Script
def scriptBldr = new StringBuilder()
// Create SET commands for Calc script
scriptBldr << """SET AGGMISSG ON;
SET EMPTYMEMBERSETS ON;
SET NOTICE LOW;
SET UPDATECALC OFF;
SET CACHE HIGH;
SET LOCKBLOCK HIGH;
"""
// Grab members of the Page
def curMrkt=povmbrs.find {it.dimName =='Market'}.essbaseMbrName
// Grab members of the POV
def curScen=povmbrs.find {it.dimName =='Scenario'}.essbaseMbrName
// Create lists all the members Period dimensions (columns on form)
def colmbrs = curDataGrid.columns
// create an empty list
def perColMbrs= []
colmbrs.each{ cols->
	cols.each{
      if(it.dimName =='Period'){
        perColMbrs << it.essbaseMbrName
      }
	}
}
// remove dynamic members from year
currApp.getDimension("Period").getEvaluatedMembers('Descendants("Period")',currCube).each{
	if (it.toMap().find{it.key == "Data Storage ($currCube.name)"}.Value == "dynamic calc"){
    	perColMbrs -= it.getName(MemberNameType.ESSBASE_NAME)
    }
}

// Create a hashsets and map to get  unique row member combination from edited cells
def uniqueRowSet = [] as HashSet
// Create iterator of edited cells
curDataGrid.dataCellIterator({DataCell cell -> cell.edited}).each{
	def prdMbr=it.getMemberName('Product')
    //If more sparse members are there in rows add them here
    uniqueRowSet.add("\"$prdMbr\"")
}
scriptBldr << """
FIX("$curMrkt")
	FIX("${perColMbrs.unique(false).join('","')}")
    	FIX("$curScen")
"""
uniqueRowSet.each{
	scriptBldr << """    		FIX($it)"""
}
%Template(name:="TMPL_Groovy_MetaLoop",application:="Sample",plantype:="Basic",dtps:=("scriptBldr":=[[scriptBldr]],"currApp":=[[currApp]],"currCube":=[[currCube]]))
scriptBldr << """
			ENDFIX
		ENDFIX
	ENDFIX
ENDFIX
"""
return scriptBldr