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

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:

• OWB can directly extract existing metadata from most database sources or targets. For example, when connecting to an Oracle database, OWB 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 already exist, the metadata that describes the object is created by the design process.

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

• OWB application adapters or application connectors provide additional metadata about ERP and CRM application sources.

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.

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.

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:

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

OWB 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).

• OWB 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

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:

• External Table Option

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

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.

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.