Comparing the metadata of multiple dimensions to source system flat files can be a time-consuming manual task, especially during the build phase of any EPM implementation. We’ll look at how to avoid manually comparing EPBCS exported metadata files with source system files just to find out the differences, as well as how to incrementally upload metadata (just changed records) into the system, all through Calculation Manager.
Steps to be followed:
- Upload the metadata flat files from the source system to the inbox/outbox directory
- Put the groovy rule into action
The groovy rule does the following action:
- Compare metadata file records with the system member properties
- Compile a list of all the records in a csv file that have been added or whose properties have been changed
- Generate a reduced version of the source system meta data file that only contains incremental modifications
- Decide whether or not modified records must be uploaded to the application while the Groovy rule is being executed
CSV ITERATOR TO ACCESS FILE FROM INBOX/OUTBOX
A function to read the csv file from the inbox/outbox explorer has been added to the library. The csv iterator loops through each row, returning data in String[]. We’ll go over each record, get the current member from the iterator, see if the member in the file is available in the EPBCS system, and if so, obtain its member properties and compare them to the member properties in the file.
PROCESS HEADER ROW FROM FILE
We’ll start by looking at the flat file’s header row and extracting the indexes for a few predefined properties. Only a few member properties from the system and the flat file will be evaluated. This might also be used to other types of properties.
//define parameters - dimensions, file to be processed..
String dimensionName = 'Product'
String metaDataFileName = 'productMetaDataShared.csv'
//properties to be checked from system members & file members
List<String> propertyCheck = [dimensionName, "Parent", 'Alias: Default', 'Data Storage']
//to capture the index of properties
Map<String, Integer> propertyIndex = [:]
csvIterator(metaDataFileName).withCloseable() { rows ->
rows.eachWithIndex { row, idx ->
List<String> fileRecord = row.collect { it.toString().trim() } as List<String>
if (idx == 0) {
header = fileRecord.collect { it == dimensionName ? 'Member' : it }
propertyIndex = propertyCheck.collectEntries { it == dimensionName ? ['Member': fileRecord.indexOf(it)] : [(it): fileRecord.indexOf(it)] }
}
}
}PROCESS MEMBER PROPERTY ROWS FROM FILE
Once the header row has been discovered, we must examine the other rows and match them to the system property members. Because the getMember function only returns the principal members, shared members must be treated differently. We must use Cubes instead of a single cube when calling getMember() as we are validating at the application level.
//define parameters - dimensions, file to be processed..
String dimensionName = 'Product'
String metaDataFileName = 'productMetaDataShared.csv'
//properties to be checked from system members & file members
List<String> propertyCheck = [dimensionName, "Parent", 'Alias: Default', 'Data Storage']
Cube[] cubes = operation.application.getCubes()
Dimension dimension = operation.application.getDimension(dimensionName, cubes)
//to capture the index of properties
Map<String, Integer> propertyIndex = [:]
csvIterator(metaDataFileName).withCloseable() { rows ->
rows.eachWithIndex { row, idx ->
List<String> fileRecord = row.collect { it.toString().trim() } as List<String>
if (idx == 0) {
header = fileRecord.collect { it == dimensionName ? 'Member' : it }
propertyIndex = propertyCheck.collectEntries { it == dimensionName ? ['Member': fileRecord.indexOf(it)] : [(it): fileRecord.indexOf(it)] }
} else {
//get file member Property and its value based on the properties defined
Map<String, String> fileMbrProperties = propertyCheck.collectEntries { it == dimensionName ? ['Member': fileRecord.getAt(propertyIndex['Member'])] : [(it): fileRecord.getAt(propertyIndex[it])] }
//get system member property and its value based on the properties defined
Map<String, String> sysMbrProperties = [:]
// check if the member available and process primary and shared members
if (dimension.hasMember(fileMbrProperties['Member'], cubes)) {
if (fileMbrProperties['Data Storage'].toLowerCase() != 'shared') {
sysMbrProperties = dimension.getMember(fileMbrProperties['Member'], cubes).toMap().findAll { it.key in fileMbrProperties.keySet() } as Map<String, String>
} else {
List<Member> sharedMbrs = dimension.getMember(fileMbrProperties['Member'], cubes).getBaseAndSharedMembers(cubes)
if (sharedMbrs.size() > 0) {
List<Member> matchingSharedMbrs = sharedMbrs.findAll { mbrs -> mbrs.toMap().findAll { it.key.toLowerCase() in ['member', 'parent'] } == fileMbrProperties.findAll { it.key.toLowerCase() in ['member', 'parent'] } }
if (matchingSharedMbrs.size() == 1) {
sysMbrProperties = matchingSharedMbrs.getAt(0).toMap().findAll { it.key in fileMbrProperties.keySet() } as Map<String, String>
}
}
}
}
}
}
}COMPARE THE TWO MAPS
We have to remove null items and remove case sensitivity to check if both properties are identical or not. I have a simple function which gets the clean version of both maps. The comparison portion is added in the final version of the script.
//trim properties and remove case sensitive items
Map<String, String> trimProperties(Map<String, String> properties) {
Map<String, String> trimedProperties = [:]
properties.each { key, value ->
if (value.size() != 0 && value.toLowerCase() != '<none>') {
trimedProperties[key.toLowerCase()] = value.toLowerCase()
}
}
return trimedProperties
}CREATE / UPDATE CHANGED RECORDS
If system map and file map are not matching, then it means its a changed record. The below code is used to get the changed record added into the system. Although I am checking only few of the properties, I am adding all properties which are not null into the system.
//save identified changed Member to the system
void updateMembers(Dimension curDimension, List<String> headers, List<String> properties) {
Map<String, Object> mapHeaderRecords = [headers, properties].transpose().flatten().toSpreadMap().findAll { it.value } as Map<String, Object>
try {
curDimension.saveMember(mapHeaderRecords)
println "SUCCESS : Member properties updated successfully"
} catch (java.lang.Exception e) {
println "ERROR : " + e.message
}
}FINAL CODE
I am adding two new files which needs to capture the changed elements. Two files are,
- Capture the row number, member name and type of change for the changes records
- Filter only the changed records from the original flat file if that needs to be consumed via meta data import
//define parameters - dimensions, file to be processed..
String dimensionName = 'Product'
String metaDataFileName = 'productMetaDataShared.csv'
Cube[] cubes = operation.application.getCubes()
Dimension dimension = operation.application.getDimension(dimensionName, cubes)
//properties to be checked from system members & file members
List<String> propertyCheck = [dimensionName, "Parent", 'Alias: Default', 'Data Storage']
//capture the index of properties
Map<String, Integer> propertyIndex = [:]
List<String> header = []
//create a file with closeable interface
createFileWriter("${dimensionName}_Changed_Summary.csv").withCloseable() { summaryToFile ->
createFileWriter("${dimensionName}_Incremental.csv").withCloseable() { writeToFile ->
//access meta data file from inbox/outbox directory
csvIterator(metaDataFileName).withCloseable() { rows ->
rows.eachWithIndex { row, idx ->
List<String> fileRecord = row.collect { it.toString().trim() } as List<String>
//process header record
if (idx == 0) {
header = fileRecord.collect { it == dimensionName ? 'Member' : it }
propertyIndex = propertyCheck.collectEntries { it == dimensionName ? ['Member': fileRecord.indexOf(it)] : [(it): fileRecord.indexOf(it)] }
//write header to the file
writeToFile.write(fileRecord.collect { it.contains(',') ? cscParams(it) : it }.join(',') + "\n")
summaryToFile.write(['Record', 'Member', 'Type'].join(',') + "\n")
//process member
} else {
//get file member Property and its value based on the properties defined
Map<String, String> fileMbrProperties = propertyCheck.collectEntries { it == dimensionName ? ['Member': fileRecord.getAt(propertyIndex['Member'])] : [(it): fileRecord.getAt(propertyIndex[it])] }
//get system member property and its value based on the properties defined
Map<String, String> sysMbrProperties = [:]
// check if the member available and process primary and shared members
if (dimension.hasMember(fileMbrProperties['Member'], cubes)) {
if (fileMbrProperties['Data Storage'].toLowerCase() != 'shared') {
sysMbrProperties = dimension.getMember(fileMbrProperties['Member'], cubes).toMap().findAll { it.key in fileMbrProperties.keySet() } as Map<String, String>
} else {
List<Member> sharedMbrs = dimension.getMember(fileMbrProperties['Member'], cubes).getBaseAndSharedMembers(cubes)
if (sharedMbrs.size() > 0) {
List<Member> matchingSharedMbrs = sharedMbrs.findAll { mbrs -> mbrs.toMap().findAll { it.key.toLowerCase() in ['member', 'parent'] } == fileMbrProperties.findAll { it.key.toLowerCase() in ['member', 'parent'] } }
if (matchingSharedMbrs.size() == 1) {
sysMbrProperties = matchingSharedMbrs.getAt(0).toMap().findAll { it.key in fileMbrProperties.keySet() } as Map<String, String>
}
}
}
}
//create comparison map elements by removing null and empty items
Map<String, String> compareFileMbrProperties = trimProperties(fileMbrProperties)
Map<String, String> compareSysMbrProperties = trimProperties(sysMbrProperties)
//check if system properties matches with file member properties (excluding shared members)
if (compareFileMbrProperties != compareSysMbrProperties) {
newRecords = true
Map<String, String> changedStat = ['Record': (idx + 1).toString(), 'Member': fileMbrProperties['Member'], 'Type': dimension.hasMember(fileMbrProperties['Member'], cubes) && fileMbrProperties['Data Storage'].toLowerCase() != 'shared' ? "Property Change" : "New Member"]
println "$changedStat"
summaryToFile.write(changedStat.values().collect { it.contains(',') ? cscParams(it) : it }.join(',') + "\n")
writeToFile.write(fileRecord.collect { it.contains(',') ? cscParams(it) : it }.join(',') + "\n")
updateMembers(dimension, header, fileRecord)
}
}
}
}
}
}
//trim properties and remove case sensitive items
Map<String, String> trimProperties(Map<String, String> properties) {
Map<String, String> trimedProperties = [:]
properties.each { key, value ->
if (value.size() != 0 && value.toLowerCase() != '<none>') {
trimedProperties[key.toLowerCase()] = value.toLowerCase()
}
}
return trimedProperties
}
//save identified changed Member to the system
void updateMembers(Dimension curDimension, List<String> headers, List<String> properties) {
Map<String, Object> mapHeaderRecords = [headers, properties].transpose().flatten().toSpreadMap().findAll { it.value } as Map<String, Object>
try {
curDimension.saveMember(mapHeaderRecords)
println "SUCCESS : Member properties updated successfully"
} catch (java.lang.Exception e) {
println "ERROR : " + e.message
}
}CHECKING FINAL RESULTS
I have uploaded the below meta data file in the inbox/outbox directory. Added a stored member and a shared member for validation.
Source file:
Before Update – Dimension Editor:
Job Console Output of Groovy Rule:
After Update – Dimension Editor:
Generated Output Files:
Changed Records file:
Loadable meta data file only with changed records:
CONCLUSION
When comparing a flat file with meta data members, there are numerous aspects to consider. The goal is to prove that this is achievable and that it can make our lives easier. As we constantly say, there are multiple approaches to accomplish the same goal. I hope this information is useful.