Introduction

This 45-minute tutorial shows you multiple ways to generate focused calculation scripts in Oracle EPM Cloud Planning to calculate only data that has been edited, instead of the entire data entry form.

Background

The Groovy EPM object model enables you to determine what cells in a data entry grid have been edited, and then generate a focused Essbase calculation to calculate only those members impacted by your data entry.

In this tutorial, you work with a Product Volume Analysis dashboard. The top form, Product Volume, captures the volume for various products in monthly periods. The bottom form, Product Revenue, displays revenue for the various products. When you edit the top form, you want revenue to be calculated in the bottom form for the edited products only.

Product Volume Analysis Dashboard

This tutorial is in three parts and covers three different ways to calculate the edited products:

  1. Generating and executing a focused calculation script
  2. Creating a Groovy script that executes an existing calculation job
  3. Generating and executing a focused calculation script, then returning to the Groovy script for further evaluation

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. If you've previously uploaded the snapshot for another Groovy tutorial, you can continue using the same snapshot.

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.

Tip:

The scripts you need for this tutorial are linked as text files within each section.

Setting up the Planning environment

Setting user variables in Planning

First, you set the required user variables to work with the Product Volume Analysis dashboard.

  1. From the Home page, click Tools, and then select User Preferences.
    2. User Preferences
  2. Select the User Variables tab.
  3. Enter the following variable definitions:
    4. User Variable Member
    Currency USD
    Entity Sales US
    Reporting Currency USD
    Scenario OEP_Plan
    Version OEP_Working

    User Variables Selected

  4. Click Save. At the confirmation message, click OK, then return to the Home page.

Importing variables for run-time prompts in Planning

Next, you import calculation variables to the OEP_FS cube to support the run-time prompts (RTPs) used later in this tutorial.

  1. Right-click the link for HP4_OEP_FS_Variables.xml, and save the file to your local drive.
  2. From the Home page, navigate to Rules (under Create and Manage ) to open Calculation Manager. In the System View, expand EPM Cloud > HP4. Right-click OEP_FS and select Import.
  3. Under File Import Details, browse to select HP4_OEP_FS_Variables.xml from your local drive.
  4. Under Location Details, make the following selections:
    • Application Type: EPM Cloud
    • Application: HP4
    • Cube: OEP_FS
  5. Under Import Options, select Override Existing Objects.
    Import Variables
  6. Click Import. Review the import results (they may say either Updated or Inserted), and then click OK.
    Import Results
  7. Click Cancel to close the Import dialog box.

Generating a focused calculation script for edited products

In this section, you implement a Groovy script in Planning to calculate product revenue for edited products.

Creating the Groovy script

  1. In Calculation Manager, create a rule named Groovy Calculate Product Revenue in the OEP_FS plan type.
    2. New Object
  2. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    3. Rule Editor Options
  3. Copy this script and paste it into the editor: 
    4. // Capture the edited periods and products
Set<String> editedMembers = [] 
operation.grid.dataCellIterator({DataCell cell -> cell.edited}, MemberNameType.ESSBASE_NAME).each {
DataCell cell ->
        editedMembers << cell.periodName << cell.getMemberName("Product") 
    }

if(editedMembers.size() == 0){ 
    println("No edited cells found!")
    return;
}

// Generate the calc script to calculate revenue for the products whose volume was edited
String calcScript = """ 
Fix(${cscParams(operation.grid.pov, editedMembers)})  
"OFS_Calculated"
(
   "OFS_Product Revenue" = "OFS_Volume" * "OFS_Avg Selling Price";
)
EndFix;""" 

println("The following calc script was executed by $operation.user.fullName: \n $calcScript") 
return calcScript.toString()

    Define a Set to capture unique edited members.

    Capture edited members.

    Check if any members were edited. If none were edited, no further processing is required so return from the script.

    Generate a calculation script to calculate the product revenue for edited cells by fixing on the POV members and edited members.

    Note:

    The cscParams() API is a utility that returns a string value with each of the specified items flattened, quoted and separated by a comma. The string returned by this method can be plugged into an Essbase calculation script command as parameters.

    Print a message indicating the user who executed the rule and the generated calculation script.

    Return the calculation script, which will then be executed.

  4. On the toolbar, click Save (Save) to save the script, then click ValidateAndDeploy (Validate and Deploy) to validate and deploy the script. Click OK when prompted. Leave the Calculation Manager window open.

Adding the rule to a Planning form

In this section, you add your Groovy script rule to the Product Volume form, to be run after saving.

  1. Select the Planning window, and from the Home page, navigate to Forms (under Create and Manage). Select the Product Volume form.
    2. Form Manager
  2. Edit the form. Add the Groovy Calculate Product Revenue rule and set it to Run After Save. Save the form and close the Form Manager window.
    3. Run After Save

Testing the focused calculation rule

In this section, you test your Groovy rule by editing data in the Product Volume Analysis dashboard.

  1. From the Home page, click Dashboards. Open the Product Volume Analysis dashboard.
    2. Product Volume Analysis Dashboard
  2. For Envoy Standard Notebook, edit product volume for January. For Ultra Notebook 13 in, edit product volume for March. Save the form (not the dashboard).
    3. Save the Product Volume form.
  3. Click OK to close the information message. The changes are reflected in the Product Revenue form.
    4. Changes are reflected in the Product Revenue form.
  4. Close the dashboard. Navigate to Jobs (under Application) and select the Groovy Calculate Product Revenue job.
    5. Jobs displayed in Job Console.
  5. The log message for the completed job identifies the user who executed the rule and the generated calculation script. After viewing the job details, close the window and return to the Home page.
    6. Log message for job.

Executing a calculation job with a Groovy script

In this section, you create two Groovy scripts: one to calculate product revenue for edited members in a form, and a second to execute a calculation job that runs the first calculation. The second script is attached to the form and executed on saving data, and the first script uses RTPs to capture edited members passed to it from the second script. Offloading the calculation into a standalone rule that is called by another rule allows for code to be maintained, tested, and re-used in an efficient manner.

Creating a Groovy script for focused calculations using run-time prompts

  1. Select the Calculation Manager window and create a rule named Groovy Calculate Product Revenue RTPs in the OEP_FS plan type.
    2. New Object
  2. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    3. Rule Editor Options
  3. Copy this script and paste it into the editor:
    4. /*RTPS: {editedMembers}*/ 
String calcScript = """Fix($rtps.editedMembers)
"OFS_Calculated"
(
   "OFS_Product Revenue" = "OFS_Volume" * "OFS_Avg Selling Price";
) 
ENDFIX"""
println("The following calc script was executed by $operation.user.fullName: \n $calcScript") 
return calcScript

    Define a run time prompt to accept the edited members on the first line.

    Generate a calculation script to calculate the product revenue for edited cells by fixing on the edited members.

    Print a message indicating the user who executed the rule and the generated calculation script.

    Return the calculation script, which will then be executed.

  4. On the toolbar, click Save (Save) to save the script.
  5. Click Validate and Deploy (Validate and Deploy). For the Edited Members RTP, enter a value of Accessories for validation purposes, then click OK.

    6. Note:

    During this step, the rule is not executed; however, you must enter valid members for the validation process to succeed.
  6. Click OK when prompted.

Creating a Groovy rule to execute a calculation job

Now you create the second Groovy rule, which executes a calculation job that passes variables to and runs the Groovy Calculate Product Revenue RTPs rule.

  1. In Calculation Manager, create a rule named Groovy Calculate Product Revenue by Calling Execute Job in the OEP_FS plan type.
    2. New Object
  2. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    3. Rule Editor Options
  3. Copy this script and paste it into the editor: 
    4. // Capture the edited periods and products
Set<String> editedMembers = [] 
operation.grid.dataCellIterator({DataCell cell -> cell.edited}, MemberNameType.ESSBASE_NAME).each { DataCell cell ->
        editedMembers << cell.periodName << cell.getMemberName("Product") 
    }

if(editedMembers.size() == 0){ 
    println("No edited cells found!")
    return;
}

// Execute the job that calculates the product revenues
JobFactory jf = operation.application.jobFactory 

List<DataGrid.HeaderCell> povMemberCells = operation.grid.pov.findAll{ DataGrid.HeaderCell it -> it.dimName != 
	"Plan Element" } 

Job job = executeJob(jf.job("Groovy Calculate Product Revenue RTPs", "Rules", 
	["editedMembers" : cscParams(povMemberCells, editedMembers)]))

    Define a Set to capture unique edited members.

    Capture edited members.

    Check if there were any members edited. If none were edited, no further processing is required so return from the script.

    Get the job factory.

    Get a list of POV member header cells for dimensions other than Plan Element because the script has a member block on a Plan Element dimension member.

    Execute the Groovy Calculate Product Revenue RTPs rule by passing the POV and the edited members as values for the editedMembers run time prompt.

  4. On the toolbar, click Save (Save) to save the script, then click ValidateAndDeploy (Validate and Deploy) to validate and deploy the script. Click OK when prompted. Leave the Calculation Manager window open.

Adding the rule to a Planning form

In this section, you add your Groovy script rule to the Product Volume form, to be run after saving.

  1. Select the Planning window and from the Home page, navigate to Forms (under Create and Manage). Select the Product Volume form.
    2. Form Manager
  2. Edit the form. Remove the Groovy Calculate Product Revenue rule from selected rules. Add the Groovy Calculate Product Revenue by Calling Execute Job rule and set it to Run After Save. Save the form and close the Form Manager window.
    3. Add Execute Job rule to the Product Volume form

Testing the Groovy rule to call a calculation job

In this section, you test your Groovy rule by editing data in the Product Volume Analysis dashboard.

  1. From the Home page, click Dashboards. Open the Product Volume Analysis dashboard.
    2. Product Volume Analysis Dashboard
  2. For Envoy Standard Notebook, edit product volume for January. For Ultra Notebook 13 in, edit product volume for March. Save the form (not the dashboard).
    3. Save the Product Volume form.
  3. Click OK to close the information message. The changes are reflected in the Product Revenue form.
    4. Changes are reflected in the Product Revenue form.
  4. Close the dashboard. Navigate to Jobs (under Application) and select the Groovy Calculate Product Revenue by Calling Execute Job job.
    5. Jobs displayed in Job Console.
  5. In the job details, the Groovy Calculate Product Revenue RTPs rule is listed as a child job. Click the child job.
    6. Child jobs displayed in job details.
  6. In the child job details, you can see the list of members passed to the script for the Edited Members prompt. The log message for the completed job identifies the user who executed the rule and the generated calculation script. After viewing the job details, close the window and return to the home page.
    7. Log message for job.

Generating an inline calculation script for edited data with further Groovy processing

The EPM object model provides a way to execute a calculation script inline and then return to the Groovy script for further evaluation. In this section, you explore this calculation script and Groovy integration by implementing a Groovy script to calculate product revenue for edited products and count how many cells were calculated.

Creating the Groovy script

  1. Select the Calculation Manager window and create a rule named Groovy Execute Calc Script in the OEP_FS plan type.
    2. New Object
  2. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    3. Rule Editor Options
  3. Copy this script and paste it into the editor:
    4. // Capture the edited periods and products
Set<String> editedMembers = [] 
operation.grid.dataCellIterator({DataCell cell -> cell.edited}, MemberNameType.ESSBASE_NAME).each {
DataCell cell ->
        editedMembers << cell.periodName << cell.getMemberName("Product") 
    }

if(editedMembers.size() == 0){ 
    println("No edited cells found!")
    return;
}

// Generate the calc script to calculate revenue for the products whose volumes were edited and to count and return the number of cells calculated
String calcScript = """
VAR numCellsCalculated = 0;
Fix(${cscParams(operation.grid.pov, editedMembers)}) 
"OFS_Calculated" (
    "OFS_Product Revenue" = "OFS_Volume" * "OFS_Avg Selling Price";
    numCellsCalculated = numCellsCalculated + 1;
)
EndFix;
Fix(${cscParams(operation.grid.pov, editedMembers)})
"OFS_Calculated" (
    @RETURN(@HspNumToString(numCellsCalculated), ERROR);
)
EndFix;
"""
Cube cube = operation.application.getCube("OEP_FS") 
try {
    cube.executeCalcScript(calcScript) 
} catch(CalcScriptException e) {
    println("The following calc script was executed by $operation.user.fullName: 
    \n $calcScript") 
    println("Total number of cells calculated: $e.atReturnMessage")  
}

    Define a Set to capture unique edited members.

    Capture edited members.

    Check if any members were edited. If none were edited, no further processing is required so return from the script.

    Generate a calculation script to calculate the product revenue for edited cells by fixing on the POV members and edited members. The calculation script also returns a count of number of cells calculated via the @RETURN command.

    Get the OEP_FS cube.

    Execute the calculation script against the cube.

    Print a message indicating the user who executed the rule and the generated calculation script.

    Print the total number of cells calculated returned by the calculation script.

  4. On the toolbar, click Save (Save) to save the script, then click ValidateAndDeploy (Validate and Deploy) to validate and deploy the script. Click OK when prompted. Close Calculation Manager.

Adding the rule to a Planning form

In this section, you add your Groovy script rule to the ProductVolume form, to be run after saving.

  1. From the Planning Home page, navigate to Forms (under Create and Manage). Select the Product Volume form.
    2. Form Manager
  2. Edit the form. Remove the Groovy Calculate Product Revenue by Calling Execute Job rule from Selected Business Rules. Add the Groovy Execute Calc Script rule and set it to Run After Save. Save the form and close the Form Manager window.
    3. Run After Save

Testing the Groovy Rule to execute a calculation script

In this section, you test your Groovy rule by editing data in the Product Volume Analysis dashboard.

  1. From the Home page, click Dashboards. Open the Volume Analysis dashboard.
    2. Product Volume Analysis Dashboard
  2. For Envoy Standard Notebook, edit product volume for January. For Ultra Notebook 13 in, edit product volume for March. Save the form (not the dashboard).
    3. Save the Product Volume form.
  3. Click OK to close the information message. The changes are reflected in the Product Revenue form.
    4. Changes are reflected in the Product Revenue form.
  4. Close the dashboard. Navigate to Jobs and select the Groovy Execute Calc Script job.
    5. Jobs displayed in Job Console.
  5. The log message for the completed job identifies the user who executed the rule and the generated calculation script, as well as the count of calculated cells.
    6. Log message for job.