Business Intelligence – Oracle

Oracle Data Integrator 10.1.3.5 – Loading Data into Hyperion Essbase Cubes from Relational Data Sources – Using Rule Files

Posted by Venkatakrishnan J on February 13, 2009

I was on a call yesterday with an Oracle partner who was developing a custom data loading solution(for a complex reporting system involving Essbase, Oracle OLAP and Oracle 10g) for one of his customers. One of the issues that they were facing was in using ODI to do data loads into Hyperion Essbase. Before i proceed further, i would like to point out John’s article series here which has the details on how to go about using ODI for loading into Essbase. The issue faced by the partner was, they were not comfortable in using JAPI provided by ODI as they felt that the data loads could be much faster using traditional approaches. They were actually used to loading data through rule files using the SQL Interface. And they were of the opinion that SQL Interface was performing better than the JAPI. Though the conclusion arrived at was not correct, as there were no quantifying facts/metrics stating JAPI load was slower than the SQL Interface, this question made sense as the partner was more or less new to ODI and was not comfortable in using JAPI for data loads. But one of the features that they liked about the ODI and Essbase integration was the ability to define Essbase connections which offered them the flexibility of doing every data load using the ODI interfaces. The partner basically wanted to know of a way where an ODI interface could be created with no mappings to the source and all the interface should do is to call a Rule file through the SQL Interface and load the data. I thought i would blog about this since this is more or less a common requirement considering the fact that many Essbase customers use the SQL interface rather than the JAPI. First let us start with creating a physical schema using the Hyperion Essbase Technology.

       

The next step is to create a logical schema and establish a relationship between the physical and logical connections using a context.

       

Now, lets jump over to the designer and create a model. While importing the objects choose customized reverse and enter the relevant details as shown below.

       

       

       

Now, the next step is to create a custom project (or use an existing project). This project should have all the Knowledge Modules. Now duplicate the “IKM SQL to Hyperion Essbase (DATA)” and give it a custom name “IKM Essbase Data Load using Rule Files”.

       

And in the custom tab, remove the steps “Load Data Into Essbase” and “Report Statistics” from the IKM.

       

The reason for removing these 2 steps is to ensure that the IKM does not use JAPI to do the data loads. Now, lets create a new interface and include the fact table related to the Global cube as target into the Interface. Ensure that the staging area is SUNOPSIS_MEMORY_ENGINE.

       

       

Now go to the flow control and choose the custom IKM that we create above. Since we have removed 2 steps, Load Data into Essbase and Report Statistics, the not all the options that you see for the IKM are relevant anymore. The only options that are relevant are given below

       

Now, in order to use the SQL interface to load the rule files, create a maxL script as shown below and save it as GlobalUnitsLoad.maxL

spool on to 'D:\GlobalUnitsLoad.log';
import database 'Global'.'Global' data connect as 'global' identified by 'global' using server rules_file 'UnitsLd' on error write to 'D:\GlobalUnitsLoad.err';

As you see the maxL script basically spools the log out to a specific file. And also, it calls the rules file to do a data load. When this data load happens, it would be through the sql interface. Now, come back to the Interface and in the options for the IKM enter the below details. The maxL script would be called through the PRE_LOAD_MAXL_SCRIPT option.

       

Now save and run this interface.

       

Now, you should be able to bypass the JAPIs to do your data loads. This is not always the recommended option. But neverthless this is something that would be wanted by everyone who is new to ODI and who is used to loading rule files through the sql interface.

4 Responses to “Oracle Data Integrator 10.1.3.5 – Loading Data into Hyperion Essbase Cubes from Relational Data Sources – Using Rule Files”

  1. BASH said

    Hi Venkatakrishnan,
    Your blog is great. Your topics are very useful. Your explanations make sense and they are very easy to follow. Thank you !

    We’re loading over 6 million records into an Essbase ASO cube. The load is fast at under 2 minutes. However, getting the data out of the Relational server and over to the Essbase server is killing us; that part takes over 2 hours. In addition to optimizing our query as a stored procedure, would you recommend ODI as the fastest way to get the data from our data warehouse into an Essbase ASO cube ? Any other suggestions about getting this done in lightning speed?

    Thank you.

  2. kalyan said

    Hi
    This is kalyan,I am working as a software Engineer in a small company hyderabad.
    Few days back I got a chance to work on Hyperion Essbase, from that time I am not able to find any PDF or step by step guide to work on that tool.

    I hope you understand my problem and hope to suggest me.

    Regars
    kalyan chakravarthy.k

  3. Ahmad said

    Hi ,

    Nice Blog special for beginner like me .
    on one of our client they have oracle EBS R12 and Hyperion System 9 and client want to load data in such a way that he can schedule uploading like backup in night etc.

    you are aware of any tool / script by using which we can achieve this functionality.

    Thanks,
    Ahmad

  4. Reid Lai said

    Hi Venkatakrishnan,

    When I followed your instruction to populate date from Oracle DB to Essbase, I found there Flow tab was disabled until I drag source data model into Interface Diagram. I also tried to map loading and integration knowledge module in order to execute the interface but can’t find appropriate KM from the list box. Do you have any hint? Thanks.

    Cheers,

    Reid

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: