Here is the situation, say you are extracting member information from an essbase outline and the member contains formulas that run over more than one line then you could encounter a small issue.
In this example I am going to extract measures metadata from everybody’s favorite sample basic.
I have a simple interface that is going to extract Idescendants of the measures dimension, this information is going to be written to a file, this means the LKM Hyperion Essbase METADATA to SQL will be used to load into the staging area and use IKM SQL to File Append to transfer from the staging area to a flat file.
As there are line breaks in the formula in essbase these are being passed down into the flat file and causing formatting issues, if you have lots of formulas then this can make the file messy and unfit for purpose.
Fortunately there is an extremely simple solution; all that is required is the line breaks to be removed from the extract. ODI uses SQL to move and transform the data so a SQL function can be used to remove unwanted characters.
Now there are different functions you could use depending on your staging technology but a function that is common to most SQL technologies is the REPLACE function.
The syntax for the function is
replace( string, string_to_replace, [ replacement_string ] )
[ replacement_string ] is optional, if it is omitted then the function will remove all occurrences of string_to_replace in string
You won’t be able to enter a line break directly in the string_to_replace so you can use the CHR/CHAR (depends on technology being used) to convert a numerical value to a line break; a line break consists of two numerical character values 10 & 13
These functions will just need to be applied on the target DataStore mapping for the Formula column.
The above expression will remove character values 10 and 13 (line break) from the formula string
If you look in the operator you will be able to see the SQL and that is being generated and the functions in operation.
Within the file the link breaks have been removed and the formula is shown on one line.
So there we have a quick and easy solution to removing unwanted characters from an essbase metadata extract.
John, Great blog. Got me going in the right directon. For CR-LF issue, I was trying to put you REPLACE solution in the Interface that Integrates (loads) data to Planning. The REPLACE for CR and LF only worked for me if I put it on the LKM (extract) data from Essbase. I also had to add a REPLACE to remove the tab character (CHAR(09)) on the LKM.
ReplyDelete