6 Complex Files

This chapter describes how to work with Complex Files in Oracle Data Integrator.

This chapter includes the following sections:

6.1 Introduction

Oracle Data Integrator supports several files types. This chapter describes how to work with the Complex (or native) File format. See Chapter 3, "Files" for information about simple fixed or delimited files containing ASCII or EBCDIC data.

For complex files it is possible to build a Native Schema description file that describes the file structure. Using this Native Schema (nXSD) description and the Oracle Data Integrator Driver for Complex Files, Oracle Data Integrator is able to reverse-engineer, read and write information from complex files.

See Section 6.3, "Building a Native Schema Description File Using the Native Format Builder" for information on how to build a native schema description file using the Native Format Builder Wizard, and Appendix C, "Oracle Data Integrator Driver for Complex Files Reference" for reference information on the Complex File driver.

6.1.1 Concepts

The Oracle Data Integrator Driver for Complex Files (Complex File driver) converts native format to a relational structure and exposes this relational structure as a data model in Oracle Data Integrator.

The Complex File driver translates internally the native file into an XML structure, as defined in the Native Schema (nXSD) description and from this XML file it generates a relational schema that is consumed by Oracle Data Integrator. The overall mechanism is shown in Figure 6-1.

Figure 6-1 Complex File Driver Process

Description of Figure 6-1 follows
Description of ''Figure 6-1 Complex File Driver Process''

Most concepts and processes that are used for Complex Files are equivalent to those used for XML files. The main difference is the step that transparently translates the Native File into an XML structure that is used internally by the driver but never persisted.

The Complex File technology concepts map the Oracle Data Integrator concepts as follows: A Complex File corresponds to an Oracle Data Integrator data server. Within this data server, a single schema maps the content of the complex file.

The Oracle Data Integrator Driver for Complex File (Complex File driver) loads the complex structure of the native file into a relational schema. This relational schema is a set of tables located in the schema that can be queried or modified using SQL. The Complex File driver is also able to unload the relational schema back into the complex file.The relational schema is reverse-engineered as a data model in ODI, with tables, columns, and constraints. This model is used like a standard relational data model in ODI. If the modified data within the relational schema needs to be written back to the complex file, the driver provides the capability to synchronize the relational schema into the file.

Note that for simple flat files formats (fixed and delimited), it is recommended to use the File technology, and for XML files, the XML technology. See Chapter 3, "Files" and Chapter 5, "XML Files" for more information.

6.1.2 Pre/Post Processing Support for Complex File Driver

You can now customize the way data is fed to the Complex File driver. You can set up intermediate processing stages to process the data that is retrieved from an external endpoint using Oracle Data Integrator, or to write the data out to an external endpoint.

For detailed information about configuring and implement the pre and post processing stages for Complex File driver, see Appendix D, "Pre/Post Processing Support for XML and Complex File Drivers".

6.1.3 Knowledge Modules

You can use a Complex File data server as any SQL data server. Complex File data servers support both the technology-specific KMs sourcing or targeting SQL data servers, as well as the generic KMs. See Chapter 4, "Generic SQL" or the technology chapters for more information on these KMs.

You can also use the IKM XML Control Append when writing to a Complex File data server. This Knowledge Module implements specific option to synchronize the data from the relational schema to the file, which is supported by the Complex File driver.

6.2 Installation and Configuration

Make sure you have read the information in this section before you start working with the Complex File technology:

6.2.1 System Requirements

Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The list of supported platforms and versions is available on Oracle Technical Network (OTN):

http://www.oracle.com/technology/products/oracle-data-integrator/index.html.

6.2.2 Technology Specific Requirements

There are no technology-specific requirements for using Complex Files in Oracle Data Integrator.

6.2.3 Connectivity Requirements

This section lists the requirements for connecting to complex files.

Oracle Data Integrator Driver for Complex Files

Complex files are accessed through the Oracle Data Integrator Driver for Complex File. This JDBC driver is installed with Oracle Data Integrator and requires no other installed component or configuration.

You must ask the system administrator for the following connection information:

  • The location of the Native Schema (nXSD) file associated with your native file

  • The location of the native complex file

6.3 Building a Native Schema Description File Using the Native Format Builder

You can build a Native Schema (nXSD) description file using the Native Format Builder Wizard. You can start the Native Format Builder Wizard from the Data Server Editor when creating the Complex File data server.

To build a native schema description file using the native format builder:

  1. In the Topology Navigator expand the Technologies node in the Physical Architecture accordion.

  2. Select the Complex File technology.

  3. Right-click and select New Data Server.

  4. In the JDBC tab, click the Edit nXSD button. The Native Format Builder Wizard appears.

  5. Follow the on-screen instructions and complete the Native Format Builder Wizard to create a Native Schema description file.

See "Native Format Builder Wizard" in the User's Guide for Technology Adapters for more information on the Native Schema format.

6.4 Setting up the Topology

Setting up the topology consists in:

  1. Creating a Complex File Data Server

  2. Creating a Complex File Physical Schema

6.4.1 Creating a Complex File Data Server

A Complex File data server corresponds to one native file that is accessible to Oracle Data Integrator.

6.4.1.1 Creation of the Data Server

Create a data server for the Complex File technology using the standard procedure, as described in "Creating a Data Server" of the Developing Integration Projects with Oracle Data Integrator. This section details only the fields required or specific for defining a Complex File data server:

  1. In the Definition tab:

    • Name: Name of the data server that will appear in Oracle Data Integrator.

    • User/Password: These fields are not used for Complex File data servers.

  2. In the JDBC tab, enter the following values:

    Table 6-1 Complex File Driver Properties

    Property Value Notes

    f

    <native file name>

    Native file location. Use slash "/" in the path name instead of back slash "\". It is possible to use an HTTP, FTP or File URL to locate the file. Files located by URL are read-only. This parameter is mandatory.

    d

    <native schema>

    Native Schema (nXSD) file location. This parameter is mandatory.

    re

    <root element>

    Name of the element to take as the root table of the schema. This value is case sensitive. This property can be used for reverse-engineering for example a specific section of the Native Schema. This parameter is mandatory.

    s

    <schema name>

    Name of the relational schema where the complex file will be loaded. This parameter is optional.

    This schema will be selected when creating the physical schema under the Complex File data server.


6.4.2 Creating a Complex File Physical Schema

Create a Complex File physical schema using the standard procedure, as described in "Creating a Physical Schema" in Administering Oracle Data Integrator.

The schema name that you have set on the URL will be preset. Select this schema for both the Data Schema and Work Schema.

Create for this physical schema a logical schema using the standard procedure, as described in "Creating a Logical Schema" in Administering Oracle Data Integrator and associate it in a given context.

6.5 Setting Up an Integration Project

Setting up a project using the Complex File technology follows the standard procedure. See "Creating an Integration Project" of the Developing Integration Projects with Oracle Data Integrator.

It is recommended to import the following knowledge modules into your project for getting started:

  • LKM SQL to SQL

  • IKM XML Control Append

In addition to these knowledge modules, you can also import file knowledge modules specific to the other technologies involved in your product.

6.6 Creating and Reverse-Engineering a Complex File Model

This section contains the following topics:

6.6.1 Create a Complex File Model

A Complex File model groups a set of datastores. Each datastore typically represents an element in the intermediate XML file generated from the native file using the native schema.

Create a Complex File model using the standard procedure, as described in "Creating a Model" of the Developing Integration Projects with Oracle Data Integrator.

6.6.2 Reverse-engineer a Complex File Model

The Complex File technology supports standard reverse-engineering, which uses only the abilities of the Complex File driver.

Standard Reverse-Engineering

To perform a Standard Reverse- Engineering with a Complex File model use the usual procedure, as described in "Reverse-engineering a Model" of the Developing Integration Projects with Oracle Data Integrator.

This reverse-engineering uses the same process as the reverse-engineering of XML Files. The native schema (nXSD) provided in the data server URL is used as the XSD file to describe the XML structure. See Section 5.5.2, "Reverse-Engineering an XML Model" and XML to SQL Mapping for more information.

6.7 Designing a Mapping

You can use a complex file as a source or a target of a mapping.

The KM choice for a mapping or a check determines the abilities and performances of this mapping or check. The recommendations below help in the selection of the KM for different situations concerning a Complex File data server.

Complex File data models are handled in mappings similarly to XML structures. For example, the Synchronization model is the same for complex files and XML files and the same knowledge modules can be used for both technologies.

See Section 5.6, "Designing a Mapping" in Chapter 5, "XML Files" for more information.