Get Text IDs from PBCS

I always had issue with the way OutLineLoad extracts data, it can called as a sort of one dimensional (or two) export.

Only one dimension in row and one dimension in column. Now you might say why use that when you’ve DATAEXPORT. I agree and at the same time disagree with you.

I agree DATAEXPORT can work if you are extracting numbers
I disagree when you are using DATAEXPORT for text and Smart List, because that is just going to give you numbers.

Well, now if you are the smart one (which I know you are and that’s why you asked me to use DATAEXPORT) you can load the extracted data to a table, perform a lookup against HSP_TEXT_CELL_VALUE table (ID column) and return the text.

Yes that is perfectly possible in an On-Prem world, what about PBCS? 🙂

You cannot see the backend table, so there is no possibility of looking at HSP_TEXT_CELL_VALUE table.

Back to the Future (hmm no, Forward to the Future)

Let’s take a look at the Migration options, shall we.

Oh, what do we see here, oooh Text Values, Cell Texts (lot of other stuff, which is not important right now)

Perform a “Migration” of the artifact and let’s look at the file.

It is an XML file (pity!!!) with the text value and id, this is the same id that gets stored in Essbase.

Now let’s create a DATAEXPORT script (now you are going to ask me what file path will I give in PBCS, hold on, dear reader :))

What do you see?!!!! (It took a long time to know that I could use /u03/lcm (I was using /u03/lcm/import/<LCMName>, thanks to Chris Rothermel and my older brother from completely different set of parents, Cameron Lackpour for sharing this wisdom on a N54 post)

Well now I know and you know that
/u03/lcm is the PBCS Inbox/Outbox location
/u03/lcm/import is the Migration aka LCM location
/u03/inbox/data is the Integrations aka Data Management aka FDMEE lite Inbox location

There we are I got my extract in PBCS inbox location.

File before conversion

Steps that you should perform
EPMAutomate to run the rule
EPMAutomate to exportsnapshot
EPMAutomate to download both files.
Extract the XML file from LCM.zip
Use the below given groovy script (Well I’m still learning, so you might find more faster, less number of line code to do the same job) to convert Essbase output to Planning Text output.

// Read the file
datafile = new FileReader('fullpath to DATAEXPORT filename')
// Read the first line and store it, this also makes sure that it gets removed from eachline later
firstline = datafile.readLine()
// The below given will be the new Text File
textdatfile = new File('newtextfile with text')
textdatfile.write(firstline)
// Read the XML file
fullnametexts = new XmlSlurper().parse('full path to Text Values.xml')
// Read data file line by line
datafile.eachLine { line ->// Split the line by the delimiter, (in this case |)
          line.splitEachLine('\|'){fullnamedat ->// Check the number from the dataexport file in XML and return it's text (@id is the number and @value is the text)
                fullnametexts.text.each{
                      if("${it.@id}" ==  fullnamedat[-1]){
                          // Write the file               
                          textdatfile.append(System.getProperty("line.separator") + fullnamedat[0..fullnamedat.size()-2].join("|")+'|'+"${it.@value}")         
                      }
                 }
           }
}

File after conversion

Cameron is going to kill me for teasing him, with all these new stuff that I’m talking about. Older brother forgive me 😉

There you go a faster way to get Text data from Planning. Now I’m lazy enough to write the entire code, back to sleep now.