Before you begin
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.
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.
What do you need?
An EPM Enterprise Cloud Service instance allows you to deploy and use one of the supported business processes. To deploy another business process, you must request another EPM Enterprise Cloud Service instance or remove the current business process.
- Have Service Administrator access to EPM Enterprise Cloud Service. The instance should not have a business process created.
- If you haven't already, register for a free Oracle Cloud Customer Connect account so you can access the required files. Upload and import this Planning snapshot into your environment. If you've previously uploaded the snapshot for another Groovy tutorial, you can continue using the same snapshot.
For more information on uploading and importing snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.
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:
- 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.
- 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.
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
The 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.
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
- In a Planning application in EPM Enterprise Cloud, open the navigator and under Create and Manage, click Rules to display Calculation Manager.
- 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.
- A message that the rule is created and open for editing is displayed. Click OK to continue.
- In the Designer drop-down list, select Edit Script.
- In the Script Type drop-down list, select Groovy Script.
- Click (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.