Updating dimension member properties is a common practice in an EPM Planning/Financial Close system. This includes updating alias, member formulas, aggregation operator, etc. The traditional way is to load an updated dimension file to the system.
However, bulk uploading dimension member names is always a tricky task. Especially in the on-premises Hyperion Planning world or the legacy PBCS world.
This is due to the fact that if you want to update a member name from 4000 to 40000, loading a dimension file will add the new member 40000 as a brand-new member to the system, instead of renaming the old member. Also, if you try to clear the current members and do a clean dimension load, then all the existing member data will get lost. You end up updating the member names one by one from the dimension page.
Manually updating hundreds of dimension members could cause user errors. Even though updating a lot of dimension members is not a very common case, but if your organization happens to decide to re-design the full chart of account, having the capability to bulk upload members is crucial. This is actually what I experienced very recently.
As we always said, with the Groovy capabilities, we can do a lot of things that we cannot achieve in the old Hyperion world. There is a very SMART way using Groovy rule to bulk update dimension member names WITHOUT data loss.
First of all, check the following reference page for the method we are going to use, “RENAME”.
https://docs.oracle.com/en/cloud/saas/enterprise-performance-management-common/groov/oracle/epm/api/model/Member.html#rename-java.lang.Object-
The rename section has the detailed explanation.
With the technical background, let’s look at a real-life example.
For example, all the account names need to be re-designed as following.
- from A5000 to A50000
- from A6000 to A60000
- from A7000 to A70000
Let’s create a sample form to check data before making any changes.
To use Groovy rule bulk updating the member names, we will need 2 things.
1.csv load file
A csv file that includes the old member names and new member names. The file can be simple enough only having 2 columns.
This file needs to be uploaded to the inbox/outbox folder.
2.Groovy script
The 2nd thing we need, of course, is the Groovy business rule. In this rule, we will need to reference the csv load file as well as the dimension name, in this case, it will be Account.
The core method of this rule is the rename section. Here is a sample rule.
Next, we just need to run this rule.
The rule has been customized in a way to display what members have been updated to what. I always like the Groovy log capability.
Go back to the application dimension. The new member names have been updated in here.
We can refresh the database. The data stays in the system.
Bonus point. If we run the Groovy business rule again, the log will say, these old members do not exist in the application. This is simply because we have updated these member names already.
Hope you enjoy this post. See you next time.