Recommended Sequence for Implementing Siebel EIM Processes

The following sequence is recommended for implementing Siebel EIM processes:

Customize and test the IFB file to meet the business requirements.
Tune the IFB file parameters.
Separate the Siebel EIM processes.
Set the database parameters, making sure that the basic requirements are met, including the hardware, the settings, and no or minimal fragmentation.

Before you start optimizing Siebel EIM processes, make sure there are no network problems or server performance problems that can affect the results. Oracle Advanced Customer Services recommends using at least 100 MB network segments and network-interface cards (NICs) to connect the Siebel Server and the Siebel database, and also recommends using a network switch or similar technology, rather than a hub, to maximize throughput. Contact your Oracle sales representative to request assistance from Oracle Advanced Customer Services.

This topic contains the following information:

"Optimizing the IFB File for Siebel EIM"
"Checking IFB File Optimization for Siebel EIM"
"Separating Siebel EIM Processes by Operation"

Optimizing the IFB File for Siebel EIM

This topic is part of "Recommended Sequence for Implementing Siebel EIM Processes".

When you have finished coding and testing the IFB file to meet your business requirements, the next step is to optimize the IFB file. The selected parameters in each section of the IFB file determine the focus of each Siebel EIM task. The following recommendations are provided for each section of the IFB file:

ONLY BASE TABLES or IGNORE BASE TABLES. These parameters specify and restrict the selected base tables for the Siebel EIM process. A single EIM table (sometimes referred to as an interface table) is mapped to multiple user or base tables. For example, the table EIM_ACCOUNT is mapped to S_PARTY, S_ORG_EXT, and S_ADDR_ORG, as well as other tables. The default configuration is to process all base tables for each EIM table.

Note:

Oracle Advanced Customer Services strongly recommends that you always include these parameters in every section of the IFB file, and list only those tables and columns that are relevant for a particular Siebel EIM task. Contact your Oracle sales representative to request assistance from Oracle Advanced Customer Services.

ONLY BASE COLUMNS or IGNORE BASE COLUMNS. These parameters specify and restrict the selected base columns for the Siebel EIM process. The default is to process all base columns for each base table. It is likely that you are not using every column in a base table, and these parameters will make sure that Siebel EIM is only processing the desired columns in the table.

You will see an additional performance increase if you exclude those columns that are defined as foreign keys (FKs) and are not used by the Siebel configuration, because Siebel EIM does not need to perform the interim processing (using SQL statements) to resolve the values for these FKs. Set the Siebel EIM task parameter Error Flags to a value of 1 to see which FKs are failing to be resolved by Siebel EIM (you might have missed excluding that FK with this parameter).

Note:
Do not use the IGNORE BASE COLUMNS parameter for merge processes or export processes. Use this parameter only for import processes and delete processes.

Checking IFB File Optimization for Siebel EIM

This topic is part of "Recommended Sequence for Implementing Siebel EIM Processes".

One method to find out whether the IFB file is optimized is to check the status of the records being processed in the EIM tables. This indicates if there are tables or columns that are being processed unnecessarily. The following query can be used to check the status of records in an EIM table:

select count(*), IF_ROW_STAT from EIM Table
where IF_ROW_BATCH_NUM = ?
group by IF_ROW_STAT;

If many rows have a status of PARTIALLY IMPORTED, then it is likely that further tuning can be done by excluding base tables and columns that are not necessary. For example, two tests were run to import 5000 accounts from EIM_ACCOUNT table. The first test included all of the base tables while the second test only focused on the four necessary tables by including the following line in the IFB file:

ONLY BASE TABLES = S_ORG_EXT, S_ADDR_ORG, S_ACCNT_POSTN, S_ORG_TYPE

The first test took 89 minutes to import (excluding the Updating Primaries step), while the second test took only 2 minutes to import (excluding the Updating Primaries step).

Separating Siebel EIM Processes by Operation

This topic is part of "Recommended Sequence for Implementing Siebel EIM Processes".

Wherever possible, divide the Siebel EIM batches into insert-only transactions and update-only transactions. For example, assume that you are loading 50,000 records into an EIM table as part of a weekly process. 10,000 records represent new data and 40,000 records represent updates to existing data.

By default, Siebel EIM can determine which records are to be added and which records are to be updated in the base tables, however, Siebel EIM will need to perform additional processing (through SQL statements) to make these determinations. If you were able to divide the 50,000 records into different batch numbers based on the type of transaction, then you could avoid this additional processing.

In addition, the columns being processed as part of the update activity might be fewer than those for the insert activity (resulting in an additional performance increase). To illustrate this, the IFBs in the preceding example can be coded with the following sections:

IFB for mixed transactions:

[Weekly Accounts]
TYPE = IMPORT
BATCH = 1-10
TABLE = EIM_ACCOUNT
ONLY BASE TABLES = S_ORG_EXT
IGNORE BASE COLUMNS = S_ORG_EXT.?

IFB for separate insert or update transactions:

[Weekly Accounts – New]
TYPE = IMPORT
BATCH = 1-2
TABLE = EIM_ACCOUNT
ONLY BASE TABLES = S_ORG_EXT
IGNORE BASE COLUMNS = S_ORG_EXT.?
INSERT ROWS = TRUE
UPDATE ROWS = FALSE

[Weekly Accounts – Existing]
TYPE = IMPORT
BATCH = 3-10
TABLE = EIM_ACCOUNT
ONLY BASE TABLES = S_ORG_EXT
ONLY BASE COLUMNS = S_ORG_EXT.NAME, S_ORG_EXT.LOC, S_ORG_EXT.?
INSERT ROWS = FALSE
UPDATE ROWS = TRUE