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!

One year ago, I decided to release my first training course on Groovy. A year later, I want to thank everybody for supporting me and celebrate everybody that has joined the Groovy revolution with me.

12 months later more than 200 people have participated in one form of training and the reviews are amazing. The training is changing career paths, Oracle implementation successes, and inspiring people to break out of the normal development paths. Groovy has opened up new opportunities for implementations that Oracle Planning was never considered for before.

To celebrate, I am offering a bundle of Groovy for EPM Cloud Planning & Mastering The EPM Planning Javadocs at a discounted price when purchased together. I am further discounting it to hopefully get more people educated around the possibilities. Furthermore, for every sale in August, I am donating another $100 to help those suffering with the economic changes due to Covid.

You will be seeing posts on Linked In and Twitter about being a hero.

• Week 1(Captain America) – You don’t have to be a superhero to be a hero!
• Week 2(Hulk) – people sad, hulk mad!
• Week 3(Spiderman) – My spidey senses are tingling!
• Week 4(Ironman) – It’s an imperfect world, but it’s the only one we got!

To take advantage of this throughout August, go to my In2Hyperion Podia center and sign up. And if you still don’t know how Groovy can change your implementations, think about these. These are just a start and in no particular order.

• Performance improvement
  • Calculations
  • Data movement
  • Run processes synchronously
• Proactive user input validation
  • RTPs
  • Form level
  • Cell level
• Functionality
  • Java classes and methods
• Real-time cube synchronization/consolidation
• Business logic execution in ASO (No Essbase calculations)
• Advanced traffic lighting
• Custom logging
• Custom user error messages
• Metadata management
• Interact with Smart Lists
• Connect to external sources
  • Internal relational DBs
  • Third party (currency rates, Amazon, NFL, Best Buy)
• Process management
  • Email users when long calculations complete
  • Email admins when a calculation fails
  • Terminate calc if taking over x
• Multi-dimensional security
  • By form / app / user
• Relational component
  • Metadata properties
  • Mapping
• User input at dynamic members
  • Rates
  • Consolidated Hybrid members
• Eliminations at first common parent
• User POV execution of Data Management processes
• What If analysis in existing models with complete undo at any level

You all stay happy, safe, and let’s all be heroes and change the world!

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(...)
        {...}
}

There are benefits to moving to Hybrid, but there are also some challenges.  The content of this post is not around the pros and cons, but the fact that you can upgrade your environment to use it.  If you find it isn’t for you, you can “downgrade” back to BSO.  The flexibility provides everybody the ability to try it.

Recreate Introduction

EPMAutomate comes with a function that allows the ability to restore an environment to a clean slate.  I don’t think this is new to anybody that has used EPMAutomate or EPM Cloud Planning.  What might be a surprise is that it does more than just reset an environment so you can start over.  It can also:

1. Change the type of Essbase database to Hybrid or a standard BSO.
2. Temporarily convert a Planning, Enterprise Planning, Tax Reporting, or Financial Consolidation and Close environment to an Account Reconciliation, Oracle Enterprise Data Management Cloud, or Profitability and Cost Management environment.

Using Recreate

The usage of the Recreate command is as follows, which all options.

 epmautomate recreate [-f] [removeAll=true|false] [EssbaseChange=Upgrade|Downgrade] [TempServiceType=Service_type]

• -f forces the re-create process to start without user confirmation. If you do not use the -f option, EPM Automate prompts you to confirm your action. Be careful using this option.  If you have a long day and aren’t focused, this can make the day a whole lot worse!
• removeAll, removes all of the existing snapshots, as well as the content of the inbox and outbox.  The default is false, meaning it retains the snapshots and the content of inbox and outbox and nothing is removed.
• EssbaseChange upgrades or downgrades the current Essbase version in legacy Oracle Financial Consolidation and Close Cloud, Oracle Enterprise Planning and Budgeting Cloud or Planning and Budgeting Cloud Plus 1 environments.
• TempServiceType temporarily converts an environment to a different service environment.

Changing Your Essbase Version

To change your environment to BSO from Hybrid

epmautomate recreate EssbaseChange downgrade

To change your environment to Hybrid from BSO

epmautomate recreate EssbaseChange upgrade

Trying A Different Service

There are some details that must be understood to use this option and is dependent on the version of the cloud service you have.  For subscriptions other than EPM Standard Cloud Service and EPM Enterprise Cloud Service, meaning PBCS and EPBCS, you can use this option to convert, temporarily, to

• Account Reconciliation
• Oracle Enterprise Data Management Cloud
• Profitability and Cost Management environment

To use this option to convert your environment to something it wasn’t originally intended for:

epmautomate recreate -f removeAll=true TempServiceType=ARCS

To change your environment back to its original service:

epmautomate recreate

For EPM Standard Cloud Service and EPM Enterprise Cloud Service subscriptions, you can use this option to convert to any supported EPM Cloud service.  EPM Enterprise Cloud Service subscriptions use a common EPM Cloud platform. Initially, you can deploy any supported EPM Cloud business process. 

To switch from a deployed business process to another, you must re-create the environment to delete the current deployment and to bring it back to the original EPM Cloud platform. You then re-create it again as the new service type.

For example, if you created an Account Reconciliation business process but now want to create an Oracle Enterprise Data Management Cloud environment, you must run the re-create command twice.

First, reset the service.

epmautomate recreate -f removeAll=true

Second, change the service type.

epmautomate recreate -f TempServiceType=EDMCS

The acceptable service types, currenty, are

• ARCS (Account Reconciliation)
• EDMCS (Oracle Enterprise Data Management Cloud)
• EPRCS (Narrative Reporting)
• PCMCS (Profitability and Cost Management)

That’s A Wrap

It is great that Oracle allows us to do these things. We have a ton of flexibility, not normally afforded to us in the cloud, to test and use different core database types.  It also allows those using the old SKU to try the new services, or business processes, to see if they might be something you want to purchase. 

If you want to give Hybrid a try, use your test environment and give it a spin.  If you want to get exposed to one of the other business processes, you now have the ability to see it without jumping through hoops.

Most of us know that there is a button in the calc rule editor that allows us the ability to select a smart list and the smart list entry.  It will add something [[smartlist name.smartlist entry]].  If this is new to you, what it does is replace reference the smart list and replaces it with the numeric value that exists in Essbase.  The beauty of this is that it is dynamic, so if the smart list is changed in any way, you don’t have to go into your rules and replace change the index values for the smart list entries to match.  Guess what, there are more!

Well, don’t I feel like the F*@$& idiot, to pull a quote from A Few Good Men.

What Is A Planning Formula Expression

As described above, it is an expression that allows you to get valuable information dynamically about artifact properties in a Planning application.  The following formula expressions currently exist.

• SmartLists
• Dimensions
• Planning User Variables
• Periods
• Scenarios
• Cross-References
• Workforce Cube Year to Date
• Get ID for String

SmartLists

I already discussed the Smart List, but here is an example if this is new to you.  The calculation manager syntax is [[SLName.entryname]].

FIX (Mar, Actual, Working, FY15, P_000, "111") 
   "Product Channel" = [[Channel.Retail]] ;
ENDFIX

Which would return something like this.

FIX (Mar, Actual, Working, FY15, P_000, "111") 
   "Product Channel" = 2 ;
ENDFIX

Dimensions

The dimension expressions are not all that useful unless you are building calculations that might go across applications that have different names for the 6 required dimensions, plus currency.  Using the following dimension tags, the customized name will be returned when they are added to the dimension expression. The syntax used for this function is [[Dimension("DIM_NAME_ENTITY")]].

• DIM_NAME_PERIOD
• DIM_NAME_YEAR
• DIM_NAME_ACCOUNT
• DIM_NAME_ENTITY
• DIM_NAME_SCENARIO
• DIM_NAME_VERSION
• DIM_NAME_CURRENCY

An example would look like this. This runs a calc dim on whatever your account dimension is.

CALC DIM([[Dimension("DIM_NAME_ENTITY")]]);

In this application, Entity is named Entity, so the above script returns:

CALC DIM ("Entity");

If the entity dimension was named Cost Center, it would return:

CALC DIM ("Cost Center");

Planning User Variables

Planning user variables return the user variable’s member.  This can be pretty useful if you have variables that are used to do things like fix on their area of a hierarchy.  These can be gathered through run time prompts if they exist in the POV, but that isn’t always the case.  You might use them to show the products, for example, that are under a user variable, in the rows, in which case without Groovy, it can’t be passed in RTPs. The calculation manager syntax is [[PlanningFunctions.getUserVarValue("xyz")]].

An example where the user variable is used to run a calculation might look like this.

FIX (Feb, Actual, Working, E_000, @RELATIVE([[PlanningFunctions.getUserVarValue("Product View")]],0) )
   Revenue = Units * Cost;
ENDFIX

Period Functions

Period(periodName) returns the specified period. The options for this function are and the calculation manager syntax is [[Period("FIRST_QTR_PERIOD")]].

• FIRST_QTR_PERIOD
• SECOND_QTR_PERIOD
• THIRD_QTR_PERIOD
• FOURTH_QTR_PERIOD
• FIRST_PERIOD
• LAST_PERIOD

This example:

FIX ( Mar, Actual, Working, P_000, "6100", FY15 )
   "120" =[[Period("FIRST_QTR_PERIOD")]];  
ENDFIX

would return a script like this

FIX (Mar, Actual, Working, P_000, "6100", FY15) 
   "120" = "Mar";
ENDFIX

The NumberofPeriodsInYear returns the number of periods in the year and NumberofYears returns the number of years in the application. The calculation manager syntax for this is

• [[NumberOfPeriodsInYear]]
• [[NumberOfYears]]

The following example

FIX (Mar, Actual, Working, P_000, "6100", FY15)
   "120"=[[NumberOfPeriodsInYear]];
   "120"=[[NumberOfYears]]; 
ENDFIX

would produce this.

FIX (Mar, Actual, Working, P_000, "6100", FY15)
   "120"=12;
   "120"=9;
ENDFIX

Scenarios

This one is my favorite ones.  I have been using Groovy to get these not knowing they existed.  These allow the reduction of if statements and improved performance.  We can get the open periods.  If this go across years, then my groovy solution probably comes back into play.  For ranges that include one year, or even two could be handled, this offers great functionality.  The options available are

• Start Year
• End Year
• Start Month
• End Month

The calculation manager syntax is as follows.

• [[getStartYear(“ScenarioName”)]]
• [[getEndYear(“ScenarioName”)]]
• [[getStartMonth(“ScenarioName”)]]
• [[getEndMonth(“ScenarioName “)]]

A use case would look something like this. Assume{rtpScenario} is a run-time prompt variable of type member with a default value of “Actual”:

FIX({rtpScenario}, [[getStartYear({rtpScenario})]]:[[getEndYear({rtpScenario})]],
   [[getStartMonth({rtpScenario})]]:[[getEndMonth({rtpScenario})]])
      FIX ( Working, P_000, "111")
        "5800" = 5500;
      ENDFIX
ENDFIX  

This would build out the following calculation

FIX ("Actual", "FY10" : "FY18", "Jan" : "Dec") 
    FIX (Working, P_000, "111") 
      "5800" = 5500;
    ENDFIX 
ENDFIX

If your open range consisted of two years, you could do something like this

FIX({rtpScenario},[[getStartYear({rtpScenario})]],[[getStartMonth({rtpScenario})]]:”Dec”)
      FIX ( Working, P_000, "111")
         "5800" = 5500;
      ENDFIX
ENDFIX  
FIX({rtpScenario},[[getEndYear({rtpScenario})]],”Jan:[[getEndMonth({rtpScenario})]]
      FIX ( Working, P_000, "111")
        "5800" = 5500;
      ENDFIX
ENDFIX  

Cross-References

This function comes in a few flavors but does something pretty awesome. How it works might change your naming convention a little, or make it more consistent anyway. What id does is generate a cross dimensional reference to our default members, like No Product. The syntax is CrossRef(accountName, prefix, true) but the last two parameters are optional. If you use CrossRef(“Revenue”), it would produce the following, assuming your 6 required dimensions and a product dimension.

"BegBalance"->"No Scenario"->"No Version"->"No Entity"->"No Product"->"Revneue";

I can change my prefix by adding the second parameter. I don’t like having spaces in my member names, so I would do the above with CrossRef(“Revenue”,”No_”) which would produce

"BegBalance"->"No_Scenario"->"No_Version"->"No_Entity"->"No_Product"->"Revneue";

If I change my syntax to CrossRef(“Revenue”,”No_”,true) I have a cross dim operator for all dimensions Except Period (uses BegBalance), and Currency, but includes year

"BegBalance"->"No_Year"->"No_Scenario"->"No_Version"->"No_Entity"->"No_Product"->"Revneue"; 

Used in a Fix Statement the following example

FIX (Aug, Actual, Working, FY15, P_000, "112")
   "111" = [[CrossRef("5800", "No_", true)]];
ENDFIX

would produce the following script.

FIX (Aug, Actual, Working, FY15, P_000, "112") 
   "111" = "BegBalance"->"No_Year"->"No_Scenario"->"No_Version"->"No_Entity"->"No_Product"->"5800";
ENDFIX

Workforce Cube Year to Date

If you use workforce, you probably have noticed the members it generates to get the month index for both the calendar and fiscal period. These can be used with this function to build a calendar to date value. The syntax is [[CYTD(memberName)]]. If you have renamed “Cal TP-Index” and “Fiscal TPIndex,”, then you have to specifically name the members in two additional parameters and the syntax is [[CYTD(memberName, calTpIndexName, fiscalTPIndexName)]]. This method is really easy to use and looks like this.

Fix (NOV, Actual, Working, FY15, P_000, "112")
   "5800" = [[CYTD("6100")]];
ENDFIX

If the default names are changed, it would look a little different.

Fix (Dec, Actual, Working, FY15, P_000, "112")
   "5800" = [[CYTD("6100", "Cal TP-Index", "Fiscal TPIndex")]];
ENDFIX

Get ID for String

This doesn’t solve all the problems around Smart Lists and text accounts, but it is a step in the right direction. If you don’t know, both of these are held in the Planning repository. Essbase ONLY stores numbers. Look at this as the index to the value you see in Planning. The repository has the map from index to value. In an Essbase calculation, you can’t set a text account to a text value. Well, actually, you can. The syntax for this function, which assigns a text value, is [[PlanningFunctions.getIdForString("text")]]. This allows you to set the value of a text account to a string.

In Planning, you have an account named “acct1 text” that is of type text. You want to copy your values from FY16 Dec to FY17 Mar, and change the text account to “Not Budgeted,” it would look like this.

FIX (Actual, Working, P_000, "210")
   DATACOPY FY16->Dec TO FY17->Mar;
   Mar(
      "acct1 text"->FY17 = [[PlanningFunctions.getIdForString("Not Budgeted")]];
   )
ENDFIX

That’s A Wrap

One last thing. If you use any of these in a member formula, for some reason you have to remove a bracket on each side. So, instead of two, you just need one. I will say I have not tested all of these, but the ones I have tested/used do follow this pattern. Hopefully Oracle keeps expanding these. Although they aren’t as helpful as they were prior to Groovy, they are simpler to use than implementing a Groovy solutions for some of these needs. For you lifers, it is things like this that a newb tells you. Don’t ever think you can’t learn from somebody that “knows nothing.”

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.

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.

• Mastering The EPM Planning Javadocs
• Groovy for EPM Cloud Planning
• Groovy for EPM Cloud Planning (Grid Builders Only)
• A REST API offering will be available next month

Try it out for free getting a few samples.

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.

Before I head out to California to do another Groovy workshop, and to celebrate the completion of all the modules, I wanted to celebrate by offering a discount.  This week only, get a $150 discount.  I have not talked about this in an official post, but it has gotten a ton of great feedback.  The discount will be available through Friday.  It is time to get excited!  I am adding a case study with full code to make your application a real-time what-if application with full calculations and undo operations before the data is committed.

Prospective Audience

The industry is made up of a wide range of backgrounds. We are surrounded by financial resources with an aptitude toward technology. We have IT trained resources that have been exposed to coding. And we have engineers with a strong java background. All of these resources are like minded in that we learn by doing and like to find the answers ourselves. Many will get an example from an industry peer and replicate that example. Sometimes the example isn’t efficient, or it gets used for purposes it was not intended to be used.  The java experts might stick with what they know and write groovy as if it was java.

The result is perpetuating scripts that are less than optimal in the community, building scripts that are not flexible causing issues when data forms change, and code that can’t easily be reused. Both of these strategies will get the job done but neither is the good approach.

So, why take Groovy for EPM Cloud Planning when you think you have a strong background in groovy calculations?

• So you don’t write a script like this to get the members in the point of view [pause] when it can be written like this.
• So you don’t build a dynamic fix statement like this [pause] when it can be written like more efficiently.
• So you dynamically reference all run time prompts that can be reused in all your scripts [pause] rather than hard code the references that have to be changed for every rule.

So you know that there are an infinite amount of things that can be done rather than being limited by only what you have seen. When you go through Groovy for EPM Cloud Planning, you will be able to do things like

• Update outlines sourced from a rest source in a calculation.
• Allow power users to limit what can be entered unless the planner documents a reason for the override in a cell comment
• You can even build a what-if process that uses existing architecture and allows results to be viewed and stored virtually before the data is committed to the database with full undo or cancel abiliy
• Or, email administrators immediately when a user executed calculation that doesn’t finish successfully

This is just a taste!

Why Is This Different

Other internal initiatives and in class or web learning covers the basics of the Planning API. Understanding the components of Groovy to effectively use the Planning API is missed. Without that knowledge, implementation specialists will never be as effective as they should be. Taking the approach of bypassing learning the Groovy basics isn’t is like learning advanced algebra without knowing how to add and subtract. You will go through the motions, maybe get the right answer, but you will never understand why you are doing what you are doing and your path to the solution will be long and frustrating. You won’t be able advance your knowledge without seeing examples from somebody else and your creative solutions will be minimized to replicating what others have done.

Course Content

This class is designed to be self-paced and will give participants everything they need to know about Groovy to build complex solutions. Everyone will leave with the knowledge of reading and understanding the Java Docs, enabling them to expand their abilities as enhancements are released. The following topics are covered.

• Understanding Groovy variables
• Understanding Groovy syntax and scripting basics
• Understanding Groovy error trapping and logging
• Optimizing Essbase calculations by dynamically creating scripts based on edited data
• Optimizing data maps and smart pushes
• Interacting with run-time prompts to validate user input
• Interacting with data forms to validate user input
• Building calculations for ASO play types
• Creating real time play type data synchronization
• Creating real time consolidated application reporting

Module Format

Every module is organized in the same way, so those attending have a consistent and positive experience. They start with an introduction and a review of the module objectives. Every module includes instruction that covers both basic and advanced content in a way that is easy to understand for both the novice and the experienced. The modules include a narrated presentation of the material. The concepts are demonstrated with an application modeled after Best Buy* products, departments, and stores, and I apply the teachings to real-world scenarios. Each module concludes with reiterating the module objectives and gives the attendees exercises to complete to reinforce what was learned. Every module is supported by downloadable copies of every line of code that was discussed, as well as the answers to the learning labs when applicable.

Real-World Learning

When participants complete the modules, they will not only be capable of building end-to-end solutions with Groovy, they will also leave with critical information I have consolidated by using Groovy since its release. There are challenges I share that have taken me months to overcome. These include roadblocks developers encounter, from data precision issues using data grids to optimization techniques when calculating ASO play types.

That’s A Wrap

If you have any questions, you are more than happy to reach out to me directory, post a question, or email me.  Have a great week!

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.