Without uploading the data into the system, there are few methods for validating the data file to see if all of its records are going to be loaded or not without rejections. This type of validation is frequently carried out on a local server by comparing members to downloaded meta data csv files. I’d want to demonstrate how groovy can be used to implement these types of validations in Calculation Manager by comparing the data file directly with the outline.

Steps to be followed:

1. Upload the data file to the inbox/outbox explorer
2. Execute groovy business rule that traverse through the file and throw an error if any unknown members are found

GROOVY BUSINESS RULE

DATA FILE FORMAT – PLANNING FORMAT

I’m trying to use a “Planning” format csv file to implement member check. Except for rows and columns, all dimension members are grouped together in the planning format’s ‘Point-of-View’ column.  Since all of the members are properly comma separated, it’s simple to implement for other format files compared to Planning format data files. 

We need to split the ‘Point-of-View’ column and separate the dimensions that we need as we iterate through the file. This is a little complicated because planning puts those special character type member names inside double quotes, thus dividing with a simple comma will cause issues. We must carefully divide the ‘Point-of-View.’

Planning Format Data File:

GET UNIQUE ENTITY MEMBERS FROM THE FILE

I’m trying to collect all of the file’s unique Entity dimension members. We’ll traverse each row with a csv iterator, separate the ‘Point-of-view’ column, and then fetch all entity members to a Set List.

//define dimension name, dimIndex from 'Point-of-View' column index after its split using commas
String fileName = 'exportedData.csv'
String dataLoadCube = 'Plan1'
String dimensionName = 'Entity'
int dimIndex = 4

//iterate through the file and capture unique members from a specific dimension
Set<String> mbrsFromFile = []
csvIterator(fileName).withCloseable() { fileRows ->
	int povIndex
	String pov
	fileRows.eachWithIndex { row, idx ->
		if (idx == 0) {
			povIndex = row.collect{it.toString().trim() }.indexOf('Point-of-View')
		} else {
			pov = row[povIndex]   
			String[] povAsList = stripQuotes(pov).split(",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*\$)") 
			mbrsFromFile << povAsList[dimIndex]          
		}
	}
}

CHECK THE UNIQUE ENTITY MEMBERS EXIST IN THE SYSTEM OR NOT

Creating a List object called “exceptions” to hold all the members that aren’t part of the system. If the exceptions object contains any members, the data file contains unknown members.

//capture unknown members to a list from outline
Cube cube = operation.application.getCube(dataLoadCube)
Dimension dimension = operation.application.getDimension(dimensionName, cube)
List<String> exceptions = mbrsFromFile.findAll{!(dimension.hasMember(it, cube))}.collect{it}

THROW ERROR IF EXCEPTIONS FOUND

It is easier to fail this script if any exceptions are encountered, especially when utilizing epmautomate; for example, if the current data file validation failed, we can quickly halt the batch script without continuing to load the file.

//Throw error if unknown members found
if(exceptions) {
	println "There are members in the file that are not part of the system."
	throwVetoException("The file cannot be loaded due to unknown members:\n$dimensionName:\n$exceptions")
}

JOB CONSOLE OUTPUT

I deleted two members from the Entity dimension from the Vision application after exporting the data in a planning format file. The groovy rule fails and unknown members’ details are displayed in the job console.

We can execute multiple format data file validations for more than one dimension check in a similar way. There are numerous approaches to making it happen. I hope you found this information useful.