Adventures in Groovy – Part 54: Collections Are Your Best Friend

I received a number of questions when I attended KScope. A lot of people had a tough time wrapping their head around the fact that using Groovy could actually reduce cost and development time, and significantly decrease the effort to maintain an application. The feedback I get on my lightning lessons has been overwhelmingly positive. The basis of the concept of the use of Groovy making things simpler stems from understanding, using, and becoming confident using collections. I thought I would share some examples, use cases that you all use, and some samples to get those started. If you doubt Groovy can mean simpler and faster, I HOPE this will get you to at least think about coming over to the dark side and provide some perspective that will help.

I would like to create more of these and potentially move my training to be free. Before I can do that I need everybody to watch what I post AND subscribe! If I can generate enough of that, I can create enough add revenue to fun my effort and time. So subscribe, watch, and we will see what happens.

In the mean time, let me know what you think of having videos like this rather than traditional written content. If you would like to learn more about dynamic scripts for data maps, forms, and improving performance with Esssbase scripts by removing serial mode and threading the right way, head over to in2hyperion.podia.com. I have more lightening lessons ready for you to take advantage of!




Adventures in Groovy – Part 52: And You Thought Essbase Only Stored Numbers

My 20+ years of using Essbase I was told, and had no reason not to believe, only stored numbers. We obviously have lists and with Planning it appears we have text. If you aren’t familiar with how this works, the strings are stored in the Planning repository and the index is stored in Essbase. If you thought the same thing, you were as wrong as I was.



What is NaN

I have been learning and implementing Groovy solutions for 2-3 years now and came across something I now only have never seen, but didn’t think was possible. Java, and therefore Groovy, has a concept of NaN. NaN stands for Not A Number. NaN is the result of mathematical operators that create non numbers. Log, square root, division, and I am sure plenty of other formulas that I learned before I was 15 and long forgot, can result in what Java interprets as non numeric values. The two that I have found are NaN and Infinity. An example of 4/0 would result in NaN. 0/4 would result in Infinity.

NaN in Groovy

Prior to about 2 months ago, I accounted for these scenarios in my logic and never had an issue. Recently, in writing some basic math, like revenue / units, I didn’t account for the possibility that revenue or units would be a zero. If these scenarios are tested in Groovy, errors are encountered and honestly, I thought my logic in a business rule would have produced a divide by 0 error when the denominator was a 0.

java.lang.ArithmeticException: Division by zero
at ConsoleScript2.run(ConsoleScript2:1)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

I thought, like in Essbase, 4/0 would result in a 0. I found out the hard way that is not the case!

Types Of NaNs and Infinities

In my case, I didn’t care of the sub type of Nan or Infinity the results was, just that it happened and I needed to account for it. These can be checked very simply.

double simpleSample = 4/0
if( simpleSample.inNaN() || simpleSample.isInfinite() ) {
  println 'ERROR'
}

Your situation might be different. If it is, these are the types I am aware of that you can check for

//NaN variances
isNaN(nan)
isNaN(zero_div_zero)
isNaN(sqrt_negative);
isNaN(inf_minus_inf);
isNaN(inf_times_zero);
isNaN(quiet_nan1);
isNaN(quiet_nan2);
isNaN(signaling_nan1);
isNaN(signaling_nan2);
isNaN(nan_minus);
isNaN(log_negative);
isNaN(positive_inf);
isNaN(negative_inf);
 // Infinite variances
isInfinite(positive_inf);
isInfinite(negative_inf);

What Do You See In Planning and Essbase

So here is where I really was confused! Everything I I thought I knew was wrong.

Surprise Number One

If either of these conditions occurs, the cell that was calculated in Groovy and stored in Planning/Essbase is actually stored differently. I can’t say for sure what happens on the back end, but when the data is exported, rather than a numeric value, it will export NaN. Yes, you will see something like 10,20,20,NaN,40….

Surprise Number Two

If either of these conditions occurs, the cell that was calculated in Groovy and stored in Planning/Essbase shows a number that makes no sense in a data form when opened in Smart View. A value of 65535 will be displayed. This value can be edited/changed. If it is the source of another member formula or calculation, it will also show a value of 65535.

Surprise Number Three

The same thing is NOT what you see in a data form opened in the UI. In the UI (web version), NaN or Infinity will actually be displayed in the effected cell. This almost makes sense if I didn’t see 65535 in Smart View.

Stop NaNs From Happening

There are probably a million ways to handle this. For what it is worth, I want to share how I handled it and why. First, I created a function in my calculation that accepted one parameter, which was the value in which I was evaluating for Nan or Infinity. Inside this I used an Elvis operator and returned 0 if it was Nan or Infinity, and the value submitted to the function if it was a numeric value. The reason I created a function was because I had more than 30 formulas that I needed to check for this and it was easier to write the code once.

double nanCheck(double input){ (input.isNaN() || input.isInfinite()) ? 0 : input }

// Use Case Example
DataCell rate
DataCell units
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{cell->
    rate = cell
    if(rate.accountName == 'Small_Unit_Cost'){
        units = cell.crossDimCell('Small_Units')
        units.data = nanCheck(cell.crossDimCell('Revenue').data / rate.data)
    }
    else if(...)
        {...}
}

That’s A Wrap

it is really important to account for this for obvious reasons. If you are testing for NaN and Infinity, save yourself some trouble and if there is a possibility of it occurring, start doing it now. It is a pain to strip it out afterwards if it gets into a UAT situation or even Production. One last thing. If you are looking at this and thinking, this should really return #Missing. You surely can do that. There are a few changes that have to be made. First, the function can’t be double. Since #Missing is a string, it would need to be a string. The second issue is that you can’t set data, which is a double, to a string. You would have to use formattedValue. The changes would look something like this.

String nanCheck(double input){ (input.isNaN() || input.isInfinite()) ? '#Missing' : input }

// Use Case Example
DataCell rate
DataCell units
operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{cell->
    rate = cell
    if(rate.accountName == 'Small_Unit_Cost'){
        units = cell.crossDimCell('Small_Units')
        units.formattedValue = nanCheck(cell.crossDimCell('Revenue').data / rate.data)
    }
    else if(...)
        {...}
}



Adventures in Groovy – Part 51: Dynamically Changing Storage Properties When Using Hybrid

With hybrid being used more and more there is a need to manage the storage methods of different levels of sparse dimensions.  Whether it is a staggered hierarchy or not, getting the storage method from the source can sometimes be challenging.  More often times than not, you may want to own it on the Planning side so you can change it at will and not have to go through the typical IT change order process that may take weeks, or even months, to go through the full development cycle.



Managing this manually would not be fun, especially if the hierarchy is loaded more often than monthly.  Yes, you could use the Smart View admin option, but it is manual and let’s face it, you have a ton going on and you will make mistakes. 

In Comes Groovy

With Groovy, a calculation can be written to update metadata.  I have talked about this in several other posts, but I am going to walk through a couple specific examples that are for specific situations.  I think this will spark some interest of taking this further for situation similar, or completely different. 

Reusable Concepts

Before I jump into the situations and examples, there are a couple techniques that will be reused in all the examples.  Rather than repeatedly explain them, let me first introduce them.

First, this situation assumes that the storage methods are different for the plan types.  This might be more unique, but it is easy to deal with.  If this isn’t the case, the properties in the example can be change to “Data Storage”

It is always a good idea to start every Groovy script off with the RTPS tag.  To understand more about why this is important, read Part 49  This will be used in each example.

/*RTPS: */

Each example requires methods that have to have the dababase passed to it.  The easiest way to get the cube the rule runs on is to use rule.cube.  There are other ways to accomplish it, but this is the shortest and most dynamic.

List<Member> products = operation.application.getDimension("Product",rule.cube).getEvaluatedMembers("Descendants(Product)", rule.cube)

Each example gets the dimension and holds it in a variable.  The method requires a pointer to a cube, or cubes.  Often it is easier to pass the cubes in the application, rather than one cube, to make sure all artifacts are available and not hard coded.  operation.application.cubes as Cube[] returns all the plan types as an array of variables that are of type cube.

A note about the parameters that can be used.  It is much faster to use the same parameters that are used in planning, like the options in a data map.  You CAN use most of the Essbase function.  Oracle doesn’t recommend them.  They are slower, but if you are not iterating and running the request numerous times, I haven’t noticed a difference.  In this example, it is executed once, so the performance degradation is minimal.

Dimension objDim = operation.application.getDimension('Product',operation.application.cubes as Cube[])

To get and set the properties of a member, the toMap method is used.  This will return all the properties of the member and I wrote a summary of the use of this method in a prior post found in this post – Part 11 – Accessing Metadata

Map<String,Object> memberProps = it.toMap()

Lastly, if you aren’t familiar with regular expressions, they can be of great use.  I have a module dedicated to this in xxxxx.  I struggled understanding regular expressions for years.  But I promise you, if you take 4 hours and focus on learning them, it will click.  To use it in Groovy, using the matches method allows this.  Briefly, here are some basic concepts.  A ^ means starts with.  A $ means ends with. A dot means any character, and following that with an asterisk means many.  So .* means one to many characters of any type

.matches("^.*Region$") || it.name.matches("^District.*$")

Setting All Parents To Dynamic

If you have a smaller hierarchy, one with maybe only a few levels, it might be advantageous to just set all the parents to dynamic.  The following script iterates through all the product members and sets every parent to dynamic.

/*RTPS: */
List products = operation.application.getDimension("Product",rule.cube).getEvaluatedMembers("Descendants(Product)", rule.cube)
List<Member> lev0Products = operation.application.getDimension("Product",rule.cube).getEvaluatedMembers("ILvl0Descendants(Product)", rule.cube)
Dimension objDim = operation.application.getDimension('Product',operation.application.cubes as Cube[])
products.each{
try{
       Map<String,Object> memberProps = it.toMap()
       if(lev0Products.contains(it)){
           memberProps["Data Storage (${rule.cube})".toString()] = 'never share'
    }
    else{
           memberProps["Data Storage (${rule.cube})".toString()] = 'dynamic calc'
    }
    objDim.saveMember(memberProps)
}
catch(Exception e) {
    println("Exception: ${e}")
    println it.name
}
}

Use Patterns To Set Parent Storage Property

In some situations, there are patterns to the levels of your hierarchy.  Maybe you have regional levels that are definable and unique that can be used to set different levels to dynamic.  Assume the following naming convention for this example

  • Total Products
    • West Region (everything ends in Region)
      • District 1 (everything starts with District)
/*RTPS: */
// Get every product in the hierarchy
List<Member> products = operation.application.getDimension("Product",rule.cube).getEvaluatedMembers("IDescendants(Product)" , rule.cube)
// Assign the producdt dimension to a variable
Dimension objDim =  operation.application.getDimension('Product',operation.application.cubes as Cube[])
// Loop through each product
products.each{
       // if the product matches these expressions, change the app setting to dynamic
       if(it.name.matches("^.*Region$") || it.name.matches("^District.*$") || it.name == 'Total Products'){
              Map<String,Object> memberProps = it.toMap()
        memberProps["Data Storage (${rule.cube})".toString()] = 'dynamic calc'
        objDim.saveMember(memberProps)
       }
       // otherwise change it to never share
    else{
              Map<String,Object> memberProps = it.toMap()
        memberProps["Data Storage (${rule.cube})".toString()] = 'never share'
        objDim.saveMember(memberProps)
    }
}

More Complicated Possibilities

There are a bunch of other possible needs.  Let’s say you have a need to make everything above level 3 dynamic.  First, if the hierarchy is staggered, the same level can be a 1 and 5.  You would have to decide how to handle that.  I would lean toward if it was a level 1 and a 5, I would make it dynamic because that might also mean your level 5 and 9 in that portion of the hierarchy would be a pretty deep hierarchy to make 9 levels dynamic.  Every situation is different, and performance would have to be evaluated, but the complexity of identifying how to set the storage in these situations is what I am trying to explain.

If you want to use patterns, you may also want to ensure that the pattern isn’t replicated at a parent and level 0, so there may be a need to check for both a pattern and the level of the member.

Obviously, there are an infinite amount of possibilities and each one could introduce complexity.  Just understand that almost anything can be defined by patterns and levels and can be accomplished, but the level of complexity of your logic or your regular expression may increase.

That’s A Wrap

The bottom line is that we now have the ability to do a lot of things we relied on the source system to do.  Or, maybe external scripts were run using Perl, or VBScript, or PowerShell.  We can use metadata properties, dynamic levels, any other repeatable pattern that might possibly come up.  It is fast and reliable, and completely in our control so we don’t rely on others when it is broken or needs changed.




Getting The Most From Your Oracle Support

Nobody will argue that Oracle Support is a word class experience.  Like it or not, the service is here to support thousands and individuals can get lost in the process.  If you navigate the process correctly, you can get results.

What Do Do First

Your first step should be to troubleshoot the issue.  Do everything you can for support.  The more you provide, the less “did you plug in the computer” type questions that delay you getting help  you will get.  Be clear and concise.  The first few levels of support are likely less experienced than you and will only be able to provide novice level help most of the time.

Next, make sure the content of your support ticket is complete.  Don’t leave any questions.  What I have found to be extremely helpful is to video the issue.  Make sure you show the problem and walk through the calculation or script or whatever is the source of the issue.  This doesn’t have to be a professional video.  There are free screen recording applications.  There are also some pretty inexpensive ones.  If you use a Mac, ScreenFlick is 35 bucks and well work it.  It gives you the basics.  If you use SnagIt, it also works just fine.

If you are a Windows user, there is a nice free option, but you probably haven’t noticed it, and for good reasons.  If you look through the Start menu, you won’t find any reference to a screen recorder. You’re not missing it… it simply isn’t there. That’s because rather than making the screen recorder a standalone app, Microsoft included it as a feature of the Xbox Game Bar in Windows 10.

Your Second Step

After you open a ticket and provide your video of the issue, proving there is an issue without a doubt, follow these steps

  • Make sure you get a duty manager assigned and push for escalation
  • Call 1-800-223-1711
  • Press 1 and enter your SR number
  • Press 2 to speak with a manager
  • Do not press 1 to speak with Engineer, this will likely result in going straight to Voicemail
  • Tell the Support Hub the critical impact you are facing
  • Request a Duty Manager callback ASAP
  • Repeat request level 2, then level 3, to level n, until you get what you want
  • Call hourly until you get what you want – the squeaky wheel gets the grease

During this process, here are some things make sure you get the most out of the experience.

  • Speak with the support hub manager
  • Describe the impact, include key project milestones at risk and communicate what you need for progress
  • If your issue is CRITICAL you should ALWAYS make a request for a duty manager call back.
  • If you ask for a duty manager call back, wait 30-60 minutes until you receive the call and reiterate the problem
  • Be strategic with your request
  • Don’t just say you need to escalate – describe the impact and communicate what you need for progress
  • Keep in mind, your SR will NOT appear in an “escalated” state unless you negotiate this with a duty manager

That’s A Wrap

Should you have to do all this?  No.  The experience should be better, but yelling about it and banging your fists on the table won’t change anything.  You have to play the game and go through the process.  If you use these tactics, I guarantee you will improve the response.

 

 




Adventures in Groovy – Part 50: Incredibly Awesome New Methods

Oracle has recently added 2 fantastic new methods that significantly improve our ability to minimize our scripts and reduce the effort to build and maintain them.  The first is a method in the EPMScript class called cscParams.  The second is in the cube class named executeEssbaseScript.

cscParams

This method accepts almost any parameter, whether it be a member, string, list, array, or map, and converts every one of them to a delimited, quoted list of strings primarily used in FIX statements.

This is far and away better than fixValues, as that only accepted certain objects.  It doesn’t accept strings, lists, or maps.  It is also better than building string concatenations using escapes for quotes.

executeEssbaseScript

This method, in the cube class, gives us the ability to execute strings as calculations scripts.  Yes, we have had this before, but not in this capacity.  This has significant improvements over previous methods.

  1. This allows us to continue another process, unlike using return or just sending the last string as a calculation.
  2. This method returns the Essbase calculation error back to groovy so it can be used to dynamically account for errors.
  3. It also allows access to the @RETURN response, so that can be used to interact with calculations in a way we never had had before.

Examples And Use

Since I added these to the training offered at in2hyperion.podia.com, I thought it would benefit you all to have access.  I created a class that is free.  All you have to do is create an account and “purchase” a free class…for free!.  Each module is almost 10 minutes and goes through the use case.  I know many have concerns about investing in the classes, so this will provide a great sample of how the classes are constructed and facilitated.

That’s A Wrap

I am really happy/relieved/excited at the feedback I am getting.  Here are a few comments I have received.

  • Wow!  I can’t tell you how happy I am with the course you put together.

  • Module 10 is incredible.  This whole experience has changed my role in the organization.  I am the go-to person and asked to get involved in all of our projects.  The explanations are fantastic and delivered in a way that it just makes sense.  This is the best training I have ever taken.

  • I just finished your Groovy training. I happened to start it just ahead of needing it on a new project. I’ve now written a few Groovy rules to do things not otherwise possible and am loving it.

I know you might be concerned about the effort to create an account, but it will take just a minute or two, and it protects the content from being shared and copied on YouTube.  You will also be notified of any updates to the classes that you have access to.  The class can be purchased here.

COVID-19

We are living in unprecedented times.  Every few days I learn something new about how people are impacted.  People are spending thousands to create home offices.  People are isolated from the ones they love the most.  My college roommate passed away last week and I couldn’t be there, I couldn’t help his parents, and the funeral is for 10 people.  I know people have it way worse than me.  Our medical peers are in harm’s way.  But I hope positive things come out of all of this.  Working from home will be more acceptable.  People will appreciate things they took for granted before.  We will all become better cooks.  I think services are stretched and it will create opportunities for new and improved offerings.

The stress we are all under is starting to show though.  I see it in meetings.  I see it in my family.  I see it in social media posts.  People are moving less and we all know that exercise helps stress. I know my family’s sleep patterns are messed up.  Please be kind to each other.  Please let snippy comments go.  Please bite your tongue when somebody says something they shouldn’t.  And if you don’t, take a breath, walk away, come back and show some compassion.  None of us are perfect and this is causing enormous amounts of stress that honestly, I am just realizing.




Adventures in Groovy – Part 49: Unable To Retrieve Variable Deployed In Application

Have you ever gotten an error that reads, “Unable to retrieve variable variable name deployed in application?”  When you look at the error, the variable name in the error message doesn’t exist and you aren’t trying to retrieve such a variable? If you spent enough time, or your script was small enough, you might have recognized that the variable name that it can’t find is a Groovy variable.

The Issue

The following script is an example, and likely a piece of a larger script.  All this does is create a string variable named uda and sets it equal to Locked.  It is then used in a metadata query that returns a list of the name of all the members with a UDA of Locked.

String uda = "Locked"
List<String> lockedAccts = operation.application.getDimension('Account',rule.cube).getMembersWithUda("${uda}",false,rule.cube)*.name

When this is executed, it returns an error that reads Unable to retrieve variable uda deployed in application.

Why is the script looking for a substitution variable of uda?  You might be thinking, I know I have put Groovy variables in strings and this hasn’t happened, so what the heck?  Can I now use Groovy variables?

Struggling through this, once again the Oracle development team, and you know who you are, helped me figure this out.  And, here is the reason this is happening, how to fix it, or use it to your advantage.

The Why

This is actually a bug of sorts. As Oracle has added functionality, this cropped up as an unintended issue.  Originally, anything in squiggly brackets is interpreted as a variable, like in an Essbase calculation.  This was built to interpret this.  When Groovy was added, and using the same syntax to reference Groovy variables, not substitution variables, the interpreter of the Groovy script kind of tripped over itself.  The way it is built is to look at these in the native Essbase variable ONLY IF there are no run time prompts identified.  If there are RTPs in the script, the interpreter changes and interprets them as Groovy variables.  Since so many clients use this for the unintended purpose of identifying substitution variables, Oracle can’t “fix” it as it would completely hose a lot of people.

The Fix

The fix is simple, add run time prompts!  Seriously, I have to add an RTP even though I don’t need one?  No, all you have to do is enter /*RTPS:*/ ANYWHERE in the script. I normally add it to the top and the script above would now look like this.  When I add the RTP indicator, even with no run time prompts, the interpreter doesn’t look for anything in squiggly brackets to variables, but the more likely expectation, a Groovy variable.

/*RTPS:*/
String uda = "Locked"
List<String> lockedAccts = operation.application.getDimension('Account',rule.cube).getMembersWithUda("${uda}",false,rule.cube)*.name

When I run this script, I get a successful message.

That’s a Wrap

This post was a long time coming. I had somebody ask me about it and it made me realize that I haven’t shared this information.  I make it a practice to always add /*RTPS:*/ to every Groovy script.  If you want to get more information like this, become an industry leader, check out Groovy For Planning.




Adventures in Groovy – Part 47: Real World Wins, Part 1

A specialty chemical products company created an ASO reporting cube in ePBCS.  This application housed detailed data that rolled up to a GL account.  The problem was the source didn’t always tie to the GL due to restatements, GL entries, and manual corrections.  Since they wanted the data to match to the book of record, but still have the level of reporting  at the detailed level, they wanted to load data from both systems, calculate the variance, and store that variance so users would see the difference between the two sources.

There were a number of challenges.  Procedural calculations were “too complex” and could not calculate at all the levels.  They didn’t want to engineer a new solution that included a BSO calculation where the data was replicated and calculated.  So, Groovy was selected to calculate the variance.

Dimension Explanation

To simplify the explanation, this following Smart View retrieve will help tell the story.

Organization dimension includes sub organizations from the ERP, as well as the official organization in HFM.  If Ohio was an organization in HFM, the hierarchy would look similar to this.

  • Ohio_Total
    • Ohio
    • Cincinnati
    • Columbus
    • Cleveland

I would have preferred to use a source dimension for this, but the application didn’t include one.  So, we used version, and it look like this.

  • Total Working
    • Working (where the actuals were loaded) – named working1 in the script below
    • Working Adjustment (where the adjustment to get back to the book of record was stored) – named Working1_LStat_Adj in the script below
  • System Versions (hidden from users)
    • HFM Stat (where HFM was loaded) – named HFMLocalStat in the script below

Groovy Calculation Overview

The calculation prompted the user for a start and end month, and the year. The follow was done at the bottom of every other dimension in the application.

  1. The first piece of the calculation queried the Org hierarchy and found all the level 1 members that ended in _Total (Ohio_Total in the example above).  These were stored in an array to be used later.
  2. The second step was to retrieve the data from HFM (HFMLocalStat) and the data loaded from the ERP (Working1) at level 1.
  3. The third step was to calculate the variance between x_Total member for the ERP and HFM data and store it at the HFM child member in Working1_LStat_Adj.
  4. The last step was to submit the results

The application didn’t have a source dimension, which is normally where I would account for this, so we added a few additional versions.  The first was a statistical version that was only used to house the data imported.  Users didn’t have access to this version.  The GL data was loaded to a statistical version.  The working version was compared to the statistical version, and the difference was stored in working adjustments.  The two of these combined was used to report from

Groovy Calculation Deep Dive

The calculation would skip the months that had no data.  For 12 months, the calculation finished in less than 5 minutes.  The calculation was designed, built, system tested, and went through user validation in a few days.  The total development time was less than a day.  The administrators could use the existing architecture with no changes required (outside of creating a new member in the xxx dimension)

[membership level=”0″]


In2Hyperion has premium content that is only available to those who make a small annual donation.

Already have an account with
in2Hyperion?

Login

Want to purchase a subscription to
the premium content?

Purchase Today


[/membership]
[membership]

/*RTPS: {rtpMonth} {rtpYear} */
def rtpMonth = rtps.rtpMonth.getEnteredValue()
def rtpYear = rtps.rtpYear.getEssbaseValue().replaceAll('"', '')

def lastMonth = Calendar.instance.with {
time = new Date().parse("MMM", rtps.rtpMonth.getEnteredValue() )
it[ MONTH ]
}

// Setup connections
Cube cube = operation.application.getCube("Operations")

// Identify the orgs that are taken into account and need adjusted
// members with a parent equal to the child with a _Total suffix
Dimension orgDim = operation.application.getDimension("Org", cube)
def keyProp = "Parent"
def orgParentChild = [:]
def Orgs = orgDim.getEvaluatedMembers("ILvl0Descendants(All Org)", cube) as String[]
Orgs.each{
  Member orgMbr = orgDim.getMember(it)
  def memberProps = orgMbr.toMap()
  if(memberProps[keyProp] == it.toString() + "_Total"){
    orgParentChild[it] = it.toString() + "_Total"
  }
}
def orgParents = orgParentChild.collect{it.value}

// Loop through months selected
(0..lastMonth).each{ iMonth ->
  def runMonth = new java.text.DateFormatSymbols().months[iMonth]
  def runDay = "${iMonth+1}-1"
  println "Executing for ${runMonth}"

  // retrieve the ERP and HFM data points
  DataGridDefinitionBuilder EntityBuilder = cube.dataGridDefinitionBuilder()
  EntityBuilder.addPov(['Market','Project','Scenario','View','Years','Currency','Product','BT_Customer','Customer'],
                       [['Total_Market'],['Total Project'],['Actual'],['PTD'],[rtpYear],['USD'],['Total Product'],['BT_Customer'],['TotalCustomer']])
  EntityBuilder.addColumn(['Period','Version'], [[runMonth] , ['Working1','HFMLocalStat','Working1_LStat_Adj']]) 
  EntityBuilder.addRow(['Entity','Org','Account'], [ ['ILvl0Descendants("Total Entity")'],orgParents,['ILvl0Descendants("NetIncome")'] ]) 
  EntityBuilder.setSuppressMissingBlocks(true)
  DataGridDefinition EntityDefinition = EntityBuilder.build()

  // Calculate and submit the adjustments
  // Build the grid based on the rows in the previous grid
  cube.loadGrid(EntityDefinition, false).withCloseable { entityGrid ->
    if(entityGrid.size() > 0){
      DataGridBuilder builderSubmit = cube.dataGridBuilder("MM/DD/YYYY")
      builderSubmit.addPov('No Market','No Project','Actual','PTD',rtpYear,'USD','No Product','BT_No Customer','No Customer','Working1_LStat_Adj')
      builderSubmit.addColumn(runDay)
      entityGrid.dataCellIterator("Working1").each{ it -> 
        builderSubmit.addRow([it.getMemberName("Entity"), it.getMemberName("Org").toString().reverse().drop(6).reverse(), it.getMemberName("Account")], [it.crossDimCell("HFMLocalStat").data - it.data])// it.crossDimCell("HFMLocalStat").data
      }
      DataGridBuilder.Status status = new DataGridBuilder.Status()
      DataGrid gridx = builderSubmit.build(status)
      cube.saveGrid(gridx)
      println(" Total number of cells accepted: " + status.numAcceptedCells)
      if(status.cellsRejected.size() != 0){
        println(" Total number of cells rejected: " + status.numRejectedCells)
        println(" First 100 rejected cells: " + status.cellsRejected)
      }
      gridx.close()
    }
    else{
      println "No data available."
    }
  }
}
println "Finished"
return

[/membership]

That’s A Wrap

This is a great example of how we can inject new and creative ways to solve problems with speed in processing, speed in developing, and introducing no additional maintenance.  Look for more of these real world wins in the future.

 




Adventures in Groovy – Part 46: Start Making Rules More Reusable, Part 1

One thing that I have spent a lot of time on is making calculations independent of forms so that they can be used on any form.  For example, rather than hard coding a script to look at a form with one column header and one row header, I am now building things to be dynamic so that the POV, the rows, and columns all are read dynamically and identifying the edited cells is independent of the source it is looking at.  This will be a multi-post article because there is a lot to cover.

Think Different

I have not talked about some of the, what might seem like, less functional classes and methods in the API.  Most of my examples don’t go into their use.  The reason is solely trying to break concepts apart and try to not overload people with everything at once.  What if I told you you could eliminate most of your substitution variables?  What if I said you only need one data map?  What if I told you that you could use rule properties like never before?  Spoiler alert, you can!

Building The Foundation

The first concept I want to share is a simple one, but it is the start of making your scripts dynamic.  When working with grid builders, a reference has to be made to the plan type.  This is a very simple thing but has to be changed for different plan types.  You might first think to put this in a script and embed the script, but there is an easier way that makes it completely independent of the plan type or application.

Everything I have shown you with grid builders starts with this.  This means that every rule has to have a hard-coded plan type in it.

Cube cube = operation.application.getCube("plantypename")

Well, this isn’t the case.  It can be done without hard coding the play type name.   The cube variable can be set by getting the cube that the rule is created in so the rule will work on any plan type in any application.

Cube cube = rule.getCube()

I can take that one step further and eliminate the cube variable all together.

DataGridBuilder builderSubmit = rule.getCube().dataGridBuilder("MM/DD/YYYY")
//or
DataGridBuilder builderSubmit = rule.cube.dataGridBuilder("MM/DD/YYYY")

Grid builders aren’t the only class that uses this.  If you are doing anything with metadata, this will also benefit those scripts.

Dimension productDim = operation.application.getDimension("Period", cube)
// can be changed to
Dimension productDim = operation.application.getDimension("Period", rule.cube)

That’s A Wrap

We have access to all kinds of things that we can make use of now through these classes. The application class exposes the currency mode and the default currency.  We have access to the smart lists and can access those.  Could we use those in calculations?  Maybe it is used as a map, where label and description is an account conversion between plan types?  We can get the dimensions, so a calculation could see if a dimension exists.  Maybe we can dynamically create fix statements based on the dimensions in the cube (aggregate everything that is sparse).  User variables can be set, so maybe if a calculation runs and the user variables aren’t set, we ask the user to set it with an RTP, then continue the calculation?

The rule class has methods to get the description, the name, and the rule properties.  I can’t say I have done it, but maybe we used the description as a variable?  Maybe we have the name in a convention that specific characters mean something and are brought into the rule, like a the scenario name?

Some of these are just thoughts, and some of them are things that we could implement and use.  My point is that there are all kinds of things we have access to dynamically that we didn’t before.  The apps I am building don’t have variables for the open periods anymore because I can get them dynamically in the calculation based on the scenario being calculated.  No more start and end month.  No more current month.  And, if they are needed for reports or forms, have the calculation set them if they are wrong.

So, what are you thinking?  Do you have something you have done that you couldn’t do before Groovy?  Share it by commenting.




Adventures in Groovy – Part 45: Locking Cells On Load

Often times, there is a need to lock cells from a user outside of the traditional dimensional security methods.  Maybe you don’t want to add dimensional security to accounts so your data push allows users to push data to another cube.  Or, maybe there is more complicated logic to identify who can and who can’t edit a cell.  The logic to do with is very simple.

Locking Cells

If you have read many of my articles, you likely have read about how to iterate though cells with the data grid iterator.  If you haven’t, starting with Part 3 would be a good start.  The grid iterator allows the script to loop through all cells, edited cells, or a filtered list of cells.  This is traditionally used to execute logic after a user edits a form, but it can be used to lock cells when the form is loaded.  Whether it is simply specific accounts, products, or a more complicated filter like products that have or don’t have a specific attribute, it quite easy to do.

Following are two ways to lock the cells.  The first is more dynamic.  This could do all kinds of things, like lock the cell if it was above a specific value, or if it had a specific attribute, or even something silly, like if it the user’s name was Kyle!

// identify the rows to be locked in a list collection
List<String> lockCells = ['Net Income']
// lock the cells
operation.grid.dataCellIterator().each {
     // Lock the cells if the measure member of the cell is in the lockCells list
     if(lockCells.contains(it.getMemberName("Measures"))){
          it.setForceReadOnly(true)
     }
}

The next example would do the same thing as above, but would filter the cells the iterator loops on and lock all the cells that meet the filter applied.

// identify the rows to be locked in a list collection 
List<String> lockCells = ['Net Income']
// lock the cells
operation.grid.dataCellIterator(lockCells.join(',')).each {
     it.setForceReadOnly(true)
}

Add To The Form

Now that the rule is built, the only thing left is to add this to the form in which it should run.  The key is to add this rule and check the Run After Load checkbox.  This will allow the form to load (or render), and then run the rule, which in this case, will change predefined cell properties so that it is not editable.

That’s A Wrap

This is not the longest post I have ever made, but it doesn’t mean it is less useful.  Locking cells based on criteria, criterial we never had access to before, is quite interesting.  When security is set to lock a cell from a user through dimensional security, it also means the users can’t use data pushes to move that calculated data to other cubes, which is an issue.  Think about adding an attribute or UDA to those accounts and then building a common script that runs on all forms that locks the cells with that attribute or UDA.  It wouldn’t be any more difficult to build, and it can be automated through metadata builds.  Hmmm, wouldn’t that be nice!




Adventures in Groovy – Part 44: Don’t Waste User Time and System Resources

Data forms that have rules on them run when a user saves a form, regardless of whether the form has edited data.  Sometimes this is by design, to let a user run a calculation.  But, most of the time, it is an accident.  There is no reason to use system resources and have the user wait for a calculation to finish when it doesn’t need to be executed.

Check If Data Has Changed

There is a very simple way to check if a data form submitted has data that has been edited, or changed.  Several previous posts have talked about the data iterator.  The data iterator is used to loop through the cells in a form.  Predicates can be used to only loop through the cells that have been edited.  The documented example shows the code to identify and loop through the edited cells, printing each to the job console.

operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each {
     println("$it.memberNames, cell data: $it.data")
 }

If you are starting to understand the methods and collections, you notice that this returns a list of data cell types.  From this, I can get all the properties of each cell.  Since this returns a list, I can also use the size method.  The size method returns the number of elements.

operation.grid.dataCellIterator({DataCell cell -> cell.edited}).size()

This will return the number of edited cells.  It isn’t a stretch to take this one step further and wrap it in an if statement to see if the number of edited cells is more than 0.  If it is 0, then no cells have been edited.

IF(operation.grid.dataCellIterator({DataCell cell -> cell.edited}).size() == 0){
   ...take action
}

Inform The User Or Not

This is where there is some administrator preference.  I can argue both ways, but I think it depends on the users and the expectations of what happens when a form is saved.  One one hand, I can argue that the rule should exit without notifying the user that nothing ran.  It is an extra click that they have to respond to that might be annoying.  Telling them that nothing changed, and no calculation ran may not be needed.  In this case, the calculation exits gracefully, and everybody moves on.  Using return exits the calculation.

IF(operation.grid.dataCellIterator({DataCell cell -> cell.edited}).size() == 0){
    return
}

On the other hand, the user be expecting something to happen.  They might need to know that they didn’t edit anything.  If this is the situation, I can throw an exception and prompt the user there was an issue.  Using throwVetoOperation will do just that.  It initiates an error and nothing further runs.

IF(operation.grid.dataCellIterator({DataCell cell -> cell.edited}).size() == 0){
     throwVetoException{"No data was edited and no business logic was executed."}
}

The throwVetoException method can also use the Groovy message bundle and bundle loader if you are using those classes.

That’s A Wrap

I have used both methods, exiting without notifying the user and existing and notifying the user.  My preference is to simply exit and not run or notify the user because normally they don’t need to know nothing ran because nothing changed.  Regardless, I think it is a good practice to add this to your Groovy calculations to account for a user saving a data form when no data was edited.