This chapter describes how to work with Oracle OLAP in Oracle Data Integrator.
This chapter includes the following sections:
Section 23.5, "Creating and Reverse-Engineering an Oracle Model"
Section 23.6, "Working with Oracle OLAP KMs in Integration Interfaces"
Oracle Data Integrator (ODI) seamlessly integrates data in an Oracle OLAP. All Oracle Data Integrator features are designed to work best with the Oracle OLAP cubes, including reverse-engineering and integration interfaces.
Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to the Oracle database instance containing the Oracle OLAP cubes.
The Oracle Data Integrator Knowledge Modules for Oracle OLAP provide integration and connectivity between Oracle Data Integrator and Oracle OLAP cubes. Oracle Data Integrator is able to handle two different types of cubes with the Oracle OLAP KMs, depending on the storage mode of these cubes:
ROLAP (Relational OnLine Analytical Processing) cubes are based on a relational storage model. ROLAP cubes can handle a large amount of data and benefit all features of the relational database.
MOLAP (Multidimensional OnLine Analytical Processing) data is stored in form of multidimensional cubes. The MOLAP model provides high query performance and fast data retrieval for a limited amount of data.
Note:
Only Analytic Workspaces containing OLAP 10g Cubes are supported. Relational OLAP (ROLAP) support is limited to CWM 1 only.
The Oracle Data Integrator KMs for Oracle OLAP use mature integration methods for Oracle OLAP in order to:
Reverse-Engineer Oracle OLAP data structures (all tables used by a ROLAP or a MOLAP cube).
Integrate data in an Oracle Analytical Workspace target in incremental update mode.
Note:
The Oracle Data Integrator Oracle OLAP KMs are similar to the standard Oracle Database KMs. This chapter describes the Oracle OLAP specificities. See Chapter 2, "Oracle Database" for a description of the Oracle Database KMs.
Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 23-1 for handling Oracle OLAP data. The KMs use Oracle OLAP specific features. It is also possible to use the generic SQL KMs and Oracle Database KMs with the Oracle OLAP. See Chapter 4, "Generic SQL" and Chapter 2, "Oracle Database" for more information.
Table 23-1 Oracle OLAP Knowledge Modules
| Knowledge Module | Description |
|---|---|
|
RKM Oracle OLAP (Jython) |
Reverse-engineering knowledge module to retrieve the tables, views, columns, Primary Keys, Unique Keys and Foreign keys from Oracle Database, which are used by a ROLAP or a MOLAP Cube. This KM provides logging (Use Log & Log File Name) options. |
|
IKM Oracle AW Incremental Update |
This KM is similar to the IKM Oracle Incremental Update. It has additional options for handling MOLAP cubes. |
Make sure you have read the information in this section before you start using the Oracle OLAP Knowledge Modules:
Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.
The list of supported platforms and versions is available on Oracle Technical Network (OTN):
http://www.oracle.com/technology/products/oracle-data-integrator/index.html.
There are no connectivity requirements for using Oracle OLAP data in Oracle Data Integrator. The requirements for the Oracle Database apply also to Oracle OLAP. See Chapter 2, "Oracle Database" for more information.
The RKM Oracle OLAP (Jython) uses in addition Oracle OLAP libraries. Copy the awxml.jar and olap_api.jar from the ORACLE_HOME/olap/api/lib folder into the additional drivers folder for ODI.
There are no connectivity requirements for using Oracle OLAP data in Oracle Data Integrator. The requirements for the Oracle Database apply also to Oracle OLAP. See Chapter 2, "Oracle Database" for more information.
Setting up the Topology consists of:
This step consists in declaring in Oracle Data Integrator the data server, as well as the physical and logical schemas that store the Oracle OLAP cubes.
Create a data server for the Oracle technology as described in Section 2.3.1, "Creating an Oracle Data Server".
Create an Oracle physical schema using the standard procedure, as described in "Creating a Physical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator and associate it in a given context.
Setting up a project using the Oracle OLAP features follows the standard procedure. See "Creating an Integration Project" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
It is recommended to import the following knowledge modules into your project for getting started with Oracle OLAP:
IKM Oracle AW Incremental Update
RKM Oracle OLAP (Jython)
Import also the Oracle Database knowledge modules recommended in Chapter 2, "Oracle Database".
This section contains the following topics:
Create an Oracle Model using the standard procedure, as described in "Creating a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.
Oracle OLAP supports Customized reverse-engineering. The RKM Oracle OLAP (Jython) retrieves the metadata from the Oracle tables used by an Oracle OLAP cube.
Customized Reverse-Engineering
To perform a Customized Reverse-Engineering on Oracle OLAP, use the usual procedure, as described in "Reverse-engineering a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields specific to the Oracle technology:
In the Reverse tab of the Oracle Model, select the KM: RKM Oracle OLAP (Jython).<project name>.
Set the RKM options as follows:
MOLAP: Set to YES to reverse an Analytic Workspace. If this option is set to YES, the following options are mandatory:
AW_NAME: Indicate the name of the Analytical Workspace.
AW_URL: Specify the URL of the Analytical Workspace.
AW_OWNER: Indicate the name of the Analytical Workspace Owner.
AW_PASSWORD: Indicate the password of the Analytical Workspace Owner.
ROLAP: Set to YES to reverse tables from a ROLAP schema.
USE_LOG: Set to YES to write the log details of the reverse-engineering process into a log file.
LOG_FILE_NAME: Specify the name of the log file.
The reverse-engineering process returns the tables used by a cube as datastores. You can then use these datastores as a source or a target of your interfaces.
You can use the Oracle Data Integrator Oracle OLAP KMs as well as the standard Oracle Database KMs. The Oracle OLAP KM specific steps are detailed in the following sections.
After performing a reverse-engineering using the RKM Oracle OLAP (Jython), you can use Oracle OLAP data tables as a source of an integration interface to extract data from the Oracle OLAP database and integrate them into another system (Data warehouse, other database...). Using Oracle OLAP as a source in these conditions is identical to using an Oracle datastore as a source in an integration interface. The Generic SQL and Oracle Database KMs can be used for this purpose.
See the following chapters for more information:
After performing a reverse-engineering using the RKM Oracle OLAP (Jython), you can use Oracle ROLAP data tables as a target of an integration interface to load data from any system to the Oracle ROLAP database. Using Oracle ROLAP as a target in these conditions is identical to using an Oracle datastore as a target in an integration interface. The Generic SQL and Oracle Database KMs can be used for this purpose.
See the following chapters for more information:
Using Oracle MOLAP as a Target in an integration interface is similar to using Oracle ROLAP as a target with the difference that, in addition to the standard features of the integration process, you can refresh the MOLAP cube at the execution of the integration interface by using the IKM Oracle AW Incremental Update.
This IKM is similar to the IKM Oracle Incremental Update. See Chapter 2, "Oracle Database"for more information. It has four additional options for handling MOLAP cubes:
AW_NAME: The name of the Analytical Workspace.
AW_OWNER: The name of the Analytical Workspace owner.
CUBE_NAME: The name of the cube.
REFRESH_CUBE: Set this option to YES to refresh the cube for an Analytical Workspace.
In order to avoid refreshing the cube at every integration interface step, use the IKM Oracle AW Incremental Update with the refresh cube options only in the last integration interface of the package.
In the last integration interface set the options to refresh the cube as follows:
Set the REFRESH_CUBE option to YES.
Specify the values for the AW_OWNER, AW_NAME, and CUBE_NAME options.