ODI Series - Loading SQL Metadata into essbase and its problems

I was going to move on to essbase data loading with ODI but a couple of questions cropped up on the forums this week and I wanted to cover them off first.

The question was around loading essbase metadata from a database repository and some of the rule separator options not working.

Seeing as I went through loading metadata from a file source in the last blog I am just got to update the interface I used but link it to SQL DataStore.

First all I had to create a database table to mirror the flat file structure and populate it with data.



Next I reversed the database table in ODI



And then updated the interface to the use the SQL DataStore instead of the flat file DataStore.



Now I used the same rules file that the delimiter was set to comma (,) and in the interface the RULE_SEPERATOR set to (,).



Running this interface causes no issues but the question that was brought up on the forum that when you create an essbase rules file and use a sql load then delimiter is set to tab.



In the “IKM SQL to Hyperion Essbase (METADATA)” options for the RULE_SEPARATOR it says you can use Comma, Tab, Space and custom characters.



Ok, lets change the separator to Tab in the LKM and the essbase rules file.



Run the interface and we would expect no problems but the interface fails, if you look in the outputted log file.



And if you change to Space and run the interface again once again it fails.
Running it for any other custom characters is fine the problem just arises with Tab and Space.
So it doesn’t work with them end of story, well I don’t like leaving things in that way and want to know the reason why.

I should have just left it there because it has taken a fair bit of digging into the Java code which ODI runs when the interface is executed.

In part of the JAVA code the values that have been entered for the RULE_SEPERATOR are converted.



So if you enter comma it is converted to “,”, tab is converted “\t” and space is converted to “ “.

All seems fine there but stepping through the code later on there is a validation check on the value in the ruleSeparator variable.



What this means is that the variable is trimmed to remove any trailing whitespaces and checks if the trimmed value equals “”, if you have chosen tab or space then it is also going to remove all the whitespaces so will equal “” and then throw an error.

This is obviously a bug then but can it be fixed? Yes, and very easily.

All I needed to do was remove the trim() from the code so it would only fail if the rule separator was left blank



I compiled the class (ODIEssbaseMetaWriter.class) and updated the jar file (odihapp_essbase.jar)
Now running the interface I can set the separator to Tab or Space without any issues.

I can also answer the question why do you have to use an essbase load rule, if are using a database table as a source you would expect the JAVA code to handle the integration of the metadata but unfortunately it is not very sophisticated.

What happens when you run the interface it creates a temporary file that it then uses with the load rule to load in the metadata, so it is converting the database records to a text file or if you have a flat file source it will also create a temporary file in the correct format for the load rule.



Not the most efficient way of getting the data into essbase and is also a warning if you have large amounts of metadata to load, for one it is going to create a large temporary text file in your temp directory and is not going to be the quickest integration.

I also noticed something else, I am using version 11.1.1 essbase and using the ODI essbase drivers for 9.3.1, I have not seen any 11.1.1 drivers for ODI so I have no choice. In the IKM there is an option RESTRUCTURE_DATABASE but when I look at the log after running the interface I see :-



Versions prior to 9.3 but I am using 11.1, another hunt around in the JAVA code explains why this occurs.



Basically if the version is above 9 and less than .3 the warning will always happen, not to worry though it is only a warning and doesn’t stop the restructuring, it also can be fixed easily in the code.

One final thing I spotted and I am not clear why this is happening yet, in my source file/database there is a field for Alias.



If I use EAS and load the metadata in then all the aliases are loaded into the outline, if I use ODI with the same source and same load rule the alias for the first member is not loaded, so in this case member 500 never gets the alias loaded. I am not sure if anybody else has encountered this?

Well I am going to leave it there for today as once again it has taken a lot longer than expected to produce.