Business Intelligence – Oracle

Archive for January 4th, 2009

Oracle Data Integrator and Oracle OLAP – RKM and IKM – Using Oracle OLAP as a Source – Part 1

Posted by Venkatakrishnan J on January 4, 2009

As you might probably be aware, the latest version of ODI also the prior release) supports a new set of KM’s which can extract and load data into Oracle OLAP Cubes. We shall try to understand how this connectivity works in this release. There are 2 aspects to this connectivity. They are

1. Using Oracle OLAP as a data source – This is supported by a Reverse Knowledge Module called “RKM Oracle Olap (Jython)”.
2. Incremental data load into Oracle OLAP – This is supported by a Integrating Knowledge Module called “IKM Oracle AW Incremental Update”

Both the above KM’s are available in the default install of ODI. One would have to manually import the KM’s from the {ODI_HOME}\oracledi\impexp directory. We shall be using the Global Analytic Workspace for all our testing. This Global AW contains 4 dimensions

1. Channels Dimension – Containing one normal Level Based Hierarchy
2. Customer Dimension – Containing one normal Level Based Hierarchy
3. Product Dimension – Containing a value based hierarchy
4. Time Dimension – Containing the normal level based fiscal time hierarchy

Also this AW contains 2 cubes, one having one measure each. The below screenshots would give you an understanding on how my AW is structured.

To use this AW as our data source in ODI, we need to create the physical connection in the Topology Manager of ODI.

Once the physical connection is created, lets create a logical connection to denote the usage of this connection as a source.

Also tie the physical and logical connections together by means of the Global context as shown above. This will ensure that the logical connection will use the physical connection defined above. Once this is done, let us login to Designer and import all the out of the box KMs provided by ODI.

As a next step lets create a model and import this AW using the RKW imported above. While creating the model, we would have to use Oracle as the Technology and then assign the Logical connection that was created above.

And in the Reverse tab, choose the context and use the customized option as shown below.

Ensure that you are choosing the RKM properly in the dropdown. Also, enter all the the Analytic Workspace related connection details as shown above. Now for the Reversing operation to work there are 2 jar files that are needed. They are

1. olap_api.jar
2. awxml.jar

These 2 files need to be copied from the Oracle database home i.e Copy the 2 files from {ORACLE_HOME}\olap\api\lib to {ODI_HOME}\oracledi\drivers. The documentation states that awxml.jar alone is sufficient. But i was not able to get the Reverse functionality to work with just that single jar. Only after i copied over the olap_api.jar file over to the drivers directory, the reverse started to work. Once the files are copied over, a restart of the designer is required. After the designer restart, just click on reverse from the Model that we created. The reversing operation is in itself treated as an execution and can be tracked using the Operator.

Once the reverse operation is successful, we should be having the imported objects in the Model as shown below.

If you observe closely, what we have basically obtained is not actually the OLAP object itself. Instead, what the RKM has done, is that it has used the XML API (using awxml.jar) of the Global AW to reverse engineer all the dimension and fact tables from the mappings defined in the AW. If you see, we have obtained 3 dimension tables and 2 fact tables. Also, one dimension table is missing which is the value based product dimension. I believe the current version of RKM does not support reverse engineering Value based hierarchies. So, the question now is if we incorporate these imported dimension/fact tables into our interface, will we be extracting data out of the cube or from the fact tables? Unfortunately, i believe the current version does not support direct extract of data from OLAP using these 2 KM’s (if data is required directly from the cubes one would have to create custom SQL Views on top of the AW and then use them in the interface). To test this out, lets create a sample dummy fact table with no rows as shown below


We shall be using the above table as our target in the interface. For the source, we shall use the imported UNITS_FACT table. The screenshots of the interface is shown below

As you see, we are treating this as a normal oracle table in our interface. So, when we execute this interface the data extract would happen only from the underlying fact tables instead of the Cube AGGMAPs. I was expecting a bit more advanced form of the KMs to support Oracle OLAP natively. But considering the fact that the cube level data can be exposed through SQL Views this is not much of a hinderance as it is possible to use them to extract data directly from the cube. Neverthless, it would be interesting to see what the Incremental Update IKM does. My hunch is that this will support data load into the fact tables and in turn will kick start the cube aggregation. We shall get into more details on the AW data load using ODI in a future blog entry.

Posted in All Posts, OLAP, Oracle Data Integrator | 2 Comments »