Introduction to Models:
A Model is the description of a set of datastores. It corresponds to a group of tabular data structures stored in a data server. A model is based on a Logical Schema defined in the topology. In a given Context, this Logical Schema is mapped to a Physical Schema. The Data Schema of this Physical Schema contains physical data structure: tables, files, JMS messages, elements from an XML file, that are represented as datastores.
Models as well as all their components are based on the relational paradigm (table, attributes, keys, etc.). Models in Data Integrator only contain Metadata, that is the description of the data structures. They do not contain a copy of the actual data.
Datastores:
A datastore represents a data structure. It can be a table, a flat file, a message queue or any other data structure accessible by Oracle Data Integrator.
A datastore describes data in a tabular structure. Datastores are composed of attributes.
As datastores are based on the relational paradigm, it is also possible to associate the following elements to a datastore:
Keys
A Key is a set of attributes with a specific role in the relational paradigm. Primary and Alternate Keys identify each record uniquely. Non-Unique Indexes enable optimized record access.
References
A Reference is a functional link between two datastores. It corresponds to a Foreign Key in a relational model. For example: The INVOICE datastore references the CUSTOMER datastore through the customer number.
Conditions and Filters
Conditions and Filters are a WHERE-type SQL expressions attached to a datastore. They are used to validate or filter the data in this datastore.
Data Integrity
A model contains constraints such as Keys, References or Conditions, but also non-null flags on attributes. Oracle Data Integrator includes a data integrity framework for ensuring the quality of a data model.
This framework allows to perform:
Static Checks to verify the integrity of the data contained in a data model. This operation is performed to assess the quality of the data in a model when constraints do not physically exist in the data server but are defined in Data Integrator only.
Flow Check to verify the integrity of a data flow before it is integrated into a given datastore. The data flow is checked against the constraints defined in Oracle Data Integrator for the datastore that is the target of the data flow.
Reverse-engineering
A new model is created with no datastores. Reverse-engineering is the process that populates the model in Oracle Data Integrator by retrieving metadata from the data server containing the data structures. There are two different types of reverse-engineering:
Standard reverse-engineering uses standard JDBC driver features to retrieve the metadata. Note that unique keys are not reverse-engineered when using a standard reverse-engineering.
Customized reverse-engineering uses a technology-specific Reverse Knowledge Module (RKM) to retrieve the metadata, using a method specific to the given technology. This method is recommended if a technology specific RKM exists because it usually retrieves more information than the Standard reverse-engineering method.
Creating and Reverse-Engineering a Model
Now that the key components of an ODI model have been described, an overview is provided on how to create and reverse-engineer a model:
Creating a Model
Creating a Model and Topology Objects
Reverse-engineering a Model
Creating a Model
A Model is a set of datastores corresponding to data structures contained in a Physical Schema.
To create a Model:
In Designer Navigator expand the Models panel.
Right-click then select New Model.
Fill in the following fields in the Definition tab:
Name: Name of the model used in the user interface.
Technology: Select the model's technology.
Logical Schema: Select the Logical Schema on which your model will be based.
On the Reverse Engineer tab, select a Context which will be used for the model's reverse-engineering.
Note that if there is only one context that maps the logical schema, this context will be set automatically.
Select Save from the File main menu.
The model is created, but contains no datastore yet.
Creating a Model and Topology Objects
You can create a Model along with other topology objects, including new data servers, contexts and schemas, at the same time:
In ODI Studio, select File and click New....
In the New Gallery dialog, select Create a New Model and Topology Objects and click OK.
The Create New Model and Topology Objects wizard appears.
In the Model panel of the wizard, fill in the following fields.
Name: Name of the model used in the user interface.
Technology: Select the model's technology.
Logical Schema: Select the Logical Schema on which your model will be based.
Context: Select the context that will be used for the model's reverse-engineering.
Click Next.
In the Data Server panel of the wizard, fill in the following data server fields.
Name: Name of the data server, as it appears in the user interface.
Note: or naming data servers, it is recommended to use the following naming standard: <TECHNOLOGY_NAME>_<SERVER_NAME>.
Technology: Technology linked to the data server.
Note: Appears only if the Technology selected for the Model is of the type Generic SQL.
User: User name used for connecting to the data server.
Password: Password linked with the user name.
Note: This password is stored encrypted in the repository.
Driver List: Provides a list of available drivers available to be used with the data server.
Driver: Name of the driver used for connecting to the data server.
URL: Provides the connection details.
Properties: Lists the properties that you can set for the selected driver.
Click Next.
In the Physical Schema panel of the wizard, fill in the physical schema fields.
Name: Name of the physical schema. It is calculated automatically and is read-only.
Datasource (Catalog): Name of the catalog in the data server.
Note: Appears only if the Technology selected supports Catalogs.
Schema (Schema): Name of the schema in the data server. Schema, owner, or library where the required data is stored.
Note: Oracle Data Integrator lists all the schemas present in the data server. Sometimes, Oracle Data Integrator cannot draw up this list. In this case, you should enter the schema name, respecting the case.
Datasource (Work Catalog): Indicate the catalog in which you want to create these objects. For some data validation or transformation operations, Oracle Data Integrator may require work objects to be created.
Note: Appears only if the Technology selected supports Catalogs.
Schema (Work Schema): Indicates the schema in which you want to create these objects. For some data validation or transformation operations, Oracle Data Integrator may require work objects to be created. If you do not set a Work Schema, it defaults to the Schema during execution
It is recommended that you create a specific schema dedicated to any work tables. By creating a schema named SAS or ODI in all your data servers, you ensure that all Oracle Data Integrator activity remains totally independent from your applications.
Note: Oracle Data Integrator lists all the schemas present in the data server. Sometimes, Oracle Data Integrator cannot draw up this list. In this case, you should enter the schema name, respecting the case.
Driver: Name of the driver used for connecting to the data server.
URL: Provides the connection details.
Properties: Specifies the properties for the selected driver.
Click Next.
Click Finish. The model and topology objects are created, but the model contains no datastore yet.
Reverse-engineering a Model
To automatically populate datastores into the model you need to perform a reverse-engineering for this model.
Standard Reverse-Engineering
A Standard Reverse-Engineering uses the capacities of the JDBC driver used to connect the data server to retrieve the model metadata.
To perform a Standard Reverse- Engineering:
In the Reverse Engineer tab of your Model:
Select Standard.
Select the Context used for the reverse-engineering
Select the Types of objects to reverse-engineer. Only object of these types will be taken into account by the reverse-engineering process.
Enter in the Mask field the mask of tables to reverse engineer. The mask selects the objects to reverse. This mask uses the SQL LIKE syntax. The percent (%) symbol means zero or more characters, and the underscore (_) symbol means one character.
Optionally, you can specify the characters to remove for the table alias. These are the characters to delete in order to derive the alias. Note that if the datastores already exist, the characters specified here will not be removed from the table alias. Updating a datastore is not applied to the table alias.
In the Selective Reverse-Engineering tab select Selective Reverse-Engineering, New Datastores, Existing Datastores and Objects to Reverse Engineer.
A list of datastores to be reverse-engineered appears. Leave those you wish to reverse-engineer checked.
Select Save from the File main menu.
Click Reverse Engineer in the Model toolbar menu.
Oracle Data Integrator launches a reverse-engineering process for the selected datastores. A progress bar indicates the progress of the reverse-engineering process.
The reverse-engineered datastores appear under the model node in the Models panel.
Customized Reverse-Engineering:
A Customized Reverse-Engineering uses a Reverse-engineering Knowledge Module (RKM), to retrieve metadata for a specific type of technology and create the corresponding datastore definition in the data model.
For example, for the Oracle technology, the RKM Oracle accesses the database dictionary tables to retrieve the definition of tables, attributes, keys, etc., that are created in the model.
To perform a Customized Reverse-Engineering using a RKM:
In the Reverse Engineer tab of your Model:
Select Customized.
Select the Context used for the reverse-engineering
Select the Types of objects to reverse-engineer. Only object of these types will be taken into account by the reverse-engineering process.
Enter in the Mask the mask of tables to reverse engineer.
Select the KM that you want to use for performing the reverse-engineering process. This KM is typically called RKM <technology>.<name of the project>.
Optionally, you can specify the characters to remove for the table alias. These are the characters to delete in order to derive the alias. Note that if the datastores already exist, the characters specified here will not be removed from the table alias. Updating a datastore is not applied to the table alias.
Click Reverse Engineer in the Model toolbar menu, then Yes to validate the changes.
Click OK.
The Session Started Window appears.
Click OK.
You can review the reverse-engineering tasks in the Operator Navigator. If the reverse-engineering process completes correctly, reverse-engineered datastores appear under the model node in the Models panel.
