Models

What are Models?

Learn about models in Oracle Data Integrator.

A model in Oracle Data Integrator (ODI) is a description of a relational data model. It's a representation of the structure of a number of interconnected datastores stored in a single schema on a particular technology. The model in ODI does not, however, hold data, such as banking figures or names of customers. Instead, it holds only metadata: the names of columns, and the constraints that exist between tables. This metadata can be imported automatically into ODI by a process called reverse-engineering. You can also define the metadata manually, creating columns or constraints directly in ODI. These additions do not necessarily exist in any physical database table.

See Introduction to Models in Developing Integration Projects with Oracle Data Integrator.

What is Reverse-Engineering

Learn about reverse-engineering in Oracle Data Integrator, what it is and when you would use it.

Reverse-engineering is a completely automated process in ODI for retrieving metadata from a database. It can be used to create new models or to fill in gaps in existing models. ODI can perform reverse-engineering in two ways, Standard and Customized. Standard reverse-engineering uses the standard capabilities of the JDBC API. ODI queries the JDBC driver for information about table structures, foreign keys, and so on. It then writes these to the ODI repository, where all metadata is stored. Although it's technically independent of the technology being used, standard reverse-engineering requires a sufficiently capable JDBC driver. Customized reverse-engineering is quite different. In this case, ODI connects to the database by using the basic features of the JDBC driver and directly queries the system tables to retrieve the metadata. It then transforms and writes this metadata into the ODI repository. Naturally, the method for doing this varies greatly depending on the technology that is being reverse-engineered. Thus, for each technology, there is a specific Reverse-engineering Knowledge Module (RKM). This RKM tells ODI how to extract metadata for the given technology.

In addition to standard and customized reverse-engineering for databases, you can reverse-engineer files:

Delimited files contain data separated by a delimiter, such as a comma or tab character. ODI can parse the file content directly and determine the number of columns and their name depending on the file header.
Fixed format files allocate a specific length to each field, rather than using a defined delimiter between fields. They are reverse-engineered with a different method.

To collect metadata for these fields, you require a description of the fields in the COBOL copybook format. ODI can parse this type of file natively. ODI can also reverse-engineer metadata in XML files through standard reverse-engineering. ODI uses the ODI JDBC driver for XML to access them like any other data source. Similarly, lightweight directory access protocol (LDAP) directories can be reverse-engineered by using the standard method. Here, ODI uses the ODI JDBC driver for LDAP. You should, therefore, consider XML files and LDAP directories as normal databases for the purpose of reverse-engineering.

See Reverse-engineering in Developing Integration Projects with Oracle Data Integrator.

Create a Model

Follow these steps to create a data model in Oracle Data Integrator.

The basic procedure to create a model in ODI is as follows:

Create and set up an empty model.
Define the reverse-engineering strategy that ODI follows. This includes selecting a Knowledge Module and setting up other parameters. In particular, you must choose between standard and customized reverse-engineering.
Tell ODI to run the reverse-engineering process by using the strategy that you defined.
If you use standard reverse-engineering, you can choose to import only certain metadata. This is known as performing a selective reverse.
Flesh out the model that you have reverse-engineered.
This means that you add information that was not retrieved during the reverse-engineering process. For example, you add datastores, columns, constraints, or ODI-specific metadata.

The basic steps in creating and naming a model are as follows:

Select the Models view.
You can choose to create the model in selected model folder or you can create a top-level model.
Click the New Model icon.
The window for the new model appears.
Enter the name of your model.
Like most objects in ODI, there is also a code to enter. By default, this is the same as the name, but uppercase with underscores instead of spaces.
Specify the technology of the underlying database in this field.
A model must be linked to a specific technology.
Specify the logical schema to which the model belongs from the drop-down list.
You later specify a context, which determines the physical schema that is be used as the source of the metadata.
Enter a description for your model.

See Creating a Model in Developing Integration Projects with Oracle Data Integrator.

Define a Reverse-Engineering Strategy

Learn the steps to standard or custom reverse-engineering in Oracle Data Integrator.

To define a reverse-engineering strategy in ODI, perform the following steps:

Click the Reverse Engineer tab in the model window.
Select the reverse-engineering type to be used.
The standard mode requires a JDBC driver, which provides metadata for the specific technology. The customized mode requires a specific ODI Knowledge Module to directly access the system tables. This choice causes some differences in the reverse-engineering process, as is discussed.
Select the reverse-engineering context.
Remember you previously defined only a logical schema, which describes where the model fits in the logical architecture. However, this logical schema might correspond to several physical schemas in different physical contexts, such as development, testing, and production.

By selecting the context, you specify which one of those physical schemas is to be used to provide metadata for the model. You should therefore select the context that best conforms to the structure of the model that you want to create.
To start the reverse-engineering process, right-click the model, and then select Reverse Engineer.
Reverse-Engineering Knowledge Modules (RKMs) are used to perform customized reverse-engineering of data models for a specific technology. Customized reverse-engineering is more complete and entirely customizable, but also more complex to run. RKMs are provided only for certain technologies.

To perform customized reverse-engineering by using an RKM, perform the following steps:

Click the Reverse Engineer tab of the model.
Select the Customized option button and fill in the following fields:
- Context: the context used for the reverse-engineering process
- Object Type: the type of objects to reverse-engineer (tables, view, and so on)
- Mask: %
- Select the Knowledge Module: “<Project_Name>.<Project_Folder>” for example, “Knowledge Module: HandsOnLabs.HandsOnLoads”
Click the Reverse button, and then click Yes to validate the changes. Click OK.
Click OK in the “session started” dialog box.

If you use standard reverse-engineering, you can individually select the tables to reverse-engineer. To do so, perform the following steps:

Click the Selective Reverse-Engineering tab.
Select the Selective Reverse-Engineering check box; this enables the next few options.
Select the New Datastores check box to reverse-engineer the datastores that do not exist in your model.
Select the Existing Datastores check box to select the datastores that exist in your model. Selecting both options selects all datastores for reverse-engineering.
Select the Objects to Reverse Engineer check box to select the individual datastores that you want to include.
The datastores that are displayed depend on the New Datastores and Existing Datastores options that you just selected.
Select (with a check mark) the datastore that you want to include in the reverse-engineering process.
Click the Reverse button to launch the process.

You can manually add, remove, or edit any element of a model by using the ODI mapping. This includes creating datastores, columns, keys, and so on. You can do this through the Designer Navigator. Note that these changes are applied only to the model in ODI, they do not update the underlying model in the database. To edit models graphically, you can edit the model’s diagram, a graphical depiction of the model and the relationship between its elements. You can drag and drop elements to define new relationships. This component also enables ODI to modify the model in the original database with your changes. Thus, you can create your model in ODI, and then generate the code to implement this on a database server.

See Reverse-engineering a Model in Developing Integration Projects with Oracle Data Integrator.

Steps 1.5 and 2.4 in the OBE Oracle Data Integrator 12c - Creating an ODI Project and Mapping: Flat File to a Table also take you through how to create and reverse-engineer a model.