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.

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.

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

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

Dimension Explanation

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

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

• Ohio_Total
  • Ohio
  • Cincinnati
  • Columbus
  • Cleveland

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

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

Groovy Calculation Overview

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

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

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

Groovy Calculation Deep Dive

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

[membership level=”0″]

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

[/membership]
[membership]

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

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

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

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

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

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

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

[/membership]

That’s A Wrap

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

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

Think Different

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

Building The Foundation

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

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

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

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

Cube cube = rule.getCube()

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

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

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

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

That’s A Wrap

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

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

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

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

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

Locking Cells

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

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

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

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

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

Add To The Form

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

That’s A Wrap

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

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

Check If Data Has Changed

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

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

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

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

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

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

Inform The User Or Not

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

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

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

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

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

That’s A Wrap

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

If you haven’t heard, we now have the ability to execute REST API within a calculation script.  Not only does that mean we can interact with other environments and do things in calculations we couldn’t do before, we also have the ability to interact with any provider that has REST.  You could pull current prices for products from Amazon.  You could see how many open box items there are at area Best Buy stores.  You could pull in currency rates.  That doesn’t even touch on the things like DM processes, metadata updates, and application refreshes. You can even send emails!

First, what is REST API?

A RESTful API is an application program interface (API) that uses HTTP requests to GET, PUT, POST and DELETE data.  A RESTful API — also referred to as a RESTful web service — is based on representational state transfer (REST) technology, an architectural style and approach to communications often used in web services development.

What this means is you can interact with services through web URLs.  When you send an email or purchase a product, you are using HTML Post and Get requests.

How does it work?

REST is basically a way to post (do something) and get(receive something) through website URL calls.  There is more to it than that, but that is the basic concept.  There are all kinds of services, free and paid, that provide services through REST API.

Since I am all about automation and communication, one thing I thought would be great is if a user runs a calculation and it fails, to proactively notify an admin.  Prior to this month, you would have to write something outside of the UI for Planning to do this.  This would be reactive.  Wouldn’t it be better to get an email or text immediately so you can act quickly and diagnose the problem?  Heck yeah it would.

The REST API Provider

There are a number of providers that give you the ability to send emails via REST API.  A few are free for a limited number of emails per day/month.  Mailjet is one of them.  I went out and signed up for a free account.  It allows you to send up to 6k emails a month, with a maximum of 200 per day.  That is cool for my purposes of demoing functionality.

There are a few things you need.  First is an encrypted key and private key.  That you will get when you create our account.  Next, the documentation lays out what needs included in a post (do something) to send an email.

To send an email through mailjet, you post to a url of https://api.mailjet.com/v3/send and pass a body that has the typical information to send an email, like from, to, and subject.  The body is formatted as a json object.  Json is not super complicated, as it is basically the same as a Groovy map, or an xml format.  To focus on the API, I will explain json at a later time.

Send An Email

The first thing that needs built is a connection to the REST API provider.  You can create a connection to be reused in Planning (epbcs), but for this example, I am just going to keep it simple and use a connection object with the appropriate parameters.  To keep my account private, I remove my keys and replaced them with PublicKey and PrivateKey.  Just remember these both need replaced with the actual keys.  The status should return 200 if the request is successful.

Connection connection = connection("https://api.mailjet.com","PuclicKey","PrivateKey")
println  connection.get().asString().status

Next, I am going to walk through the body that needs passed.  Again, this is basic info that is not surprising.  Each element has a property and value.  To avoid any more spam in my email account, I replaced the from and to email with invalid emails.  These should be changes to include valid from and to emails.

def bodyMap = new JSONObject()
bodyMap.put("FromEmail","xxx@in2hyperion.com")
bodyMap.put("FromName","Kyle Goodfriend")
bodyMap.put("Subject","My first email from a Groovy Calculation!")
bodyMap.put("Text-part","This is an email from a calculation")
bodyMap.put("Html-part","<h3>Dear Admin,</H3><br />This is an email from a calculation")
bodyMap.put("To","xxx@in2hyperion.com")

Next is the actual request to post (or do something).  The path to send emails in the URL is https://api.mailjet.com/v3/send.  The only thing we need to add to the post here is the path since the URL is in our connection.  The post requires two parameters.  First, the request requires one header identifying the content as json.  The second parameter is the body that includes the email information, which we have in the bodyMap, which we simply convert to a string.

HttpResponse response = connection.post("/v3/send")
.header("content-type","application/json")
.body(bodyMap.toString()).asString()
println response.status
println response.body
println response.statusText

This all said, the result is an email to your inbox and a log in the job console that looks like this.

Last Call

Well boys and girls, that is all she wrote.  That is it.  If your calc throws an error, use try/catch/finally and send the appropriate people an email.  If the calc is a long running calc, user operation.user to send the person that executed it an email when it finishes.  The possibilities are endless but you now have a mechanism to have proactive communication, not reactive and time-consuming effort.

One note about mailjet.  I have no affiliation to this service.  I have not used it other than to demonstrate this functionality.  A FREE account does allow you to send 6k emails a month with 200 at most every day.  If you want to use this in a production situation, you likely will need to pay for basic account, which is less than 9 bucks a month.