Adventures in Groovy – Part 3: Acting On Edited Cells
Introduction
With the introduction of Groovy Calculations this summer, one of the things I use most, especially for applications with data forms that include a large sparse dimension in the rows with suppression on, is the option to loop through cells and identify only the POV on the cells that have changed. In applications like workforce planning, or product level applications that have hundreds, if not thousands, of possible blocks, isolating only the changed data can have significant impacts on performance. Normally when a data form is saved, the fix includes all level 0 members of the employee dimension and must run the calculations on all of them, regardless of whether employee changed or not. Being able to fix on only a row, or the handful that change, give us a significant advantage in user response.
This post will go into how this is executed, and a few use cases to get you thinking about the possibilities. All of these I have developed and are in a production application.
The Code
Using a grid iterator, with the appropriate parameter, is an extremely easy way to deploy functionality that looks through ONLY the cells that have been changed.
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
[actions]
}
The cell object, and all its parameters, are available inside the loop. By adding {DataCell cell -> cell.edited}, the loop is isolated to only cells that have changed. The representative member names, the data value, if it is locked, has attachments, and many other things can be accessed with the example above.
Use Cases
An infinite number of uses are possible, as you are probably already thinking about. If not, the following will probably spark some creativity.
Customizing an Essbase Fix Statement
One of the most significant benefits of this is the ability to be able to dynamically generate a fix statement and filter what is calculated. Although the calculation on the Essbase side isn’t improved just by using Groovy, the ability to dynamically write the calculation on only what changed is significant, especially when allocations, data pushes, and other longer running processes are required.
Assuming the rows of a data grid include the dimension Product, and Period is in the columns, the following will create variables that will include only the periods and products that have been updated. These can be used in the string builder that is passed to Essbase. So, rather than @RELATIVE(“Product”,0) running on all possible products, it can be replaced with “Product 1″,”Product 2”.
The following creates list and string variable for Product and Period. Every cell that is updated will add the relative product and period to the list variables. After all the cell values have been read, the two string variables are set to include a unique list of the dimension members, surrounded by quotes, and separated by commas, which are immediately ready to include in the FIX statement.
def lstProducts = []
def lstPeriods = []
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
lstProducts.add(it.getMemberName("Product"))
lstPeriods.add(it.getMemberName("Period"))
}
def strProducts = '"' + lstProducts.unique().join('","') + '"'
def strPeriods = '"' + lstPeriods.unique().join('","') + '"'
The string builder would look something like this. In the following example, the other variables are pulled from the POV.
def sScenario=povmbrs.find {it.dimName =='Scenario'}.essbaseMbrName
def sCompany=povmbrs.find {it.dimName =='Company'}.essbaseMbrName
def sYear=povmbrs.find {it.dimName =='Year'}.essbaseMbrName
StringBuilder strEssCalc = StringBuilder.newInstance()
strEssCalc <<"""FIX($sScenario,
$sCompany,
$sYear,
$strProducts,
$strPeriods
)
Revenue = units * price;
ENDFIX
"""
At this point, the strEssCalc value can be passed to Essbase and executed. If only 2 products are changed in 1 month, only those 2 cells would be calculated. If this data form included 12 months and 1,000 products, the calculation would take roughly 1/500th of the time.
Customizing Smart Push
Smart Pushes on forms, depending on the POV, can exceed a threshold of what a user perceives as acceptable performance. In the 17.11 release, Data Maps and Smart Pushes can now embedded in the Groovy Calculations. The 2 huge benefits to this are that
- the data that is pushed can be filtered to only the data that changes, decreasing the time of the operation, and
- the ability control the operation order of when a push runs (for example, calculation, push, calculation, push)
If a data form has a smart push associated to it, it can be accessed and further customized. If not, data maps can also be accessed, customized, and executed.
One thing I learned from the Oracle development team is that the Smart Pushes have a max memory that can be accessed. One Smart Push may never hit that limit if it is isolated enough, but we found issues when multiple Smart Pushes were executed a the same time. We were seeing multiple, and intermediate, failures in the logs. So, it is even more critical to make these pushes as small as possible to eliminate that issue.
If we reference the example above in the customized fix, we expand on that and apply the same filter to the Smart Push. The only addition needed is to encapsulate the strProducts variable in quotes. If nothing is passed, it runs the smart push as it is setup in the form, so operation.grid.getSmartPush(“appname”).execute() would simply execute the same thing as if the Smart Push was set to run on save.
strProducts = """ + strProducts + """
if(operation.grid.hasSmartPush("appname"))
operation.grid.getSmartPush("appname").execute(["Product":strProducts,"Period":strPeriods])
Validate data
Having the ability to proactively perform data validation is another great addition with Groovy. Rather than running a calculation, and after the Data Form save returning a message telling the user that they have to change something, or changing it for them, we now can interrupt the data form save and instruct the user to change it before it has any adverse impact on the data. The following will change the cells that violate the validation to red, add a tooltip, stop the form save, and throw an error message. Assume we don’t want the user to enter more than 50,000 in salary. This threshold can point to data in the application, but is hard coded below for simplicity.
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
if(it.data > 50000){
it.addValidationError(0xFF0000, "Salary is outside of the allowed range.")
}
}
Conclusion
This is just a taste of what can be done. As you can see, with the ability to isolate actions on only the dirty cells, we now have opportunities we haven’t had since pre Smart View, and functions are completely new. The impact on performance is game changing and the ability we now have to interact with a user pre and post save is ground breaking to the possibilities.
Hi Kyle,.
How do we pass the edited cell to ASO rule. Also, what advantage does Groovy bring to ASO could you provide any example apart from movement of data from ASO to BSO
I have not passed edited cells to a procedural calculation, but I would think it would be the same process as BSO. I have several articles in this series about looping through edited cells that might help you. Apart from moving data, there is a lot of functionality at your fingertips. I have a colleague that bypassed BSO completely and wrote all the business logic in Groovy on data forms connected directly to ASO. Submitting data to ASO is the same as BSO, and honestly, the same logic as we have in this article. I am working on a post that will be available soon that walks through this in more detail, so stay tuned. Make sure you sign up for notifications about future posts and you will see it in the next few months.
OK, will wait for these post. Also, in one of your presentation you mentioned we can use Data Map to override security using Groovy rule, did you get it work with the end users ?
Unfortunately, I didn’t get the security bypass to work. Some other presentations has success with this, but I am wondering if this was something Oracle fixed/closed, or it the other contributors at KScope were incorrect. Oracle is working on a way to handle this. I am also working on a post on using valid intersections to get around this, but it is not completed yet. I have a lot of starts! I started a new opportunity with http://www.accelytics.com, and my time has been limited with respect to posting more info that I started. I sent you an email with an example of bypassing Essbase calculations. Thanks for participating in the community!
Hi Kyle,
Did you got success on overriding security.
If possible can you please mail the way the security is bypassed. I also facing the security issue when user triggers groovy rule.
Thanks,
Hi Kyle,
Can you please share how the user security issue was overcome while using groovy script to push data. I am also having similar issue admin is able to execute the same rule but for user it shows security error.
Thanks,
Vikas
Unfortunately, this doesn’t work anymore. The way I got around it was to setup valid intersection on account and some other static dimension. This way, users can’t enter data into the accounts you don’t setup as valid. They are locked in forms, but security doesn’t stop them from entering data so the data pushes work. I believe you can also use grids in groovy to write to those, but I haven’t tried that in a while.
editing the smart push with groovy worked as planned, thanks
is there a way to push only a specific point of view?
for example, if on the form the user edited the produt apple in january and the product orange in febuary. your smartpush will also push orange in january and apple in febuary , despite the fact they were not edited.
is there a way to be more presice? maybe not using lists but cubes?
Yes. I cover this in my class. You would have to iterate through the combinations And do multiple executions. https://in2hyperion.podia.com/groovyforplanning.
You have to be careful because there is a performance issue if you do too many.
i actully meant to iterate all the differante edited pov’s- save them to a variable of some sort, and only then push them at once.without multipule executions
also, are those classes help people who are not developers?
Not that I know of. The class is geared toward people of all experiences, but people that know less would benefit more. I am a finance person. I have no IT background at all. The class starts with very basic concepts and works through things like how to read the documentation and it builds on the basics. It is built with what I wish I would have had when I started. I knew nothing about groovy 2 years ago. The first time I touched it was when a client demanded things that pbcs could do. During the project groovy was released and I had to figure it out. I think it would be perfect for you and others with little to no experience.
Hi Kyle,
In the data form,The period dimesions are having Ilev0descentants(Period).
So the form has jan,feb..Q1,Q2,…Yeartotal. When the Jan cells are edited then
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
lstPeriodss.add(it.getMemberName(“Period”)) will give Jan,Q1 and Yeartotal..
I want to get only Jan..How to achieve this.Thanks in advance.
the space inside the dataCellItterator is called a predicate, which basically means anything that returns a boolean value. I think you would get a lot of out the training and highly recommend it. Check out https://in2hyperion.podia.com/groovyforplanning.
There are a ton of ways to do this. I use this method personally, but since Period isn’t a dimension that will likely ever change, you could hard code the months.
lstBasePeriods = operation.application.getDimension(‘Period’,rule.cube).getEvaluatedMembers(“@RELATIVE(YearTotal,0)”,rule.cube)*.name
operation.grid.dataCellIterator({ DataCell cell -> lstBasePeriods.contains(cell.getMemberName(‘Period’)) && cell.edited}).each{ cell ->
….
}