Oracle® Fusion Middleware Connectivity and Knowledge Modules Guide for Oracle Data Integrator 11g Release 1 (11.1.1) E12644-05 |
|
![]() Previous |
![]() Next |
This chapter describes how to work with Oracle SOA Suite cross references in Oracle Data Integrator.
This chapter includes the following sections:
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.
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.
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.
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:
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.
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.
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). |
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:
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.
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.
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.
Make sure you have read the information in this section before you start using the SOA XREF Knowledge Modules:
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
.
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:
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:
This section consists of the following topics:
The steps to create the topology in Oracle Data Integrator, which are specific to projects using SOA XREF KMs, are the following:
Create the data servers, physical and logical schemas corresponding to the sources and targets.
Create a data server and a physical schema for the Oracle or Microsoft SQL Server technology as described in the following sections:
Section 2.3.1, "Creating an Oracle Data Server" and Section 2.3.2, "Creating an Oracle Physical Schema"
Section 7.3.1, "Creating a Microsoft SQL Server Data Server" and Section 7.3.2, "Creating a Microsoft SQL Server Physical Schema"
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.
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.
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
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.
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'
.
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.
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)"
Select your staging area in the Flow tab of the interface and select the IKM SQL Control Append (SOA XREF).
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. |
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:
|
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: |
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: |
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: |
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 |
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 |
DELETE_TEMPORARY_OBJECTS |
Yes|No |
Yes |
Set this option to |
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 |
XREF_TABLE_NAME |
XREF table name |
Yes, if using Legacy XREF table structure. |
Table Name to use in the XREF table. Example: |
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: |
XREF_ROWNUMBER_EXPRESSION |
SYS_GUID() |
Yes |
Enter the expression used to populate the row_number for the XREF table. For example for Oracle: |
XREF_DATA_STRUCTURE |
New|Legacy |
Yes |
Enter |
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_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. |