Thoughts on Why Groovy Can Be a Game Changer for Your Oracle EPM Apps

Use case : @return Essbase function x Groovy try/catch

This post explores some thoughts on what Groovy can bring : for the best and the worse!

Today I want to share some thoughts on why Groovy can be a game changer for your Oracle EPM apps. I’ve been into Groovy for +5 years now, and I feel very lucky to be amongst those who both see its potential and want to push it to its best.

First of all, something you should know about me is that when I face a challenge, in particular when it comes from a client, I really try my best to try to answer – and if possible with a success outcome.

I remember the first time I struggled with Groovy : a few years ago, I was working on a migration project from On-Prem Planning to Enterprise PBCS. They had this custom solution using FDMEE x VB scripting, that allowed them to allow or disable DLR to run. One pain point in our migration strategy was to create something similar on PBCS.

At the time, the event options to trigger business rules in Data Integration were not available : it was only available in the monthly update of October 2020 (https://www.oracle.com/webfolder/technetwork/tutorials/tutorial/cloud/epm/wn/epm/releases/Oct/20oct-epm-wn.htm). So how would we do this ?

First we analyzed the existing solution

The existing solution was based on a simple principle : dates were added into a parameter file (format YYYYMMDD), and this file was parsed by a VB script upon data file load so that depending on various conditions (period, year, location, DLR, accounts hierarchy), the data file was loaded or not.

EPM functional or technical admin was inputing dates, that were owned by the business, and at the end users were applied this data load file policy. It was working fine for years, and everyone was happy. And wanted to keep this when the migration project to the cloud araised.

At the time, Groovy was already on all lips, but possibilites not very clear : this is still true today for more advanced use cases, and probably less true today for basic use cases. We were exploring what we could do, not really knowing if what we wanted to achieve was possible.

On the other side, after proper due diligence, we understood the existing solution had several painpoints :

it needed technical expertise to understand technical script
it needed security to edit a parameter file within FDMEE
it was error prone
it was not user-friendly at all

But still it was doing what it was supposed to do : to allow or forbid data file loads from users. So the deal was as simple as that : this customer accepted to pay the price of these painpoints because they valued the purpose of what this solution was implementing.

How could Groovy both allow the migration of this feature ?

Then we tried something which I was both excited to have achieved, but yet I felt frustrated until…

We rapidly came to a solution design where we would store the dates in the cube. So far so good. Foreseeing business users inputting these dates on their own through data forms was then obvious. 6 accounts hierarchy x 2 dates (opening & closing) = 12 new accounts to store dates.

How would we now use that information at desired time ? Remember we were before update 20.10 ! Only the “Embedded” section of the Business Rules option in Data Integration was available. So that’s when we started to think of loading to a temporary version and then to execute an embedded Groovy rule that would conditionnally copy the data from the temporary version to the expected version.

The technical design of the Groovy rule was to generate a classic script dynamically and then to run it :

Loop to access the data file, row by row
- Depending on the account name on the row, assign the corresponding date amongst the 12. Remember, the functionality here is to be able to have different opening/closing dates depending on the accounts hierarchy
- Using 3 Essbase temporary variables to hold current date, opening date, closing date, and using the calc function “@CalcMgrDateToExcel”
  - Opening date missing : return an error using @return function with message “opening date missing”
  - Closing date missing : return an error using @return function with message “closing date missing”
  - Current date < opening date : return an error using @return function with message “opening date not yet reached”
  - Current date > closing date : return an error using @return function with message “closing date already passed”
Execute the script generated dynamically

Once embedded in the DLR, the rule was self-checking if the current date was between the opening and the closing date, dynamically for each row of the data file, and was copying the data from the temporary version to the target version.

So far, we were able to do what we wanted, right ?

Actually, there were one major drawback : the DLR status would always be “Completed” even if there was a row with unauthorized data.

Using the throwVetoException in the Groovy script, along with the @return in the classic script, was really bold. And I expected Groovy to communicate natively with the classic script, meaning that any error sent from the generated classic script would return an error to the Groovy script with the @return command.

I opened an SR and trying to solve this with the help of the support.

They did told us to use the function “hasAtReturnMessage”, which we used to write the following code :

if (e.hasAtReturnMessage()) {
    throwVetoException("error")
}

The status of the DLR would now be in “Error” if there was a row with unauthorized data, that was already a relieve ! The @return was designed so the exact row that was eventually not allowed by the opening/closing dates, was identified really explicitely on all dimensions, but then we had no way of letting Groovy get that information. So frustrating ! Until…

I had an answer to another SR, and after having explained what I was expecting, I was finally told to use the function “getAtReturnMessage”, which then ended in following code :

if (e.hasAtReturnMessage()) {
    throwVetoException(e.getAtReturnMessage())
}

EU-RÊ-KA !

I can still feel the satisfaction from achieving this as it happened yesterday ! This was so exciting !

Yet another challenge

We finally had our solution to migrate this feature from On-Prem to Cloud. Cherry on the cake was that all painpoints from FDMEE x VB were now gone, thanks to the data forms to input dates directly in the cube.

The other challenge we tried to achieve was to use these dates to lock/unlock data input on forms. To do that, the design was the following:

Modify all data input forms to add new hidden rows to have dates available within the forms
Attach a Groovy rule on “Run after load” so that each modified cell on a perimeter that has been closed is rejected on “Save” action

Lessons learned

Thanks to this solution design involving the capabilities of Groovy, not only the inital requirement to migrate was fulfilled, but since that the process leaders were owning their closing agenda in the simplest way possible.

The lessons learned about this experience are :

Groovy can be a key building block for creating user-focused features
Do not hesitate to ask the Oracle support team for help, they have Groovy experts that could really help you out.
Clearly explain what you are trying to achieve to the support team. This sounds obvious, I know !
The documentation is still to be improved. So far, I have still not found any occurence of this “getAtReturnMessage” function in any documentation, Oracle CCC forums, or any blog. Question is : what other existing functions are there to be found ?