18 Oracle Business Intelligence Enterprise Edition

This chapter describes how to work with Oracle Business Intelligence Enterprise Edition in Oracle Data Integrator.

This chapter includes the following sections:

18.1 Introduction

Oracle Data Integrator (ODI) seamlessly integrates data from Oracle Business Intelligence Enterprise Edition (Oracle BI).

Oracle Data Integrator provides specific methods for reverse-engineering and extracting data from ADF View Objects (ADF-VOs) via the Oracle BI Physical Layer using integration interfaces.

18.1.1 Concepts

The Oracle Business Intelligence Enterprise Edition concepts map the Oracle Data Integrator concepts as follows: An Oracle BI Server corresponds to a data server in Oracle Data Integrator. Within this server, a catalog/owner pair maps to an Oracle Data Integrator physical schema.

Oracle Data Integrator connects to this server to access, via a bypass connection pool, the physical sources that support ADF View Objects.

Oracle Data Integrator uses Java Database Connectivity (JDBC) to connect to an Oracle BI Server.

18.1.2 Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 18-1 for handling Oracle BI data. These KMs use Oracle BI specific features.

Table 18-1 Oracle BI Knowledge Modules

Knowledge Module Description

RKM Oracle BI (Jython)

Retrieves the table structure in Oracle BI (columns and primary keys).

LKM Oracle BI to Oracle (DBLink)

Loads data from an Oracle BI source to an Oracle database area using dblinks.

LKM Oracle BI to SQL

Loads data from an Oracle BI source to any ANSI SQL-92 compliant database.

IKM Oracle BI to SQL Append

Integrates data into a ANSI-SQL92 target database from an Oracle BI source.


18.2 Installation and Configuration

Make sure you have read the information in this section before you start using the Oracle BI Knowledge Modules:

18.2.1 System Requirements and Certifications

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.

18.2.2 Technology Specific Requirements

There are no technology-specific requirements for using Oracle BI in Oracle Data Integrator.

18.2.3 Connectivity Requirements

This section lists the requirements for connecting to an Oracle BI Server.

JDBC Driver

Oracle Data Integrator uses the Oracle BI native driver to connect to the Oracle BI Server. This driver must be installed in your Oracle Data Integrator drivers directory.

Bypass Connection Pool

In Oracle BI, a sqlbypass database connection must be setup to bypass the ADF layer and directly fetch data from the underlying database. The name of this connection pool is required for creating the Oracle BI data server in Oracle Data Integrator.

18.3 Setting up the Topology

Setting up the Topology consists of:

  1. Creating an Oracle BI Data Server

  2. Creating an Oracle BI Physical Schema

18.3.1 Creating an Oracle BI Data Server

A data server corresponds to a Oracle BI Server. Oracle Data Integrator connects to this server to access, via a bypass connection pool, the physical sources that support ADF View Objects. These physical objects are located under the view objects that are exposed in this server. This server is connected with a user who has access to several catalogs/schemas. Catalog/schemas pairs correspond to the physical schemas that are created under the data server.

18.3.1.1 Creation of the Data Server

Create a data server for the Oracle BI technology using the standard procedure, as described in "Creating a Data Server" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator. This section details only the fields required or specific for defining a Oracle BI data server:

  1. In the Definition tab:

    • Name: Name of the data server that will appear in Oracle Data Integrator

    • Server: Leave this field empty.

    • User/Password: Oracle BI user with its password

  2. In the JDBC tab:

    • JDBC Driver: oracle.bi.jdbc.AnaJdbcDriver

    • JDBC URL: jddbc:oraclebi://<host>:<port>

      <host> is the server on which Oracle BI server is installed. By default the <port> number is 9703.

  3. In the Properties tab, add a JDBC property with the following key/value pair.

    • Key: NQ_SESSION.SELECTPHYSICAL

    • Value: Yes

    Note:

    This option is required for accessing the physical data. Using this option makes the Oracle BI connection read-only.

  4. In the Flexfield tab, set the name of the bypass connection pool in the CONNECTION_POOL flexfield.

    • Name: CONNECTION_POOL

    • Value: <connection pool name>

    Note:

    Note this bypass connection pool must also be defined in the Oracle BI server itself.

18.3.2 Creating an Oracle BI Physical Schema

Create a Oracle BI 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.

In the physical schema the Data and Work Schemas correspond each to an Oracle BI Catalog/schema pair.

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.

18.4 Setting Up an Integration Project

Setting up a project using an Oracle BI Server 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 BI:

  • RKM Oracle BI (Jython)LKM Oracle BI to Oracle (DBLink)LKM Oracle BI to SQLIKM Oracle BI to SQL Append

Import also the knowledge modules (IKM, CKM) required for the other technologies involved in your project.

18.5 Creating and Reverse-Engineering an Oracle BI Model

This section contains the following topics:

18.5.1 Create an Oracle BI Model

Create an Oracle BI Model using the standard procedure, as described in "Creating a Model" of the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator.

18.5.2 Reverse-engineer an Oracle BI Model

Oracle BI supports Customized reverse-engineering.

To perform a Customized Reverse-Engineering on Oracle BI with a RKM, 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 BI technology:

  1. In the Reverse tab of the Oracle BI Model, select the KM: RKM Oracle BI (Jython).<project name>.

This KM implements the USE_LOG and LOG_FILE_NAME logging options to trace the reverse-engineering process.

18.6 Setting up Data Quality

Data integrity check is not supported in an Oracle BI Server. You can check data extracted Oracle BI in a staging area using another technology.

18.7 Designing an Interface

You can use Oracle BI as a source of an integration interface.

The KM choice for an interface determines the abilities and performance of this interface. The recommendations in this section help in the selection of the KM for different situations concerning an Oracle BI server.

18.7.1 Loading Data from and to Oracle BI

The LKM choice in the Interface Flow tab to load data between Oracle BI and another type of data server is essential for the performance of an interface.

18.7.1.1 Loading Data from Oracle BI

Use the knowledge modules listed in Table 18-2 to load data from an Oracle BI server to a target or staging area database.

Table 18-2 KMs for loading data From Oracle BI

Staging Area/Target Technology KM Notes

Oracle

LKM Oracle BI to Oracle (Dblink)

Loads data from an Oracle BI source to an Oracle Database staging area using DBLinks.

To use this knowledge module, a DBLink must be manually created from the source Fusion Transaction DB (that is the database storing the underlying data tables) to the Oracle staging area. This DBLink name must be the one specified in the Oracle staging area data server connection.

SQL

LKM Oracle BI to SQL

Loads data from an Oracle BI Source to an ANSI SQL-92 compliant staging area database via the agent.

SQL

IKM Oracle BI to SQL Append

Loads and Integrates data from an Oracle BI Source to an ANSI SQL-92 compliant staging area database via the agent.

To use this KM, you must set the staging are of your interface on the source Oracle BI server.

In this configuration, no temporary table is created and data is loaded and integrated directly from the source to the target tables.


18.7.1.2 Loading Data to Oracle BI

Oracle BI cannot be used as a staging area. No LKM targets Oracle BI.

18.7.2 Integrating Data in Oracle BI

Oracle BI cannot be used as a target or staging area. It is not possible to integrate data into Oracle BI with the knowledge modules.