ODI / Essbase challenge - Extracting alternate alias member information

Back again with another ODI session, this time it is more of a challenge that I encountered and the solution I came up with. On the Oracle essbase forums there was a post about extracting metadata from a dimension but using an alternate alias table, must be pretty easy I thought but unless I am missing something its turns out it is not as simple as it seems.

When you reverse a cube for each dimension you get an Alias column which extracts an Alias name, there are no options to select which alias table you want to use.

I wanted to find out if there was a way around this because it seems a bit of let down if you can never directly extract alternate aliases, I know you could probably write a MaxL script to extract the aliases to a text file then perform an integration in ODI to link them up with the extracted metadata, this solution I would look at if I couldn’t find a way to extract using ODI.

To perform a metadata extraction ODI uses “LKM Hyperion Essbase METADATA to SQL”, knowledge modules are basically a list of steps to perform.



KMs have the added benefit of options that you have already come across if you have been following the blogs.



Each step can use any of the ODI technologies available; by default ODI uses its own API and these are direct calls to methods implemented in Java.
If you double click the “Drop work table” you can see an example of the ODI API



<%=snpRef.getTable("L", "COLL_NAME", "W")%> uses the ODI API, basically it is saying retrieve the current DataStore table name. I am not going to really go into any more detail than that as it is not the aim for today.

If you double click “Begin Essbase Metadata Extract” you will see it uses the Jython technology, Jython is an implementation of the Python programming language written in Java, if you have ever used Java then you will see the similarities straight away.



In this step the command calls upon the Hyperion ODI classes, these are all contained in the jar files that you would have copied into the oracledi/drivers directory. The code contained within all the various Java classes performs the main extract of the essbase metadata.

What I needed to do was somehow pass in the name of the Alias table into the calls made to Java, this is where options come in very handy.

In the code you can see where the options are stored



I duplicated the current “LKM Hyperion Essbase METADATA to SQL” so I can easily revert back to the other one if things go wrong.



Right clicking the LKM lets you insert an option; I created a new option called ALIAS_TABLE



Once the option has been created you can see it in the LKM options tab



And now to add to the code so the ALIAS_TABLE option can be retrieved and stored.



In the same code we want to pass the stored value into a Java method that will be retrieved later by one of the Java classes.



For now I just used “ALIAS_TABLE”, I am probably going to update the Java ODIConstants Class and create a class variable called ALIAS_TABLE which that will hold “ALIAS_TABLE”, then the code above would resemble

extractOptions.put(ODIConstants.ALIAS_TABLE,alias_table)

I used the interface I created in the last blog that extracts metadata from the sample.basic measures dimension and populates a SQL server db table, if you need a refresher have a look at:- ODI Series - Essbase Outline Extractor

In the flow diagram I changed the LKM to the one I had just been making changes to “LKM Essbase METADATA V2”



By changing the LKM you will notice that the new option is available ALIAS_TABLE

I ran the interface just to make sure there were no issues, it doesn’t produce any different results at the moment but it still worked.

In EAS I created a new Alias table called ODI_Demo



Using the Default Alias table I created an alias for the Sales member



For the ODI_Demo alias table I created another alias for the Sales member



So just to recap, I have an interface which uses an updated LKM that has the option to enter an alias table, the LKM stores alias table value and passes it into a java method where it is stored again ready to be retrieved from within a Java class.

What I needed to do now is try and get one of the Java classes to retrieve that stored value and retrieve member details for the associated alias table, now if you don’t understand Java you can either skip some of the next part of the blog or try and bear with it, for those that do understand Java please don’t curse me for what you are about to receive as I have not really been writing Java for very long.

To do this I first decompiled some of the Java classes and followed the flow through the code until I came to where I thought the main activity was for extracting member information.

The classes containing the core elements are in odihapp_essbase.jar, the first class I came across of interest was :- ODIEssbaseMetaReader.class

In this class it retrieves and validates the values that were stored in executing the LKM, I added a few lines code to retrieve the Alias table value and if it the value is blank set it to null so the default alias table will be used.



Now in the class there is a method called getAppData that basically runs member selection query depending what you had entered in the options section of the LKM. My original idea was to add a line of code that sets the current alias table to that of the stored value.



I compiled the class and put it back into odihapp_essbase.jar, I updated the interface to use the ODI_Demo alias table and executed the interface again.



Unfortunately the extract still retrieved the default alias member, so back to delving a bit deeper into the Java code.

After the member selection query has returned the member information it is passed into EssbaseMetadataIterator.class which steps through each member (it overrides the Iterator class) and stores each member property with the use of EssbaseMetaRecordBuilder.class.

In the EssbaseMetaRecordBuilder class there is a line that stores the Alias for the current member



iessmember.getAlias(null) means that it will always return the default alias table value so no wonder my earlier code didn’t work, which also proves with no changes you can’t return alternate alias table member values.

For those that don’t really understand Java I hope you are managing to still keep your eyes open and are not drifting off, not to worry the end is in sight.

To return alternate alias table values I would have to pass the original stored alias table value into the EssbaseMetaRecordBuilder class.

To go about this I added the aliasTable variable in the parameters of the creation of the EssbaseMetadataIterator object



In the EssbaseMetadataIterator class I added the aliasTable parameter to the constructor.



I added the aliasTable variable in the parameters of the creation of the EssbaseMetaRecordBuilder object



In the EssbaseMetaRecordBuilder class I added the aliasTable parameter to the constructor and created an instance variable called AliasTableName to store in the passed in value.



And finally updated the line of code retrieves and stores the member alias.



The classes were compiled and put back into odihapp_essbase.jar, for the non-Java people welcome back, the horror of coding is over.

It was time to go back to the designer and try the interface once more, green lights so it ran through with no problems.



Looking at the db table the correct Alias was extracted.

But does it handle the default alias.



In the interface I set the ALIAS_TABLE back to default and ran the interface again.



All good the default alias was extracted. So by updating the LKM and three Java classes my version of ODI can now extract metadata against any alias table.

If anybody is interested in obtaining the updated files then just let me know.