EPM GROOVY – Renaming Members in Bulk: A Guide to Using CSV Files with Calculation Manager

We don’t have many choices for renaming many members at once. One common use case is when the source system decides to change the prefix of members, renaming all of the members becomes a challenge in a fully data loaded plan types. Here’s how groovy can help you solve the problem.

RENAME MEMBERS

I am trying to change few members in product dimension using Groovy.

DIMENSION EDITOR : BEFORE CHANGE

CSV FILE TO RENAME MEMBERS

BUSINESS RULE EXECUTION LOG

DIMENSION EDITOR : AFTER CHANGE

GROOVY BUSINESS RULE

I’m using CSV Iterator to get the member names and their respective new members from an uploaded file in Inbox/outbox explorer because we’re processing old and new members from the file. We’ll check to see if each member read from the file already exists in the system before proceeding with the rename. There are a few requirements to follow when it comes to member renaming, particularly the naming restrictions rules that apply to every member creation and renaming. We’re catching exceptions and using throwVetoException to stop the script from renaming any more members.

Groovy
//define parameters - dimension, file to be processed..
String dimensionName = 'Product'
String renameFileName = 'Product Dimension - Bulk Rename.csv'
Cube[] cubes = operation.application.getCubes()
Dimension dimension = operation.application.getDimension(dimensionName, cubes)

//access meta data file from inbox/outbox directory
csvIterator(renameFileName).withCloseable() { rows ->
	//process each row to get old and new member names
	rows.eachWithIndex { row, idx ->                  
		//ignore the header row and process rest
		if(idx > 0) {
			List<String> fileRecord = row.collect{it.toString().trim() } as List<String>
			if( fileRecord.size() == 2) { 
				def (String oldMemberName, String newMemberName) = [ fileRecord[0], fileRecord[1] ]	  
				print "Renaming $oldMemberName to $newMemberName : "                
				//check if the member available in the application
				if(dimension.hasMember(oldMemberName,cubes)){
					Member oldMember = dimension.getMember(oldMemberName,cubes)
					try {
						oldMember.rename(newMemberName)
						println "Successful"
					} catch (Exception e) {           
						println "Failed"
						throwVetoException("Error Occured while renaming $oldMemberName.  $e.message")                       
					}
				//throw error if the file not contains proper member name
				} else {
					println "Failed"
					throwVetoException("$oldMemberName is not an existing member")
				}                	                                                      
			}
		}
	}
}

VALIDATING EXCEPTIONS

CASE 1: NEW MEMBER NAME DOESN’T MEET NAMING RULES

CSV FILE:

JOB CONSOLE:

CASE 2 : EXISTING MEMBER DOESN’T EXIST

CSV FILE:

JOB CONSOLE:

These are the two exceptions that have piqued my interest. I’m certain there are others. I hope you found this information useful.