Empowering business users with the flexibility to manage dynamic product hierarchies is a common challenge in Essbase environments. Imagine needing to quickly add multiple members under a parent category, without burdening users with complex technical procedures. This blog post dives into a practical solution that leverages groovy scripting and Smart View to enable users to create multiple dynamic children with a single click, using a simple comma-separated input. Say goodbye to manual, one-by-one member creation and hello to streamlined, user-friendly hierarchy management.

In one of our projects, we have a product dimension as a well-structured hierarchy. However, the organization wants to create an independent hierarchy for “Other Products” under P_600, which may or may not make it to long term planning.

Currently, the users will create their own products and after the feasibility study these will be moved to original hierarchy next year. So I need to provide a mechanism for users to create multiple products under P_600 on the fly. Most consultants know this can be achieved by enabling a parent for dynamic children creation.

But,

– Can I provide a feature to create multiple dynamic children to product at once?
– Can a user create more than 10 members in just a click?
– Can a user do this without technical knowledge of Essbase?
– Can a user do this just from Smart View without any extra access?

The answer to all these questions is YES!

To achieve this, I have to create a simple mechanism to extend dynamic children functionality using a script. The input to a script should be a comma separated list of names, so we do not rely much on user’s technical knowledge.

Step I: Enable the creation of dynamic children

First obvious step will be to identify the parent. We will edit the parent member and enable it for Dynamic Children. This will in a way restrict other parent members, if user mistakenly selects wrong parent for member creation.

Step II: Create a groovy script

Open calc manager and create a new rule. Select Edit Script and Script Type as Groovy Script. Groovy has the capabilities to call available cloud features. It helps in handling input as a classic language and then links it to Oracle platform features.

Step III: Define basic objects

There are few basic objects we should always use. One is messageBundle, to collect all warnings and errors at one text collection/list and the another is messageBundleLoader, which can help you to link user language with your custom text messages. In this demo, I am going to use English as language.

Step IV: Create variables/RTPs

Create variables/RTPs is a simple process. You can create new variables while writing a business rule. For groovy, RTPs are declared at the top of script.

Names: A text field, where user will input list of comma separated member names
ParentName: A parent member for which Dynamic Children creation is enabled.

Step V: Process user input and iterate

Here in the script, I am processing the input received from user. I will split it into list of names which was a normal text previously. In groovy the list/collection is like array which you can traverse and loop through. I can write member validation code in this loop. I can write a regular expression to check whether the name follows product naming policy or not.

Step VI: Create each member

As I have the parent enabled for this functionality, I will be passing new name to addDynamicChild method. This will create new member in logical model and return a reference to that new object for me to use it further.

Conclusion:

Oracle EPM has come a long way in customisation and just-in-time features. Groovy is a tool to achieve user oriented customisations. Using a groovy rule I can add members on the fly. I can give user a freedom to create their own members with little security and no database refresh.

Still, I will recommend to read through the concept of physical and logical Essbase model. The physical Essbase refresh may or may not be required based on the number of users and frequency of creating members.

Stractive offers a free of cost, no obligation migration readiness assessment to do TCO & ROI analysis, gauge health of the current setup and recommend future roadmap. For more information reach out to sales@stractive.com.