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

Leave a comment

Your email address will not be published. Required fields are marked *

8 thoughts on “Metadata loop using Groovy scripting in EPM Enterprise Cloud

  • Gaurav Saraf

    Hi Celvin,
    Is there any way to load a Planning Text Account dimension member using a groovy script. I’m doing some manipulations and creating a String in my groovy script and I want to copy that string to a Planning Text Account dimension member so that it is visible in a data form. I think the issue with creating a script in a groovy script is that it is a calc script which executes on essbase and not on Planning. Is there any way to achieve this and execute the script on Planning so that hsp_cell_text_value table is loaded

    Regards,
    Gaurav

      • Bharathi Durai Raj

        Hello Celvin,

        Is there any way to extract data from ASO cube and push it to BSO cube using a groovy scripting. I really appreciate if you could help me on this.

        Cheers,
        Bharathi Durai Raj

      • Tech_Hub

        Hi Expert –

        Is there a Groovy script, where i can import and run the existing Calc scripts from the front end instead of attaching it to the forms. and Select the POV from dialogue box, facilitate getting POV selection from front end? I am working on a custom cube and using the exisiting rules from other scripts. Just need a Groovy script to call them and get the POV on front end and run those scripts. withour any forms. I am not a Groovy expert and trying to acheive unable to get to the point on short notice.

        R,
        Tech_Hub

        • ckattookaran Post author

          how are you going to get a pov selection from the front end and run groovy using the backend? You can call existing rules, set them up as job,s and then use jobfactory to run them. You can also create Essbase calc scripts in groovy and then use executeCalcScript to run it.

  • Lakshmi Vurla

    nice code

    is there a way to calcualte the data base only dirty cells not from the impuit form
    for example i load the data using the DM but only few transactions when i run the aggregation i need to only calculate the new blocks or dirty blocks

    • ckattookaran Post author

      I’ve done it in old FDM, where you can get a unique list of loaded members and use that list for aggregating.