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.