1. Developing Knowledge Modules with Oracle Data Integrator
  2. Reverse-Engineering Strategies

4 Reverse-Engineering Strategies

It is important to understand the customized reverse-engineering process and the strategies used in the Reverse-engineering Knowledge Modules for retrieving advanced metadata.

This chapter includes the following sections:

Customized Reverse-Engineering Process

Oracle Data Integrator Standard Reverse-Engineering relies on the capabilities of the driver used to connect a given data server to return rich metadata describing the data strucuture.

When this metadata is not accurate, or needs to be enriched with some metadata retrieved from the data server, customized reverse-engineering can be used.

SNP_REV tables

The Oracle Data Integrator repository contains a set of metadata staging tables, called the SNP_REV tables.

These SNP_REV tables content is managed using the following tools:

  • OdiReverseResetTable resets the content of these tables for a given model.

  • OdiReverseGetMetadata populates these tables using a process similar to the standard JDBC reverse-engineering.

  • OdiReverseSetMetadata applies the content of these staging tables to the repository tables describing the datastores, columns, constraints, etc. This action modifies the Oracle Data Integrator model.

See SNP_REV Tables Reference for a reference of the SNP_REV table, and the Developer's Guide for Oracle Data Integrator for more information for a reference of the reverse-engineering tools.

Customized Reverse-Engineering Strategy

Customized Reverse-Engineering strategy follows a pattern common to all RKMs.

This patterns includes the following steps:

  1. Call the OdiReverseResetTable tool to reset the SNP_REV tables from previous executions.
  2. Load the SNP_REV tables. This is performed using three main patterns:

    • Retrieve metadata from the metadata provider and load them into to SNP_REV tables. This is the pattern used for example in the RKM Oracle.

    • Retreive metadata from a third party provider. This is the pattern used for example in the RKM File (FROM EXCEL). Metadata is not extracted from the files described in the model but from a Micrisift Excel Excel spreadsheet that contains the description of these files.

    • Pre-populate the SNP_REV tables using OdiReverseGetMetadata and then fix/enrich this metadata using queries targeting these tables.

  3. Call the OdiReverseSetMetaData tool to apply the changes to the current Oracle Data Integrator model.

In an RKM, the source and target commands work are follow:

  • The Command on Target specified with an Undefined technology on the Autocommit transaction targets the SNP_REV tables in the repository.

  • The Command on Source specified with an Undefined Schema on the Autocommit transaction retreives data from the data-server containing the data structure to reverse-engineer. If you want to use a metadata provider (for example an Excel spreadsheet), you must specify a specific technology and logical schema.

  • Calls to Tools (such as OdiReverseSetMetadata) are specified in the Command on Target, with the ODI Tools technology.

Case Studies

This section provides examples of reverse-egineering strategies.

RKM Oracle

The RKM Oracle is a typical example of a reverse-engineering process using a database dictionnary as the metadata provider.

The commands below are extracted from the RKM for Oracle and provided as examples. You can review the code of this knowledge module by editing it in Oracle Data Intagrator Studio.

Reset SNP_REV Tables

This task resets the content of the SNP_REV tables for the current model.

Command on Target (ODI Tools)

OdiReverseResetTable -MODEL=<%=odiRef.getModel("ID")%>
Get Tables

This task retreives the list of tables from the Oracle system tables and loads this content into the SNP_REV tables.

Command on Source

Select	t.TABLE_NAME			TABLE_NAME,	t.TABLE_NAME			RES_NAME,	replace(t.TABLE_NAME,'<%=odiRef.getModel("REV_ALIAS_LTRIM")%>','')		TABLE_ALIAS,	substr(tc.COMMENTS,1,250)		TABLE_DESC,	'T' TABLE_TYPE,	t.NUM_ROWS			R_COUNT,	SUBSTR(PARTITIONING_TYPE ,1,1) PARTITIONING_TYPE,	SUBSTR(SUBPARTITIONING_TYPE,1,1) SUBPARTITIONING_TYPEFrom	ALL_TABLES	t,	ALL_TAB_COMMENTS 	tc,	ALL_PART_TABLES tp
Where ...
...

Command on Target

insert into SNP_REV_TABLE(	I_MOD,	TABLE_NAME,	RES_NAME,	TABLE_ALIAS,	TABLE_TYPE,	TABLE_DESC,	IND_SHOW,	R_COUNT,	PARTITION_METH,	SUB_PARTITION_METH)values(	<%=odiRef.getModel("ID")%>,	:TABLE_NAME,	:RES_NAME,	:TABLE_ALIAS,	'T',	:TABLE_DESC,	'1',	:R_COUNT,	:PARTITIONING_TYPE,	:SUBPARTITIONING_TYPE)
Get views, partitions, columns, FK, Keys and other Oracle Metadata

Subsequent commands use the same pattern to load the SNP_REV tables from the content of the Oracle system tables.

Set Metadata

This task resets the content of the SNP_REV tables for the current model.

Command on Target (ODI Tools)

OdiReverseSetMetaData -MODEL=<%=odiRef.getModel("ID")%>