Defining the Topology
The steps to create the topology in Oracle Data Integrator, which are specific to projects using ESB Cross- References KMs, are the following:
Create the data servers, physical and logical schemas corresponding to the sources and targets.
Create an Oracle data server, a physical and a logical schema called ESB_XREF for the schema containing the cross-reference table named XREF_DATA. If this table is stored in a data server already declared, you only need to create the schemas.
Note: For the generic procedure for creating the topology in Oracle Data Integrator please refer to the Oracle Data Integrator User’s Guide.
Setting up the Project
Import the following KMs into your Oracle Data Integrator project, if they are not already in your project:
IKM SQL Control Append (ESB XREF) LKM SQL to SQL (ESB XREF) or LKM MSSQL to SQL (ESB XREF) if using Microsoft SQL Server.
Designing an Interface with Oracle Data Integrator ESB Cross- References KMs
To create an integration interface, which both loads a target table from several source tables and handles cross-references between one of the sources and the target, run the following steps:
Create an interface with the source and target datastores which will have the cross-references.
Create joins, filters and mappings as usual. In the Diagram tab, make sure to check the UD1 flag for the column of the target datastore that will be the placeholder for the Common ID. You do not need to map this column.
In the Flow tab of the interface, select the source set containing the source table to cross-reference, and select the LKM SQL to SQL (ESB XREF) or LKM MSSQL to SQL (ESB XREF) if the source data store is in Microsoft SQL Server.
Specify the KM options as follows: SRC_PK_EXPRESSION Specify the expression representing the Source Primary Key value that you want to store in the XREF table. If the source table has just one column defined as a key, enter the column name (for example SEQ_NO). If the source key has multiple columns, specify the expression to use for deriving the key value. For example, if there are two key columns in the table and you want to store the concatenated value of those columns as your source value in the XREF table enter SEQ_NO|DOC_DATE. This option is mandatory. SRC_UPDATE_DELETE_ACTION Indicates what action to take on the source records after integrating data into the target.
Specify NONE for no action on the source records.
Enter UPDATE to update the source records flag according to SRC SRC_UPD_EXPRESSION.
UPD COL and
If you select the UPDATE option you also need to specify the following options: SRC_PK_LOGICAL_SCHEMA, SRC_PK_TABLE_NAME, SRC_PK_TABLE_ALIAS, SRC_UPD_COL, and SRC_UPD_EXPRESSION.
Enter DELETE to delete the source records after the integration.
If you select the UPDATE option, you also need to specify the following options: SRC_PK_LOGICAL_SCHEMA, SRC_PK_TABLE_NAME, and SRC_PK_TABLE_ALIAS.
Knowledge Modules Reference Guide