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

March 17th, 2010 by

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

1. ess_es_server.jar
2. ess_japi.jar
3. odihapp_common.jar
4. 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

1. 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

2. 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.

3. 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.

Picture 2

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.

Picture 3

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

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.

Picture 4

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.

Picture 5

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

Picture 6

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

Picture 8

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

Picture 12

<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.

Picture 7

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

Picture 9

Picture 10

Picture 11

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.

Comments

  1. David Says:

    Nice post, a couple of comments;

    * ODI doesn’t use an Essbase JDBC driver, its java code that interacts in the end via essmsh and maxl.
    * The RKM again is a custom java app that returns the metadata.
    * The stub driver is just to satisfy the deployment of agent connectors (data sources) as J2EE data sources in the agent. I’ll blog on this topic of a stub driver, just so its fleshed out.

    There are different reverse engineering options in OWB now in 11gR2;

    1. the Oracle data dictionary import (plus import from Designer)
    2. the Oracle SQL based CMI (Custom Metadata Import) – for example EBS,Siebel etc
    3. the JDBC based import of metadata (new in 11gR2)
    4. the SQL based CMI on JDBC for native connectivity to Apps (for example) on non-Oracle (new in 11gR2)
    5. an open API based CMI (this is for example what the Essbase one does). Just need to satisfy a Java interface

    Nice post and great enthusiasm Venkat!
    Cheers
    David

  2. Terry Says:

    Venkat,

    Great Blog. Thanks for the info.

    Its nice to know OWB11gr2 can do lots of new stuff.

    The download links are not working.

    Regards,
    Terry

  3. antonio romerom Says:

    Venkat,

    Thanks for your enthusiasm in exploring this part of OWB 11.2.

    I did want to clarify one thing, though: you write “Remember, just to use the jar files itself requires the license of ODI.”

    Using any part of the code templates/ODI-like functionality of OWB 11gR2 requires the “ODI Enterprise Edition” license. Customers license ODI-EE and get the right to use OWB with its advanced features or ODI (or even both) on a given set of target CPUs. Which one you implement is a matter of taste (or, really, use case).

    So any customer using the features you describe will have to license ODI-EE.

  4. antonio romero Says:

    Actually, Venkat, never mind my last comment– drop it. I misread your text to say that using the jar file did NOT require a license…

  5. Prat Says:

    Venkat and other experts,
    I’ve followed the steps above precisely to add essbase platform. Unfortunately when I tried pressing the Browse button to retrieve a schema it will display the following error message: “Failed. java.sql.SQLException: Not Supported: getMetaData”, and when I imported the table it will show nothing (no table/view/object). But when I connected to the same Essbase server (using similar credential and connect to the same server) via Essbase Excel Add-Ins, it will display a list of objects (measures & dimensions).
    Did I miss something? And could you please let me know how does OWB know of miv_essbase.xml (in other word where does we put the properties — or something like it — that contains string of “miv_essbase.xml”?)

    Note: I downloaded stubdriver.jar and miv_essbase.xml from this link (http://www.rittmanmead.com/wp-content/uploads/2010/03/). The version of essbase drivers was “Implementation-Version: 11.1.1.3.00 Build 196″ (from manifest.mf)

  6. QueHv Says:

    Help

    Dear Venkatakrishnan.

    I have config as guide and cant not import table from essbase to OWB with RKM.

    Current i have copy file KM_IKM SQL to File Append.xml and KM_LKM Hyperion Essbase METADATA to SQL.xml and KM_RKM Hyperion Essbase.xml to folder code template of OWB.

    Venkatakrishnan cant help me to fix problem ?

    Thank.

Website Design & Build: tymedia.co.uk