One of the major benefits of Groovy business rules in Oracle Enterprise Planning and Budgeting Cloud Service (EPBCS) is the ability to manipulate metadata, something that was not possible with traditional Oracle EPM (Hyperion) business rules. This blog post aims to introduce the concept of adding new members to dimensions using a Groovy script.
This process is like adding members in Dimension Editor. However, with Groovy, you can now create a business rule that lets you add new members when you right click on a form. This is an admin-only function, and the user needs to have a “Service Administrator” role to do this, which is also handy in housekeeping of manually-maintained dimensions. A good use case for this is the “Employee” or “Position” dimension, both of which require “to be hired” members to be added on a regular basis.
In the example here, we are going to use the “Department” dimension to show how the functionality works. This Groovy business rule makes use of runtime prompts for the parent member (varParent is of type Member); the new child member to be added (varMember is of type String); and alias value for the child member (varAlias is of type String). There can be additional runtime prompts based on what needs to be achieved using this rule.
You can define variables of type “Dimension” and “Member” to hold the dimension and parent member names. I have used “Department” as the dimension in this example, but it can be any dimension that makes sense. Assume that the form where this business rule is used will have the specified dimension in rows.
Certain validations can be added to the script. Below are examples of validations to ensure that the selected parent member exists and the new member to be created does not already exist.
To create a new member, use the child member value from runtime prompt in the “newChildAsMap()” method. This will return a map with member properties which can then be used in the “saveMember()” method to create the new member.
The above code completes new member creation, however, there are certain shortcomings which need to be fixed.
The new member gets created with the data storage property of its parent, which might not always be what is desired for the new child member. Since this is a level 0 member that we are creating, we want the new member to have data storage set to “Store.” The below piece of code helps in achieving that along with adding the “Alias” value to the member.
First, define a variable of type “Member” to capture the new member to be added. Use the “getMember()” method to capture member name from the runtime prompt. The data storage property must be setup for each of the cubes, hence we use “getCubes()” method to get a list of cubes in the application. Use the “toMap()” method to get a map containing the properties of the member. Then, update the required member properties as shown.
We are only updating the “Alias” and “Data Storage” properties here, but can be used to update really any member property that is available. Runtime prompts can be added to the script to take input for updating additional properties.
To use this rule with a form, attach it to a menu and add this menu to the form. This will enable you to use the add member business rule as a right click option on the form. Note that you are required to refresh the database after adding new members. Without database refresh, you will not be able to save data against the new members as these will not be available in Essbase.
If you have any questions about how to add new members on EPBCS forms using Groovy, please contact the Performance Architects team at firstname.lastname@example.org and we’ll be happy to address your questions.