Automate version creation and renaming in Oracle EPM with Groovy scripting!

 

Groovy scripting, which enables automation, dynamic validation, and metadata updates.

In this blog, we’ll walk through two Groovy business rules that manage version members in the Version dimension:

1. Creating a new version dynamically
2. Renaming an existing version member

We’ll explain the logic behind each rule, 

Rule 1: Creating a New Version Dynamically

Use Case

Organizations often need to create archived versions of Actual data for historical comparisons. This rule ensures that a new version member, such as Archive_Actual, is created under Archived Actual Versions while preventing duplicates.

Step-by-Step Breakdown

 

1. Define Error Messages

User-friendly error messages are defined using messageBundle to improve script maintainability.

 

def mbUs = messageBundle([

    "validation.memberexists": "🚨 The member '{0}' already exists and cannot be created."

])

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

 

2. Retrieve the Version Dimension

We access the Version dimension using the operation.application.getDimension() method.

 

Dimension versionDim = operation.application.getDimension("Version")

 

3. Get the Parent Version Member

We identify the parent under which the new version will be added.

 

Member parentVersion = versionDim.getMember("Archived Actual Versions")

 

4. Validate if the Member Already Exists

Before creating a new member, we check if it already exists under the parent. If it does, we veto the operation with an error message.

 

String newMemberName = "Archive_Actual"

 

if (parentVersion.hasChild(newMemberName)) {

    throwVetoException(mbl, "validation.memberexists", newMemberName)

}

 

5. Add the New Member as a Dynamic Child

If the validation passes, we create the new member dynamically.

 

Member newVersion = parentVersion.addDynamicChild(newMemberName)

println "✅ Successfully added new version member: ${newVersion.name}"

 

6. Print Execution Summary

A final message is logged with the executing user’s name.

 

println("Rule was executed by ${operation.user.fullName}")

 

Validation implemented,

• Use hasChild() to prevent duplicates.
• Throw a meaningful error instead of allowing silent failures.
• Keep logs informative for debugging and auditing purposes.

 

Rule 2: Renaming an Existing Version

Use Case

Over time, organizations may need to rename archived forecast versions for better tracking. This rule renames Archive_Actual to a new name provided via a Runtime Prompt (RTP).

Step-by-Step Breakdown

 

1. Start Execution and Retrieve Runtime Prompt

The script begins execution, and we retrieve the new member name from the Runtime Prompt.

 

println "🚀 Starting Rename Member Rule Execution..."

String oldMemberName = " Archive_Actual "

String newMemberName = rtps.NewMemberName

 

2. Retrieve the Version Dimension

We fetch the Version dimension from all available cubes.

 

println "🔍 Retrieving 'Version' dimension..."

Cube[] cubes = operation.application.getCubes()

Dimension versionDim = operation.application.getDimension("Version", cubes)

 

if (versionDim == null) {

    throwVetoException("🚨 The 'Version' dimension could not be found.")

}

 

3. Check if the Old Member Exists

Before renaming, we check if the old member exists.

 

println "🔍 Checking if '${oldMemberName}' exists in the Version dimension..."

Member oldMember = versionDim.getMember(oldMemberName, cubes)

 

if (oldMember == null) {

    throwVetoException("🚨 The member '${oldMemberName}' does not exist and cannot be renamed.")

}

 

4. Rename the Member

If the member exists, we rename it.

 

if (versionDim.hasMember(oldMemberName, cubes)) {

    try {

        oldMember.rename(newMemberName)

        println "✅ Successfully renamed $oldMemberName to $newMemberName"

    } catch (Exception e) {

        throwVetoException("Error occurred while renaming $oldMemberName. $e.message")

    }

} else {

    throwVetoException("$oldMemberName is not an existing member")

}

 

5. Print Execution Summary

println "✅ Rule executed successfully by ${operation.user.fullName}"

Best Practices

• Use hasMember() to check existence before renaming.
• Wrap renaming in a try-catch block to handle unexpected failures.
• Use throwVetoException() for validation failures instead of letting errors occur unexpectedly.

You can have one single rule to get the rtps and create that member too, the requirement i had needed it to be 2 steps rather than a single step. 

These two Groovy rules demonstrate how metadata management in Oracle Cloud EPM can be automated using dynamic member creation and member renaming. By following best practices such as validations, error handling, and informative logging, we can ensure robust and reliable business rules.

When to Use These Rules

✅ When needing to automate version archiving
✅ When requiring dynamic member creation
✅ When renaming members to maintain clarity

You can invoke these rules via the pipeline and create a simplified approach for the end users.