Skip Headers
Oracle® Warehouse Builder Concepts
11g Release 2 (11.2)

Part Number E10581-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 Data Access and Movement

This section discusses data sources and targets, and how OWB provides solutions for accessing and moving data among disparate systems with simple to complex requirements.

This section contains the following topics:

About Metadata on Source Data

Metadata is the data that describes the contents of a given object in a data source or target. For example, metadata for a table indicates the column names and data types for each column.

Before you import source metadata into OWB, first create a module that will contain these metadata definitions. The type of module you create depends on the source from which you are importing metadata. For example, to import metadata definitions from an Oracle database, you create or use an Oracle module. To import metadata definitions from flat files, you create a flat file module.

See Also:

"Modules and Locations" in this guide, and "General Steps for Importing Metadata from Sources" in Oracle Warehouse Builder Sources and Targets Guide

OWB must have metadata for any source or target object that will be manipulated in your project. The most basic metadata needed by OWB can be created or derived in several ways:

The metadata management and reporting features in OWB, and data lineage and impact analysis, depend on, and leverage the metadata about the sources and targets and transformations that move data among them, which accumulates in your OWB projects over time.

See Also:

"Connecting to Sources and Targets in Oracle Warehouse Builder" in Oracle Warehouse Builder Sources and Targets Guide

The Import Metadata Wizard

The Import Metadata Wizard automates importing metadata from a database into a module in OWB. You can import metadata from Oracle Database and non-Oracle databases. Each module type that stores source or target data structures has an associated Import Wizard, which automates the process of importing the metadata to describe the data structures. Importing metadata saves time and avoids keying errors, for example, by bringing metadata definitions of existing database objects into OWB.

The Welcome page of the Import Metadata Wizard lists the steps for importing metadata from source applications into the appropriate module. The Import Metadata Wizard for Oracle Database supports importing of tables, views, materialized views, dimensions, cubes, external tables, sequences, user-defined types, and PL/SQL transformations directly or through object lookups using synonyms.

When you import an external table, OWB also imports the associated location and directory information for any associated flat files.

See Also:

These topics in Oracle Warehouse Builder Sources and Targets Guide:

Modules and Locations

A module is a container structure for the data objects in OWB. Modules are equivalent to schemas from a database perspective. A location stores credentials needed to access a schema. Locations are linked to modules to provide access to metadata and the data itself. A module can have many locations associated with it, but only one can be the configured location at any point in time.

The association of a module to a location enables you to perform certain actions more easily in OWB. For example, you can reimport metadata by reusing an existing module. Furthermore, when you deploy ETL processes in subsequent steps, modules enable you to deploy related objects together, such as process flows.

Modules are created by expanding the Projects Navigator until you find the node for the data object type for which you want to create the module. For example, if the source data is stored in an Oracle Database, then you expand the Databases node to view the Oracle node. If the source data is in an SAP R/3 system, then you expand the Applications node to view the SAP node. By right-clicking the node, you can select New and launch the Create Module Wizard.

See Also:

"Modules" in this guide, and "Creating Modules" in Oracle Warehouse Builder Sources and Targets Guide

About Connectors

A connector is a logical link created by a mapping between a source location and a target location. The connector between schemas in two different Oracle Databases is implemented as a database link, and the connector between a schema and an operating system directory is implemented as a database directory.

You do not need to create connectors manually if your user ID has the credentials for creating these database objects. OWB will create them automatically the first time you deploy the mapping. Otherwise, a privileged user must create the objects and grant you access to use them. You can then create the connectors manually and select the database object from a list.

To create a database connector, from within the Connection Navigator, expand the Locations folder and the subfolder for the target location. Right-click DB Connectors and select New. The Create Connector wizard opens with prompts for creating the connection. You can create a directory connection by right-clicking Directories and selecting New, following the same steps.

See Also:

Summary of Supported Sources and Targets

This section summarizes the supported sources and targets for each Location node as displayed in the Connections Navigator.

OWB supports sources from:

OWB supports the following targets:

See Also:

"Supported Sources and Targets" in Oracle Warehouse Builder Sources and Targets Guide for a detailed and complete list

Flat Files as Data Sources or Targets

OWB supports using flat files as data sources. Flat files are typically in plain text comma-separated or tab-separated format, or proprietary binary formats, and may be stored on different types of operating systems. You first ensure that you have direct access either locally, or by creating a network connection, through TCP/IP or NFS for example. OWB provides the Create Flat File Wizard to create a file object, which will contain the imported flat-file definitions.

The Create Flat File Wizard provides intuitive prompts for importing metadata. To start the wizard, you right-click the file module and select Import. You can filter the filenames from which to import by applying wildcards. The wizard creates definitions for the files and inserts the file names under the Flat File module in the Project Navigator.

The locations that correspond to this module appear as folders on your computer's file system. The metadata is imported into a file module in OWB and becomes visible in the workspace.

You can then sample the metadata from these flat files. The Flat File Sample Wizard enables you to view a sample of the flat file and define record organization and file properties. You can sample and define common flat file formats such as string and ASCII. The Flat File Sample Wizard also enables the importation of new data types such as GRAPHIC, RAW, and SMALLINT.

After you have created the flat-file locations and have imported the flat-file metadata, you are ready to import data. You introduce data from a flat file into an OWB mapping either through an external table or a flat-file operator. Depending on how the data is to be transformed, use one of the following options:

Note:

The Create Flat File Wizard enables specifying the character set and defining single or multiple record types.

See Also:

"Using Flat Files as Sources or Targets" in Oracle Warehouse Builder Sources and Targets Guide for procedures

External Table Option

If the data is to be joined with other tables or requires complex transformations, then use the External Table option. An external table can be used as a source and enables data from the associated flat file to be viewed from SQL as a table. When you use an external table in a mapping, its column properties are based on the SQL properties that you defined when importing the flat file. OWB generates SQL code to select rows from the external table. You can also get parallel access to the file through the table. You can either import an existing external table from another database or define a new external table.

You can also use an external table to combine the loading and transformation within a single set-based SQL DML statement. You do not have to stage the data before inserting it into the target table.

See Also:

"Using External Tables" in Oracle Warehouse Builder Sources and Targets Guide for procedures

Flat File Operators

In cases where large volumes of data are to be extracted and little transformation is required, use the flat file operator. When you use a flat file operator, SQL*Loader code is generated. From the flat file operator, you can load the data to a staging table, add indexes, and perform transformations as necessary. The transformations you can perform on data introduced by a flat file operator are limited to SQL*Loader transformations only.

See Also:

Oracle Database Utilities for more information about differences between external tables and SQL*Loader (flat file operators)

Data Systems Access with Code Templates

OWB achieves seamless management of JDBC-accessible data systems through its Code Template (CT) technology. Code Templates provide native heterogeneous connectivity to Oracle and JDBC-accessible data systems and disparate platforms. Code templates can be used as an alternative to Oracle Gateways as a means of accessing other databases. In addition to OWB being the best ETL solution for Oracle databases, with OWB you can move data that is located in non-Oracle systems into and out of your project quickly and easily. JDBC connectivity provides an alternative to Oracle Gateways as a means of accessing other databases.

Code Template technology in OWB also provides direct data movement among JDBC-accessible databases, without stopping in an Oracle database in between. For example, if you need to move data from DB/2 to SQL Server for some reason, then you can do so from OWB without moving the data through Oracle at all.

See Also:

"Using Code Templates to Load and Transfer Data" in Oracle Warehouse Builder Sources and Targets Guide

Note:

When getting ready to generate code for a CT, you must have only the editor open for the CT on which you are focused. Otherwise, when you generate code for the CT, you will get conflicting results.

Generic Connectivity and Oracle Database Gateways

Oracle alternatively provides the generic connectivity agent and optional Oracle Database Gateways for connecting to non-Oracle databases such as SQL Server, Sybase, Informix, Teradata, DRDA, ODBC, and other sources. OWB can communicate with non-Oracle systems using Oracle Database Heterogeneous Services and a complementary agent if you choose this route.

The generic connectivity agent is intended for low-end data integration solutions. The transfer of data is subject to the rules of specific ODBC or OLE DB drivers installed on the client computer. In this case, you do not need to purchase a separate transparent gateway. You use the generic connectivity agent included with Oracle Database. You must still create and customize an initialization file for your generic connectivity agent.

Oracle Database Gateways provide specific connection agents, designed and optimized for other databases, which you install and configure separately as needed. For example, for a Sybase data source, you install the Sybase-specific gateway. The non-Oracle system appears as a remote Oracle Database to which you can then create a connection and import its data into Oracle. This is especially useful for database environments that do not intend to harbor data marts or data warehouses, but that need integration with a set of other data sources.

See Also:

Transportable Modules for Moving Large Volumes of Data

A transportable module enables OWB to rapidly copy a group of related database objects from one database to another.

You use the Design Center to create a transportable module for which you specify the source database location and the target database location. Then you select the database objects to be included in the transportable module. The metadata of the selected objects are imported from the source database into the transportable module. The metadata is stored in the workspace. To physically move the data and metadata from source into target, you need to configure and deploy the transportable module to the target location. During deployment, both data and metadata are extracted from the source database and created in the target database.

See Also:

"Moving Large Volumes of Data Using Transportable Modules" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide