Sunday, 13 December 2009

ODI – Patch Update –

I have been waiting a while for a patch to resolve an issue that I logged with Oracle, the problem occurs when loading data into essbase and error records are produced, the record where the rejected record occurs is logged but not the member, instead you are given an error 3303 in the logs.

This goes back to a patch that fixed the original issue when loading data to essbase, the original problem was when an error record was hit then the adaptor would start loading record by record instead of in chunks. I documented the error in this blog , the patch to fix this was when documented in the following blog and finally the error 3303 issue was logged in this blog.

So with ODI and prior versions if your load encounters a rejection the adaptor will go into record by record load mode. ONE-OFF PATCH resolved this issue but introduced the error record 3303 issue.
Now Patch 9200535: ORACLE DATA INTEGRATOR CUMULATIVE PATCH has been released and should fix the logging issue.

The patch is cumulative patch so as long as you have it will include all patch updates up to and including

This patch actually includes a number of fixes relating to ODI Hyperion adaptors so it is well worth looking into applying.

Here are the Hyperion related fixes.

8912703 - When an Essbase member is rejected due to error "3303: Member not found in database" the log does not indicate which member caused the record to be rejected.

This fix I will test out shortly

8713986 - Consolidation attribute "^" (Never Consolidate - Added in Essbase 9.3.1) is not handled in the Essbase KM. The Extract Metadata step fails with error "No_matching_Enum_found".

I go through this and test out shortly.

9080483 - Essbase Interface fails with "ImportError: No module named hyperion" on the "Prepare for loading step" when loading ASO/BSO data into Essbase.

This issue I have only come across in the forums and I thought it was due to drivers not being picked up in the classpath, maybe it is a different issue, if anybody has this problem and it can be easily recreated then let me know I will document it.

8529169 - "Class not registered" error while loading metadata into Hyperion Financial Management classic application using an 64-Bit HFM Client.
A new 64-bit driver (HFMDriver64.dll) has been added to the /oracledi/drivers directory. To use this driver on a 64-Bit platform with a 64-Bit HFM Client installed, rename HFMDriver.dll as HFMDriver32.dll and rename HFMDriver64.dll as HFMDriver.dll.

Installing the patch is very straightforward, first make sure you

Perform a backup of the Oracle Data Integrator installation directory.
Perform a backup of the Master and Work Repositories.

Close all ODI components e.g. Topology Manager, Designer, Operator, Security Manager. Stop any agents running on the machine where you are going to install the patch.

Next uncompress the patch file and copy the content of the oracledi directory over the top of your ODI installation, overwriting any existing files.

Open the designer, go to Help > About Oracle Data Integrator. The patch version should be displayed.

The knowledge module “IKM SQL to Hyperion Essbase (DATA) has also been updated, so you will need to import the KM replacing the original one.

If you right click your original KM and select “Import Replace..”

If you just use the standard import for a KM then it will import it in duplication mode and just create another KM.

So lets have a look and see if the patch fixes the error 3303 problem.

I have created a very simple interface to load data from a text file into essbase sample.basic

In the load file I have added some members that do not exist in the essbase database.

In the market dimension there are two members in the load file “Florida1” & “Florida2” that should error out in the load.

Please not make sure you are using a load rule when loading data otherwise it will use the original method to load the data and when it encounters a rejected record it will revert to loading record by record.

Good news, in the log the 3303 error message now contains the member that was rejected.

Now on to one of the other fixes 8713986 - Consolidation attribute "^" (Never Consolidate - Added in Essbase 9.3.1) is not handled in the Essbase KM. The Extract Metadata step fails with error "No_matching_Enum_found".

Prior to this fix say the following member in your hierarchy.

The member is using the never consolidate across all dimensions operator ^
If you have an interface to extract metadata from an essbase database outline that contains the operator then the interface would fail.

The error would be “No_matching_Enum_found”
After applying the latest patch and running the same interface.

The interface runs successfully

The member is extracted with the correct consolidator operator.

Sunday, 15 November 2009

ODI Series - Extracting essbase formula issue

Just a quick blog from me today. I was contemplating writing a useless blog having a go at companies I know nothing about for absolutely no reason but I decided there is more to life and I am going to answer a question asked on the otn forums, which hopefully will do more good than bad.

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.

Sunday, 1 November 2009

ODI Series – Planning enhancements continued

In the last blog (a while ago I know, my spare time is extremely limited lately) I highlighted a few of the new enhancements available with, today I am going to continue with the new functionally and go through an area that I know has had a lot of questions raised in the past.

There have been numerous times I have been asked whether the process for setting the opening and closing year/periods in the scenario dimension can be automated, until now you have been pretty restricted and you either have had to manually change them through planning web of write custom SQL that basically updates the underlying table, this method still requires a planning restart because of the caching system planning adheres to and you can certainly feel uneasy about directly altering the tables.

Just so nobody pulls me up you have been able to use the outline load utility to set the year/periods since the release of V11 but is the first release to let you do this via ODI.

If you reverse a planning application you will notice that there is a column to be able to update everything in the scenario dimension.

The easiest method to control the process of updating the start/end year/periods would be to create a flat file with the information you want to update.

Then in the usual way build your interface to update the information in the planning application.

Nice and simple update, though still requires you to populate the flat file (you could build an ODI process that will create and populate the file based on date logic for your system)

Another way to automate the process could be to extract the year/period from your data and update the scenario member based on it.

As an example say you wanted to base the Start/End Year using the Year column from the data and the Start/End periods based on the min and max periods.

With a combination of functions the first month and last month is calculated, this example is based on Jan-Dec calendar, if the calendar year is Apr-Mar or different then a function could be created to calculate first/last months in the data.

The Distinct Rows checkbox is ticked in the Flow so this should only return one record from the data.

If you want to make sure the SQL query is retrieving the desired results then execute the interface, go into the operator and extract the SQL.

Then run the code using your SQL GUI (in my case SQL Developer)

Results are as expected so the planning application should updated correctly.

These methods are fine and have their place but the majority of times the question has been raised about automating the process it has involved the use of substitution variables, many planning applications that are already in place can rely heavily on essbase substitution variables, the process for setting their value has already been automated and it is possible the variables that exist could be used to set the opening/closures values in planning.

So how can we use ODI to transfer the values of the essbase sub vars to planning, well one method could be to create an ODI procedure that uses an OS command to fire off MAXL, the MAXL could use the command “DISPLAY VARIABLE” and this could be spooled to a file, once the file is generated ODI could read in the file, transform the text and send it into planning. Is it certainly a feasible method but I find it a little messy.

How about using the essbase JAVA API, the API is installed and accessible with the default install of ODI so why not take advantage of this.

Extracting variable values using the API is easy so don’t run away just yet.

Before I rush into how to go about using the API I need to explain the solution I am aiming for so hopefully you won’t get totally lost.

Lets take a look at the interface to set the scenario information

So I want to use the interface but populate the target information with a mixture of substitution variables and hard coding, by populating all the target information I will not need a source, this will need some customization but I will go through that shortly.

I am going to retrieve values from substitution variables CurrYear and this will define the Start Year in the interface, CurrPeriod and this will define the Start Period. I am going to set the End Year the Same as the Start Year and in this example the End Period is going to be hard coded as December but could easily be a sub var. I am also going to set it against scenario member Plan.

Ok, lets look at a quick example of using the essbase java API to retrieve a substitution variable value.

Basically all the code does is log into an essbase server (EPMV11) and retrieve substitution variable valus for CurrYear/CurrPeriod and then print their values. It doesn’t matter if you don’t understand it totally you just need to know what it is achieving.

Here is the text version so if you do want to try this exercise it will save you having to manually entering it.

import com.essbase.api.base.EssException;
import com.essbase.api.datasource.IEssOlapServer;

import com.essbase.api.domain.IEssDomain;

import com.essbase.api.session.IEssbase;

public class EssTest {

private static String s_userName = "admin";

private static String s_password = "password";

private static String s_olapSvrName = "EPMV11";

private static String s_provider = "Embedded";

public static void main(String[] args) {
IEssbase ess = null

try {
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);

IEssOlapServer olapSvr = dom.getOlapServer(s_olapSvrName);


String sYearValue = olapSvr.getSubstitutionVariableValue("currYear");

String sPerValue = olapSvr.getSubstitutionVariableValue("currPeriod");

System.out.println("Current Year=" + sYearValue + " Current Period=" + sPerValue);


} catch (EssException e) {
System.err.println("Error: " + e.getMessage());




So how do we get this into ODI in a useable format? Well when you run an interface to load metadata into planning it uses the “IKM SQL To Hyperion Planning”, the KM goes through a number of steps to set up variables, connect to planning, and then load data into the underlying repository tables. So we need to customize the existing KM to incorporate the substitution variables.

First duplicate the Planning IKM so you can still keep you original IKM and not affect any of your existing interfaces.

Edit the duplicated KM and rename it to something meaningful

Click on the Details tab and you will see each stage of the IKM.

What we need to do is add a new step to the IKM, this step will log into essbase and return the values of the substitution variables specified, these values will be stored in Java variables that will be able to be used in the Interface.

Once the add new command icon is clicked a name is given for the step and as I am going to be using Java the technology is set to “Java BeanShell”

The Java code is then copied into the command window.

Now I removed the references to the class and removed the main as these are not required, I just want all the code be executed. Also the code is surrounded by <@ …. @>

So this is how the code looks

import com.essbase.api.base.EssException;
import com.essbase.api.datasource.IEssOlapServer;
import com.essbase.api.domain.IEssDomain;
import com.essbase.api.session.IEssbase;

private static String s_userName = "admin";
private static String s_password = "password";
private static String s_olapSvrName = "EPMV11";
private static String s_provider = "Embedded";

IEssbase ess = null;

try {
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
IEssOlapServer olapSvr = dom.getOlapServer(s_olapSvrName);
String sYearValue = olapSvr.getSubstitutionVariableValue("currYear");
String sPerValue = olapSvr.getSubstitutionVariableValue("currPeriod");
System.out.println("Current Year=" + sYearValue + " Current Period=" + sPerValue);

} catch (EssException e) {
System.err.println("Error: " + e.getMessage());

If this step were to be run in the IKM in its current state it would just basically connect to essbase and retrieve the sub values and then write them to the agent log.

Not much use yet then, also there is a lot of hard coding such as the admin/password/server and substitution variable names. If these ever changed then the step in the IKM would have to be edited.

What I want to do is pick up admin/password and server name using ODI API commands.

Now there is a very useful ODI API command odiRef.getInfo(“PARAMETER”), this command can retrieve many different values depending on the PARAMETER used, to see the full list have a look in the ODI API documentation

The parameters I am going to use are :-


This will retrieve the user for the target datastore, so in this example it will be the username to connect to the planning application that is being used in the interface.


This will retrieve the password for the target datastore ( planning application)

Now I am doing using a little bit assumption here, I assuming that the planning application will have the same log account as the essbase server, I think in most circumstances it will have the same provisioning rights in Shared Services, if not the account could easily be provisioned to do so or just leave the code as it is.

This will retrieve the destination server name, this is another assumption that the essbase server is on the same server as planning, this is probably never really the case so the code could be left as it is or an Option created ( will be covering that in a minute )
In my example they are on the same server so I am going to stick with it as it gives you a feel of what can be achieved.

For the substitution variable names I am going to create new options in the IKM.

Right click the KM and select insert Option to create a new Option

So I have created a new option that will hold the substitution variable name that is going to be retrieved and be used for the Start Year. I have given it a default value of CurrYear.

I repeated the process to create an option that will be used for retrieving the Start Period (CurrPeriod) substitution variable.

This time the default value is CurrPeriod. As I pointed out before if your essbase server is on a different server than planning you could create another option to set your essbase server.

If you go into the interface and flow area, once you set the IKM to the newly customized one you will notice you now have the new options, the options can easily be changed altered to match the sub var names, this is useful as you could have different interfaces that require to use different sub vars.

Right back into the KM step that we created before with the Java code, it now needs updating to use the ODI API commands described earlier instead of the hard coding of values.

I had to enclose the username and servername with <%=….%> to retrieve the values correctly otherwise I kept getting errors saying the properties didn’t exist.

So that is the username/password/server name taken care of, I know need to retrieve the options and this can be done with another ODI API command - odiRef.getOption("OPTION_NAME")

If you are using the server name as an option then you can just change the code to use .getOption instead of .getInfo

As I am picking up the target server name to be used as the essbase server I had to add a little extra code, this is because it is a planning connection so the destination server will include the RMI port e.g. EPMV11:11333 and I only want the server name.

The code just splits the string into an array of strings where it encounters “:”
So it will end up with an array of two strings, one holding server name (EPMV11) and the other holding the RMI Port (11333)
I just set the variable to pick the first array value (EPMV11) so now I have the server name minus the RMI Port.

Right just one more addition to the code, if the script fails in any way such as unable to connect to essbase it will only output the error to the log, this will not cause the interface to fail and it definitely needs to stop.

So I just added code “throw new Exception(“…. This will raise an error and cause the step to fail.

The final code is :-

import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.IEssOlapServer;
import com.essbase.api.domain.*;

String s_userName = "<%=odiRef.getInfo("DEST_USER_NAME")%>";
String s_password = odiRef.getInfo("DEST_PASS");
String olapSvrName = "<%=odiRef.getInfo("DEST_DSERV_NAME")%>";

String sYearVar = "<%=odiRef.getOption("VAR_START_YEAR")%>";
String sPerVar = "<%=odiRef.getOption("VAR_START_PERIOD")%>";
String s_provider = "Embedded";
String sYearVal;

String[] s_Split = olapSvrName.split(":");
olapSvrName = s_Split[0];

IEssbase ess = null;

try {
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
IEssOlapServer olapSvr = dom.getOlapServer(olapSvrName);
sYearValue = olapSvr.getSubstitutionVariableValue(sYearVar);
sPerValue = olapSvr.getSubstitutionVariableValue(sPerVar);

System.out.println("Current Year=" + sYearVaue + " Current Period=" + sPerValue);


} catch (EssException e) {
System.err.println("Error: " + e.getMessage());
throw new Exception("Error: " + e.getMessage());


Once the step in the IKM is saved it will be put the command at the end.

I just moved the command to be the first step.

Just to recap if the interface is run in its current state using the customized IKM it will retrieve the two substitution values, store them in variables and print them to the log. What we need to do now is use the stored variables in the target section of the interface.

Enclosing the mapping in <@ code @> ensures you can call Java code.

Basically I have just set Start Year to be the value stored in the Java variable sYearValue, End Year is the same as Start Year (it doesn’t have to be this way you can easily add your own logic into it. Start Period is set to the value stored in the Java variable sPerValue and finally I hard coded the End Period to be ‘Dec’

I could just hard code the Scenario in as well but I decided to add another option to the IKM.

The ODI API command getOption is used again to retrieve the value stored against the option.

If you look at the interface you will notice I have nothing in the source area, this is because I don’t need to use the source to retrieve any information though if you try to run the interface as it stands you will receive an error.

If you look at the SQL that is being generated it is failing because there is no table name, the IKM by default will try and generate a table name depending on your source/staging area table name, as there is no source it can’t generate a table name.

The ODI API command getFrom() is used in the “Load data into planning” step in the IKM, this command generates the table name.

To get around this we need to give it a dummy table name, I am using Oracle in this example so I can use the DUAL table.

I could just easily replace the getFrom() command with DUAL but as I am going to once again create another option in the KM so I can set whether I want to use the getFrom or DUAL at the option level of the KM.

I am going to keep the default as the standard method of generating the table name, now I have to update the “Load SQL to Planning” step in the IKM

I have used the getOption command again to retrieve the value from the option I just created called “FROM_TABLE”

In the flow section of the interface I can now set how I want to use the option, in this case I have changed it from the default value to use DUAL

Running the interface again will now generate the correct SQL using the DUAL table name.

If you are using SQL Server then you can easily update the KM, you wouldn’t need the “from” or a table name in the sql e.g. select ‘Plan’ “Scenario”

All green in the operator now and you can see the additional step “RETRIEVE_SUB_VAR_VALUES”

So there we go planning has been updated based on a combination of essbase substitution variables and KM options.

Say you wanted to load the same values into more than one scenario member for instance Current & Forecast, this can easily be done as well all you would need is a source with the members in.

Then just map the target datastore to the source.

Remember to set the “FROM_TABLE” option back to the default as you need to retrieve data from a table this time.

Executing the interface should populate the scenario members Current & Forecast with the same substitution variable values

I am going to leave it now for today. I hope you have managed to follow and understand this blog, it should have given you an insight to what can be achieved with setting scenario member properties and how to integrate this with substitution variables, maybe it has given you some ideas to work on for your own projects.