Oracle Warehouse Builder 11gR2 - Using Code Templates - Loading Metadata from Essbase to Relational Tables

In the first part of this series here, i had shown how to go about using the ODI RKM's from within Warehouse Builder to reverse engineer Essbase metadata. Though i did promise to come back sooner on the rest of the series, a client engagement completely held me away from doing this earlier. Better late than never,i guess. Now that we know how to reverse engineer Essbase Metadata within OWB, lets look at a way of using the Essbase Integration Knowledge Modules for loading Essbase Hierarchies to relational tables.

The first step in this process is to ensure that we have the Essbase Metadata properly reverse engineered. Once that is done, the next step is to copy the Essbase specific ODI Knowledge Modules from {ODI_HOME}/impexp folder to {ORACLE_HOME}/owb/misc/CodeTemplates directory

After we have copied the knowledge modules over to OWB, we need to import them by creating a new Code Template Module as shown below. For more details on Code Templates refer Mark's blog entry on this before here

Once we have imported all the knowledge modules, we need to ensure that the Control Center Agent is started correctly. Control Center Agent basically spawns up a RMI instance (part of OC4J) through which all executions will be done

Configure the DEFAULT_AGENT within OWB to point to this Control Center Agent

After configuring the Agent, we need to deploy all the Code Templates(KM’s). This step is not necessary, but will help in actually testing whether the agent is setup correctly or not.

The steps listed above are basically pre-requisites to get us started on the actual deployment. Now let us start with a very simple example of loading the Sample->Basic cube's Year dimension into a database table. To keep this simple, lets create a database table with exactly the same structure as the reversed Year dimension

CREATE TABLE YEAR_PC
(
PARENTNAME VARCHAR2(80),
MEMBERNAME VARCHAR2(80),
ALIAS VARCHAR2(80),
DATASTORAGE VARCHAR2(80),
TWOPASSCALC VARCHAR2(80),
CONSOLIDATION VARCHAR2(80),
UDA VARCHAR2(80),
FORMULA VARCHAR2(255),
COMMENTS VARCHAR2(80)
)

After the creation of the database table, we start with creating a new "Template Mapping" where we can map the source (Reversed Year Dimension) and the target (table created above). In the execution view of this Template mapping, for the Essbase source use the imported "Hyperion Essbase to SQL" code template. For the database table, use the "SQL to Control Append" code template.

If you notice, when we drag and drop the Essbase Year Dimension into the mapping editor, we lose the case sensitivity of the columns. This is shown below. The ODI Knowledge Modules are case-sensitive and hence if you execute this, you will start getting errors. To work around this, we will have to update the Knowledge Modules as shown below. The credit for this goes to David Allan of the OWB team again. Basically your code templates should look as shown below (updated part of the code template)

Now just deploy the mapping and start executing the Template Mapping. This will load the entire Year Hierarchy from Essbase to a database table as shown below

This is pretty neat and quite simple as well once we start getting a hang of the pieces involved. The trickiest part was in updating the Code Template itself which is where David helped me out. I was actually expecting a lot of changes to the KM's to make it work with OWB at-least for Essbase related loads. I was pleasantly surprised to see everything working almost out of the box. As i have said before, i think this capability of OWB is extremely good and efficient as well, atleast as far as i have tested. Next in this series is data loads into and from Essbase.