If you didn’t read Part I of editing Essbase outline using XML, you are missing out on how this XML can be read.
Now that we know how to read the XML, let’s start creating the XML file and update our outlines. I’m a lazy person, and I guess you all know that by now. I’m going to use Groovy to generate the XML file from a flat file. If you are just interested in the code, then you can get the code from the end of the blog post and be done. I know you want to follow along 😬
Since we are dealing with a file with delimiters why don’t we get an excellent file reader for that?
@Grab('com.opencsv:opencsv:4.1')
The above command will resolve all dependencies and get opencsvreader. Next part is easy we need to import opencsv as it is not natively available in Groovy. Let’s also import groovy XML while we are at it.
import com.opencsv.CSVReader import groovy.xml.* otlfile = new FileReader("D:/OneDrive-Business/OneDrive - Intekgrate/GroovyScripts/testuda.txt") outlineXML = new File("C:/Users/ckatt/Documents/testessbaseXML.xml")
Use a file reader to read the member file and a location for the XML file.
char delimiter ='|'
CSVReader reader = new CSVReader(otlfile,delimiter)
// read header to check columns
header=reader.readNext()
def now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Start XML creation"
You can specify a delimiter of your choice, by default as the name implies CSVReader take comma as the delimiter. I’m going to read the first line to check for the columns (line 4). Now we need to read the header information. I’m going to make it flexible enough that the columns don’t need to follow an order. Here is a sample of CSV file that I’m using in this example.
Here is another format.
All headers except Alias must remain the same, or you’ll have to update that in the following code.
To add multiple UDAs, you can use comma separated list. To add multiple attribute association, you must specify the values as comma seperated list in the following format
Attribute Member:Attribute Dimension.
// find the column positions
storagecol=header.findIndexOf{it.equalsIgnoreCase("storage")}
consolcol=header.findIndexOf{it.equalsIgnoreCase("aggregation")}
opcol=header.findIndexOf{it.equalsIgnoreCase("operation")}
mbrcol=header.findIndexOf{it.equalsIgnoreCase("member")}
prntcol=header.findIndexOf{it.equalsIgnoreCase("parent")}
siblcol=header.findIndexOf{it.equalsIgnoreCase("prevsibling")}
newnamecol=header.findIndexOf{it.equalsIgnoreCase("newname")}
udacol=header.findIndexOf{it.equalsIgnoreCase("uda")}
attrcol=header.findIndexOf{it.equalsIgnoreCase("attribute")}
udareplacecol=header.findIndexOf{it.equalsIgnoreCase("replaceuda")}
aliascols=header.findIndexValues{it =~ /^Alias.*/}
What we are doing here is trying to find the column indexes that can be used to generate XML. For the alias, we are going to find all the columns that start with “Alias”.
Now that we’ve column indexes, let’s create the XML file. I’m using StreamingMarkupBuilder to create XML.
Below given is just a portion of StreamingMarkupBuilder
otlxml = new groovy.xml.StreamingMarkupBuilder().bind {
mkp.xmlDeclaration()
mkp.comment "You need to keep track of otlversion or you can always reset it to 0 using -1 as a value for otlVersion"
otlEditMain ("xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", xmlns: "mbredit","xmlns:xs": "http://www.w3.org/2001/XMLSchema", otlVersion: "0") {
mkp.comment "You can ignore otlUpdate section if you are not adding AliasTable or changing DTS or other properties"
otlUpdate(caseSensitive:"true",enableMemberType:"false"){
impliedShareSetting("default")
mkp.comment "Add new alias table name, if you are adding one"
aliasTable("LongName")
mkp.comment "Use for changing dynamic time series"
dtsMbr(mbrName:"Q-T-D",number:"2",enable:"true")
}
}
mkp.xmlDeclaration() will add this nice line to the generated XML file.
<?xml version="1.0" encoding="UTF-8"?>
To add comments, you just write the comment inside mkp.comment(“your comment”).
To add child elements inside a parent, you enclose the child elements inside a closure. Elements attributes are defined like maps.
The above code will produce a file like the one given below.
Now that we have the information for otlUpdate let’s start reading rest of the file contents.
reader.each{list ->
if(storagecol !=-1){
storageval=list[storagecol]
}
if(siblcol !=-1){
prvsibling=list[siblcol]
} else {
prvsibling=""
}
if(attrcol != -1){
attrvals=list[attrcol]
}
if(udacol != -1){
udavals=list[udacol]
}
if(consolcol != -1){
consolval=list[consolcol]
}
if(list[opcol].equalsIgnoreCase("update")){
mbrUpdate(thisMbr:list[mbrcol]){
mbrInfo(''){
if(aliascols){
aliascols.each{ tblindx ->
aliasindx=tblindx.intValue() ?: null
aliasval=list[aliasindx]
if(aliasval){
alias(aliasTable:header[aliasindx].split(':')[1].trim(), alias:aliasval)
}
}
}
if(storageval!='shared'){
if(udavals){
udareplace=list[udareplacecol]
udavals.split(',').each{udaname->
if(udareplace){
udas(replace:udareplace, name:udaname)
} else {
udas(name:udaname)
}
}
}
}
if(storageval){
dataStorage(storageval)
}
if(consolval){
consolidation(consolval)
}
}
}
if(storageval!='shared'){
if(attrvals){
attrvals.split(',').each{attr->
dimattr=attr.split(':')
mbrAssoc(thisMbr:list[mbrcol],attrDim:dimattr[1],attrMbr:dimattr[0]){
}
}
}
}
}
}
reader.each will read the file line by line (see it is that easy)
We are checking whether the operation column is Update if it is then create the tags for mbrUpdate.
Aliases we need to provide the table name and alias.
Lines 2-16 makes sure that you got those columns, findIndexOf returns -1 if nothing is found.
Line 21 checks whether the file got any Alias columns if present it loops through all alias indexes (line 22). Line 23 is called as an Elvis operator in Groovy. Groovy lists store the numbers as Long and to get the output using the index from a list you need an integer.
Line 26 splits the header (e.x. Alias:LongNames) at ‘:’ and then we take the last value.
If you are not adding a shared member, you can assign UDAs (line 30), values from UDA column are split at ‘,’ so if you have got a UDA which contains comma you’ll have to change that here (line 33).
Line 43 is where the dataStorage element is created and if you are on a duplicate name allowed outline you’ll have to change this to the following.
prototypeMbr(unique name of the member)
I would add another column in the CSV file called PrototypeMember and get the index of that column and use it as shown below
prototypecol=header.findIndexOf{it.equalsIgnoreCase("prototypemember")}
prototypeMbr(list[prototypecol])
If not a shared member, attribute association tag creation starts at line 50, here attributes needs the attribute dimension too. Attribute name and Attribute dimension are separated using “:”, multiple associations are separated using “,”s.
You’ll see two splits, one to separate the attributes (line 52) and second to separate the attribute dimensions and attribute members (line 53).
Rest of the code follows the similar logic of looking at the operation and add appropriate tags.
Keep in mind that rename using this method will not retain data!!
Here is the entire code.
@Grab('com.opencsv:opencsv:4.1')
import com.opencsv.CSVReader
import groovy.xml.*
otlfile = new FileReader("C:/Users/ckattookaran/Documents/testdense.txt")
outlineXML = new File("C:/Users/ckattookaran/Documents/testessbaseXML.xml")
char delimiter ='|'
CSVReader reader = new CSVReader(otlfile,delimiter)
// read header to check columns
header=reader.readNext()
def now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Start XML creation"
// find the column positions
storagecol=header.findIndexOf{it.equalsIgnoreCase("storage")}
consolcol=header.findIndexOf{it.equalsIgnoreCase("aggregation")}
opcol=header.findIndexOf{it.equalsIgnoreCase("operation")}
mbrcol=header.findIndexOf{it.equalsIgnoreCase("member")}
prntcol=header.findIndexOf{it.equalsIgnoreCase("parent")}
siblcol=header.findIndexOf{it.equalsIgnoreCase("prevsibling")}
newnamecol=header.findIndexOf{it.equalsIgnoreCase("newname")}
udacol=header.findIndexOf{it.equalsIgnoreCase("uda")}
attrcol=header.findIndexOf{it.equalsIgnoreCase("attribute")}
udareplacecol=header.findIndexOf{it.equalsIgnoreCase("replaceuda")}
aliascols=header.findIndexValues{it =~ /^Alias.*/}
storageval=""
prvsibling=""
attrvals=""
udavals=""
consolval=""
otlxml = new groovy.xml.StreamingMarkupBuilder().bind {
mkp.xmlDeclaration()
mkp.comment "You need to keep track of otlversion or you can always reset it to 0 using -1 as a value for otlVersion"
otlEditMain ("xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", xmlns: "mbredit","xmlns:xs": "http://www.w3.org/2001/XMLSchema", otlVersion: "0") {
mkp.comment "You can ignore otlUpdate section if you are not adding AliasTable or changing DTS or other properties"
otlUpdate(caseSensitive:"true",enableMemberType:"false"){
impliedShareSetting("default")
mkp.comment "Add new alias table name, if you are adding one"
aliasTable("LongName")
mkp.comment "Use for changing dynamic time series"
dtsMbr(mbrName:"Q-T-D",number:"2",enable:"true")
}
reader.each{list ->
if(storagecol !=-1){
storageval=list[storagecol]
}
if(siblcol !=-1){
prvsibling=list[siblcol]
}
if(attrcol != -1){
attrvals=list[attrcol]
}
if(udacol != -1){
udavals=list[udacol]
}
if(consolcol != -1){
consolval=list[consolcol]
}
if(list[opcol].equalsIgnoreCase("update")){
mbrUpdate(thisMbr:list[mbrcol]){
mbrInfo(''){
if(aliascols){
aliascols.each{ tblindx ->
aliasindx=tblindx.intValue() ?: null
aliasval=list[aliasindx]
if(aliasval){
alias(aliasTable:header[aliasindx].split(':')[1].trim(), alias:aliasval)
}
}
}
if(storageval!='shared'){
if(udavals){
udareplace=list[udareplacecol]
udavals.split(',').each{udaname->
if(udareplace){
udas(replace:udareplace, name:udaname)
} else {
udas(name:udaname)
}
}
}
}
if(storageval){
dataStorage(storageval)
}
if(consolval){
consolidation(consolval)
}
}
}
if(storageval!='shared'){
if(attrvals){
attrvals.split(',').each{attr->
dimattr=attr.split(':')
mbrAssoc(thisMbr:list[mbrcol],attrDim:dimattr[1],attrMbr:dimattr[0]){
}
}
}
}
} else if(list[opcol].equalsIgnoreCase("delete")){
mbrDelete(thisMbr:list[mbrcol])
} else if(list[opcol].equalsIgnoreCase("add")){
mbrAdd (mbrName:list[mbrcol],parent:list[prntcol],preSibling:prvsibling){
mbrInfo(''){
if(aliascols){
aliascols.each{ tblindx ->
aliasindx=tblindx.intValue() ?: null
aliasval=list[aliasindx]
if(aliasval){
alias(aliasTable:header[aliasindx].split(':')[1].trim(), alias:aliasval)
}
}
}
if(storageval!='shared'){
if(udavals){
udareplace=list[udareplacecol]
udavals.split(',').each{udaname->
if(udareplace){
udas(replace:udareplace, name:udaname)
} else {
udas(name:udaname)
}
}
}
}
if(storageval){
dataStorage(storageval)
}
if(consolval){
consolidation(consolval)
}
}
}
if(storageval!='shared'){
if(attrvals){
attrvals.split(',').each{attr->
dimattr=attr.split(':')
mbrAssoc(thisMbr:list[mbrcol],attrDim:dimattr[1],attrMbr:dimattr[0]){
}
}
}
}
} else if(list[opcol].equalsIgnoreCase("move")){
mbrMove (thisMbr:list[mbrcol],parent:list[prntcol],preSibling:prvsibling)
} else if(list[opcol].equalsIgnoreCase("rename")){
mbrRename (thisMbr:list[mbrcol],mbrName:list[newnamecol])
}
}
}
}
outlineXML.withWriter("UTF-8") { writer ->
writer.write( XmlUtil.serialize(otlxml))
}
now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Essbase outline edit XML created"
Line 170 is where this is written to a file. Line 171, XmlUtil.serialize, is doing the pretty print of XML.
Yes, I agree it is long. However I fell in love with the builder, it is effortless to add tag and elements.
After running the above code, the following XML file is generated.
Now to import this XML file, you’ll have to use either C API or JAVA API.
I chose to use Groovy 😉
Before running the code in groovy, you need to add the following jar files to your Groovy lib location.
import com.essbase.api.session.IEssbase
import oracle.core.ojdl.logging.ODLLogger
import java.util.logging.Level
def keyConfig = new ConfigSlurper().parse(new File('C:/Users/ckattookaran/Documents/usercred.txt').toURL())
appname=keyConfig.essbaseEnv.prod.esbApp
dbname=keyConfig.essbaseEnv.prod.esbDB
svrname=keyConfig.essbaseEnv.prod.esbServer
user=keyConfig.essbaseEnv.prod.esbUser
password=keyConfig.essbaseEnv.prod.esbPass
provider="Embedded"
logger = ODLLogger.getODLLogger("oracle.EPMOHPS")
logger.setLevel(Level.SEVERE)
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION)
olapSvr = ess.signOn(user,password, false, null, provider, svrname)
now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} $user Connected to $svrname."
cube = olapSvr.getApplication(appname).getCube(dbname)
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Start editing $appname.$dbname outline."
cube.buildDimensionXml("C:/Users/ckattookaran/Documents/testessbaseXML.xml", "C:/Users/ckattookaran/Documents/XMLEdit.log", true)
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Finished editing $appname.$dbname outline."
olapSvr.clearActive()
olapSvr.disconnect()
ess.signOff()
now = new Date()
println "${now.format("yyyy MM dd-HH:mm:ss.SSS", TimeZone.getTimeZone('UTC'))} Closed connection and disconnected from $svrname."
Oh if you have got a space in XML location or log location this will not work. You’ll get an error.
If you are going to track the outlineVersion, then you either need to remember the version or reset it to zero using -1 as value or let the build fail!!! Oh yeah, there is no option as of now to retrieve the outline version using API and use that in the XML file.
Here is what a failed Dimension build log will look like.
The log file will give you the version number!!!
I think there should be a method to query the version and use that in the XML file, rather than a trial and error method.
If successful you should see something similar in the log file.
Let’s look at the outline.
New alias table is added, case sensitivity changed.
Q1 is deleted, Q2 moved and renamed, and IT expenses is added.
If I run the same Groovy using the file given below, it creates an XML as shown below.
There you go, now go ahead and kill those dimension build load rules 😉
