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!




Improve the Planner Experience with Groovy (ODTUG)

Due to the overwhelmingly positive feedback from my last KScope presentation, I was asked to repeat the presentation for ODTUG. First of all, I can’t think you all enough for the generous and kind feedback! If you would like to attend, I would love to have you there.



Event Information

You can register here. The event is on Wednesday, Dec 1, 2021 from 12:00 PM – 1:00 PM EST

Presentation Summary

You have heard of Groovy, and you probably have heard of how drastically it can improve performance and functionality. The functionality talked about largely misses the focus of planning and forecasting, which is the user experience.

This session will walk through real-word examples implemented at organizations which are focused specifically on improving the user experience. It will highlight how to improve data input and new functionality that can be added which focuses specifically on the planning process. The topics will include everything from improving data entry to identifying issues before they become issues and giving users functionality that was prior only available to administrators. Allowing users to manage metadata effectively, performing multiple transfers at one time in workforce planning, and allowing users to fix data load errors on the fly will all be covered.

Attendees will leave this session with the knowledge and examples that will allow them to go back to their workplaces and use what they learned to improve their applications. They will leave with ideas, specific use cases, and the Groovy code to implement them. If you think the cloud limits your ability to be creative, think again!




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.




Get Groovy FREE for 12 months!

Oracle is providing free access to Oracle Financial Statement Planning, including Strategic Modeling, to all existing Oracle Planning Cloud customers for the next 12 months.  This includes the use of Groovy.

You would be able to purchase Financial Statement Planning and Scenario Modeling if you choose to continue using it. Alternatively, your right to use Financial Statement Planning and Scenario Modeling will expire after April 30th, 2021.

And yes, Hackett Consulting is a preferred provider to help you take advantage of this.

The Oracle FAQ will provide answers to your most of your questions.  If you would like to take advantage of this, let’s get the ball rolling!  If you are a do-it yourselfer, the following classes will kickstart your ability to implement Groovy.

Try it out for free getting a few samples.




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.




Oracle EPBCS/PBCS May Updates – Big Changes!

I don’t normally write up monthly updated, but this month there are a number of intriguing changes/updates/enhancements that are important to know.  Some may change existing processes.  This is not an exhaustive list, but these are things I think all of us should take note of.

The changes that you will see in the UI may not be visible unless you clear your cache.

EPMAutomate

  • You MAC users are going to like this, it can now be installed and work on a MAC. By the way, I am one of them.  I can’t wait until the beta is finished for the Smart View to work with office360, meaning MAC, Ipad, etc.
  • You can now run multiple instances of EPMAutomate. So, logging out of one will not log you out of the other.  This will help those that want to run test and prod on one VM.  It will also remove any issue of one process stepping on another.
  • Admin mode can be enabled with EPMAutomate, which was a huge missing piece, in my opinion.
  • A sortMember command to order members in dimensions is now available.

Forms

  • No more composite forms, sorry guys!

Smart Push

  • There is a new Smart Push option called Use Database Suppression that enables you to apply row suppression at the database level instead of at the application level, thus eliminating the impact on query thresholds and improving Smart Push wait times.

Settings

  • There is a new setting to set the accounts that are linked between plan types to false. This will use the  a new attribute of HSP_LINK (not HSP_NOLINK).

Groovy

  • We now have the ability to use the new suppression option on the data map / smart push. This will improve performance and reduce the need to break up pushes in groups of members.
  • Groovy can now run jobs. This means a rule can execute other jobs within that rule.  They show as tasks in the job console.  This is limited to the execution of rules, rulesets, and templates.
  • All RTPs are now supported in ASO.

Smart View (AMEN on some of these as us old timers will love) – You will want to download the updated version to take advantage of these.

  • In-grid, cell-based POV—POV members are placed on the grid instead of in the POV toolbar.
  • Submit without refresh—Using the default Submit Data button in the Smart View ribbon, all data cells in a single grid (modified cells and unchanged cells) are marked dirty and submitted. Once the submit operation is complete, the entire grid will be refreshed.
  • Enhanced free-form—Users can insert empty columns and rows anywhere in a grid and change the alias table. Additionally, supports member auto-refresh where deleted members are returned to the grid upon refresh.
  • Multiple-grid ad hoc—Multiple ad hoc grids can be placed on the same Excel worksheet. With multiple-grid ad hoc, users can submit data from any grid on the sheet. Grids based on aggregate storage cubes and block storage cubes are supported on the same sheet. Each grid is independent; for example, if required, you can change the alias table for only one grid on the sheet.
  • Users will now get a warning when the version installed is out of date. Hopefully this can be turned off!

General

  • Jobs can now have a new return, success with warnings. An example of this might be loading data.  The job finished successfully but there were rejected records.  Make sure your automation accounts for this!

DM

  • There is now an option to not run the validation when data is loaded. For those of you that despise the performance challenges of using DM, this might be a game changer.  The longest part of the process is the actual validation, not the import or export to Planning.

Modules in ePBCS

  • Rolling forecast can now be configured for length.
  • There were some critical bugs that were fixed in WFP (I know first-hand).
  • Some CapX forms were improved as well.



Adventures in Groovy – Part 41: RTP Interpretation Modes

Have you ever used a variable and received this error? Error: Unable to retrieve variable [variable name] deployed in the application [app name] Rule [app name].[plan type name].[rule name]. You likely saw this when a Groovy variable was used inside of {}.I finally had the issue explained to me working with the Oracle PBCS development group today. God bless them for being so gracious to help me through some of these issues! You know who you are, and I can’t thank you enough for your time!

ePBCS interprets Groovy before the Groovy compiler is engaged. There are multiple interpretation modes (my words) and varies based on whether run time prompts are initiated by including /*RTPS:*/ in the calculation.

WHAT YOU NEED TO KNOW

Long story short is this, but I encourage you to read on for a deeper dive into what is happening.

EPBCS parses all Groovy scripts before executing it to identify the run time prompts used by the script. The way it identifies the run time prompts is by looking for explicit declaration of the run time prompts in a comment in the following format:

/*RTPS: {rtpName}*/

The reason for this is because there are many expressions (closures, string interpolation etc) in Groovy that use curly braces so the old way of defining run time prompts in curly braces {rtpName} is no longer recommended. Instead, the use of rtps.rtpName is recommended to reference run rime prompts inside a Groovy script.

Without the explicit declaration in the /*RTPS */ comment, the parser will try to interpret Groovy expressions inside {} as run time prompts causing the following error:

Error: Unable to retrieve variable [variable name] deployed in the application [app name] Rule [app name].[plan type name].[rule name].

If you are using RTPs without defining them in the comment, I recommend that the script be updated to add the RTPS comment.  The interpretation method of assuming everything in {} is an RTP will be deprecated in future releases.  To conclude, if no RTPs are used, add /*RTPS:*/ to the script so that variables inside the {} are interpreted as Groovy variables.

Example: Interpretation Without RTPS Comment

If you have worked with run time prompts, you know (or thought you knew) that they had to be defined in what looks like a comment.

/*RTPS: {rtp_Period} {rtp_Year}*/

This is not true, however.  Theoretically, the /*RTPS:*/ doesn’t have to be added to use run time prompts, which was news to me.  When /*RTPS:*/ is EXCLUDED from a calculation, the interpreter will replace anything identified with {} as a run time prompt. If like me, I had never used a variable with the same name as a valid RTP, you would simply get an error and likely not understand why you can’t reference the variable.

If by chance you actually used a variable with the same name as an existing RTP, you might have figured this out on your own because you would be prompted for the RTP. Notice that no header was added to include RTPs in this calculation (void of any /*RTPS:*/) but still get a prompt.

You would also notice that the RTP is identified in the Variables tab!

Now that you know this, forget that you do and don’t every use RTPs without the /*RTPS:*/. This was something that was missed in initial releases and will be deprecated.  So, if you don’t add your RTPs in a /*RTPS:*/ and use the RTPs, this will not work in the future.

Example: Interpretation With RTPs

When /*RTPS:*/ is used anywhere in the calculation, the variables in a conventional Groovy way.  In this example, sScenario is actually referencing the Groovy variable.

The same script with the RTPS inclusion header now works as expected!  sScenario is no longer interpreted as an RTP.  If the variable was named the same as a valid RTP, it would also be handled as expected.  Or, it is handled as I would expect it not knowing {} meant RTP without the RTPS header.  rtp_Period is a valid run time prompt.  Now that /*:RTPS*/ is added to the script, the same line of code is looking for a Groovy variable, not a RTP.  Because the RTP is not defined, the compiler returns an error.

What I Learned Today

Talking to the dev group today was awesome because I learned a number of things.

  1. There are two interpretation modes laid out above.
  2. Calculations can have /*RTPS:*/ with no variables. I never really thought about doing this.  Now that I understand the multiple modes, I am going to add this to every Groovy calculation to avoid any issues like this.
  3. /*RTPS:*/ can be anywhere in the calculation. I don’t know why it would benefit somebody to have it at the end, but it would work the same way as if it was the first line.  The reason it does this is because the modules will add RTPs for certain situation and not others.  For this to be possible, the need to add multiple RTPS comments with the appropriate RTPs through the calculation was required.
  4. /*RTPS:*/ can exist on multiple lines. If you wanted to have each variable referenced on different lines, /*RTPS:*/ can be repeated as many times as needed.

There are a couple wins for me now that I know this.

  1. I use common code and functions in scripts that I embed in groovy calculations to eliminate repetitive functionality. If these functions require an RTP (even a hidden one with an override that is a subvar), it can be referenced inside the script rather than putting them in the rules that reference the script.
  2. The rule using the script can also have its own RTPs that are not needed for the common code. So, I can have common RTPs in the script and also have RTPs needed for each specific rule in that rule and not have any conflicts.

Does This Seem Irrelevant?

If you are asking yourself why this would ever come up and why any variable would be referenced inside squiggly brackets, this might help.  The example above is simple and the println could have been written without the quotes and squiggly brackets.

println sScenario

You are correct, this would have worked.  But, let’s say you need the current month and year together.  Yes, there are other ways to accomplish this, but it emphasizes the need.  Let’s say I need the current month concatenated with the current year formatted as FYyy.

def Year = (new Date()).format('yy') // produces 19
def Month  = (new Date()).format(‘MMM’) // produces Apr
println "${Month}FY${Year}" // produces AprFY19

A second example would be referencing variables in a dynamic FIX statement

if (uniquePeriodNames.size() == 0){
  println("No cells were edited")
}
else{
  operation.grid.dataCellIterator({DataCell cell -> cell.edited}).each{
    lstProduct.add(it.getMemberName("Product"))
    lstPeriod.add(it.getMemberName("Period"))
    lstYears.add(it.getMemberName("Years"))
    }

  List povmbrs = operation.grid.pov

  StringBuilder strEssCalc = StringBuilder.newInstance()
  strEssCalc <<"""
    FIX("${lstPeriod.unique().join('","')}", 
        "${lstYears.unique().join('","')}", 
        "${lstProducts.unique().join('","')}", 
        "${povmbrs*.essbaseMbrName.join('","')}")

        "Revenue" = "REV_BASE_PRICE" * "REV_SALES_QUANTITY";
      ENDFIX
    ENDFIX
  """
}

Another example would be concatenating member names and country codes from an attribute dim.  Suppose you want to concatenate an _USD to a country code to reference an FX rate.

$CountryCode_USD tries to reference a variable named CountryCode_USD.  Using the squiggly brackets will enable this to happen without confusion. ${CountryCode}_USD would concatenate the value of CountryCode with _USD.

You can use additions and do $CountryCode + “_USD”, but it is terribly inefficient.  Once is not an issue but if it is used inside of loops, it can cause performance issues.

Last Call

I don’t think it is fair to call this a bug.  I do think there is a lack of clarity.  The dev group is actually looking at addressing this in a future release.  Again, my worlds, but changing this to always referencing things inside of brackets as a Groovy variable would make it a bit less confusing.  Basically, nothing inside of {} would be interpreted as an RTP unless it is defined in the header definition.  So, something in brackets that is defined as an RTP would be an RTP.  Anything else would be assumed a variable.




Adventures in Groovy – Part 40: Eliminating Data Sources With The Groovy Calendar Class

I am currently working on a migration of on-premise to cloud project (going GREAT by the way).  One of the things we are working on is the change with the data integration.  One of the processes loads the number of working days to the application from a source that provides it.  “Why not use Groovy,” I ask?  It turns out to be a great question.

There are two concepts to cover.  A few lines of Groovy using a calendar class and the gridbuilder to save the data.  This could also be a dynamically generated calculation that updates the data.  Since this is used on an ASO cube, the example below uses the gridbuilder option.

The Code Explained

This example will have a run time prompt for year, will calculate the values for each month in the year selected and store them to the appropriate account.  The prompt and the variables required are declared here.

/*RTPS {rtpYear} */
int iYear = rtps.rtpYear.getEnteredValue().toString().substring(2).toInteger() + 2000
def sYear = rtps.rtpYear.getEnteredValue().toString()
Def values = []
Calendar calendar = GregorianCalendar.instance

Once this is setup, the calendar class will be used to identify the working days.  Working days here is defined as any weekday in the month.  These days are used to calculate payroll accruals and monthly averages.  Holidays are not considered in the calculations that use this.

The following uses the calendar class to create a list variable that will be passed to the gridbuilder later in the script.

for (int currentMonth = 1; currentMonth <= 12; currentMonth++) {
  Calendar startCal = new GregorianCalendar(iYear, currentMonth, calendar.getActualMinimum(GregorianCalendar.DAY_OF_MONTH))
  Calendar endCal = new GregorianCalendar(iYear, currentMonth, calendar.getActualMaximum(GregorianCalendar.DAY_OF_MONTH))
  int workDays = 0
  startCal.upto(endCal) {Calendar it ->
    if( (it[Calendar.DAY_OF_WEEK]).toString().toInteger() > 1 && it[Calendar.DAY_OF_WEEK].toString().toInteger() <7 )
       workDays += 1
     }
    def sMonth = Date.parse( 'MM', "$currentMonth" ).format( 'MMMM' )
   values << workDays
   println "for the month of $sMonth we have $workDays working days."
}

At this point, a list object has 12 values, one for each month.  This will be loaded to a specific POV that won’t change.  If the requirement was more dynamic, this example could certainly be expanded to account for it.  The last step is to store the data back to the database.  There are many examples on the gribuilder in previous articles, so it won’t be explained in detail.

Cube cube = operation.application.getCube("Plan1")
DataGridBuilder builder = cube.getDataGridBuilder("MM/DD/YYYY")
builder.addPov($sYear, 'Local', 'Working', 'Plan', 'No Entity')
builder.addColumn('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug’, 'Sep', 'Oct', 'Nov', 'Dec’ )
builder.addRow(['Working Days'], values )
DataGridBuilder.Status status = new DataGridBuilder.Status()
builder.build(status).withCloseable { grid ->
  println("Total number of cells accepted: $status.numAcceptedCells")
  println("Total number of cells rejected: $status.numRejectedCells")
  println("First 100 rejected cells: $status.cellsRejected")
  // Save the data to the cube
  cube.saveGrid(grid)
 }

The Results

The log will provide some descent information.  This can be expanded for POV, user info, the time it took to process, but as it is, this is what the log would produce.  Remember, the security of the user that runs this is used.  If this is for a forecast, for example, and the start month is April, the accepted cells would only be nine.  3 cells would be rejected because they are not writable.

for the month of January we have 21 working days.
for the month of February we have 21 working days.
for the month of March we have 22 working days.
for the month of April we have 22 working days.
for the month of May we have 20 working days.
for the month of June we have 22 working days.
for the month of July we have 22 working days.
for the month of August we have 21 working days.
for the month of September we have 22 working days.
for the month of October we have 21 working days.
for the month of November we have 21 working days.
for the month of December we have 22 working days.

Total number of cells accepted: 12
Total number of cells rejected: 0

The Calendar Class

Although not required in this example, there are all kinds of things that this can be used for.  Have you ever needed to calculate the week of the year?  The day of the week?  How about the days between two dates?  You likely have come across these things for WFP or CapEx at least.  The calendar object itself has a ton of useful cases and the object that is returned is basically an array of information.  If the object is sent to the log with a println, all the values are exposed.  Of course, you can always google it, but it looks like this.

java.util.GregorianCalendar[time=-62130585600000,areFieldsSet=true,areAllFieldsSet=true,lenient=true,zone=sun.util.calendar.ZoneInfo[id="UTC",offset=0,dstSavings=0,useDaylight=false,transitions=0,lastRule=null],firstDayOfWeek=1,minimalDaysInFirstWeek=1,ERA=1,YEAR=1,MONTH=2,WEEK_OF_YEAR=10,WEEK_OF_MONTH=1,DAY_OF_MONTH=2,DAY_OF_YEAR=61,DAY_OF_WEEK=4,DAY_OF_WEEK_IN_MONTH=1,AM_PM=0,HOUR=0,HOUR_OF_DAY=0,MINUTE=0,SECOND=0,MILLISECOND=0,ZONE_OFFSET=0,DST_OFFSET=0]

It would take pages to explain and provide examples of all that can be done, which won’t be in this post.  Here are some ideas of uses.

  • use copyWith to duplicate an instance
  • use the format method to format the date as a month, year, long date, etc.
  • use minus to subract two calendar dates from each other (datediff)
  • use next and previous to increment days, months, or years
  • use set to set a specific date

Last Call

Will this change the world?  No.  Is it useful?  To some, absolutely.  The goal here is to just provide some information on how to use the calendar class and give you some ideas of what it could be used for.  Manipulating dates and time has always been a challenge in Essbase.   It is better now with some of the custom functions that have been exposed (and thank goodness they did this on the cloud or we wouldn’t be able to register custom functions).  But, performance, complexity, rolling through the same blocks multiple times, all can be minimized with the ability to calculate this outside of Essbase, pass it to a calculation, let Groovy do what it is good at, and let Essbase handle what its strengths are.

To read about more uses, Google “Groovy Calendar” and take a look at all the methods it has – pretty useful stuff.

You have any other thoughts?  Post a comment.  You know  we would love to hear from you.




Adventures in Groovy – Part 38: Challenge Accepted (Exporting Data With The System Date In The Filename)

I had a great question today so challenge accepted!  A reader asked if it was possible to run a data export and have the system date in the file name.  The answer is very simply, yes.  I don’t have any content around the question, so I will answer it in two ways.

Option One – Groovy

If you have read any of the pieces of this series, you know how excited I am about Groovy.  Yeah, I know, Groovy can do everything.  You are tired of hearing me say that.  I know it can’t do everything, but it comes close!  In Exporting Data in PBCS With Business Rules, I talk about exporting data with a business rule.  In Part 1 of my Groovy Series, I lay out how to execute a business rule with Groovy and how you can alter the Essbase calculation at run time.  If we put these two concepts together, we can set the name of the export to anything we want, even a system date.

The Date() method will return a date in a default format and inside a Groovy calculation it uses GMT time.  Since I am in EST, it is always 5 hours ahead.  So, if the date is printed at 5PM EST, the results is Wed Jan 16 22:00:00 GMT 2019.  This can be altered by formatting it to just about anything your heart desires.  I am sure you see where I am going.  If you google “format date Groovy” it will list nearly 50 options for date and time, AM/PM or military time, month as a number, 3 digit abbreviation, or the whole month, as examples.  For this example, the file to be just the system date.

Def currentDate =  new Date().format("MM_dd_yyyy")
// this returns the a two digit month, two digit day, and four digit year, separated by underscores

If you don’t want GMT, there is a trick.  The date method accepts a date.  It can be a fully qualified date like “1/15/19 22:00:00” or you can enter the seconds from January 1, 1970, 00:00:00 GMT.  This might sound crazy but in a Groovy calculation you can use currentTimeMillis() to get exactly that.  Again, since this is GMT, and EST is the want, add 3,600,000 milliseconds for each hour you want to change from GMT.  If these two methods are combined, the appropriate time can be obtained.

def adjEST = currentTimeMillis() + (3600000 * -5)
def currentDate =  new Date(adjEST).format("MM_dd_yyyy")
// This would give me the date 5 hours behind GMT

Now that we have the system date (and this is obviously my interpretation because I want it represented in my home time zone), we can combine this with the examples referenced above to export the data to a file with a name that includes the “system date.”

If you don’t know, the last string in a Groovy calculation is actually submitted to the calculation engine.  A stringbuilder is not required as noted in the examples.  The drawback of this is that we don’t have the option to log the actual calculation script.  This is more helpful when debugging and isn’t always critical to have documented in a production application via a log.  So, here goes!  The only change from the example is that we define a date variable as mm_dd_yyyy and pass that to the calculation.

// Get the date in EST and format as needed
def adjEST = currentTimeMillis() + (3600000 * -5)
def currentDate = new Date(adjEST).format("MM_dd_yyyy")

// Pass the calculation with the currentDate variable as the file name
"""
 SET DATAEXPORTOPTIONS
 {
 DataExportLevel LEVEL0;
 DataExportDynamicCalc OFF;
 DataExportNonExistingBlocks OFF;
 DataExportDecimal 4;
 DataExportPrecision 16;
 DataExportColFormat ON;
 DataExportColHeader Period;
 DataExportDimHeader ON;
 DataExportRelationalFile ON;
 DataExportOverwriteFile ON;
 DataExportDryRun OFF;
 }

 FIX(@Relative("Account", 0),
     @Relative("Years", 0),
     @Relative("Scenario", 0),
     @Relative("Version", 0),
     @Relative("Entity", 0),
     @Relative("Period", 0),
     @Relative("<em>custom_dim_name_1</em>", 0),
     @Relative("<em>custom_dim_name_1</em>", 0),
     @Relative("<em>custom_dim_name_1</em>", 0))
  DATAEXPORT "File" "," "/u03/lcm/${currentDate}.txt" "";
 ENDFIX
"""

This would produce a file in the inbox named 01_16_2019.txt.  By adding a parameter and formatting it to the date we want the file to be named, we have our solution.  Two additional lines and adding the parameter to the Essbase calculation is all that is required.

Part Two – Without Groovy

This is a little more difficult without Groovy.  I have not found a way to use something like the following as the file name.  If a reader sees this and knows of a way a way to do this, please share it and post a comment.

@CONCATENATE (@CONCATENATE ("/u03/lcm/", @FORMATDATE(@TODAY(),"mm_dd_yyyy")),".txt")

I assume in the original question that the ask is really not to have a file in the inbox with a specific name.  By using EPMAutomate and execute the calculation with a static name, the file can be download and renamed.  A simple PowerShell script could be used to rename the file.  If there really is a need to have this in the inbox, it can be uploaded.  I don’t know of any method that exists to rename an existing file with EPMAutomate.

$localFile = "c:\PBCS_Downloads\"
$exportFileName = "DataExport.txt"
Start-Process "epmautomate downloadfile ""$exportFileName"""
# Assume the script is executed from c:\PBCS_Downloads\ or the file is moved to this folder
$fileObj = get-item "c:\PBCS_Downloads\dataexport.txt"
rename-item "$($fileObj.FullName)" "$(Get-Date -Format "MM_dd_yyyy")$($fileObj.Extension)"
Start-Process "epmautomate uploadfile ""$($fileObj.DirectoryName)\$(Get-Date -Format ""MM_dd_yyyy"")$($fileObj.Extension)"""

Challenge Completed

Another challenge accepted has been easily completed.  Keep them coming!