4 Data Access and Movement

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.

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.

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.

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.

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:

• "External Table Option"

• "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.

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.

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.