This chapter describes how to work with Oracle Business Intelligence Enterprise Edition in Oracle Data Integrator.
This chapter includes the following sections:
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 mappings.
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.
Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 16-1 for handling Oracle BI data. These KMs use Oracle BI specific features.
Table 16-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. |
Make sure you have read the information in this section before you start using the Oracle BI 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 technology-specific requirements for using Oracle BI in Oracle Data Integrator.
This section lists the requirements for connecting to an Oracle BI Server.
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.
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.
Setting up the Topology consists of:
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.
Create a data server for the Oracle BI technology using the standard procedure, as described in "Creating a Data Server" of the Developing Integration Projects with Oracle Data Integrator. This section details only the fields required or specific for defining a Oracle BI data server:
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
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
.
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.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.Create a Oracle BI physical schema using the standard procedure, as described in "Creating a Physical Schema" in Administering 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" in Administering Oracle Data Integrator and associate it in a given context.
Setting up a project using an Oracle BI Server follows the standard procedure. See "Creating an Integration Project" of the Developing Integration Projects with 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.
This section contains the following topics:
Create an Oracle BI Model using the standard procedure, as described in "Creating a Model" of the Developing Integration Projects with Oracle Data Integrator.
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 Developing Integration Projects with Oracle Data Integrator. This section details only the fields specific to the Oracle BI technology:
In the Reverse Engineer 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.
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.
You can use Oracle BI as a source of a mapping.
The KM choice for a mapping determines the abilities and performance of this mapping. The recommendations in this section help in the selection of the KM for different situations concerning an Oracle BI server.
The LKM choice in the Loading Knowledge Module tab to load data between Oracle BI and another type of data server is essential for the performance of a mapping.
Use the knowledge modules listed in Table 16-2 to load data from an Oracle BI server to a target or staging area database.
Table 16-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 mapping 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. |