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.

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

What Do Do First

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

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

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

Your Second Step

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

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

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

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

That’s A Wrap

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

There are a ton of reasons to convert a planning load file to an Essbase load file.  Maybe you are migrating a file from one environment to another, or simple want to load the file faster, but there are reasons to use the Essbase format.

Oracle is working on an enhancement that should be released in the next month or two that will use a load rule to load data to the app using the Essbase load format, which means the logging will be much improved, it won’t stop at the first failed line, and it will log all the errors, just like the Planning load format.  That is great news for those of us that use the planning format purely for the logging.

Performance

Before I get into the script, I want to touch on the speed of this method.  The file I used, based on a real situation, was over 89 million lines (yes, that is correct, million), and took over 5 hours to load as a Planning file.  It had to be split into three files to be under the 2GB limit, but it successfully loaded.  The file was received late in the morning and had to be loaded before the start of the day, so a 5 to 6-hour processing time was unacceptable.  By the way, yes, the file was sorted appropriately based on the sparse and dense settings.

I was able to build a unix/linux script using awk to convert this file to an Essbase load format and it only took about 9 minutes to convert.  The improved load time was pretty drastic.  It finished in under 15 minutes.

For testing, it was great, and it was perfect to improve the processing until the source system could rebuild the export in the Essbase format.  Just to reiterate, I added less than 10 minutes to convert the file, and reduced the load time by 4.5 hours, so it was worth the effort.

The Catch

Before I continue, if you are unfamiliar as to why the two load formats, here is the difference.  Essbase loads the data directly to Essbase.  The Planning load will bounce the file off the Planning repository to convert any smart list string account to the appropriate number, which is what is stored in Essbase.  This process creates a new file on the server, in an Essbase load format, with the numeric representation of each smart list account.  If you have no smart list conversions, this entire process is done for no reason, which was the case in this situation.  So, this isn’t the answer in every situation.

The Script

Before I get into the script, if you know me you know I love my Mac.  One of the reasons is because I have the performance of a Mac, I can obviously can run Windows whenever I want, and I have the ability to run Bash scripts through the terminal.  I am not a Bash scripting expert, but it is extremely powerful for things like this, and I am learning more as I need to build out functionality.

If you are a Windows user, you can install and use Linux Bash scripting in Windows 10.  You can read about it here.

There are several languages that can be used, but I chose AWK, which is a domain-specific language designed for text processing and typically used as a data extraction and reporting tool. It is a standard feature of most Unix-like operating systems.

First the script.  Here is it.  I put the awk on multiple lines so it was a little more readable, but this is one command.

SOURCEFILE="Data.csv";
LOADFILE="DataLoad.csv";
HEADERMBR=$(head -1 $FILE | cut -d ',' -f2)

awk -v var="$HEADERMBR" 
     'BEGIN {FS=","; OFS="\t"} 
     NR>1 
     {gsub(/"/, ""); 
          print "\""$1"\"", "\""$3"\"", "\""$4"\"", 
                "\""$5"\"", "\""$6"\"", "\""$7"\"", 
                "\""$8"\"", "\""var"\"", $2}' 
     $SOURCEFILE > $LOADFILE;

There are a few things you will need to change to get this to work.  Update the source file and the load file to reflect the file to be converted, and the file name of the converted file, respectfully.  Inside the AWK script, I have 8 fields, 1 through 8.  This represents the 8 columns in my Planning file, or the dimensions and the data.  Your file might have a different count of dimensions.  If your file has more or less delimited columns (ignore the POV field quotes and assume that each delimited field in that is an additional field), update the script as needed

In this example is a planning file example and each arrow represents a field.  The print section of the awk command changes the column order to fit what the Essbase load format requires.

Breaking down AWK

This won’t teach you everything there is to know about AWK, as I am still learning it, but it will explain the pieces used in this command so you can get started.

This piece is simply creating two variables, the source file and the converted file name, so there aren’t multiple places to make these changes when the script needs updated.

SOURCEFILE="Data.csv";
LOADFILE="DataLoad.csv";

The head command in Linux grabs specific lines, and -1 grabs the first line of the file.  I pipe that with the cut command to grab the second field of the header line, which is the dimension member I need to add to every row.  That gets stored in the HEADERMBR variable for later use.

HEADERMBR=$( head -1 $FILE | cut -d ',' -f2)

The example file above is repeated here.  You can see that the second field is the member and HEADERMBR is set to source_SAP.

Now the AWK command.  Before I jump into it, the AWK looks like this.

awk 'script' filenames

And in the script above, the awk script has the following form.

/pattern/ { actions }

You can also think of pattern as special patterns BEGIN and END.  Therefore, we can write an awk command like the following template.

awk '
     BEGIN { actions }
     /pattern/ { actions }
     /pattern/ { actions }
     ……….
     END { actions }
' filenames

There are also a number of parameters that can be set.

This script starts with a variable.  The -v allows me to create a variable.  The first part of this command creates a variable named var and set it equal to the HEADERMBR value.  I have to do this to use the variable in the script section.

-v var="$HEADERMBR"

The BEGIN identifies the delimiter as a comma and sets the output delimiter to a tab.  FS and OFS are short for Field Separator and Outbound Field Separator.

'BEGIN {FS=","; OFS="\t"}

Since the file has a header file, and I don’t want that in my Essbase load file, I only include the lines greater than 1, or skip the first line.  NR>1 accomplishes that.

NR>1

Gsub allows me the ability to create substitutions.  The source file has quotes around the POV field.  AWK ignores the quotes, so it interprets the field with the start quote and the field with the end quote as a field with a quote in it.  These need to be removed, so the gsub replaces a quote with a blank.  The first parameter is a literal quote so it has to be “escaped” with a /.

gsub(/"/, "");

The next piece is rearranging the columns.  I want to have the second column, or the column with the data, at the end.  I have 8 columns, so I put then in the order of 1, skip 2, 3 through 8, then the variable that was created that has the dimension member in the header line, then 2(the data field).  It looks a little clumsy because I append a quote before and after each field, which is required for the Essbase load format.  But, this is just printing out the fields surrounded by quotes (except for field 2, the data field) and separated by columns.

print "\""$1"\"", "\""$3"\"", "\""$4"\"", "\""$5"\"", "\""$6"\"", "\""$7"\"", "\""$8"\"", "\""var"\"", $2

The last piece is identifying the file I want to do all this work to.

$SOURCEFILE

I want to send the results to a file, not the screen, and the > tells the command to send the results to a new file.

> $LOADFILE

The Result

The outcome is a file that is slightly larger due to the additional quotes and replicating the member from the header in every row, normalizing the file.  It is converted to a tab delimited file rather than a comma delimited file.  The header is removed.  The app name is removed.  And the columns are slightly different as the data column was moved to the end.

That’s A Wrap

I am not ashamed to say this simple, basically one line script, took me forever to build and get to work.  By forever, I don’t mean days, but definitely hours.  That is part of the learning process though, right?  It was still quicker than waiting 6 hours for the file to load!  So now you have basically a one line awk command that converts a Planning load file (or an export from Planning) to an Essbase load file and you can get home to have dinner.

I love my Mac and I am getting really close to not needing my Windows VM. I don’t think Smartview for Office 365 is going to be a replacement for the Windows version in my lifetime, but that is the only thing really making me keep my VM current.

Installation Woes

First, I an not a UNIX guy.  I love some of the functionality in terminal, especially manipulating files.  But I have tried to get EPMAutomate installed and working for a year and just about given up.  Every few months I try again and fail.  For all you Unix/Linux people, I am going to embarrass myself.  For everybody else having the same challenges, I think this will get you over the hump.

Choose Your Poison

You probably know you can run commands in terminal and you probably assume Bash is the default scripting language.  As of Catalina, Apple is using Z shell as the default.  You can change this if you want.  I am not an expert but everything I read, people really like Z shell and prefer it over Bash.

But if you want to change it or see what your default is, open up your system preferences and go to Users and Groups.  Here is a trick.  If you hold Control and click on your profile name, an option for Advanced Options appears.  Click that and you will see the Login Option is probably set to /bin/zsh, which is Z shell.  You can change this to /bin/bash if you want to use bash.

It is important to know the above because you will want to set some environment variable defaults, which I will get to shortly, and you have to know the default to update the appropriate file.

Installing EPMAutomate

First, download EPMAutomate through the normal way to download the utilities.  For cloud users, click on your use name when you log in and you will have a download options  Download the Linux/Mac version.  It doesn’t matter where you download it, but if you download it to your Home directory (the parent of downloads, documents, etc), it is easier to install in that location.  This is my preferred area because my path to the tool is shorter and it is separated from my other content.  The download is a zip file so unzip it to your preferred location.  There will be an epmautomate folder.  Inside that folder will be a bin folder, and the shell command is inside that.

You are finished with the easy part.

Configuring EPMAutomate

This is where I struggled and gave up a number of times.  Thank goodness I finally figured it out because I was really tired of using my VM when I needed to run quick commands to do basic things.  There are a few things that have to be done to get it to work effectively.  First, for me, my java home directory was not set and I edited the epmautomate.sh to include it.  Every time I updated the version, I had to go back in and do it again.  The other frustrating thing was that I couldn’t figure out how to make my default profile include the bin folder noted above in my path.  So, I always had to execute epmautomate with a path or drill into the path to make it work.  I also had to prefix it with a dot to get it to run.

You may have known enough to know you had to set your environment variables.  You may have known that a file existed, or should exist ,named .profile in your home directory.  You may have even known how to edit that file, and that it is hidden (anything with a dot in front is a hidden file).  If you went down this path, tried to update your profile and it didn’t work, here is why.

If your default scripting language is Z shell, or zsh, it looks to a profile in .zprofile, not .profile or .bash_profile!  There are several editors, but the easiest one for me to use is nano.  Open terminal and enter the following command to edit/create the profile

nano .zprofile

Update .zprofile

When you open the file, it will likely be blank.  There are two things we need to add.  First create your JAVA_HOME variable.  It will look something like this.

export JAVA_HOME='/library/Java/JavaVirtualMachines/jdk-13.0.2.jdk/Contents/Home’

If you don’t have a Java SDK installed, download and install the latest JDK.  Once downloaded, type the following into terminal, which will provide the path of the most recent version if multiple versions are installed.

/usr/libexec/java_home

If you want a specific version, add the version to the end of the command, like this.

/usr/libexec/java_home -v 1.8

Next, add the path of where you unzipped EPMAutomate to your path.  This should include the bin directory.  Your path may be different than what I chose.  The following will update the path variable to append your EPMAutomate path.

export PATH=$PATH:~/epmautomate/bin

Your .zprofile will look something like this.

Hold the Control key down and hit X.  This will exit and ask you to save your changes.

Test Your Variables

Close your terminal window and open a new one.  Your profile should load now.  You can check that the variables are loaded by entering the following.  Each should return the variable. The Java_Home should return your java path, and  the Path should include your EPMAutomate directory.

echo $JAVA_HOME
echo $PATH

At this point, if you enter epmautomate.sh, it should return the current version installed.

That’s A Wrap

There you go, use it just like on Windows!  Rather than epmautomate or epmautomate.bat, you enter epmautomate.sh.  Other than that, everything is pretty much the same.  It seems simple, but it took me forever to figure out how to add my variables.  All my script templates are updated and I am loving it!

Having the best of both worlds, ASO and BSO, doesn’t come without some gotchas.  Before you jump in with both feet, beware of some things that are not supported in hybrid.  As of Friday, May 22, 2020, @ISMBR in planning does NOT work. I don’t know if this is a bug, but it is not documented as a function that doesn’t exist.  What is documented is the following.  There isn’t a ton in this post, but I thought it would be beneficial to share this as a warning, as well as an easy way to find the list. If you find more things that don’t work, please share with the community.

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.