Metadata and Groovy - Bulk Update Members through Groovy

Use a simple CSV and groovy to bulk update member properties

In this part of the Metadata and Groovy series, I will explore how you can harness the capabilities of Groovy to bulk update dimension members using a CSV file.

The CSV Format

We plan to update the members so first we must identify the member. Instead of just having one column for member name (and having to search every dimension to locate the member), I wanted to identify each unique member using two columns

• Dimension
• Member

All other columns of the CSV must have the same name as the property we need to update. It could be one property or three or twenty.

Not every property must have a value when updating members in bulk. We will ignore cells that are blank so that we only update the member property if there is a cell value.

The Code

/*RTPS:*/

Application app = operation.application
Cube[] cube = app.getCubes()

Map columnMap = [:]
csvIterator('Metadata Update.csv').withCloseable() { reader ->
	String[] headers = reader.next()
	headers[2..-1].eachWithIndex { header, index -> columnMap[index] = header }

	reader.each { String[] values ->
    	Dimension dim = app.getDimension(values[0], cube)
        Member mbr = dim.getMember(values[1], cube)

        if (mbr) {
        	def mbrProperties = mbr.toMap()
			values[2..-1].eachWithIndex { value, index ->
                if (value != "") { mbrProperties << ([(columnMap[index]):value] as Map) }
			}
            mbr = dim.saveMember(mbrProperties, DynamicChildStrategy.NEVER_DYNAMIC)
        }
	}

}        

I had some thoughts on other features that can be added on to make this more robust:

1. Ability to add a Dimension Whitelist to limit the bulk update to specified dimensions only
2. Add modifiers (I was thinking something similar to the secfile format) with a member and 'access mode' to have Member/Hierarchy Whitelist so that you can limit updates to certain hierarchies
3. Add a keyword (maybe something like "!REMOVE") to specify if a certain property needs to be removed from a member.
4. Output a summary with the properties that failed to be updated, and maybe have it emailed to the admin

Summary

In this first article in the _Groovy and Metadata_ series, I have covered how you can bulk update metadata of a dimension Member building on what we've learned in the previous articles in this series.