27 Oracle Enterprise Service Bus

27.1.1 Concepts

Cross-referencing is the Oracle Fusion Middleware Function, available through its Enterprise Service Bus (ESB) component, and leveraged typically by any loosely coupled integration, which is truly built on the Service Oriented Architecture. It is used to manage the runtime correlation between the various participating applications of the integration.

The cross-referencing feature of Oracle SOA Suite enables you to associate identifiers for equivalent entities created in different applications. For example, you can use cross-references to associate a customer entity created in one application (with native id Cust_100) with an entity for the same customer in another application (with native id CT_001).

Cross-reference (XRef) facilitates mapping of native keys for entities across applications. For example, correlate the same order across different ERP systems.

The implementation of cross-referencing uses an Oracle database schema to store a cross-reference table (called XREF_DATA) that stores information to reference records across systems and data stores.

The optional ability to update or delete source table data after the data is loaded into the target table is also a need in integration. This requires that the bulk integration provides support for either updating some attributes like a status field or purging the source records once they have been successfully processed to the target system.

27.1.2 Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 27-1 for handling ESB cross-references. The KMs use ESB specific features.

27.1.3 Overview of the XREF KM Process

The overall process can be divided into the following three main phases:

Loading Phase (LKM)

During the loading phase, a Source Primary Key is created using columns from the source table. This Source Primary Key is computed using a user-defined SQL expression that should return a VARCHAR value. This expression is specified in the SRC_PK_EXPRESSION KM option.

For example, for a source Order Line Table (aliased OLINE in the interface) you can use the following expression:

TO_CHAR(OLINE.ORDER_ID) || '-' || TO_CHAR(OLINE.LINE_ID)

This value will be finally used to populate the cross-reference table.

Integration and Cross-Referencing Phase (IKM)

During the integration phase, a Common ID is created for the target table. The value for the Common ID is computed from the expression in the XREF_SYS_GUID KM option. This expression can be for example:

• A database sequence (<SEQUENCE_NAME>. NEXTVAL)

• A function returning a global unique Id (SYS_GUID() for Oracle, NewID() for SQL Server)

This Common ID is pushed to the target columns of the target table that are marked with the UD1 flag.

Both the Common ID and the Source Primary Key are pushed to the cross-reference table (XREF_DATA). In addition, the IKM pushes to the cross-reference table a unique Row Number value that creates the cross-reference between the Source Primary Key and Common ID. This Row Number value is computed from the XREF_ROWNUMBER_EXPRESSION KM option, which takes typically expressions similar to the Common ID to generate a unique identifier.

The same Common ID is reused (and not re-computed) if the same source row is used to load several target tables across several interfaces with the Cross-References KMs. This allows the creation of cross-references between a unique source row and different targets rows.

Updating/Deleting Processed Records (LKM)

This optional phase (parameterized by the SRC_UPDATE_DELETE_ACTION KM option) deletes or updates source records based on the successfully processed source records:

• If SRC_UPDATE_DELETE_ACTION takes the DELETE value, the source records processed by the interface are deleted.

• If SRC_UPDATE_DELETE_ACTION takes the UPDATE value, the source column of the source records processed by the interface is updated with the SQL expression given in the SRC_UPD_EXPRESSION KM option. The name of this source column must be specified in the SRC_UPD_COL KM option.

27.2.1 System Requirements and Certifications

Before performing any installation you should read the system requirements and certification documentation to ensure that your environment meets the minimum installation requirements for the products you are installing.

The list of supported platforms and versions is available on Oracle Technical Network (OTN):

http://www.oracle.com/technology/products/oracle-data-integrator/index.html.

27.2.2 Technology Specific Requirements

There are no connectivity requirements for using ESB in Oracle Data Integrator. The requirements for the Oracle Database apply also to ESB. See Chapter 2, "Oracle Database" for more information.

27.2.3 Connectivity Requirements

There are no connectivity requirements for using ESB in Oracle Data Integrator. The requirements for the Oracle Database apply also to ESB. See Chapter 2, "Oracle Database" for more information.

27.3.1 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:

1. Create the data servers, physical and logical schemas corresponding to the sources and targets.

2. Create a data server for the Oracle technology as described in Section 2.3.1, "Creating an Oracle Data Server".

3. Under this Oracle data server, create 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.

   See "Creating a Physical Schema" and "Creating a Logical Schema" in the Oracle Fusion Middleware Developer's Guide for Oracle Data Integrator for more information.

27.3.2 Setting up the Project

Import the following KMs into your 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.

27.3.3 Designing an Interface with the 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:

1. Create an interface with the source and target datastores which will have the cross-references.

2. Create joins, filters and mappings as usual. Make sure to check the UD1 flag for the column of the target datastore that will be the placeholder for the Common ID. Note that you do not need to map this column.

3. 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.

4. 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. See Table 27-2 for a list of possible values.

     Table 27-2 Values of the SRC_UPDATE_DELETE_ACTION

     Value	Description
     NONE	Specify NONE for no action on the source records.
     UPDATE	Enter UPDATE to update the source records flag according to SRC_UPD_COL and SRC_UPD_EXPRESSION. 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.
     DELETE	Enter DELETE to delete the source records after the integration. If you select the DELETE option, you also need to specify the following options: SRC_PK_LOGICAL_SCHEMA, SRC_PK_TABLE_NAME, and SRC_PK_TABLE_ALIAS.

     
     

5. Select your staging area in the Flow tab of the interface and select the IKM SQL Control Append (ESB XREF).

6. Specify the KM options as follows:

   • XREF_TABLE_NAME - Enter the name of the source table that will be stored in the reference table.

   • XREF_COLUMN_NAME - This is the name of the source primary key that will be stored in the XREF table.

   • XREF_SYS_GUID_EXPRESSION - Expression to be used to computing the Common ID. This expression can be for example:

   • a database sequence (<SEQUENCE_NAME>.NEXTVAL)

   • a function returning a global unique Id (SYS_GUID() for Oracle and NewID() for SQL Server)

   • XREF_ROWNUMBER_EXPRESSION - This is the value that is pushed into the Row Number column of the XREF_DATA table. Use the default value of GUID unless you have the need to change it to a sequence.

   • FLOW_CONTROL - Set to YES in order to be able to use the CKM Oracle.

   Note:

   If the target table doesn't have any placeholder for the Common ID and you are for example planning to populate the source identifier in one of the target columns, you must use the standard mapping rules of ODI to indicate which source identifier to populate in which column.

   If the target column that you want to load with the Common ID is a unique key of the target table, it needs to be mapped. You must put a dummy mapping on that column. At runtime, this dummy mapping will be overwritten with the generated common identifier by the integration knowledge module. Make sure to flag this target column with UD1.