Introduction

This 15-minute tutorial introduces you to the Groovy scripting language and shows you how to create a Groovy script for Planning in Oracle EPM Enterprise Cloud service.

Background

Groovy is an agile, dynamic language for the Java platform that combines smooth Java integration with the benefits of Java performance. Many features of Groovy are inspired by languages like Python, Ruby, and Smalltalk, making them available to Java developers using a Java-like syntax. Groovy has been adopted by many large and small companies, including Oracle, IBM, and SAP, for its powerful features of both static and dynamic language, securability for cloud environments, and domain-specific languages.

Prerequisites

Cloud EPM Hands-on Tutorials may require you to import a snapshot into your Cloud EPM Enterprise Service instance. Before you can import a tutorial snapshot, you must request another Cloud EPM Enterprise Service instance or remove your current application and business process. The tutorial snapshot will not import over your existing application or business process, nor will it automatically replace or restore the application or business process you are currently working with.

Before starting this tutorial, you must:

  • Have Service Administrator access to a Cloud EPM Enterprise Service instance.
  • Upload and import this snapshot into your Planning instance.

Note:

If you run into migration errors importing the snapshot, re-run the migration excluding the HSS-Shared Services component, as well as the Security and User Preferences artifacts in the Core component. For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

Why Groovy?

Understanding the limitations of calculation scripts

Planning uses calculation script-based business rules to perform calculations and actions. While these scripts are powerful, they have some limitations:

  • They're unable to dynamically generate scripts at runtime based on contexts other than user-entered run time prompts. For example, if you change a few cells in a form, a calculation script business rule has no way to identify what data has been changed and then calculate based only on that data.
  • They're unable to dynamically specify the parent for a current member (for example, by project type or region).
  • You can't validate user-entered data or run time prompt values before the calculation is run.
  • You can't combine multiple EPM functions into a single rule or rule set (for example, automatically running a Smart Push operation after calculating).
  • They may experience performance issues due to their static nature (for example, requiring a calculation of a large portion of the database even when only a few cells have been updated).
  • Although lilmited context-specific calculations are available in forms by right-clicking and calculating only a specific row, this option is less useful for large forms or where you update cells in multiple rows.
  • For ASO applications, they provide only simple calculations.

Benefits of Groovy script rules

With support for Groovy rules in Oracle EPM Enterprise Cloud, you can address all of these issues. Groovy rules enable you to:

  • Dynamically generate calculation scripts at runtime based on contexts defined in the Groovy script
  • Add validation checks for run time prompt values before running calculations, add data validation checks before submitting data, and add error handling
  • Launch multiple EPM functions with a single rule (for example, run a calculation and then run a Smart Push operation to move calculated data to a reporting cube)
  • Improve performance by creating focused calculations (for example, dynamically creating custom targeted calculation scripts based on what data was modified in the current form)
  • Perform complex procedural calculations in memory before submitting the results to the database

Groovy in Oracle EPM Enterprise Cloud

Types of Groovy rules

Oracle supports two types of Groovy rules:

  1. Rules that can dynamically generate calculation scripts at runtime based on contexts such as runtime prompts, the POV, the current grid, and so on. The generated calculation scripts are then executed against Essbase cubes.

    An example of this type of rule is one that calculates expenses for projects only for the duration of the project, based on start and end dates. Another example is the trend-based calculation in Financial Consolidation and Close Cloud which restricts the calculation to the accounts available on the current form. This allows you to use the same rule for calculating various forms in revenue, expense, balance sheet, and cash flow, which optimizes the calculation and reuse of the rule.

  2. Pure Groovy rules that don't generate calculation scripts.

    Examples of this type are rules that perform data validations and cancel the operation if the data entered violates company policies, rules that change the background colors of cells based on numeric thresholds, or rules that modify dimensions and members in the outline.

Note:

Any Groovy script that returns a string is returning an Essbase calculation script that will be executed against the specified Essbase cube. Avoid using strings as the final value of the last statement unless it is a calculation script that you want to execute after the Groovy script launches successfully.

You create Groovy rules in the Calculation Manager, and you can execute them from any place in the application that a business rule can be executed.

Using the EPM Groovy scripting APIs

  • The EPM Groovy scripting APIs allow Groovy scripts to perform a variety of operations against the EPM object model. Most objects, such as the current application, cube, and grid, can be retrieved from the operation variable. In most cases, the entry point to the API will either be methods on the EpmScript base class or the Operation object.
  • he base script and the current operation have a set of variables that apply to the script. For example, if the script references a variable such as operation.grid, and the grid does not exist in the operation, then when the script is executed the system stops the script and logs an error in the Job Console. If the script has optional processing such as formatting the grid if it exists, then it can first check for the existence of the grid by calling operation.hasGrid(). Similar patterns are available for other objects in the model.

    • Tip:

    For the API specification of the EPM Groovy Scripting Platform, please see the Oracle Enterprise Performance Management Cloud, Groovy Rules Java API Reference

Creating Groovy business rules

Oracle supports two types of Groovy rules:

  1. In a Planning application in EPM Enterprise Cloud, open the navigator and under Create and Manage, click Rules to display Calculation Manager.
    2. Navigator Rules
  2. In Calculation Manager, select Actions, and then select New Object. In the dialog box, select an application type, application, and cube. For Object Type, select Rule. Enter a name for the rule, and then click OK.
    3. New Object Dialogue
  3. A message that the rule is created and open for editing is displayed. Click OK to continue.
  4. In the Designer drop-down list, select Edit Script
    5. Edit Script
  5. In the Script Type drop-down list, select Groovy Script.
    6. Groovy Script
  6. Click Save icon(Save).The rule is now a Groovy Script based business rule, and you can use the editor to develop your script according to your business use case. The following example shows a simple Groovy script in the editor that will change the background color of cells in a data grid, based on numeric thresholds. You can also open the script to view it in a separate tab.
    7. Script Sample