Oracle Documentation

Integrating dimension metadata from Enterprise Data Management Cloud with Planning using Groovy rules

Before you begin

This 35-minute hands-on tutorial shows you how to use a Groovy business rule in Planning to export dimension metadata from Enterprise Data Management Cloud, and then import the metadata into Planning. The sections build on each other and should be completed sequentially.

Background

Enterprise Data Management Cloud provides solutions for managing changes in your business by ensuring consistency across enterprise data, even across disparate applications.

Enterprise Data Management Cloud offers three ways to manage the integration of your dimension metadata with Planning:

  • Manual: You can manually export dimension metadata from Enterprise Data Management Cloud to a flat file that is stored either locally or exported directly to a Planning instance. Then in Planning, you can run an import job to import the metadata.
  • EPM Automate: You can download the EPM Automate client and issue commands to export dimension metadata from Enterprise Data Management Cloud, copy it to Planning, and then import it to Planning using a saved import job. This can be done with interactive commands or in a batch script.
  • Groovy rules: You can create a Groovy business rule in Planning that calls the Enterprise Data Management REST API to export the dimension metadata, then imports the metadata to Planning using a saved import job.

See the Next tutorials section for links to tutorials on manually exporting and importing, and using EPM Automate.

Groovy rules can be used anywhere a normal business rule can be used. The way you implement this type of integration rule in a production environment depends on how often the integration needs to be run and whether you want to allow planners to run it independently or reserve it for a regular application maintenance process that includes importing metadata, then refreshing and recalculating the database. You can implement one or both of the methods covered in this tutorial:

  • Add a context menu item for the rule in forms where users can run the integration and immediately see new metadata imported
  • Schedule the rule to run at a specific time

This tutorial is in three parts:

Part 1: Setup steps

  1. Set up connections between Enterprise Data Management Cloud and Planning to reference in the Groovy rule.
  2. Create an Import Metadata job in Planning to reference in the Groovy rule.
  3. Set up user variables to use Planning forms.

Part 2: Creating the Groovy rule and context menus

  1. Create a Groovy rule in Planning that uses Enterprise Data Management REST API calls to export dimension metadata from Enterprise Data Management to the Planning inbox, then issues internal REST API calls to run the saved Import Metadata job.
  2. Create an action menu item for the rule and assign it to a Planning form, so users can run the integration from the form.

Part 3: Testing the rule two ways

  1. Update the Product dimension in Enterprise Data Management Cloud.
  2. Run the Groovy rule using the Planning form context menu to see immediate results.
  3. Schedule the rule to run at a specific time.

What do you need?

An EPM 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 two instances of EPM Enterprise Cloud Service. The instances should not have business processes created.
  • Configure one of your instances for Enterprise Data Management Cloud and configure the other instance for Planning.
  • Upload and import this snapshot into your Enterprise Data Management Cloud instance.
  • Upload and import this snapshot into your Planning instance.

If you have already set up these instances for one of the tutorials listed in the Next tutorials section, you can continue to use the same environments. Your results may vary from those shown in the screenshots for this tutorial, depending on whether and in which order you have completed the related tutorials.

For more information on uploading and importing migration snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

Setup: Setting up connections

Setting up the Planning application connection in Enterprise Data Managment Cloud

In this section, in Enterprise Data Management Cloud, you set up and test a connection to your Planning instance so you can export dimension metadata directly to the Planning instance inbox.

  1. On the Enterprise Data Management Cloud home page, click Applications.
    Enterprise Data Management Cloud home page
  2. Click Corporate Planning to open the inspector.
    Enterprise Data Management Cloud applications
  3. Select the Connection tab.
    Enterprise Data Management Cloud Corporate Planning application inspector Connection tab
  4. Select the Production connection, then click Edit.
  5. Modify the connection details for your Planning instance:
    Field Valid Values Example
    Instance Location Enter a connection URL to your Planning instance in the format:

    https://ServiceName-IdentityDomain-ServiceType.DataCenter.oraclecloud.com
    https://planbudget-uscompany-entplan.US11.oraclecloud.com

    where

    • ServiceName is planbudget
    • IdentityDomain is uscompany
    • ServiceType is entplan
    • DataCenter is US11
    Identity Domain Enter the identity domain of your Planning instance.

    Note: This is the same Identity Domain used in the Instance Location.

    uscompany
    Username Enter an authorized user name.

    Note: The user must be a Service Administrator of the Planning instance.

    tom.smith

    Note: You must enter the identity domain in either the Identity Domain or Username fields. For example, enter uscompany in Identity Domain or uscompany.tom.smith in Username.

    Password Enter the user's password. xxxxxxxx
  6. Select Primary Connection to make this connection the default for importing and exporting.
  7. Click Test Connection.
    Enterprise Data Management Cloud connection setup and test

    The message "Connection Successful" is displayed.

    If your connection test is not successful, check your connection information and try again.

  8. Click OK, then click Save.
  9. Click Close to close the inspector.

Setting up the Enterprise Data Management Cloud application connection in Planning

In this section, in Planning, you set up and test a connection to your Enterprise Data Management Cloud instance so you can use Groovy to call Enterprise Data Management Cloud REST API functions from Planning.

  1. Open a new browser tab and log in to your Planning instance. From the home page, click Tools, and then click Connections.
    Planning home page with Tools and Connections highlighted
  2. Click Create.
  3. For the provider, click Other Web Service Provider.
    Select Provider to Create Connection options
  4. Enter the connection details:
    • Connection Name: EDM
    • Description: EDM connection for Groovy rules
    • URL: Enter the login URL for your Enterprise Data Management Cloud instance, in the format ServiceName-IdentityDomain.ServiceType.DataCenter.oraclecloud.com. For example:
    • https://edmcs-uscompany.epm.US1.oraclecloud.com
    • User: Enter IdentityDomain.UserName, where IdentityDomain is your Enterprise Data Management Cloud identity domain and UserName is your Enterprise Data Management Cloud service administrator user (for example, uscompany.tom.smith).
    • Password: Enter the password for your Enterprise Data Management Cloud service administrator user.
    EDM connection details in Planning
  5. Click Save and Close to save the connection.

Setting up a Planning connection for internal REST API calls

In this section, you set up a connection to the current Planning instance that you can use to issue internal REST API calls for import jobs in Groovy.

  1. Click Create.
  2. For the provider, click Other Web Service Provider.
    Select Provider to Create Connection options
  3. Enter the connection details:
    • Connection Name: Planning Rule
    • Description: Connection to run jobs via Groovy REST API on this planning instance
    • URL: Enter the REST API URL for your Planning instance, in the format https://ServiceName-IdentityDomain.ServiceType.DataCenter.oraclecloud.com/HyperionPlanning/rest/API_Version/ResourcePath. For example:
    • https://planbudget-uscompany.entplan.US11.oraclecloud.com/HyperionPlanning/rest/v3/applications/EPBCS/jobs
    • User: Enter IdentityDomain.UserName, where IdentityDomain is your Planning identity domain and UserName is your Planning service administrator user (for example, uscompany.tom.smith).
    • Password: Enter the password for your Planning service administrator user.
    • Planning REST API connection details in Planning
  4. Click Show Advanced Options.
  5. Enter the header options:
    • For Type, select Header.
    • For Name, enter Content-Type.
    • For Value, enter application/json.
    • Planning REST API connection details in Planning, advanced options
  6. Click Save and Close to save the connection.
  7. Return to the Planning home page.

Setup: Creating an import job in Planning

Using Groovy rules, you can import files stored in the Planning Inbox by referencing a saved import job. In this section, you create an import job for a file called edmc-groovy-product.csv, which you will export from Enterprise Data Management Cloud in your Groovy rule by using the REST API.

  1. From the Planning home page, click Application, and then click Overview.
    Planning home page with Application and Overview highlighted
  2. Select the Dimensions tab.
    Planning Application Dimensions tab
  3. Click Import to display the Import Metadata dialog box.
    Planning Application Dimensions tab with Import highlighted
  4. Click Create.
    Planning Import Metadata dialog box with Create highlighted
  5. For Location, select Inbox.
    Planning Import Metadata dialog box with Inbox location highlighted
  6. In the Product dimension row, enter edmc-groovy-product.csv, and accept the default (Comma delimited) for the File Type.
    Planning Import Metadata dialog box with the Product dimension row highlighted
  7. Click Save as Job.
    Planning Import Metadata dialog box with Save as Job highlighted
  8. Configure the job:
    • Name: Import_EDMC_Groovy_Product
    • Error File: import_edmc_groovy_product_error
    • This file provides information about the metadata records that were not imported for each dimension. If you specify an error zip file, a separate error file is created for each dimension, then the error files are zipped together, and the zip file is stored in the Outbox where you can download the file using Inbox/Outbox Explorer.

    • Accept the default setting (unselected) for Keep the Refresh Database if Import Metadata is successful.
    • For new members to be included in calculations, you must refresh the database after modifying dimensions and members. In a production environment, Oracle recommends that you notify all users, asking them to save their work and close the application, before you refresh the database. In this example, while planners can import new metadata, refreshing the database should be reserved as a separate administrative function. For more information on database refreshes, please see Before Refreshing the Database in Administering Planning.

    Planning Save as Job dialog box with values entered
  9. Click Save, then click OK to close the information message.
  10. Click Close to close the first Import Metadata dialog box.
  11. In the second Import Metadata dialog box, click Refresh to view the list of saved import metadata jobs.
    Planning import metadata jobs list

    The job you created is displayed in the list.

  12. Click Close, then return to the Planning home page.

Setup: Setting user variables in Planning

User variables are added when the business process is created. User variables act as filters in forms, enabling planners to focus only on certain members. You must select values for your user variables before opening Planning forms and dashboards.

  1. On the Planning home page, click Tools, then User Preferences.
  2. On the left, click User Variables.
  3. Enter the following members for the user variables:
    • Currency: USD
    • Entity: Sales US
    • Reporting Currency: USD
    • Scenario: OEP_Forecast
    • Version: OEP_Working
    • Years: FY20
    • Expense Account: OFS_Total Expenses
    • Expense Drivers: OFS_Expense Drivers for Forms

      You can use Member Selector to select members.

      User Variables
  4. Click Save. When prompted, click OK.
  5. Return to the Planning home page.

Creating the Groovy rule to integrate Product metadata from Enterprise Data Management Cloud

In this section, you implement a Groovy rule to export Product metadata from Enterprise Data Management Cloud and import it to Planning.

Creating the Groovy rule

  1. From the Planning home page, click Navigator (Navigator), then click Rules (located under Create and Manage).
    Navigator with Rules highlighted

    This opens Calculation Manager in a popup window.

  2. Click Actions, then click New Object.
  3. Define the rule:
    • Application Type: EPM Cloud
    • Application: EPBCS
    • Cube: OEP_FS
    • Object Type: Rule
    • Name: groovy-import-edmc-products
    New object rule definition in Calculation Manager
  4. Click OK to create the rule, then click OK to close the information message.
  5. In the Rule Editor, change the Designer option to Edit Script and set the Script Type to Groovy Script.
    Rule Editor Options
  6. Copy this script and paste it into the editor:
    Groovy script for importing products from EDMC
  7. 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, and close Calculation Manager.

Explaining the script logic

In this section, the script logic is described one section at a time.

This rule is hard-coded to use two named connections (EDM and Planning Rule) in Planning of the type Other Web Service Provider. It is also hard-coded for artifacts specific to the Corporate Planning application in the provided Enterprise Data Management Cloud sample environment. It exports the Product dimension using the connection Production. The rule is hard-coded to export a file named edmc-groovy-product.csv to the Planning server. Finally, it is hard-coded to use the saved import job Import_EDMC_Groovy_Product, which you created earlier in the tutorial.

  1. Initialize input variables for the script.
    // EDM instance 
    String EDMapplicationName = 'Corporate Planning' 		
    String EDMdimensionName   = 'Product'				    
    String EDMconnectionName  = 'Production'			    
    String EDMexportFileName  = 'edmc-groovy-product.csv'	
    
    // Planning instance 
    String PLNconnectionNameForEDM = 'EDM'				    
    String PLNconnectionNameForPLN = 'Planning Rule'		
    String PLNimportDimJobName = 'Import_EDMC_Groovy_Product'            

    Initialize input variables related to the Enterprise Data Management Cloud instance, including the registered Planning application name, the dimension to export, the connection name for the Planning application, and the export file name.

    Initialize input variables related to the Planning instance, including the name of the Enterprise Data Management cloud connection, the name of the internal Planning connection, and the name of the Import Metadata job.

  2. Retrieve the internal application and dimension IDs for the Enterprise Data Management Cloud application and dimension.
  3. HttpResponse<String> jsonResponse = operation.application.getConnection
    	(PLNconnectionNameForEDM).get("/epm/rest/v1/applications").asString() 
    
    String appJSON = JsonPath.parse(jsonResponse.body).read('$.items[?(
       @.name == "'+EDMapplicationName+'")]') 
    String[] EDMapplicationID = JsonPath.parse(appJSON).read('$.[?(
       @.name == "'+EDMapplicationName+'")].id') 
    String[] EDMdimensionID = JsonPath.parse(appJSON).read('$..dimensions[?(
       @.name == "'+EDMdimensionName+'")].id') 
    

    Get the list of applications and their information in Enterprise Data Management Cloud (this API).

    In the response, extract the application metadata for the Corporate Planning application.

    In the application metadata, find the application ID.

    In the application metadata, find the dimension ID for the Product dimension.

  4. Retrieve the connection ID for the Corporate Planning application in Enterprise Data Management Cloud.
  5. jsonResponse = operation.application.getConnection("EDM").get("/epm/rest/v1/applications/"
    	+ EDMapplicationID[0]+ "/connections").asString() 
    
    String[] EDMconnectionID = JsonPath.parse(jsonResponse.body).read('$.items[?(
       @.name == "'+EDMconnectionName+'")].id') 
    

    Get the list of connections for the Corporate Planning application in Enterprise Data Management Cloud (this API).

    In the response, find the connection ID for the Production connection.

  6. Export the Product dimension from Enterprise Data Management Cloud to the Planning inbox.
  7. jsonResponse = operation.application.getConnection(PLNconnectionNameForEDM).post(
    	"/epm/rest/v1/dimensions/" + EDMdimensionID[0]+"/export/connection") 
     .header("Content-Type", "application/json").body(json(["connectionId":EDMconnectionID[0],
     	"fileName":EDMexportFileName])).asString() 
    
    String[] jobURL = JsonPath.parse(jsonResponse.body).read('$.links[?(
       @.rel == "results")].href') 
    String EDMexportJobID = jobURL[0].substring(jobURL[0].lastIndexOf("/")+1) 
    

    Export the Product dimension to the configured connection Production, using the hard-coded export file name edmc-groovy-products.csv (this API)

    In the response, find the job URL.

    Extract the job ID from the job URL.

  8. Check the dimension export job status.
  9. while (getEDMexportJobStatus(PLNconnectionNameForEDM, EDMexportJobID) != "COMPLETED") {
        sleep(1000)
    }  
    
    // Get the EDM Dimension Export Job status
    String getEDMexportJobStatus(String connectionName, String jobId) {
    	HttpResponse<String> pingResponse = operation.application.getConnection(
        	connectionName).get("/epm/rest/v1/jobRuns/" + jobId+ "/result").asString()
        if (pingResponse.status != 200)
    		throwVetoException("Error occured: $pingResponse.statusText")
    	return ((String)(JsonPath.parse(pingResponse.body).read('$.status')))} 
    

    Pause until the job status is COMPLETED.

    Return the results of the job run by submitting a GET request (this API).

  10. Run a job in Planning to import the dimension metadata from the inbox.
  11. jsonResponse = operation.application.getConnection(PLNconnectionNameForPLN).post().body(
    	json(["jobType":"IMPORT_METADATA", "jobName":PLNimportDimJobName, 
    	"parameters": ["refreshCube": "false"]])).asString() 
    
    boolean importMetadataToPlanning = awaitCompletion(jsonResponse, PLNconnectionNameForPLN, 
    	'Refresh Product dimension') 
    
    // Parse the JSON response to get the status of the operation
    def awaitCompletion(HttpResponse<String> jsonResponse, String connectionName, 
    	String operation) {
    	if (!(200..299).contains(jsonResponse.status))
    		throwVetoException("Error occured: $jsonResponse.statusText")
    
    	Map result = (Map) new JsonSlurper().parseText(jsonResponse.body)
        int status = (int)result.status
        while (status < 0) {
    		status = getPlanningJobStatus(connectionName, (String)result.jobId)
    		sleep(1000)
    	}
    	if (status == 0) {
    		println("$operation successful.\n")
    		return true
    	} else {
    		println("$operation failed.\n");
    		return false
    	}
    } 
    
    // Check the Planning Job status
    int getPlanningJobStatus(String connectionName, String jobId) {
    	HttpResponse<String> pingResponse = operation.application.getConnection(
        	connectionName).get("/" + jobId).asString()
        println (pingResponse.body)
    	return ((int)((Map)new JsonSlurper().parseText(pingResponse.body)).status)} 
    

    Execute an import metadata job, Import_EDMC_Groovy_Product, in Planning using the Planning Rules connection by submitting a POST request (this API).

    Execute the awaitCompletion function that is defined in .

    Parse the response from the POST request in . If the response status is between 200 and 299, proceed with checking the job status. Otherwise throw an exception. Wait until the job status returned by the getPlanningJobStatus function defined in is 0 or > 0. If the status equals 0, then the import was successful, otherwise, the operation has failed..

    Get the status of the Planning job (this API).

Creating a context menu item to run the Groovy rule

In this section, you add an action menu with an item that runs the Groovy rule to synchronize Product metadata from Enterprise Data Management Cloud. You then add the custom action menu to the predefined Product Volume form, which has been set up for planners to enter volume data for all products. Adding a custom menu to the form lets planners execute the rule using a simple right-click in the form.

Product Volume

Creating an action menu and adding action items

  1. From the Planning home page, click Navigator (Navigator), then click Action Menus (located under Create and Manage).
  2. Click Create Menu (Create Menu).
  3. For the menu name, enter Sync EDMC Dimensions, then click OK.
    Create Manage Employees Action Menu
  4. Select Sync EDMC Dimensions, and click Edit Menu (Edit Menu).
  5. Click Actions, then select Add Child.
    Add child to Action Menu
  6. Enter or select the following options to define the menu item:
    Option Field Value to Enter or Select
    Menu Item Sync Product
    Label Sync Product
    Type Business Rule
    Required Parameters None
    Cube OEP_FS
    Business Rule groovy-import-edmc-products
    Completed Sync Product Menu Item
  7. Click Save, then click OK to save the menu item. Finally, click Save again to save the action menu.
    Completed Sync EDMC Dimensions Action Menu
  8. Return to the Planning home page.

Adding the action menu to a Planning form

  1. From the Planning home page, click Navigator (Navigator), then click Forms (located under Create and Manage).
  2. Select the Product Volume form, and then click Edit (Edit).
  3. Select the Other Options tab.
  4. In the Context Menus section, under Available Menus, select Sync EDMC Dimensions, then click Move selected items to: Selected Menus (Move selected items to: Selected Menus).
    Sync EDMC Dimensions added to selected menus
  5. Click Finish to save and close the form.
  6. Close the form manager to return to the Planning home page.

Verify the context menu

In this section, you open the Product Volume form and verify that the context menu was added to the form.

  1. Click Data.
    Planning home page with Data highlighted
  2. Click Product Volume to open the form.
    List of Data Entry forms
  3. Right-click in the form and verify that Sync Product is displayed in the context menu.
    Form context menu with Sync Product
  4. Click outside the menu to close it. Leave the form open.

Verifying metadata integration

In this section, you add a new product in Enterprise Data Management Cloud, and then you test your Groovy script by running it two ways: from the Planning form context menu, and by scheduling it to run at a specific time. When run, the script exports Product metadata from Enterprise Data Management Cloud to the Planning inbox, then runs an Import Metadata job in Planning.

Adding a product in Enterprise Data Management Cloud

Currently, the Product dimensions in Enterprise Data Management Cloud and Planning are in sync. In this section, you add a new product to the Corporate Planning application in Enterprise Data Management Cloud.

  1. On the Enterprise Data Management Cloud home page, click Views.
    Enterprise Data Management Cloud home page with Views highlighted
  2. Click Corporate Planning to open the view.
    Enterprise Data Management Cloud list of views
  3. Select the Product viewpoint.
    Enterprise Data Management Cloud Product viewpoint
  4. In the Product hierarchy, expand Total Product, then All Product, and then Notebooks.
    Enterprise Data Management Cloud Product viewpoint with hierarchy expanded
  5. Click New Request to start a request to add a product.
    Enterprise Data Management Cloud Product viewpoint with hierarchy expanded and New Request button highlighted
  6. In the Notebooks row, click the Actions icon and select Add Child.
    Enterprise Data Management Cloud request adding a child
  7. Enter property values for the new product (for any properties not listed in the table, use the default value):
    Property Value
    Name Delphi Notebook 13 in
    Description Delphi Notebook 13 in
    Hierarchy type Stored
    Enterprise Data Management Cloud properties for the new product
  8. Click Submit to commit the changes.
    Enterprise Data Management Cloud submit request

    You receive a message that the request was successfully submitted.

    Submitting the request validates your changes. If there are no validation issues and there is no approval workflow defined, the changes are immediately commited to the dimension.

  9. Click Close to close the view, then return to the home page.

Running the Groovy script from a Planning form

  1. Return to the Planning browser tab, where the Product Volume form is still open.
  2. Right-click in the form to display the context menu, and select Sync Product.
    Form context menu with Sync Product

    The groovy script is launched. It might take a few seconds to run.

    Message that the rule is in Progress
  3. You receive an information message when the rule finishes running. Click OK.
    Message that the rule ran successfully
  4. Notice that the product you added in Enterprise Data Management Cloud is now displayed in the form.
    Product Volume form with added product
  5. Click Close to close the form, then return to the home page.

Scheduling a Groovy rule to run

In addition to, or instead of, letting users running the Groovy import rule manually in a form, you can incorporate loading metadata from Enterprise Data Management Cloud into a scheduled maintenance routine. In this section, you schedule the Groovy rule to run at a specific time.

After importing any dimension metadata as part of a regular maintenance routine, it is best practice to schedule a second job to refresh the database so that new metadata can be included in calculations. This database refresh step is not included in this tutorial.

This section of the tutorial is optional; you have already integrated the new products from Enterprise Data Management Cloud by running the rule in the preceding section.

  1. On the Planning home page, click Application, then click Jobs.
  2. Notice that the two most recent entries are the groovy-import-edmc-products rule and the Import_EDMC_Groovy_Product import job, which was initiated by the rule.
    List of recent jobs
  3. Click Schedule Jobs to open the job scheduler.
  4. Under "What type of job is this?" select Rules.

    Although this job does import metadata, the import instructions are incorporated in the Groovy rule.

  5. Under "When do you want to run this job?" select Run Now. Notice that if you choose to run the job immediately, no recurring options are available.
  6. Select the following options:
    • For the schedule option, select Schedule starting from.
    • For date and time, select today's date and a time five minutes from now.
    • For the time zone, select your current time zone.
  7. For Name, enter Import EDMC Product.
  8. Expand the drop-down list for Recurrence pattern to view the options. You can schedule jobs to run hourly, by minute, once, daily, weekly, monthly, or yearly.
  9. Job Scheduler recurrence options
  10. Select Run Once.
  11. When Run Once is selected, the optional End Date parameter is disabled. You can specify an end date for any other recurrence pattern.

    Job Scheduler general options
  12. Click Next to go to the Job Details page.
  13. Select the Groovy rule to run:
    • Next to the filter icon, click All Cubes.
    • For Cube, select OEP_FS, then click Apply.
    • In the row for groovy-import-edmc-products, click Select (Select).
  14. Job Scheduler job details
  15. Click Next to go to the Review page.
  16. Job Scheduler review options
  17. Review your selections, then click Finish. Notice that the job is now listed under Pending Jobs.
  18. List of pending jobs
  19. Wait until a few minutes after the scheduled run time, then click Refresh.
  20. Notice that now the two most recent entries are the Import EDMC Product rule job and the Import_EDMC_Groovy_Product import job, which was initiated by the rule.
  21. List of recent job activity

Next tutorials

Want to learn more?



Copyright © 1995, 2020, Oracle and/or its affiliates.