Oracle EPBCS/FCCS: Guide to dynamically add members to a dimension using Groovy Scripts

#Oracle #EPM #EPBCS #FCCS #Groovy #addMember #RegularExpression #Template #RTP #RefreshDatabase

Introduction

Groovy scripts, a feature exclusively available in the Enterprise version of PBCS (EPBCS) and FCCS, bring an elevated level of customization to your cloud planning solutions. These scripts grant refined control over data entry in forms, enabling the execution of actions tailored to the updates within the form. Even complex tasks, such as dynamically adding members to a dimension or moving data between Aggregate Storage (ASO) and Block Storage (BSO) cubes, can be handled.

In this guide, we will walk you through the process of building a Groovy Script template that allows you to dynamically add members to a dimension. Once created, this template can be invoked directly from a data form or seamlessly executed via the business rules interface.

1/. Setup the dimension to enable the dynamic addition of members using Groovy Script

In order to dynamically add members via Groovy, the first step is to establish the parents under which new members will be situated. For each parent, activate the “Enable for Dynamic Children” option (1), and specify the maximum quantity of children that can be manually added (2).

2/. Create the Groovy Template and setup the prompts

Create a new Template.

In the dropdown list adjacent to the “Edit” menu, select “Edit Script”. Subsequently, in the "Script Type" dropdown list, choose “Groovy Script”.

Create the 3 below prompts (1):

CostCenterCode (Type: String)

CostCenterDESC (Type: String)

CostCenterParent (Type: Member, Dimension: Cost Center, Limits: @Children(“All Cost Center”))

Create a new step (2) and add all the created prompts:

3/. Build the Groovy Script

You can copy the Groovy script provided below and paste it directly into the template. This script will dynamically add a member to the “Cost Center” dimension, based on the prompts created in the previous step. Before adding the member, we conduct several checks on both the member code and description.

IMPORTANT: There's no need to refresh the database after dynamically adding a member. The member is directly incorporated into the cubes.

?Groovy script to copy:

//****************** GROOVY SCRIPT: ADD MEMBERS ******************

//Author: David MENDELE

//This message bundle stores localized error messages for US language. It’s a good solution for managing multilingual applications.

def mbUs = messageBundle([

??????????????"validation.CostCenterCode":"Invalid Cost Center code. It must start with two letters, followed directly by three numbers: {0} ",

???"validation.CostCenterDESC":"Invalid Cost Center description. It should only contain alphanumeric characters, underscores (_), hyphens (-), parentheses (()), or single quotes ('), and must be 150 characters or less in length: {0} ",

??????????????"validation.ExistingCostCenter":"The Cost Center {0} already exists."])

//This loader facilitates the selection of the appropriate bundle, aligning with the user's regional configuration.

def mbl = messageBundleLoader(["en" : mbUs])

?

// This function uses a regular expression to verify that the Cost Center Code inputted by the user begins with two letters, immediately followed by three numbers. If the code doesn’t match this regular expression, an error is triggered with the “Validation.CostCenterCode” message defined in the bundle.

validateRtp(rtps.CostCenterCode, /.[a-zA-Z]{2}[0-9]{3}./, mbl, "validation.CostCenterCode", rtps.CostCenterCode);

//Raise an error if the member already exists.

Dimension dimCC = operation.application.getDimension("Cost Center")

Cube cubeFS = operation.application.getCube("OEP_FS")

if(dimCC.hasMember(rtps.CostCenterCode,cubeFS)){

??????????????throwVetoException(mbl,"validation.ExistingCostCenter",rtps.CostCenterCode)

}

//Raise an error if the description contains special characters or contains more than 150 characters.

validateRtp(rtps.CostCenterDESC, /.[a-zA-Z0-9 _\(\)\'-]{1,150}./, mbl, "validation.CostCenterDESC", rtps.CostCenterDESC);

?

// Dynamically add the member under the parent that the user selected.

Member parentCC = rtps.CostCenterParent.member

Member mbCC = parentCC.addDynamicChild(rtps.CostCenterCode)

//Note: To create a shared member, you should use the method newChildAsMap() instead.

?

// Set the alias and data storage properties for the new member.

def mbProps = mbCC.toMap()

mbProps["Alias: Default"] = rtps.CostCenterCode.toString().replaceAll('"','') + " - " + rtps.CostCenterDESC.toString().replaceAll('"','')

mbProps["Data Storage"] = "Never Share"

dimCC.saveMember(mbProps)

?

//****************** END ******************

4/. Run the template and check the result

When the user runs the template, he enters the cost center code and description, and finally, he selects the parent.

Result:

IMPORTANT: There's no need to refresh the database after dynamically adding a member. The member is directly incorporated into the cubes.