Most of the time all we do with EPM Groovy business rule is to fetch the members from the form and use those members in the fix and trigger the business rule. This seems like an essential thing to do that saves time for the business users, but in order to do that we have to use few lines of code each time. I have written a countless of ‘operation.grid.dataCellIterator()’ in the past few years.

I was wondering if the Oracle EPM team could provide us with a System Rule Template and Groovy APIs that perform a simple operation like this. I mean we have already been provided with a lot of Ready to Use Template for Allocation, Copy Data, Clear Data, Currency conversion, etc. Moreover, the Currency Conversion script template in ASO cubes dynamically generates Groovy script. I have written this in one of my previous post here.

But why don’t we have a template that allow us to fetch a edited members from a simple forms? If anyone from the Oracle EPM Product Development team, please generate system template for fetching edited members, data pushes, etc.

Creating My Own Template

I thought, ‘What if I create my own template so that I don’t have to write repetitive Groovy script?’ Here are the rules I set before creating my own Groovy Template.

1. It should be a GUI based template.
2. It should read the Calc Scripts
3. It needs to fetch members from the form
   • Option to check Edited Members or All Members
   • If there are more dimensions are in the form rows, it should create a nested FIX instead of plain fixes
   • Ability to ignore dimensions while fetching members from the form
4. It needs to provide ability to fetch the year and period members from the Scenario Member Start and End Periods
5. It should provide an Debug Option that logs more information during configuration

It seemed impossible since all of this would need to be written using Groovy and System Templates. Moreover, we don’t have the same leverage as the Oracle EPM Dev team. All of this would need to be done with the existing functions and objects

So, How did it go?

Okay, let me show you how to create and execute a business rule that runs a Groovy script to trigger a business rule for the form members using a GUI Template.

01 – Create a Calc Script that does a simple Calc

02 – Create a Groovy Business Rule that Triggers the Above Calc Script

03 – Add the Template to the Business Rule

04 – Define the Script Details in the Template

05 – Define the Grid Scope in the Template

06 – Define the Period and Year Scope in the Template

06 – Lets Run in the Debug Mode

Lets add this rule in a form and Validate

I have run it with Debug mode on. This will help me validate if all dimensions are being fetched from the form properly. Missing a dimension in a business rule can have catastrophic effects. So it’s important to validate this business rule on a form

Changing only Melbourne and Sydney Entities.

Lets Look at the Job Console Log:

So far, all good. The Debug is capturing the same parameters that we passed over via the tempalate.

Year and Period Ranges in the Log:

I have used a method to fetch Year and period ranges from the User Variable Scenario member. Lets look at the Job Console results.

The current application starts on Jul and Ends on Jun. So, it fetches properly.

Validating the Nesting Operation

The script creates a nested FIX statement for the members in the Rows. This ensures that only the edited member combinations are calculated, instead of creating combinations of all members. This is a crucial step and can be challenging to achieve the desired results

If Entity1-> Position1 and Entity2-> Position2 are calculated, if my script places these members in a FIX without creating a nested dimension or separate dimension, this will calculate more blocks than I intended. If my script generates the following code, it will unnecessarily calculate Entity1 -> Position2 and Entity2-> Position1.

FIX( "Entity1", "Position1", "Entity2", "Position2") 
/* */
ENDFIX

The below method calculates only the necessary blocks. Please note that this may increase your calc passes.

FIX("Entity1") 
   FIX("Position1")
      /* */
   ENDFIX
ENDFIX
FIX("Entity2") 
   FIX("Position2")
      /* */
   ENDFIX
ENDFIX

Also, the dimension order in which the outer FIX and inner FIX occur is very important. My script analyzes the dimension order in the form and generates a dimension order that requires fewer inner FIXes

This debug information shows Non-Nested (Outer FIX) and Nested (Inner FIX) Dimensions.

Viewing the Final Script Output

The script produces a final Calc Script, which is an exact replica of the script that will be executed in Essbase. This output can be found in the inbox/outbox explorer when the debug option is enabled.

Lets see the contents on the script in a conventional business rule. This is the script that gets generated each time dynamically based on the edited members and other dynamic things like scenario start and end period etc.

Let’s create a new script without Scenario Start and End Period, using the period and year from the form:

Updating data on the form:

Let’s look at the script now:

Conclusion

I successfully created a customizable template for EPM Groovy business rules that addresses the common need to fetch members from forms and use them in FIX statements. This template offers several advantages, including:

• Flexibility: The template can be adapted to various applications/cubes due to its dynamic member fetching capabilities.
• Efficiency: It eliminates repetitive coding, saving time and effort for implementation consultants.
• Clarity: The GUI-based interface simplifies the process, making it accessible to users with different technical backgrounds.

I believe that a similar template developed by the Oracle EPM Dev team could significantly benefit the broader EPM community.

I hope this information is helpful.