| Oracle® Warehouse Builder Sources and Targets Guide 11g Release 2 (11.2) Part Number E10582-02 |
|
|
View PDF |
| Oracle® Warehouse Builder Sources and Targets Guide 11g Release 2 (11.2) Part Number E10582-02 |
|
|
View PDF |
For different types of data sources and targets, Warehouse Builder provides different connection methods.
This chapter introduces the data sources and targets and specific connectivity technologies supported by Oracle Warehouse Builder. It includes the following topics:
Table 1-1 lists the data storage systems and applications that Oracle Warehouse Builder 11g Release 2 (11.2) can access. The table lists the supported sources and targets for each type of application.
Table 1-1 Sources and Targets Supported in Oracle Warehouse Builder 11g Release 2 (11.2)
| Application Type | Supported Sources | Supported Targets |
|---|---|---|
|
Oracle Database |
Oracle Database releases 8.1, 9.0, 9.2, 10.1, 10.2, 11.1,11.2 |
Oracle Database releases 9.2, 10.1, 10.2, 11.1, 11.2 |
|
Non-Oracle Databases |
|
|
|
Files |
Delimited and fixed-format flat files. |
Delimited, fixed-format, and XML format flat files. |
|
Business Applications |
|
None |
|
Process Flows and Schedules/Oracle Workflow |
None |
Oracle Workflow releases 2.6.2, 2.6.3, 2.6.4, 11i |
|
Process Flows and Schedules/Concurrent Manager |
None |
Any Oracle Database location, release 10g or later. To deploy a schedule in Concurrent Manager, Release 11i or 12i is required. However, for both releases, you must select 11i as the version when you create a location in Warehouse Builder. |
|
Business Intelligence/Discoverer |
None |
Oracle BI Discoverer Release 10.1, Oracle Business Intelligence Suite Enterprise Edition |
|
Oracle Designer |
Oracle Designer 6i, 9i, 10g |
None |
You can directly connect to an Oracle database and import metadata from the available database objects or deploy target objects to the database. To connect to a database on a remote host, you must provide the network credentials to connect to the host. See "Importing Metadata Definitions from Oracle Database" for more information about connecting to an Oracle database.
Warehouse Builder communicates with non-Oracle systems using Oracle Database Heterogeneous Services and a complementary agent. Heterogeneous Services make a non-Oracle system appear as if it were a remote Oracle Database server. The agent can be an Oracle Database Gateway or the generic Open Database Connectivity (ODBC) agent included with Oracle Database.
Figure 1-1 describes how Oracle Database uses Heterogeneous services to access a remote non-Oracle source.
Figure 1-1 Heterogeneous Services Architecture
The Heterogeneous Services component in the database communicates with the Heterogeneous Services agent process. The agent process, in turn, communicates with the remote database.
The agent process consists of agent-generic code and a system-specific driver. All agents contain the same agent-generic code. But each agent has a different driver depending on the type of data being sourced.
Oracle Database gateways provide transparent connections to non-Oracle databases. When using a gateway, you can access these non-Oracle databases just as you would an Oracle database, including importing object metadata and accessing data as source and target. See Chapter 4, "Connecting to Non-Oracle Data Sources Through Gateways" for details of connecting to different databases through gateways.
To connect to data sources that support Java Database Connectivity (JDBC), you must use the appropriate JDBC driver for that data source. The JDBC driver for a non-Oracle database or other data source may be installed with the product, or may require a separate download or purchase. For detailed information about connecting to various data sources using JDBC, see Chapter 6, "Connecting to Data Sources Through JDBC".
To connect through JDBC, you must install the appropriate JDBC drivers for the database. Most of these drivers are shipped with the database. They are also developed by third-party vendors and are usually available as free downloads. The database objects, such as tables, that you import through a JDBC connection can be used only in Code Template mappings. These database objects cannot be used with other mappings, such as PL/SQL mappings, that you create in Warehouse Builder.
To connect through a gateway, you must install Oracle Gateway for the specific database. A gateway enables you to access data from non-Oracle databases in the same way that you access data from Oracle Database. The database objects that you import through a gateway connection can therefore be used in any of the mappings that you create in Warehouse Builder.
Oracle Warehouse Builder can leverage support for ODBC provided by the Oracle Database to integrate with any data source that supports ODBC connectivity. ODBC provides a generic connectivity that is intended for low-end data integration solutions and the transfer of data is subject to the rules of specific ODBC or object linking and embedding (OLE) database drivers installed on the client computer. You need not install database-specific agents to connect to different data sources. Instead, you can use the generic connectivity agent included with Oracle Database. You must still create and customize an initialization file for your generic connectivity agent. For detailed information about connecting to data sources using ODBC connectivity, see "Connecting to an ODBC Data Source".
Using Warehouse Builder, you can also connect to ERP and CRM applications, such as SAP, Oracle E-Business Suite, Peoplesoft, and Siebel.
You can also connect to Oracle's Customer Data Hub (CDH), Universal Customer Master (UCM), and Product Information Management (PIM).
The application adapters for Oracle applications provide additional metadata to simplify ETL design from such sources, but connecting to these sources depends upon underlying database connectivity. For example, metadata extraction from E-Business Suite, which is hosted on Oracle database, is done using database links, while metadata extraction from a Peoplesoft application hosted on a DB2 database depends on having the DB2 gateway installed on your target database.Connecting to an SAP system and extracting data is accomplished using native SAP R/3 ABAP code and data extraction techniques fully supported by SAP.When using the application connectors, the details of the underlying connection technologies for different sources are generally hidden from the user, which simplifies managing the connectivity.
For more information about connecting to Oracle E-Business Suite, Peoplesoft, and Siebel, see Chapter 8, "Importing Metadata from Oracle Enterprise Applications".
For information about extracting data from SAP systems, see Chapter 7, "Extracting Data from SAP Applications".
Choosing among the different connection technologies available depends upon your specific data source type and other specifics of your use case:
Choose Oracle gateways for fully supported end-to-end connectivity with non-Oracle databases, and the most transparent access to those databases from within Warehouse Builder.
Choose JDBC connectivity in instances where you need simplified setup of flexible connectivity with good performance across a wide range of non-Oracle data sources.
Choose ODBC connectivity for sources where data volumes are relatively small and maximum performance is not a concern.
Use application connectors when working with any supported ERP, CRM, or MDM application source or target.
In some cases, the data movement provided by Oracle-to-Oracle database links, gateways, and ODBC may not be sufficient. Oracle Warehouse Builder 11g Release 2 (11.2) also supports a framework that adds more flexible data movement options based on code templates.
When designing an ETL mapping, you can now choose between Oracle-based mappings and code template-based mappings for more flexible connectivity and data movement. Both mappings offer a rich set of data transformation operators.
When using code template-based mappings, you can select code templates to assign alternative data movement methods for individual mappings or specific parts of mappings. Warehouse Builder generates executable code based on the templates you select, and then deploys that code to a Control Center Agent, where it executes.
The JDBC connectivity for data movement depends on using code template-based mappings, and the data movement code actually executes in the control center agent. You can choose alternative templates for other data movement techniques such as bulk data extraction and loading that fit your use case better. You can also construct new templates that implement data movement methods not supported by Warehouse Builder out of the box.
For more information about using code templates, see Chapter 12, "Using Code Templates to Load and Transfer Data". Also see Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for more details on code templates-based ETL mappings.
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
