Groovy – Metadata Accelerator

I have developed different versions of Metadata Accelerator tools to drive metadata build requirements in many different projects in my career. While it may be strategically the best option for companies to invest and implement MDM (Master Data Management) software within their ecosystem. It may not be possible to utilise a corporate MDM software as a solution due to many reasons while you are implementing EPM applications. Whether it is a CoA in Account dimension or any other structure like Entity hierarchy. It is almost certain you will need the structures loaded more than once and you will have to update application outline with the newer and newest version of hierarchies. I can state 2 main reasons and leave you to think more reasons you can imagine

1- EPM projects are sitting on top of existing business processes and reporting cycles if you like, it is not uncommon to find EPM implementations to cohabit and driven tightly together with Account Harmonization efforts. Simply put 2 people are using similar nature expense accounts with 2 different PL code in CoA, it can simply be merged to report correctly or make it simpler. In certain cases, complete restructuring of dimensions could be required as part of your EPM project. Complete restructuring could be even a statutory requirement. I was fortunate to work on an ICB project.

2- Life changes and it is very natural that reporting structures will evolve with the changing world. There might be fast moving structures in the EPM application like product lines, employees or relatively slow ones like legal entities. It is almost certain that dimension members in your application will be different in day 1 when you start the project vs. the last day when you are finishing the project and handing over.

My view is that Office skills especially Excel skills are essential in EPM projects. For metadata load to work correctly, we need to have the correct template populated with information. Here is one example file I have prepared to build Customers dimension. In this format, first column should be always Dimension name that corresponds to the dimension name in the application. Name and Parent fields are mandatory as you can imagine, other fields are optional and it is extendible. In other worlds, you can use same format and add Turkish Alias table for instance into the file if you want to define additional member properties.

Here is an example with additional member properties which is another alias table.

I will not focus on how to generate this file format on Excel more, just assuming file is created with the dimension structure we want to build and it is saved as MetadataAccelerator.csv. I won’t be focusing on how to automate it further with macros or uploading the file to inbox/outbox folder in EPM cloud. You can simply upload the .csv file manually to EPM Cloud inbox/outbox explorer folder. Once the file is there, groovy script that you are designing can read the file and start manipulating the application dimensions based on the content inside the file. csvIterator is the method you can use to read the files

def matrixMetadata = []
csvIterator("MetadataAccelerator.csv").withCloseable() { reader ->
    reader.each { String[] values ->
    matrixMetadata.add(values)
  }
}

You can check if there are more than 1 rows in the file. If the size of matrixMetadata here is not 2 or more, it means there is no content in the file to process further.

println(matrixMetadata.size())
if(matrixMetadata.size()>1){
    ...
}

I designed the algorithm to collect headers in another Array. I know there might be multiple different ways to do the same thing and piece of codes I am providing here may not be always the most elegant way.

def headerMetadata = []
matrixMetadata[0].each{ header ->
    headerMetadata.add(header.toString())
}
matrixMetadata.eachWithIndex{ row,int k ->
 if(k>0){
    def dimensiontobeupdated = ""
    def Map newmem = [:]
    row.eachWithIndex{ member,int j ->
     if(j==0){dimensiontobeupdated=member.toString()}else{
      if(member.toString().size()>0){
         newmem[headerMetadata[j].toString()]=member.toString()
      }
    }
   }
...
}

newmem Map is the member we will be defining inside the application. saveMember() method is used to accomplish member addition or update of the member properties. If the member name is already existing, this script will update the member properties to new ones like new parents, new Alias name etc.

Dimension dim = cube.getApplication().getDimension(dimensiontobeupdated);
try{
  if(allowedDimensions.contains(dimensiontobeupdated)){
    Member newmember = dim.saveMember(newmem);
  }
  else{
    println("Not Allowed to edit dimension "+dimensiontobeupdated)
  }
}
catch(Exception RuntimeException){
   println(RuntimeException.getMessage())
   println("Member attempted. Please check with your administrator")
   throwVetoException("Unable to create member. Check the logs");
}

if you have noticed the usage of allowedDimensions in the script. It is a mechanism to restrict this script’s ability to manipulate only certain list of dimensions. For our example to work properly, it has to be defined in such as way so Customers dimension can be edited.

def allowedDimensions = ["Customers"]

Can we extend this script to only allow members to be added/edited under certain parents ? similar to allowedDimensions, you may dtart by adding allowedParents as follows.

allowedParents = ["Premium"]

and it is a matter of checking whether the new member you are adding is destined to be under list of allowedParents.

if(allowedParents.contains(newmem["Parent"])){
   Member newmember = dim.saveMember(newmem);
   allowedParents.add(newmem["New Name"].toString())
}else{
   println("Not Allowed to edit Cost Centre Parent:"+newmem["Parent"])
}

if you have noticed the following line, its purpose is to allow new member to be allowed to be used as a parent. If you intend to add nested structures. You will need to add the parents to the list of allowed Parents.

allowedParents.add(newmem["New Name"].toString())

By using the file template suggested in this post and with some customization in the code, you can head start with metadata build in your implementations and use this mechanism tactically until a strategic MDM solution lands to the environment