Adventures in Groovy – Part 6: Converting a POV into a Fix

Print

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

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

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

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.

Joining the examples above, the fix would look like this.

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)

 

Please follow and like us:
RSS
Facebook
PINTEREST
LinkedIn
 

2 Comments

  1. 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.

       

Leave a Reply

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