4 Data Access and Movement

This section discusses data sources and targets, and how Oracle Warehouse Builder 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 Oracle Warehouse Builder, first create a module that contains 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.

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

  • Oracle Warehouse Builder can directly extract existing metadata from most database sources or targets. For example, when connecting to an Oracle database, Oracle Warehouse Builder queries the database dictionary to extract all needed metadata on tables, views, sequences, dimensions, cubes, data types, PL/SQL packages, and so on.

  • You can define and use SQL- or XML-based custom metadata stores to retrieve definitions of source and target objects such as tables and views.

  • When you design data objects that do not exist, the metadata that describes the object is created by the design process.

  • For data files extracted from some mainframe sources, Oracle Warehouse Builder can interpret Cobol Copybook files that describes the structure of the data file, and create its source metadata based on that.

  • Oracle Warehouse Builder application adapters or application connectors provide additional metadata about ERP and CRM application sources.

The metadata management and reporting features in Oracle Warehouse Builder, 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 Oracle Warehouse Builder 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 Oracle Warehouse Builder. 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 Oracle Warehouse Builder.

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, Oracle Warehouse Builder also imports the associated location and directory information for any associated flat files.

Modules and Locations

A module is a container structure for the data objects in Oracle Warehouse Builder. 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 Oracle Warehouse Builder. 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, 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 start 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 must not create connectors manually if your user ID has the credentials for creating these database objects. Oracle Warehouse Builder creates 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.

Oracle Warehouse Builder supports sources from:

  • Oracle Database Releases 8.1 and later.

  • Any database accessible through Oracle Heterogeneous Services (Gateways), including but not limited to DB2, DRDA, Informix, SQL Server, Sybase, and Teradata.

  • Any data store accessible through the Code Templates (which use JDBC), including, but not limited to, DB2, SQL Server, Sybase, and Teradata.

  • Any data store accessible through the ODBC Data Source Administrator, including but not limited to Excel and MS Access

  • Delimited and fixed-length flat files.

  • ERP and CRM applications such as Oracle E-Business Suite, Peoplesoft and Siebel, from which data can be extracted using SQL.

  • SAP R/3, from which data is extracted using officially supported methods based on native ABAP code.

Oracle Warehouse Builder supports the following targets:

  • Oracle Database Release 8.1 and later.

  • Third-party databases accessed through Oracle gateways or ODBC.

  • Comma-delimited and XML format flat files.

  • Oracle BI tools, such as Oracle Business Intelligence Suite Enterprise Edition (OBI EE).

  • Oracle Warehouse Builder can deploy or execute process flows and schedules to Oracle Enterprise Manager and Oracle Workflow. In general, you can deploy a schedule in any Oracle Database location, Release 10g or later.

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

Oracle Warehouse Builder supports using flat files as data sources. Flat files are typically in plain text comma-delimited 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. Oracle Warehouse Builder provides the Create Flat File Wizard to create a file object, which contains 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 Oracle Warehouse Builder 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 Oracle Warehouse Builder 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:


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. Oracle Warehouse Builder 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

Oracle Warehouse Builder 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 for accessing other databases. In addition to Oracle Warehouse Builder being the best ETL solution for Oracle databases, with Oracle Warehouse Builder 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 or accessing other databases.

Code Template technology in Oracle Warehouse Builder also provides direct data movement among JDBC-accessible databases, without stopping in an Oracle database in between. For example, to move data from DB/2 to SQL Server for some reason, then you can do so from Oracle Warehouse Builder 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.


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 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. Oracle Warehouse Builder 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 must not 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 Oracle Warehouse Builder 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 must 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.