Oracle Warehouse Builder 11gR2 – Importing Essbase Cubes using ODI Knowledge Modules

One of the new features introduced in OWB 11gR2 is the ability to import metadata objects through JDBC drivers. So, as long as we have an external API providing the JDBC interface to OWB, OWB should be able to leverage that and import the metadata through the API. For example, David Allan, one of the key driving people behind OWB has blogged about this extensively here for MySQL. Though i knew that we can use custom JDBC drivers, i did not know how to use them especially in the context of Essbase. As you probably know, ODI has connectivity to Essbase and can reverse engineer the metadata of Essbase as well. ODI uses Essbase JDBC drivers and a custom RKM to convert an Essbase cube in the form of relational tables and columns. The core code of the RKM exists in 4 main jar files. They are

ess_es_server.jar
ess_japi.jar
odihapp_common.jar
odihapp_essbase.jar

Out of these 4, the first 2 are essbase jdbc connectivity jar files. The remaining 2 contain the core code for reverse engineering the Essbase Metadata (RKM). This is not a straightforward JDBC interface. So, how do we leverage this within the OWB framework. This is where David Allan was extremely kind enough to get me started. To reverse engineer any metadata in OWB, through an external API requires 3 main tasks

An external Stub Driver which should be accessible to the OWB code path – This is the base for enabling custom API connectivity – The driver is available here for download
Platform Definition – Since Essbase does not exist as a platform in OWB, we need to manually define them using a set of OMB Plus scripts. There is a platform definition XML file which can be download from here.
Necessary JDBC API jar files in OWB Code path

In the first step, we have to basically copy the Stub jar file to the {ORACLE_HOME}/owb/lib/ext directory.

Once the stud driver is copied over, we need to copy the Platform definition XML file to the {ORACLE_HOME}/owb/bin/admin directory. This file will be used from our OMB Plus scripts while defining the platform.

To define the platform, run the following commands from within OMBPlus. Ensure that your working directory is {ORACLE_HOME}/owb/bin

OMBCONNECT OWB_WS_OWNER/welcome1@ORCL USE WORKSPACE 'OWB_WS_OWNER.WORKSPACE1'
OMBCREATE PLATFORM 'HYPERION_ESSBASE' SET PROPERTIES (BUSINESS_NAME) VALUES ('Hyperion Essbase')
OMBALTER PLATFORM 'HYPERION_ESSBASE' SET PROPERTIES (DRIVER_CLASS,URI_TEMPLATE) VALUES ('oracle.owb.jdbc.StubDriver','false:Accounts:Accounts:false')
OMBALTER PLATFORM 'HYPERION_ESSBASE' SET PROPERTIES (LOCAL_OBJECT_MASK) VALUES ('%SCHEMA.%OBJECT')
OMBALTER PLATFORM 'HYPERION_ESSBASE' SET  PROPERTIES (DDLNULL) VALUES ('null')
OMBALTER PLATFORM 'HYPERION_ESSBASE' SET PROPERTIES (LOCAL_OBJECT_MASK) VALUES ('%OBJECT')
OMBALTER PLATFORM 'HYPERION_ESSBASE' SET PROPERTIES (DEFAULT_MAX_NAME_LEN) VALUES ('35')
OMBALTER PLATFORM 'HYPERION_ESSBASE' SET PROPERTIES (REMOTE_OBJECT_MASK) VALUES ('%OBJECT')

OMBALTER PLATFORM 'HYPERION_ESSBASE' ADD PLATFORM_TYPE 'NUMERIC'
OMBALTER PLATFORM 'HYPERION_ESSBASE' MODIFY PLATFORM_TYPE 'NUMERIC' SET PROPERTIES(SYNTAX) VALUES ('new Integer(%precision,%scale)')
OMBALTER PLATFORM 'HYPERION_ESSBASE' MODIFY PLATFORM_TYPE 'NUMERIC' SET PROPERTIES (P1,P1MAX,P1DEFAULT,P1TYPE) VALUES ('precision','3000', '0','range')
OMBALTER PLATFORM 'HYPERION_ESSBASE' MODIFY PLATFORM_TYPE 'NUMERIC' SET PROPERTIES (P2, P2MIN, P2MAX, P2TYPE) VALUES ('scale', '0', '18', 'range')

OMBALTER PLATFORM 'HYPERION_ESSBASE' ADD FROM_PLATFORM_TYPEMAP 'NUMERIC_TOG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('NUMERIC', 'NUMERIC')
OMBALTER PLATFORM 'HYPERION_ESSBASE' ADD TO_PLATFORM_TYPEMAP 'NUMERIC_FROMG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('NUMERIC', 'NUMERIC')

OMBALTER PLATFORM 'HYPERION_ESSBASE' ADD PLATFORM_TYPE 'STRING'
OMBALTER PLATFORM 'HYPERION_ESSBASE' MODIFY PLATFORM_TYPE 'STRING' SET PROPERTIES(SYNTAX) VALUES ('new String(%size)')
OMBALTER PLATFORM 'HYPERION_ESSBASE' MODIFY PLATFORM_TYPE 'STRING' SET PROPERTIES (P1,P1MAX,P1DEFAULT,P1TYPE) VALUES ('size','3000', '1','range')
OMBALTER PLATFORM 'HYPERION_ESSBASE' ADD FROM_PLATFORM_TYPEMAP 'STRING_TOG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('STRING', 'VARCHAR')
OMBALTER PLATFORM 'HYPERION_ESSBASE' ADD TO_PLATFORM_TYPEMAP 'STRING_FROMG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('VARCHAR', 'STRING')

OMBALTER PLATFORM 'HYPERION_ESSBASE' ADD PLATFORM_TYPE 'DATE'
OMBALTER PLATFORM 'HYPERION_ESSBASE' MODIFY PLATFORM_TYPE 'DATE' SET PROPERTIES(SYNTAX) VALUES ('new Date()')
OMBALTER PLATFORM 'HYPERION_ESSBASE' ADD FROM_PLATFORM_TYPEMAP 'DATE_TOG' SET PROPERTIES (FROM_DATATYPE, TO_DATATYPE) VALUES ('DATE', 'DATE')

When you connect using OMBCONNECT, ensure that you are connecting to your workspace using the Workspace owner username/password. As you see, this basically defines certain extrinsic and intrinsic properties of the new platform that we have defined. Thanks to David Allan for providing the necessary stub driver, platform definition XML files. Without these there is no way i would have got the connectivity to work.

Once the platform is successfully defined, you should see the new platform within OWB as shown below.

So far so good. The next step is to copy the 4 ODI jar files (ess_japi.jar, ess_es_server.jar, odihapp_common.jar and odihapp_essbase.jar) from {ODI_HOME}/drivers/ directory to {ORACLE_HOME}/owb/lib/ext directory. I could not get this to work with 10.1.3.5 version of these jar files. Somehow it was always using a 9.3 version of the provider services for connecting to essbase. So, i had to upgrade to 10.1.3.6 of ODI to get this working. But David did mention the fact that it will work 10.1.3.5 version as well. Remember, just to use the jar files itself requires the license of ODI. Also, check with Oracle support on whether this is supported. Though there is no reason why this will not work, since it is not documented, i doubt Oracle Support would actually support this.

Restart the OWB client. In the new “Hyperion Essbase” platform, create a new module as shown below

In the Locations screen, enter the connection details to Essbase as shown below

The schema will be the Essbase Database name. In my case, i am connecting to the Sample->Basic cube. So Basic is the schema name. The port will be the essbase port (1423 is the default). The username/password will be the one that you will be using for connecting to Essbase. The Driver Class will be automatically populated as we had copied the Stub Driver class already. But the URL needs to be populated. the URL basically points to the options that we get while importing Essbase metadata through ODI RKM’s. In ODI, while choosing an RKM, we need to basically choose whether we want to import the measure dimension as a flat list or as a separate column. Since we are using the same RKM, the parameters will be passed through the URL as

<MultipleDataColumns>:<Account Dimension>:<AccountDimensionMembers>:<AttribMem>

In my case, i am importing the Accounts dimension as a separate column. So, my URL will look like

false:Measures:Accounts:false

In the Advanced tab, enter the Application ,database and the server names as shown below.

Once this is done, import the metadata tables in the same fashion as we do on a normal database connection. The screenshots below should give you an idea of how the Essbase metadata will be imported

As you see, OWB is now able to import Essbase metadata using the ODI RKMs. This is extremely good and shows the extensibility of OWB which to me has been the biggest downside in the prior releases. Effectively one can use this to import custom application metadata using any JDBC API. The SAP, PSFT connectors in OWB basically extend this concept by connecting through an API instead of directly to the database. The next in line is how can we use this imported Cube in a ETL process in OWB using OWB Code Templates.