Validation

Content validated on 4/14/2021 with

• ODI Version 12.2.1.4.200721.1540

Introduction

This article discusses best practices on using the Reverse Engineering features of Oracle Data Integrator (ODI) on the cloud and on premises.  The first part of this article presents the various options available in ODI to reverse-engineer metadata from a data server.  Then, the article discusses performance considerations when running and executing reverse-engineering tasks.  The last section of this article discusses the ODI reverse-engineering best practices.

RKMs are one of seven categories of Knowledge Modules (KMs) found in ODI.  Other categories of KMs include: Loading, Check, Integration, Extract, Journalizing, and Service.  This article focuses on the selection and use of RKMs.    If you would like to learn more about other categories of KMs, go to “Oracle Data Integrator (ODI) Knowledge Modules (KMs).”

Oracle Data Integrator Best Practices:  Using Reverse-Engineering on the Cloud and on Premises

In ODI, reverse-engineering is the process of selecting metadata from a data server and populating the selected metadata into an ODI model.  An ODI model contains objects or datastores such as tables, views, queues, and synonyms.  ODI models also contain attributes, keys, and constraints for each datastore.   An ODI model is connected to an ODI logical schema of a given ODI technology.

Figure 1, below, shows an example of an ODI model called Staging.  This ODI model is connected to an ODI logical schema called Oracle Warehouse – Staging.  The ODI technology for this logical schema is Oracle.  The ODI reverse-engineering options are located on the menu options of the ODI Model screen, as illustrated on Figure 1, below.

Figure 1 - ODI Reverse-Engineering – ODI Model

There are two reverse-engineering options in ODI:  Reverse Engineer, and Selective Reverse-Engineering.  The following section of this article discusses these two options.

Using the ODI Reverse Engineer Option

Figure 2, below, shows the ODI Reverse Engineer option for the ODI model called Staging.  This option offers two ways of performing a reverse-engineering task:  Standard, and Customized.  Figure 2, below, shows the Standard option.  The Standard option is the default option; it provides basic reverse-engineering capabilities – users can retrieve a minimum set of attributes with this option.

Figure 2 - ODI Reverse-Engineering – Standard Option

The Standard option can filter the selection of metadata by object type.  In this example, on Figure 2, above, the selected object type is Table; thus, only Oracle tables will be reverse-engineered.  The Mask option provides additional filtering capabilities.  In this example, the reverse-engineer task only brings the Oracle tables starting with a name of STG, followed by any additional characters - the percent wildcard (%) specifies any characters.

In this example, on Figure 2, above, the Standard option retrieves the name of the Oracle tables, the table attributes, and the table constraints.  The table attributes include the column names, the column types, and the column lengths.    The table constraints include primary keys, unique keys, foreign keys, and check constraints.

Figure 3, below, show a list of datastores for this ODI model: STG_CUSTOMER, STG_ORDERS, STG_PRODUCT, and STG_STATUS.  These datastores have been reverse-engineered with the Standard option.  The attributes of the datastore called STG_CUSTOMER are also illustrated on Figure 3, below:

Figure 3 - ODI Reverse-Engineering – ODI Data Stores

The Standard option uses the Java Database Connectivity (JDBC) API to retrieve metadata from a data server.  The JDBC API is the industry standard for database-independent connectivity between Java applications and a wide range of databases – the ODI Studio is a Java application.  The Standard option has an extensive number of features, but it can only retrieve a limited set of metadata due to the limitations of the JDBC API driver.  For instance, if an Oracle table is partitioned, the Standard option cannot reverse-engineer the partitions of a table because the JDBC API driver does not support the selection of table partitions.  On the other hand, the Customized option provides additional features, and it can retrieve additional metadata such as table partitions from a data server.  The Customized option requires a Reverse-Engineering Knowledge Module (RKM), which can be customized to perform additional tasks.  When using the Customized option, the reverse-engineering task can be executed with the local agent of the ODI Studio (default), or with any agent configured in the ODI Topology.  Figure 4, below, shows the Customized option:

Figure 4 - ODI Reverse-Engineering – Customized Option

In this example, on Figure 4, above, the Customized option uses a logical agent called OracleDIAgent-JCS.  This ODI agent is a J2EE agent, located on an instance of the Oracle Java Cloud Service (JCS).  In this example, the type of object to reverse-engineer is Table, and the Mask option has been set to STG% – all tables starting with a prefix of STG will be reverse-engineered.  The RKM for this reverse-engineering task is the RKM Oracle.  Also, the options for this RKM are illustrated on Figure 4, above.

Figure 5, below, shows a list of tasks for this RKM.  Some of the RKM tasks include retrieving partitions, foreign keys (FK), index keys, table conditions, and other metadata from the Oracle database.

Figure 5 - ODI Reverse-Engineering – RKM Oracle Tasks

When the Customized option is used for a reverse-engineer task, the ODI agent executes the code generated by the RKM, and the ODI model gets populated with metadata from the data server.  Figure 6, below, shows the Partitions screen of an ODI datastore called W_ORDERS_F.  In this example, the partitions for this datastore – an Oracle partitioned table – have been populated using the RKM Oracle.

Figure 6 - ODI Reverse-Engineering – Datastore Partitions

Using the ODI Selective Reverse-Engineering Option

The Selective Reverse-Engineering option, illustrated on Figure 7, below, offers additional capabilities such as reverse-engineering new datastores, and existing datastores.  This option works in conjunction with the Standard option, and it is only available if the Standard option is selected in the Reverse Engineer tab.  This option allows users to select from a list of objects before executing the reverse-engineering task.

Figure 7 - ODI Reverse-Engineering – Selective Reverse-Engineering

Figure 7, above, shows a list of objects to be reverse-engineered:  STG_CUSTOMER, STG_ORDERS, STG_PRODUCT, and STG_STATUS.   These objects are Oracle tables that the Selective Reverse-Engineering option found when the Objects to Reverse Engineer check-box was selected.  The objects listed on Figure 7, above, are the result from the filters put in place in the Reverse Engineer tab.

If these objects already exist in the ODI model, and the Reverse Engineer Execution button is clicked, the metadata for the existing objects will be updated.  If the objects are new, they will be added to the ODI model.

ODI Reverse-Engineering Considerations

The execution time of a reverse-engineering task depends on several factors.  For instance, a large number of tables and columns may take longer to reverse-engineer than a small set of tables or columns.  Also, the location of the ODI Studio and the type of agent used for the reverse-engineering task may also have an impact on the overall execution time.  For instance, let’s assume that an ODI user wants to reverse-engineer a set of Oracle tables located on an instance of DBCS.  Also, let’s assume that the ODI repository is located on another instance of DBCS.  Let’s assume that the ODI user selects the Standard option to execute a reverse-engineer task, and the task is executed from the premises of the ODI user.  Under this scenario, executing the reverse-engineer task from the premises of the ODI user is not a recommended strategy.  Figure 8, below, shows an example of this unfavorable practice:

Figure 8 - ODI Reverse-Engineering – On-Premise ODI Studio

In this example, on Figure 8, above, the selected metadata must be exported from Instance A – where the source data server is located – to the promises of the ODI user – where the ODI Studio is located.  Then, the local agent must upload the selected metadata from the ODI Studio into Instance B – where the ODI repository is located.  This strategy does not offer the best performance, since the content of the selected metadata must travel from the cloud to the on premises of the user, and then back to the cloud.

The best strategy is to execute the reverse-engineering task from an instance of the ODI Studio that is running on the Oracle Cloud such as the Oracle Java Cloud Service (JCS) or the Oracle Compute Cloud Service (CCS).  Figure 9, below, shows an example:

Figure 9 - ODI Reverse-Engineering - ODI Studio on JCS

This strategy, shown on Figure 9, above, offers the best performance when performing reverse-engineering tasks between database cloud services.  The same strategy can be applied to other SQL databases that are on other cloud services.

ODI Reverse-Engineering Best Practices

When using the reverse-engineering features of ODI, follow these rules of thumb:

• Use the reverse-engineering Standard option if a minimum set of attributes – such as table name, table columns, and table constraints – are needed for an ODI model.
• Use the reverse-engineering Customized option if the Standard option does not populate the required metadata due to the limitations of the JDBC API driver.
• When using the Customized option, select and import the RKM that supports the technology of the ODI model.  Get familiar with the options and steps of the selected RKM before using it.  If a RKM needs to be modified for additional tasks, rename the RKM and document the changes.
• When performing the reverse-engineering task, use the Mask option to filter the number of objects to be reverse-engineered.  The Mask option is available on both the Standard option and the Customized option.
• When the Standard option is used for a reverse-engineering task, run the ODI Studio in a location that is near both data servers: the source data server and the ODI repository data server.  This will reduce the amount of time it may take to populate the ODI model with the desired metadata.
• Use the best available ODI agent when performing a reverse-engineering task.  The best available ODI agent is the one that is located the closest to both the source data server and the ODI repository data server.
• When performing a reverse-engineering task, ensure the connection-user executing the task has the necessary privileges to access the metadata from the source data server.  The connection-user is the user configured in the physical data server of the ODI Topology.  If the reverse-engineering task completes successfully but does not populate any metadata into the ODI model, the connection-user may not have the necessary privileges to read and access the metadata from the data server.

Conclusion

The ODI reverse-engineering features offer a mechanism to retrieve metadata from a data server and to populate the metadata into an ODI model.  This metadata can then be used in ODI mappings to build data integration tasks.  The ODI reverse-engineering features offer various options – Standard and Customized – to reverse engineer objects from a data server.   The Standard option leverages the JDBC driver to retrieve metadata from a data server.   The Customized option leverages RKMs to retrieve and populate additional metadata that cannot be retrieved with the Standard option.  These RKMs offer additional features and options to reverse-engineer additional metadata from a data server.

For more Oracle Data Integrator best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit “Oracle A-team Chronicles for Oracle Data Integrator (ODI).”