Adventures in Groovy – Part 6: Converting a POV into a Fix
Introduction
One of the fundamental features of a Groovy calculation is the ability to dynamically grab aspects of a grid, and getting the POV is something that is required to dynamically generate an Essbase calculation. There are times when the entire POV is required, times when only members from specific dimensions are needed, and situations where only the rows and columns of the edited cells are used to construct effective fix statements. All 3 of these situations will be discussed.
Use Case (Pulling POV Members)
Many times, the Fix statement of a calculation being built includes all members in a data grid POV.
Code
List<String> povMemberNames = operation.grid.pov*.essbaseMbrName
String calcScript = """
Fix("${povMemberNames.join('", "')}")
[Calculation]
EndFix;"""
Breaking It Down
The operation.grid.pov provides access to all the dimensions in the POV. From that, dimension names, member names, and other properties that are useful, can be accessed. When followed by a *, it returns all the dimensions in the POV to a list. Using the essbaseMbrName instructs the function to return every member in the POV. The povMemberNames variable stores a list of all those values.
When building the calcScript variable, ${povMemberNames.join(‘”, “‘)} will return the list, delineated with “,”. This would return something like membername1″,”membername2″,”membername3. This obviously is missing surrounding quotes, which is why it is embedded inside the quotes.
Use Case (Pulling Selective Dimension Members From The POV)
Pulling one, or all of the dimension values in a POV, in individual variables for later use, provides the ultimate flexibility. The following pulls all the members and stores them in a unique variable. Then, any dimension in the POV can be accessed. An easy way to accomplish this is to use the find method of a List object to filter the elements.
Code
List<String> povmbrs = operation.grid.pov
String curYear = povmbrs.find {it.dimName =='Years'}.essbaseMbrName
String curCompany = povmbrs.find {it.dimName =='Company'}.essbaseMbrName
Fix((${fixValues(curYear, curCompany))
[Calculation]
EndFix;"""
Breaking It Down
The first line stores the entire POV collection. That variable can be accessed and used like any Groovy collection. By using the find method, items in the collection can be filtered. povmbrs.find {it.dimName ==’Years’} will return the Years dimension object and one of the properties of that object is the essbaseMbrName. Using it will return an Essbase friendly member name.
The “fixValues” method converts strings to “Fix friendly” strings that can be used in an Essbase calc script. Any of the following objects can be inserted into this method.
- AttributeDimension
- AttributeMember
- Dimension
- Member
- PeriodDimension
- RtpValue
- YearDimension
The result is the dimension member name inside a fix statement.
Rows/Columns
Some of the real efficiencies with Groovy stem from the fact that the edited cells can now be determined and a Fix statement can dynamically be generated to isolate longer running calculation on only the rows and columns that have changed. In the following example, there are two methods to get the members.
Code
Set<String> periods = []
Set<String> products = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}, MemberNameType.ESSBASE_NAME).each { DataCell cell ->
periods << cell.periodName
products << cell.getMemberName("Product")
}
String calcScript = """
Fix("${periods.join('", "')}", "${products.join('", "')}")
[Calculation]
EndFix;"""
Breaking It Down
If you are unclear about how to iterate through a grid, read Adventures in Groovy Part 3: Acting On Edited Cells. Inside the dataCellIterator, the example assigns two list objects with the respective members. Since this only touches the cells that have been edited, only the periods and products that have been updated will be stored in the respective variables.
You may be wondering if this is truly the most isolated combination of data. Theoretically, the same product may not have the same edited months. You could further condense changes in the Fix statement by looping through the rows and creating a fix for every row. In this example, Period is dense, so doing this would not change the number of blocks. Depending on the application, taking this extra step might prove more efficient. Rather than complicate this further, we are assuming this isn’t required.
Every required dimension (Account, Entity, Period, Scenario, Version, and Year) has its own method. cell.periodName returns the Period member of the cell. All the dimensions have the same naming convention.
For custom dimensions, getMemberName can be used with the required dimension passed as a parameter. If you want consistency, this method can also retrieve the 6 required dimensions. cell.getmemberName(“DimensionName“), where DimensionName is an actual dimension, returns the respective member in that dimension.
Just like the previous example, add the variable to the Fix statement with a join and it returns the delimited list of members that have been edited.
- “${periods.join(‘”, “‘)}” returns the list of Periods that have edited cells, and
- “${products.join(‘”, “‘)}” returns the rows (or products) that have been edited.
Wrapping Up
One last step that can be added, and should be, is the check to see if any cells have been modified by evaluating the size of one of the lists that is created during the grid iteration. After the iteration is completed, the following can be added to accomplish this. If there are no cells edited, the calculation is stopped at the return line and nothing is sent back to Planning/Essbase to execute.
if(products.size() == 0) {
println("No edited cells found!")
return
}
Joining the examples above, the fix would look like this.
String calcScript = """
Fix("${povMemberNames.join('", "')}", "${periods.join('", "')}", "${products.join('", "')}", ${fixValues(curYear, curCompany))
[Calculation]
EndFix;"""
return calcScript
Conclusion
If you implement this, you will likely see huge performance improvements. In the client production applications, I have implemented this in, I see at least a 95% improvement in performance. This same logic can be used for Data Maps and data movements from ASO to BSO (which we will cover later)
Hi Kyle,
In my groovy script i have 2 business rule logic 1. Copying approved project to approved version 2. aggregation. 1 rule is added inside a for loop where i am running it for multiple projects passed by the user and 2nd rule i run outside the for loop. 1st rule works fine but 2nd rule which is simple aggregation does not get executed. If i keep 2nd rule inside the for loop then it works but if i do that based on number of approved projects my aggregation will run which is not needed. Can you help me with this?
Second issue is in the same groovy rule, i am also executing datamap at the end to send data from project to finance but even those are not running. If i execute the data map in a different groovy logic it works fine. Any reason why datamap commands getting ignored?
Without seeing it, I am guessing. My guess is that you have multiple return statements, and only the first is running. The script will stop after the return – it acts like an exit. You will have to keep concatenating both calculations to one variable and return it at the end. However, that will stop and your data sync won’t run. To do this, the only way I have found is to either have two rules and execute both on the form save, or add them to a ruleset and run that.
Hi,
Following is not working and giving
A validation error was received from the Planning server.
‘Error:The Groovy script failed to compile with internal error: Compile Error:
[Static type checking] – No such property: dimName for class: java.lang.String
@ line 19, column 35.
[Static type checking] – No such property: essbaseMbrName for class: java.lang.String
@ line 19, column 21.
[Static type checking] – No such property: dimName for class: java.lang.String
@ line 20, column 35.
[Static type checking] – No such property: essbaseMbrName for class: java.lang.String
@ line 20, column 21.
[Static type checking] – Cannot find matching method Script68#fixValues(java.util.Set , java.util.Set ). Please check if the declared type is right and if the method exists.
@ line 22, column 89. Rule McDFPA.MARG.100_PP_Input_Site_Attributes’
// Capture the edited periods, years and sites
Set entity = []
Set ownershiptype = []
Set curr = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}, MemberNameType.ESSBASE_NAME).each { DataCell cell ->
entity << cell.getMemberName("Entity")
ownershiptype << cell.getMemberName("Ownership Type")
curr << cell.getMemberName("Currency")
}
if(entity.size() == 0)
{
println("No edited cells found!")
return
}
// Generate the calc script to calculate the number of months, comp status and restaurant count for sites whose attributes were edited
List povmbrs = operation.grid.pov*.essbaseMbrName
String curScn = povmbrs.find {it.dimName ==’Scenario’}.essbaseMbrName
String curVer = povmbrs.find {it.dimName ==’Version’}.essbaseMbrName
String calcScript = “””
Fix(“${ownershiptype.join(‘”, “‘)}”, “${curr.join(‘”, “‘)}”, “${entity.join(‘”, “‘)}”, ${fixValues(curScn, curVer)})
“ACC_Comp_Sales”(
“ACC_Comp_Sales” = “ACC_Comp_Sales”;
)
EndFix”””
println(“The following calc script was executed by $operation.user.fullName: \n $calcScript”)
return calcScript.toString()
Working… thanks..
List povmbrs = operation.grid.pov
String curYear = povmbrs.find {it.dimName ==’Years’}.essbaseMbrName
String curCompany = povmbrs.find {it.dimName ==’Company’}.essbaseMbrName
String calcScript = “””
Fix(${fixValues(curYear, curCompany))
[Calculation]
EndFix;”””
Hi Kyle,
How do I use @relative(POVNonLevel0Selection,0) in the fix? same way you explained below or do we other way?
http://www.in2hyperion.com/2017/08/08/my-adventures-in-groovy-calculations-part-1/
That is kind of a loaded question if I understand the question. I want to say yes, the concept is the same. You build the string builder however you need to make the calc the way you want it to work. Hope that helps. If not, please try to clarify your question and I will try to answer a different way.
Hi Kyle
I was exploring the use of the code above to selectively pull dimension members from the POV.
I keep getting an error with the following;
List povmbrs = operation.grid.pov
The error that comes up on validation is;
A validation error was received from the Planning server.
‘Error:The Groovy script failed to compile with internal error: Compile Error:
[Static type checking] – Incompatible generic argument types. Cannot assign java.util.List to: java.util.List
@ line 30, column 24.
What does this error mean and what can I have done wrong aside from copying the same code for that row.
Because of the interpreter, I always recommend that you strictly define your variables. the getPov method you using returns a list of headercells. I think what is happening is that because you haven’t defined the list with an element type, and because of the planning compiler of groovy, it doesn’t understand what you are trying to do. If you don’t strictly define your variables you will get errors like this all the time. Stuff like you are trying to assign a number to a number or you are trying to assign a string to a string. You might have GString and trying to set that equal to a string and the planning compiler doesn’t convert this for you like straight Groovy. All this is covered in my classes at in2hyperion.podia.com. You are going to continue to struggle with this type of thing if you don’t educate yourself more on the differences between straight Groovy and Groovy in planning. All I can say is when I started this journey with Groovy I wish I would have had the resources now available because it would have saved me hundreds of hours and I highly encourage you to take advantage of it. I haven’t talked to anybody that didn’t think me for pushing them in that direction.
/*RTPS: {Assumption_Departments}*/
String calcScript = “””
Fix(“OEP_Working”, “SAR”, “No Custom 1”, “Catering BV %”, “L115”, &OEP_YearRange, “OEP_Plan”, “All-Day Dining”, “No Plan Element”)
“Dec”
if (@CURRMBR(“Account”) == #Missing) {
“Begbalance”
} else {
if (@CURRMBR(“Account”)->”Begbalance” == #Missing) {
“Begbalance”->”No Year”
}
}
EndFix;”””
Error: Cannot calculate. Essbase Error(0): Invalid Calc Script syntax [
“Dec”
if]
println calcString and check your log to see what is wrong with it. You can copy it from there into a rule and run it and it should help you figure out what you have incorrectly combined.
In the following Groovy script there is no validation error. But while running the script, it is showing error
Set periods = []
Set products = []
Set saleschannels = []
Set rm = []
Set pc = []
Set ps = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}, MemberNameType.ESSBASE_NAME).each { DataCell cell ->
periods << cell.periodName
products << cell.getMemberName("Product")
saleschannels << cell.getMemberName("SalesChannel")
rm << cell.getMemberName("RawMaterial")
pc << cell.getMemberName("ProfitCenter")
ps << cell.getMemberName("ProductionSite")
}
String calcScript = """
Fix("${periods.join('", "')}", "${products.join('", "')}", "${saleschannels.join('", "')}", "${rm.join('", "')}", "${pc.join('", "')}", "${ps.join('", "')}")
ac_cogs = ac_ic_qty;
EndFix;"""
Runtime error is: Error parsing formula for [calc-expression, at or after line: ["Fix("", "", "", "", "", """]] (line 2): invalid object type
it looks like your periods is null possibly. You can use ${cscParams(products,periods,saleschannels,rm,ps,pc)}