30 Oracle SOA Suite Cross References

This chapter describes how to work with Oracle SOA Suite cross references in Oracle Data Integrator.

This chapter includes the following sections:

30.1 Introduction

Oracle Data Integrator features are designed to work best with Oracle SOA Suite cross references, including integration interfaces that load a target table from several source tables and handle cross references.

30.1.1 Concepts

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

30.1.1.1 General Principles

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-referencing (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 a database schema to store a cross reference information to reference records across systems and data stores.

For more information about cross references, see "Working with Cross References" in the Oracle Fusion Middleware Developer's Guide for Oracle SOA Suite.

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.

30.1.1.2 Cross Reference Table Structures

The XREF data can be stored in multiple cross reference tables and in two formats:

  • Generic (legacy) table - The table name is XREF_DATA and the table structure stores the cross references for all entities. The table format is as follows:

    XREF_TABLE_NAME  NOT NULL VARCHAR2(2000)
    XREF_COLUMN_NAME NOT NULL VARCHAR2(2000)
    ROW_NUMBER NOT NULL VARCHAR2(48)
    VALUE  NOT NULL VARCHAR2(2000)
    IS_DELETED  NOT NULL VARCHAR2(1)
    LAST_MODIFIED NOT NULL TIMESTAMP(6)
    

    This table stores cross references for multiple entities. In this table:

    • XREF_TABLE_NAME is the name of the cross reference table

    • XREF_COLUMN_NAME is the name of the column to be populated. This column name, for example the application name, is used as a unique identifier for the cross reference table.

    • ROW_NUMBER stores a unique identifier (Row Number) for a given entity instance, regardless of the application

    • VALUE is the value of the record identifier for a given entity in this application

    A specific XREF_COLUMN_NAME entry called COMMON exists to store a generated identifier that is common to all applications.

    For example, an ORDER existing in both SIEBEL and EBS will be mapped in a generic table as shown below:

    Table 30-1 Example of an XREF_DATA (Partial)

    XREF_TABLE_NAME XREF_COLUMN_NAME ROW_NUMBER VALUE

    ORDER

    SIEBEL

    100012345

    SBL_101

    ORDER

    EBS

    100012345

    EBS_002

    ORDER

    COMMON

    100012345

    COM_100


  • Custom (new) table structure - The table is specific to one entity and has a custom structure. For example:

    ROW_ID  VARCHAR2(48) NOT NULL PK, 
    APP1   VARCHAR2(100), 
    APP2   VARCHAR2(100), 
    ...
    COMMON   VARCHAR2(100), 
    LAST_MODIFIED  TIMESTAMP NOT NULL
    

    Where:

    • Columns such as APP1 and APP2 are used to store PK values on different applications and link to the same source record

    • ROW_ID (Row Number) is used to uniquely identify records within a XREF data table.

    • COM holds the common value for the integration layer and is passed among participating applications to establish the cross reference

    The same ORDER existing in both SIEBEL and EBS would be mapped in a custom XREF_ORDER table as shown below:

    Table 30-2 Example of a Custom Table: XREF_ORDERS (Partial)

    ROW_ID SIEBEL EBS COMMON

    100012345

    SBL_101

    EBS_002

    COM_100


    See Section 30.3.3, "Designing an Interface with the Cross-References KMs" and Section 30.4, "Knowledge Module Options Reference" for more information.

30.1.1.3 Handling Cross Reference Table Structures

The IKM SQL Control Append (SOA XREF) provides the following parameters to handle these two table structures:

  • XREF_DATA_STRUCTURE: This option can be set to legacy to use the XREF_DATA generic table, or to new to use the custom table structure.

If using the generic table structure, you must set the following options:

  • XREF_TABLE_NAME: Value inserted in the XREF_TABLE_NAME column of the XREF_DATA table. In the example above (See Table 30-1) this option would be ORDER.

  • XREF_COLUMN_NAME: Value inserted in the XREF_COLUMN_NAME column of the XREF_DATA table. This value corresponds to the application that is the target of the current interface. In the example above (See Table 30-1), this option would take either the value SIEBEL or EBS depending on which system is targeted.

If using the custom table structure, you must use the following options:

  • XREF_DATA_TABLE: Name of the cross reference table. It defaults to XREF_DATA. In the example above (See Table 30-2), this table name would be XREF_ORDER.

  • XREF_DATA_TABLE_COLUMN: Name of the column that stores the cross references for the application that is the target of the current interface. In the example above (See Table 30-2), this option would take either the value SIEBEL or EBS depending on which system is targeted.

30.1.2 Knowledge Modules

Oracle Data Integrator provides the Knowledge Modules (KM) listed in Table 30-3 for handling SOA cross references (XREF).

These new Knowledge Modules introduce parameters to support SOA cross references. See Section 30.1.1.2, "Cross Reference Table Structures" and Section 30.3.3, "Designing an Interface with the Cross-References KMs" for more information on these parameters.

Table 30-3 SOA XREF Knowledge Modules

Knowledge Module Description

LKM SQL to SQL (SOA XREF)

This KM replaces the LKM SQL to SQL (ESB XREF).

This KM supports cross references while loading data from a standard ISO source to any ISO-92 database.

Depending of the option SRC_UPDATE_DELETE_ACTION, this LKM can DELETE or UPDATE source records.

The LKM SQL to SQL (SOA XREF) has to be used in conjunction with the IKM SQL Control Append (SOA XREF) in the same interface.

LKM MSSQL to SQL (SOA XREF)

This KM replaces the LKM MSSQL to SQL (ESB XREF).

This KM is a version of the LKM SQL to SQL (SOA XREF) optimized for Microsoft SQL Server.

IKM SQL Control Append (SOA XREF)

This KM replaces the IKM SQL Control Append (ESB XREF).

This KM provides support for cross references while integrating data in any ISO-92 compliant database target table in truncate/insert (append) mode. This KM provides also data control: Invalid data is isolated in an error table and can be recycled.When loading data to the target, this KM also populates PK/GUID XREF table on a separate database.

This IKM SQL Control Append (SOA XREF) has to be used in conjunction with the LKM SQL to SQL (SOA XREF) or LKM MSSQL to SQL (SOA XREF).


30.1.3 Overview of the SOA XREF KM Process

To load the cross reference tables while performing integration with Oracle Data Integrator, you must use the SOA XREF knowledge modules. These knowledge modules will load the cross reference tables while extracting or loading information across systems.

Note:

In order to maintain the cross referencing between source and target systems, the LKM and IKM supporting cross referencing must be used in conjunction.

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

  1. Loading Phase (LKM)

  2. Integration and Cross-Referencing Phase (IKM)

  3. Updating/Deleting Processed Records (LKM)

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

30.1.3.2 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 can also be automatically 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. 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.

30.1.3.3 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, a source column of the source table will be updated with an expression for all the processed source records. The following KM options parameterize this behavior:

    • SRC_UPD_COL: Name of the source column to update

    • SRC_UPD_COL_EXPRESSION: Expression used to generate the value to update in the column

It is possible to execute delete and update operations on a table different table from the source table. To do this, you must set the following KM options in the LKM:

  • SRC_PK_LOGICAL_SCHEMA: Oracle Data Integrator Logical schema containing the source table to impact.

  • SRC_PK_TABLE_NAME: Name of the source table to impact.

  • SRC_PK_TABLE_ALIAS: Table alias for this table.

30.2 Installation and Configuration

Make sure you have read the information in this section before you start using the SOA XREF Knowledge Modules:

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

30.2.2 Technology Specific Requirements

There are no technology requirements for using Oracle SOA Suite cross references in Oracle Data Integrator. The requirements for the Oracle Database and Microsoft SQl Server apply also to Oracle SOA Suite cross references. For more information, see:

30.2.3 Connectivity Requirements

There are no connectivity requirements for using Oracle SOA Suite cross references in Oracle Data Integrator. The requirements for the Oracle Database and Microsoft SQl Server apply also to Oracle SOA Suite cross references. For more information, see:

30.3 Working with XREF using the SOA Cross References KMs

This section consists of the following topics:

30.3.1 Defining the Topology

The steps to create the topology in Oracle Data Integrator, which are specific to projects using SOA XREF KMs, are the following:

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

  2. Create a data server and a physical schema for the Oracle or Microsoft SQL Server technology as described in the following sections:

    This data server and this physical schema must point to the Oracle instance and schema or to the Microsoft SQL Server database containing the cross reference tables.

  3. Create a logical schema called XREF pointing to the physical schema. containing the cross reference table.

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

30.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 (SOA XREF)

  • LKM SQL to SQL (SOA XREF) or LKM MSSQL to SQL (SOA XREF) if using Microsoft SQL Server

30.3.3 Designing an Interface with the 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.

    Mapping the Common ID: If you want to map in a target column the Common ID generated for the cross reference table, check the UD1 flag for this column and enter a dummy mapping. For example a constant value such as'X'.

  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 (SOA XREF) or LKM MSSQL to SQL (SOA XREF) if the source data store is in Microsoft SQL Server.

  4. Specify the KM options as follows:

    • Specify in SRC_PK_EXPRESSION 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 SEQ_NO and DOC_DATE 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.

    • Optionally set the SRC_UPDATE_DELETE_ACTION to impact the source table, as described in Section 30.1.3.3, "Updating/Deleting Processed Records (LKM)"

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

  6. Specify the KM options as follows:

    • XREF_DATA_STRUCTURE: Enter New to use the new XREF_DATA Table structure. Otherwise enter Legacy to use legacy XREF_DATA Table. Default is New. Configure the options depending on the table structure you are using, as specified in Section 30.1.1.3, "Handling Cross Reference Table Structures"

    • XREF_SYS_GUID_EXPRESSION: Enter the 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. 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.

30.4 Knowledge Module Options Reference

This section lists the KM options for the following Knowledge Modules:

Table 30-4 LKM SQL to SQL (SOA XREF)

Option Values Mandatory Description

SRC_UPDATE_DELETE_ACTION

NONE|UPDATE|DELETE

Yes

Indicates what action to take on source records after integrating data into the target. See Section 30.1.3.3, "Updating/Deleting Processed Records (LKM)" for more information.

SRC_PK_EXPRESSION

Concatenating expression

Yes

Expression that concatenates values from the PK to have them fit in a single large varchar column. For example: for the source Orderline Table (aliased OLINE in the interface) you can use expression:

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

SRC_PK_LOGICAL_SCHEMA

Name of source table's logical schema

No

Indicates the source table's logical schema. The source table is the one from which we want to delete or update records after processing them. This logical schema is used to resolve the actual physical schema at runtime depending on the Context. For example: ORDER_BOOKING. This option is required only when SRC_UPDATE_DELETE_ACTION is set to UPDATE or DELETE.

SRC_PK_TABLE_NAME

Source table name, default is MY_TABLE

No

Indicate the source table name of which we want to delete or update records after processing them. For example: ORDERS This option is required only when SRC_UPDATE_DELETE_ACTION is set to UPDATE or DELETE.

SRC_PK_TABLE_ALIAS

Source table alias, default is

MY_ALIAS

No

Indicate the source table's alias within this interface. The source table is the one from which we want to delete or update records after processing them. For example: ORD. This option is required only when SRC_UPDATE_DELETE_ACTION is set to UPDATE or DELETE.

SRC_UPD_COL

Aliased source column name

No

Aliased source column name that holds the update flag indicator. The value of this column will be updated after integration when SRC_UPDATE_DELETE_ACTION is set to UPDATE with the expression literal SRC_UPD_EXPRESSION. The alias used for the column should match the one defined for the source table. For example: ORD.LOADED_FLAG. This option is required only when SRC_UPDATE_DELETE_ACTION is set to UPDATE.

SRC_UPD_EXPRESSION

Literal or expression

No

Literal or expression used to update the SRC_UPD_COL. This value will be used to update this column after integration when SRC_UPDATE_DELETE_ACTION is set to UPDATE. For example: RECORDS PROCESSED. This option is required only when SRC_UPDATE_DELETE_ACTION is set to UPDATE.

DELETE_TEMPORARY_OBJECTS

Yes|No

Yes

Set this option to NO if you wish to retain temporary objects (files and scripts) after integration. Useful for debugging.


LKM MSSQL to SQL (SOA XREF)

See Table 30-4 for details on the LKM MSSQL to SQL (SOA XREF) options.

Table 30-5 IKM SQL Control Append (SOA XREF)

Option Values Mandatory Description

INSERT

Yes|No

Yes

Automatically attempts to insert data into the Target Datastore of the Interface.

COMMIT

Yes|No

Yes

Commit all data inserted in the target datastore.

FLOW_CONTROL

Yes|No

Yes

Check this option if you wish to perform flow control.

RECYCLE_ERRORS

Yes|No

Yes

Check this option to recycle data rejected from a previous control.

STATIC_CONTROL

Yes|No

Yes

Check this option to control the target table after having inserted or updated target data.

TRUNCATE

Yes|No

Yes

Check this option if you wish to truncate the target datastore.

DELETE_ALL

Yes|No

Yes

Check this option if you wish to delete all the rows of the target datastore.

CREATE_TARG_TABLE

Yes|No

Yes

Check this option if you wish to create the target table.

DELETE_TEMPORARY_OBJECTS

Yes|No

Yes

Set this option to NO if you wish to retain temporary objects (tables, files and scripts) after integration. Useful for debugging.

XREF_TABLE_NAME

XREF table name

Yes, if using Legacy XREF table structure.

Table Name to use in the XREF table. Example: ORDERS. See Section 30.1.1.3, "Handling Cross Reference Table Structures" for more information.

XREF_COLUMN_NAME

Column name

Yes, if using Legacy XREF table structure.

Primary key column name to use as a literal in the XREF table. See Section 30.1.1.3, "Handling Cross Reference Table Structures" for more information.

XREF_SYS_GUID_EXPRESSION

SYS_GUID()

Yes

Enter the expression used to populate the common ID for the XREF table (column name "VALUE"). Valid examples are: SYS_GUID(), MY_SEQUENCE.NEXTVAL, and so forth.

XREF_ROWNUMBER_EXPRESSION

SYS_GUID()

Yes

Enter the expression used to populate the row_number for the XREF table. For example for Oracle: SYS_GUID(), MY_SEQUENCE.NEXTVAL and so forth.

XREF_DATA_STRUCTURE

New|Legacy

Yes

Enter New to use the new XREF_DATA Table structure.. Otherwise enter Legacy to use legacy XREF_DATA Table. Default is New. See Section 30.1.1.3, "Handling Cross Reference Table Structures" for more information.

XREF_DATA_TABLE

XREF table name

No. Can be used with custom XREF table structure.

Enter the name of the table storing cross reference information. Default is XREF_DATA. See Section 30.1.1.3, "Handling Cross Reference Table Structures" for more information.

XREF_DATA_TABLE_COLUMN

XREF data table column name

Yes, if using custom XREF table structure

For new XREF data structure only: Enter the column name of the XREF data table to store the source key values. See Section 30.1.1.3, "Handling Cross Reference Table Structures" for more information.