Sunday, 26 October 2008

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


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.

Thursday, 23 October 2008

ODI Series - Essbase Outline Extractor

I have at last got round to moving onto using ODI with Essbase, to start with I will be looking at reverse engineering an essbase cube and then extracting an outline into flat file format and also a database table.

Before I start I strongly suggest you download and install patch or above from metalink3 as it addresses an issue with an essbase LKM.


New Features
This section should list the new/updated KMs that are delivered with this version.
The following KMs have been updated and are delivered in this version:

* LKM Hyperion Essbase METADATA to SQL

Resolved Issues


7186129: LKM Hyperion Essbase METADATA to SQL does not extract data storage member property correctly.

If this is the first time you have read one of my blogs on ODI I would advise reading Series 1 and Series 3 as with this blog I am going to assume you have the required essbase KM’s and have set up in the topology manager a connection to the Sample/Basic essbase database. and

I also suggest if you have applied the patch to import the essbase technology again in the topology manager as it has been updated.

In the developer the first thing we want to do is import the various Knowledge Modules we require these should be in the impexp directory of the ODI installation:-

  • IKM SQL to Hyperion Essbase (DATA)
  • IKM SQL to Hyperion Essbase (METADATA)
  • LKM Hyperion Essbase DATA to SQL
  • LKM Hyperion Essbase METADATA to SQL
  • RKM Hyperion Essbase

Once that has been done I moved on to the models tab and inserted a new directory – ESSBASE_MODELS and inserted a new model using the essbase technology and logical schema that was set up in the topology manager.

In the reverse tab customized was chosen, the context selected and the KM set to RKM Hyperion Essbase

I have kept all the default options and will discuss them at a later stage when looking at data.

After reversing it produces the following DataStores.

Today’s example I will be extracting the Measures dimension but it is will be pretty much similar whatever dimension you choose.

Expanding the Measures columns displays all the available properties for each member.

The first extraction I want to run is to a flat file so I created a quick csv template with the column headings to match the ones above.

I created the second line with alphabetical values to match the number of columns, I have done this so when I reverse the flat file it reads in the column headings and sets them to being a string type, which saves having to enter them manually.

The file is saved to the same location as was set up in the topology manager in Series 3 of the ODI blogs.

I created a new model to hold any flat file DataStores that I will be using with essbase.

And then set up a new DataStore to point to the csv just created.

In the Files tab delimited was set as the file format, heading was set to 1and the field separator as comma “,”

As I set up the csv file earlier the reversing sets up all the correct columns, one thing to watch for is the physical/logical length is defaulted to 50, in the essbase reversed DataStore everything is set to 80 except the formula column which is set to 255, I replicated this just to stop any interface warnings and to make the extract will not fail, for example if you didn’t change the default and say you had a member name that was over 50 in length the extract would fail.

We can now create the interface to do the extracting as we have the source and target DataStores in place.

In the diagram tab the Measures DataStore was dragged onto the source and the file onto the target, due to the column headings being the same between the source and target everything was auto mapped.

In the Flow tab the LKM should be set to “LKM Hyperion Essbase METADATA to SQL”.

In the LKM Options there are a couple of settings to take note of :-

MEMBER_FILTER_VALUE :- This can be set as member name or a UDA value (if UDA is selected in the filter_criteria), if no value is entered which is the default then the dimension member is used.

MEMBER_FILTER_CRITERIA :- There are number of options available which are used against the filter_value, most of them are self-explanatory but can be very useful.

I stick with the defaults that will bring back everything in the hierarchy.

The IKM should be SQL to File Append, the only option in the IKM was I set TRUNCATE to Yes as I want the file to be cleared out each time.

Once the interface had been applied it could be executed

All green in the Operator so it should have extracted

And there we have it the measures dimension has been extracted, the only grudge I have is that the output is the reverse of how I would like it, I would prefer the root member to be first and the hierarchy extracts there down, something I am going to look at when I have a bit of spare time.

Extracting an outline to a database table is pretty much the same as the flat file, except you swap the technology to either SQL server or another database engine you are using such as Oracle.

First I created a SQL server table called Measures in a database named HYPDATA with columns to match the reversed essbase Measures dimension

In a previous blog I had created a physical schema in the topology manager pointing to the HYPDATA table using SQL Server technology

If you need the exact steps have a look back at ODI series part 3

In the designer I created a new model to point to the SQL server database

In the Reverse tab I selected the context; in the Selective Reverse tab I just selected the Measures table
, to display the tables you need to check the "objects to reverse" box

On clicking reverse the table was reversed engineered

So like with the flat file previously a new interface is created to move the metadata from the source DataStore to the target DataStore.

In the Diagram tab the Measures essbase DataStore was dragged to the source and the Measures Db DataStore to the target, as the source and target naming conventions are the same all the columns are automapped.

In the flow tab the LKM is set to LKM Hyperion Essbase METADATA to SQL

This time I am going to extract all the level 0 members so in the options I set MEMBER_FILTER_CRITERIA to Level0

The IKM was set to IKM SQL to SQL Append (if you don’t have this IKM then you will need to import it in from the oracledi\impexp directory, in the options I set DELETE_ALL to Yes so the table will be cleared out each time.

Running the Interface produced a green tick in the Operator and checking the database table produced the desired results

Well I have run out of time for today but it should be clear how you can extract an essbase dimension to various targets with relative ease.