Adventures in Groovy – Part 22: Looping Through Member Descendants
There are a lot of reasons one might loop through children in a Groovy Calculation. On my journeys with Groovy, I have run into a few roadblocks where this has been helpful. Some of these were related to limits in PBCS. Looping through sets of members allowed us to get around some of the limitations.
- Running Data Maps and Smart Pushes have limits on the amount of data they can push when executed from a business rule (100MB).
- Using the clear option on Data Maps and Smart Pushes has limits on the length of the string it can pass to do the ASO clear (100000 bytes).
- The Data Grid Builders have limits on the size of the data grid that can be created (500,000 cells before suppression).
All 3 of these situations create challenges and it is necessary to break large requests into smaller chunks. An example would be running the methods on one entity at a time, no more than x products, or even splitting the accounts into separate actions.
Possibilities
Before going into the guts of how to do this, be aware that member functions in PBCS are available. Any of the following can be used to create a list of members that can be iterated through.
- Ancestors (i)
- Children (i)
- Descendants (i)
- Children (i)
- Siblings (i)
- Parents (i)
- Level 0 Descendants
More complex logic could be developed to isolate members. For example, all level 0 descendants of member Entity that have a UDA of IgnoreCurrencyConversion could be created. It would require additional logic that was covered in Part 11, but very possible.
Global Process
In this example, Company was used to make the data movement small enough that both the clear and the push were under the limits stated above. The following loops through every Company (Entity dimension) and executes a Data Map for each currency (local and USD).
// Setup the query on the metadata
Cube cube = operation.application.getCube("GP")
Dimension companyDim = operation.application.getDimension("Company", cube)
// Store the list of companies into a collection
def Companies = companyDim.getEvaluatedMembers("ILvl0Descendants(Company)", cube) as String[]
// Create a collection of the currencies
def Currencies = ["Local","USD"]
// Execute a Data Map on each company/currency
for (def i = 0; i < Companies.size(); i++) {
def sCompanyItem = '"' + Companies[i] + '"'
for (def iCurrency = 0; iCurrency < Currencies.size(); iCurrency++){
operation.application.getDataMap("GP Form Push").execute(["Company":Companies[i]
On Form Save
When there are large volumes of data that are accessed, it may not be realistic to loop through all the children. In the case of a product dimension where there are 30,000 members, the overhead of looping through 30,000 grid builds will impact the performance. However, including all the members might push the grid size over the maximum cells allowed. In this case, the need to iterate is necessary, but the volume of products is not static from day to day. Explicitly defining the number of products for each loop is not realistic. Below creates a max product count and loops through all the products in predefined chunks until all 30,000 products are executed.
def slist = [1,2,3,4,5,6,7,8,9,10]
// Define the volume of members to be included for each process
int iRunCount = 4
// Get the total number of items in the Collection
int iTotal = slist.size()
// Identify the number of loops required to process everything
double dCount = (slist.size() / iRunCount)
int iCount = (int)dCount
// Identify the number of items in the last process (would be be <= iRunCount)
int iRemainder = (slist.size() % iRunCount)
//Run through each grouping
for (i = 0; i <iCount; i++) {
// loop through each of the members up to the grouping (iRunCount)
for (ii = 0; ii < iRunCount; ii++) {
// Do the action
// slist[i * iRunCount + ii] will give the item in the list to use as needed
print slist[i * iRunCount + ii] + " "
}
}
// Run through the last group up to the number in the group
for (i = 0; i < iRemainder; i++) {
// Do the action
print slist[iCount * iRunCount + i] + " "
}
A Wrap
So, the concept is pretty simple. Now that we have the ability to do things like this outside of typical planning functions really opens up some possibilities. This example ran post-save, but what about pre-save? How about changing the color of cells with certain UDAs? What about taking other properties managed in DRM that can be pushed to Planning that would be useful? How about spotlighting specific products for specific regions that are key success factors in profitability?
Do you have an idea? Take one, leave one! Share it with us.
This is great Kyle! I was looking for alternatives today to Datamaps when limit is reached. This solves that problem.
You can probably reduce the looping by looping the members having data, this can be achieved by aggregation + datagridbuilder
In this example, we are moving data to an ASO cube with exactly the same conditionality. I would bet that the time it would take to consolidate would be longer than pushing nothing as when there is no data the push is very quick. I think checking to see if data exists in many situations is a great idea, though. Great idea.
Kyle,
can you think of any way to run any of the processes in parallel? Parallel processing some processes (particularly in the context of ASO calculations) is incredibly optimal from a performance perspective.
In many circumstances, splitting a task into smaller subsets within ASO procedural calculations can greater reduce the length of the calculation compared to running it for the entire group – this optimisation is further heavily optimised if those subsets can be isolated from each other and run in parallel.
Most groovy examples seem to rely upon Gpars (http://www.gpars.org/) a parallelism framework for Groovy and Java which doesn’t seem to be available in PBCS.
Any thoughts?
Cheers
Pete
I don’t believe this is available, but maybe down the road? I have actually been playing with a business rule to multi-thread it. What I am doing now is using a ruleset and running the n calculations using the parallel option in the ruleset. I created a rule that will take a predetermined volume of entities and execute the logic on those entities, and duplicated it 2 more times (changing the range to be executed on). For example, lets assume I have 3 rules. The first runs on the first third of the entities. The second rule runs on the second third. The third rule runs on the final third. Each rule is dynamic to the number of level 0 members in Entity. If there are 8 entities, 1-3 would be rule 1, 4-6 would be rule 2, and 7-8 would be rule 3. I am actually working on a post to walk through this, but this is a teaser! You could theoretically create a template and pass the number of times you want to split the entities (example was 3 in this case) and which range you want to execute. Currently, I just duplicated the rule and change the variable I created for these two options.
Hi Kyle, did you finally post about this parallel approach? I’m interested in trying it out.
Also, why use for loops, when you can just iterate over the members in a list using .each?
Regards,
Luis
I have not posted about the parallel approach, but it is just a checkbox in the ruleset. In this example, there was no form to loop through members. This was a global sync. If this was run from a form, more logic could be added for your suggestion and also only account for edited members.
You can create the list from the Dimension object itself, without a form. That’s what I did for the “admin” type rules, that are not attached to a form.
That is correct. There are a lot of opportunities, methods, and strategies to do some of these things and produce the same results.
Hi,
This is great. I combined this script & your other script for locking cells to achieve my requirement. I want to lock cells which fall under List price details (parent). So, i wrote below script –
Cube cube = operation.application.getCube(“OEP_FS”)
Dimension accountDim = operation.application.getDimension(“Account”, cube)
def AccountRange = accountDim.getEvaluatedMembers(“ILvl0Descendants(List Price Details)”, cube) as String[]
for (def i = 0; i < AccountRange.size(); i++) {
def sAccountItem = '"' + AccountRange[i] + '"'
operation.grid.dataCellIterator(sAccountItem.join(',')).each {
if(sAccountItem.contains(it.getMemberName("Account"))){
it.setForceReadOnly(true)
}
}
}
Problem is that it is not restricting the locking to members under List price details. It locked revenue members also which are under a completely different parent. When I wrote another for loop for revenue & did – it.setForceReadOnly(false), it worked. I had to forcefully off locking for revenue members.
My question is – in above script, when I have written ILvl0Descendants(List Price Details), then shouldn't it just work for these members? Why did it lock revenue member cells too?
Appreciate your blogs Kyle a lot! They are amazingly helpful 🙂
Thanks for the positive response.
Hi, I want to aggregate a list of cost centers inside FIX using @ANCESTORS function by iterating through the array which is basically a list of all the cost centers against which the data is edited. I know that the @ANCESTORS will only accept one member at a time, I want to iterate it the way you did it but within a FIX statement.
Regards,
SM
It may not be the best way, but what I did was iterate through the edited cost centers and add the ancestors to an array. I then got the unique members. At this point, you have a list of all the parents, but not necessarily in the right order. The only time I had to do this I only had two levels so it was easier because I only had 2 levels to figure out what I had to do. I don’t know if you put all those in a fix if it will run them in the right order, but I think if you do something like this, you can get to where you need to be. Share the solution if you will…I am sure we aren’t the only people that have to deal with this situation.
We attempted to add a rule set to an action menu item and the second rule in the set runs a data map. The problem is that only admins are allowed to run data maps so all other users are unable to run it. We are unable to add it to the rule set as a smart push because it’s associated with the action menu and not the form itself. Has anybody run into this issue or have any ideas on how to get around that admin restriction?
You can use Groovy to convert any data map to a smart push.