This chapter explains 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:
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.
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 Appendix B, "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 follows a pattern common to all RKMs.
This patterns includes the following steps:
Call the OdiReverseResetTable tool to reset the SNP_REV tables from previous executions.
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.
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.
This section provides examples of reverse-egineering strategies.
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.
This task resets the content of the SNP_REV tables for the current model.
OdiReverseResetTable -MODEL=<%=odiRef.getModel("ID")%>
This task retreives the list of tables from the Oracle system tables and loads this content into the SNP_REV tables.
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 ... ...
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)
Subsequent commands use the same pattern to load the SNP_REV tables from the content of the Oracle system tables.