ODI Series – Essbase related bug fixes

There have been a couple of patches released lately for ODI which address issues with the essbase adaptor and I thought it was worth going through them as I have blogged about these specific problems in the past.

• Patch 10.1.3.6.7_01 contains the following fix - 9046176 - Wrong export order of members when using LKM Hyperion Essbase Metadata to SQL.A new column called sortid is added to the all the dimensions to order the records. This column is automatically populated using a counter.
  
  
• Patch 10.1.3.6.8 contains the following fix - 8826035 - Extract member's descriptions when we have more than one alias table in Hyperion.

Both patches contain updates to the knowledge module “LKM Hyperion Essbase (Metadata) to SQL” and updates to underlying Java code.

These bug fixes have not made it to ODI 11g yet but it might be possible to use the KM and Java files from the above patches (I have not tested this theory).

I will go through the wrong export order of members issue first, if you are not familiar with extracting essbase metadata using ODI then you can read an article I wrote on how to do it here.



Let’s use the measures dimension in Sample.Basic for the example, if a simple interface is created using the “LKM Hyperion Essbase (Metadata) to SQL” to extract the measures dimension then you would expect the output order of members to reflect the order in the outline.



As you can see the order is not correct, the dimension member “Measures” is the last in the order instead of being the first.

Now what I have done in the past is use a RDBMS target and add an ID column, in Oracle the ID column can be populated using a Sequence and for SQL Server an identity column property can be used.



This produces the above output.



If you reverse the output using SQL then “Measures” is outputted first though the order still is not correct.



If you were to build an outline based on that order you would get an outline like the one displayed on the left when what you want is the order of the outline on the right, basically all the levels are in the wrong order and not just the top level.

I was hoping that the patch release was going resolve the issue fully but let me go through it and see the output.

Once the patch has been applied make sure the “LKM Hyperion Essbase (Metadata) to SQL” is replaced using the import_replace option.



If the essbase database model is reversed again an extra numeric column called sortid is available within each dimension datastore.



A new interface was created to extract the measures dimension and sortid is mapped from the source to ID in the target.

To change the order of the output then a slight change to the IKM being used is required.



In my example I am using “IKM SQL to File Append” so the KM was edited and step “Insert new rows” was updated, the following code was added to the “Command on Source” -
ORDER BY <%=snpRef.getColList("","[EXPRESSION] ","","","UD1")%> DESC

This means the output will be ordered based on UD1 in the mapping of the interface.



UD1 was selected on the sortid column so the output will be ordered by this column and the interface was executed.



In my opinion the fix does not resolve the order issue fully, it has added a numeric counter to the output but the levels are still in the wrong order.

On to the second fix available in patch 10.1.3.6.8, this addresses the issue with being able to only extract the default alias table information.

I originally blogged about this issue here and went through a workaround by customizing the LKM and updating the Java API.



The patch release adds a new option in the LKM to enter which alias table to extract from; the idea is the same as what I used in my workaround so I had a quick look at the bug on “My Oracle Support”



I had a quick peak at the Java code in the new patch release and it is the same as what I wrote, so it looks my workaround has made it into official Oracle software, not that anybody in Oracle contacted me about it :)

There are a number of other essbase/planning/HFM related bug fixes in the latest patches so if you are experiencing any issues it is always worth have a look through the Readme.