|Oracle® Fusion Middleware Administrator's Guide for Oracle Business Intelligence Discoverer
11g Release 1 (11.1.1)
Part Number B32519-02
This chapter contains information about using Discoverer with non-Oracle databases and Oracle Rdb, and contains the following topics:
Heterogeneous Services are the common architecture and administration mechanisms provided with the Oracle database to enable you to connect to non-Oracle databases and Oracle Rdb.
You connect to non-Oracle databases and Oracle Rdb using Heterogeneous Services in two ways:
using generic connectivity (for more information, see "About using generic connectivity to connect to non-Oracle databases and Oracle Rdb")
using the Transparent Gateway (for more information, see "About using the Transparent Gateway to connect to non-Oracle databases and Oracle Rdb")
For more information about Heterogeneous Services, see the Oracle Database Heterogeneous Connectivity Administrator's Guide.
Generic connectivity is one of the mechanisms supported by the Oracle database Heterogeneous Services feature for accessing non-Oracle databases and Oracle Rdb.
Discoverer users can use generic connectivity to access ODBC or OLE DB (object linking and embedding database) databases.
The non-Oracle database must comply sufficiently with the ODBC standard (for more information about ODBC and OLE DB connectivity requirements, see the Oracle Database Heterogeneous Connectivity Administrator's Guide).
This section contains the following topics:
In previous versions of Discoverer, users could connect to non-Oracle databases using ODBC drivers. The major difference between using ODBC drivers to connect to a non-Oracle database using the Oracle database generic connectivity feature is the location of the EUL, as follows:
with ODBC drivers, the EUL is stored in the non-Oracle database
with generic connectivity, the EUL is stored in the Oracle database
The above diagram illustrates the following:
when Discoverer connects to a non-Oracle database using ODBC drivers, both the EUL and the data reside on the non-Oracle database
when Discoverer connects to a non-Oracle database using generic connectivity, the data continues to reside on the non-Oracle database but the EUL must be located on the Oracle database
Discoverer no longer supports the use of native ODBC drivers to connect to non-Oracle databases. You must now use generic connectivity to retrieve data from the non-Oracle database. In other words, the EUL must be stored in an Oracle database. To find out how to move an EUL from a non-Oracle database to an Oracle database, see "How to migrate an EUL from a non-Oracle database (accessed using native ODBC drivers) to an Oracle database (to support generic connectivity)".
Using Discoverer with the Oracle database's generic connectivity feature rather than native ODBC drivers to connect to a non-Oracle database has the following advantages:
enables connections to a greater number of ODBC databases
Generic connectivity provides access to any ODBC database that is compliant with the ODBC standard. Compliance varies with both databases and ODBC drivers.
enables Discoverer end users to query data from multiple different databases simultaneously
For example, you can create a single business area with folders based on tables held in Sybase, DB2 and Oracle databases. An end user query can return data joined across multiple databases.
enables query prediction with ODBC data
Discoverer's query prediction uses query statistics that are generated when end users run queries and which are saved in the EUL. Because the EUL is in the Oracle database, Discoverer is able to perform query prediction for the ODBC data on the Oracle database.
enables batch support for scheduling workbooks
Discoverer uses the batch scheduler in the Oracle database. Because the EUL is in the Oracle database, Discoverer is able to schedule workbooks with ODBC data.
enables you to apply Oracle analytical functions (and other Oracle functions) to data provided from an ODBC database
Because the data is brought into the Oracle database you can apply the full set of Oracle functions to the data, instead of being restricted to the functions supported by the non-Oracle database.
Before you can use generic connectivity, you must configure the Oracle database to support generic connectivity.
Note: You should work with your database administrator to set up generic connectivity for Discoverer.
For more information about generic connectivity, see the Oracle Database Heterogeneous Connectivity Administrator's Guide.
To migrate an EUL from a non-Oracle database to an Oracle database:
Export the EUL objects from the non-Oracle database to an EEX file.
For more information, see "Which export/import method to use".
Note: This must be carried out using a version of Discoverer that supports a direct ODBC connection (that is, 9.0.4 or earlier).
Set up generic connectivity in the Oracle database that will contain the EUL.
For more information, see "About setting up generic connectivity for Discoverer".
Create an EUL on the Oracle database.
For more information about how to create an EUL, see Chapter 4, "Creating and Maintaining End User Layers".
Import the EEX file you created in step 1 into the Oracle database.
For more information, see "Which export/import method to use".
Resolve any mappings (that is, Discoverer Business areas and underlying objects).
Choose View | Validate | Folders
Select the first folder that displays an error and choose Edit | Properties to display the "Folder Properties dialog".
Click the Database field to display the "Choose user or table/view dialog".
Select the database link that you created in the "About setting up generic connectivity for Discoverer" task.
Select the correct user on the non-Oracle database.
Click OK to close the Choose user dialog.
Click OK to apply the new value in the Database field and close the Folder Properties dialog.
Repeat steps a) to g) for each folder that displays an error.
Set up security on the Oracle database for database users.
For more information, see Chapter 7, "Controlling Access to Information".
Discoverer users can now connect to the non-Oracle database using generic connectivity and continue to use their existing workbooks and worksheets.
If you are accessing data from a non-Oracle database using Discoverer Administrator (for example to create a business area) and ORA-3113 errors are displayed, do one of the following:
The Transparent Gateway is one of the mechanisms supported by the Oracle database Heterogeneous Services feature for accessing non-Oracle databases and Oracle Rdb.
Discoverer users can use an Oracle Transparent Gateway with Heterogeneous Services to access a particular, vendor-specific, non-Oracle database, and Oracle Rdb. For example, you would use the Oracle Transparent Gateway for Sybase on Solaris to access a Sybase database that was operating on a Sun Solaris platform.
You must have the appropriate Oracle Transparent Gateway software installed.
For more information about the Oracle Transparent Gateway and how to set it up, see the Oracle9i Database Installation Guide.