7078740 Eim Technical Doc

Technical Document

Enterprise Integration Manager

Document Objective Siebel EIM is a process provided by the Siebel CRM package, in order to import the legacy data, export, delete or merge existing data. This document explains the basics of EIM, differ different ent functi functions ons of EIM, EIM, confi configur guring ing the IFB file, file, EIM task task traci tracing ng techni technique ques s and debugging different kinds of errors. This document requires the users to have prior Siebel CRM a nd Oracle SQL knowledge.

7/20/2009

Confidential

Page i

Technical Document


Revision History Version

Date

Description of Revisions

0.1

14-Sep-2004

Creation of Document.

0.2

03-Nov-2004

Updated the comments of self review.

7/20/2009

Confidential

Page ii

Technical Document


Table of Contents 1.0 INTRODUCTION:...... INTRODUCTION:..................... .............................. ............................... ............................... .............................. ............................ ..............1 .1 2.0 EIM FUNCTIONS:..................... FUNCTIONS:.................................... ............................... ............................... ......................... ............................2 ..................2 2.1 2.2 2.3 2.4 2.5

IMPORTING DATA ATA:...................................................................................................2 EXPORTING DATA ATA:..................................................................................................2 DELETING DATA ATA:....................................................................................................2 MERGING DATA ATA:....................................................................................................2 PROCESS FLOW BETWEEN EIM & DATABASE :......................................................................2

3.0 SIEBEL INTERFACE TABLES:....................... TABLES:...................................... .............................. ......................... .........................3 ...............3 3.1 COLUMN MAPPING:.................................................................................................4 4.0 CONFIGURATION FILE:............................ FILE:............................................ ............................... .............................. ......................... ............5 ..5 4.1 GENERAL HEADER PARAMETERS:....................................................................................5 4.2 GENERAL PROCESS PARAMETERS:...................................................................................6 4.3 IMPORT PROCESS PARAMETERS:.....................................................................................8 4.4 EXPORT PROCESS PARAMETERS:...................................................................................11 4.5 DELETE PROCESS PARAMETERS :...................................................................................11 4.6 MERGE PROCESS PARAMETERS :...................................................................................13 4.7 EXTENDED PARAMETERS:..........................................................................................13 4.7.1 User-Defined Extended Parameters:.................. Parameters:............................... .................................. ..................... ........14 4.7.2 Predefined Extended Parameters:.................... Parameters:............................................ .................................. ....................14 ..........14 5.0 IMPORT PROCESS:.................... PROCESS:................................... ............................... ............................... .............................. ....................... ..........16 ..16 5.1 5.2 5.3 5.4

IMPORT PROCESS STEPS:.........................................................................................16 MANUAL IMPORTING PROCEDURE:.................................................................................18 DIFFERENT IF_ROW_STAT VALUES:..........................................................................19 RECOMMENDED IMPORTING ORDER:...............................................................................19

6.0 EXPORT PROCESS:................... PROCESS:.................................. .............................. .............................. .............................. ..................... ............21 ......21 6.1 EXPORT PROCESS STEP: .........................................................................................21 6.2 MANUAL EXPORTING PROCEDURE :.................................................................................21 7.0 DELETE PROCESS:.................... PROCESS:................................... .............................. ........................................................23 .........................................23 7.1 DELETE TYPES:...................................................................................................23 7.2 DELETE PROCESS STEPS:.........................................................................................23 7.3 MANUAL DELETING PROCEDURE:..................................................................................24 8.0 MERGE PROCESS:................. PROCESS:................................ ............................... ............................... ................................... .............................25 .........25 8.1 MERGE PROCESS STEPS:.........................................................................................25 8.2 MANUAL MERGING PROCEDURE:..................................................................................26 9.0 RUNNING EIM:.......................... EIM:.......................................... ............................... ......................... ............................... .............................27 ........27 9.1 9.2 9.3 9.4 9.5

RUNNING EIM THROUGH GUI:..................................................................................27 RUNNING EIM THROUGH COMMAND LINE INTERFACE:............................................................27 ERROR FLAGS:....................................................................................................28 SQL TRACE FLAGS:..............................................................................................28 TRACE FLAGS:....................................................................................................28

10.0 DEBUGGING EIM:........................ EIM:....................................... .............................. ...................................................30 ....................................30 11.0 FAQS:...................... FAQS:...................................... ............................... .............................. ........................... ................................ ............................32 ........32

7/20/2009

Confidential

Page iii

Technical Document


ACRONYMS:.............................................................................................................34

7/20/2009

Confidential

Page iv

Technical Document


1.0 Introduction: Siebel EIM (Enterprise Integration Manager) is server task that deals with the exchange of data between intermediary tables called interface tables and Siebel base tables.

Siebel Interface Table External Database

EIM External Flat Files

Siebel Base Tables

External Data Sources

7/20/2009

Confidential

Page 1

Technical Document


2.0 EIM Functions: Siebel EIM can be used to achieve four kinds of functionality in which all the functions deal with the data directly. directly. The functions are viz. • • • •

Import new and revised data into Siebel Base Tables Tables Export existing data from Siebel Base Tables Delete existing data from Siebel Base Tables Merge existing data in Siebel Base Tables

2.1 Importing Data: Data importing comes into picture under following business requirements: •

• •

Initial implementation of Siebel application (Loading product data from the legacy database) Maintaining the Siebel database in long run (Importing archived data) Periodical updating of data into Siebel database from a non Siebel database

2.2 Exporting Data: Data exporting is done under the following business requirements: • •

Transferring Siebel data to a non Siebel applications Archival of data

2.3 Deleting Data: In the process of maintaining Siebel database, you will land up on certain requirements where you need to delete the existing data like removal of obsolete data. Ex: Obsolete products. 2.4 Merging Data: Merging of data arises in situations where you need to de-duplicate (removal of duplicates) the data. 2.5 Process Flow between EIM & Database: •

•

• •

Data has to be loaded into EIM tables (Interface tables). This is applicable for import, delete or merge operation. For export function, this step is not required. Edit Edit the EIM Configu Configurat ration ion file. In short short called as IFB file, this file defines defines the parameters like type of EIM, the EIM table used, the batch ba tch number, number, etc… Run the EIM task either through GUI or command line. Verify the results. EIM generates log file for every task and you can use this log file for debugging.

7/20/2009

Confidential

Page 2

Technical Document


3.0 Siebel Interface Tables: Interface tables act as staging area which holds the data that has to be imported, data exported from the Siebel base tables, data to be deleted and that to be merged. EIM process operates upon these tables and does the specified operation. Every interface table has columns that get mapped to base tables, few mandatory columns and many temporary columns. The mandatory columns are ROW_ID, IF_ROW_STAT and IF_ROW_BATCH_NUM. The temporary columns are those that are used by EIM task to manipulate data values at run times. These temporary columns store the ROW IDs of records, status of the record, unique uniquenes ness s of the record records, s, whethe whetherr the record record alread already y exists exists in the databas database. e. Some Some temporary columns store the ROW ID values of deleted, merged, exported rows. Every interface table, while loading data into, has to satisfy certain prerequisites as stated below: • • • •

Mandatory columns needs to be populated All the required columns has to be populated Populate the IF_ROW_BATCH_NUM IF_ROW_BATCH_NUM with appropriate batch number Set the IF_ROW_STA IF_ROW_STAT column with meaningful values

Every Siebel interface table has 4 types of temporary columns as defined below: • • • •

UNQ column EXS column RID column STA column

Each of the temporary columns has a naming format as follows: T_____ type> Example: T_ORG_EXT__UNQ, T_ORG_EXT__EXS, T_ORG_EXT__STA, T_ORG_EXT__STA, T_ORG_EXT__RID There are few exceptions for this naming rule. Examples: T_ACCNTPOST__STA, T_ACCNTPOST__STA, T_OPTYPOSTN__RID, T_PRODLNPOSTN__RID The temporary columns that have suffix UNQ, stores Y or N values if the interface record’s user keys are not same as another interface record’s user keys in the same batch. The temporary columns that have suffix EXS, stores Y or N values if the interface record can be mapped to an a n existing base record (i.e. base record exists for the same user keys). The temporary columns that have suffix RID, stores the ROW_ID of the record already existing in the base table or generated ROW_ID for new records in the interface table. The temporary columns that have suffix STA, stores the pass number of the EIM process wher where e it fail failed ed.. Exam Exampl ple: e: If the the EIM EIM proc proces ess s fail failed ed whil while e proc proces essi sing ng a reco record rd in the the S_ORG_EXT table, the temporary column T_ORG_EXT__STA is set the EIM pass number at that point of time.

7/20/2009

Confidential

Page 3

Technical Document


3.1 Column Mapping: Prior to loading data into the EIM table, it’s essential to find out what data goes into which column in the base table, which interface table need to be used to achieve this and what is the mapping among these columns. This column mapping can be achieved in 2 ways: • •

Using bookshelf (\ITR.pdf) Using Siebel Tools  Siebel Tools  Flat Tab  EIM Table Mapping editor, query for the base table in “Destination Table” Table” Column  In object list editor,  List of EIM tables are listed  Find out the appropriate EIM table

It’s a best practice, Siebel suggests, you should document the mapping among the source data, EIM table and base table. You can use the mapping format as shown in the excel sheet embedded below.

EIM EI M Mapping Mapping Format.xls

Steps in column mapping can be briefed as follow: • • •

Identify the kind of data in question and Siebel base table Identify the EIM table to achieve the functionality Identify mapping between Siebel base table and EIM table columns

7/20/2009

Confidential

Page 4

Technical Document


4.0 Configuration File: Any EIM process reads a configuration file, where different kinds of parameters are set for an EIM process. The configuration file, in short IFB file, defines the type of process, EIM table involved, batch number, number, and lots of other parameters. An IFB file has two sections – Header and Process sections. The header section defines the parameters that are common to all the processes defined in the file. The process section defines parameters which are specific to that particular section. This file is stored in the \ADMIN path, from where the EIM task reads. If this file is not stored in this directory prior running EIM task, the absolute path of the file has to be specified in the task. If not, an error will be thrown. These EIM parameters are classified as follows: • • • • • •

General Header Parameters General Process Parameters Import Process Parameters Parameters Export Process Parameters Delete Process Parameters Parameters Merge Process Parameters

4.1 General Header Parameters: This section describes the parameters that will be used by all the processes defined in the configuration file. •

•

•

•

CONNECT  Specifies the ODBC source name of the database to be used LOG TRANSACTIONS TO FILE  This parameter specifies whether the transactions to be logged to a file or docking transaction table.  Valid values are TRUE or FALSE. set to TRUE TRUE,, the the tran transa sact ctio ion n deta detail ils s are are logg logged ed into into the the dir directo ectory ry  If set EIM.  If set to FALSE, the transaction details are logged into the S_DOCK_TXN_LOG table.  This parameter doesn’t make sense, if DOCKING: TRANSACTION LOGGING parameter in System Preferences is set to FALSE. PASSWORD Specif ifie ies s the the pass passwo word rd that that need needs s to be valid validat ated ed for for runn runnin ing g the the EIM EIM  Spec process.  If you have not specified this in Component Parameters, this needs to be specified in the IFB file.  Example: PASSWORD = SADMIN PROCESS  Identifies the process section that has to be executed defined in the IFB file.  Example: PROCESS = Import Organizations

7/20/2009

Confidential

Page 5

Technical Document

•

•

•


[Siebel Interface Manager]  This is a special and mandatory keyword that needs to be specified, which tells the EIM task, that this particular file contains the parameters for EIM. TABLEOWNER  This parameter specifies name of the user who owns the Siebel database tables. USER NAME  Specifies the logon name of the user for running the process.  If you have not specified this in Component Parameters, this needs to be specified in the IFB file.  Example: USER NAME = SADMIN

4.2 General Process Parameters: This section describes the parameters that are generic to all EIM processes. The parameters defined in this section have a scope limited to this process only. •

•

•

BATCH  Required field that specifies the batch number, which is used to identify the set of records that should be considered for this EIM process. T his batch number corresponds to the number specified in the the  IF_ROW_BATCH_NUM IF_ROW_BATCH_NUM column of the EIM table. 31 – 1).  Valid values are 0 to 2147483647 (2  Batch numbers can be specified in ranges or as a comma delimited format.  Example: BATCH = 100 – 110, BATCH = 100, 103, 120.  If batch number is specified wrongly in IFB file, the EIM throws an error stating, no records found for the stated batch number. number. COMMIT EACH PASS  Optional parameter that specifies whether the process needs to do a commit after every EIM passes.  Pass is defined as one SQL DML statement that got executed.  Valid values are TRUE or FALSE.  Default value of this parameter is TRUE. This para parame mete terr shou should ld be set set to FALSE ALSE whil while e usin using g in dele delete te or merg merge e  This process. Since it commits after each pass, and in case of any failures, the database is left corrupted with dangling foreign key references, etc…  Example: COMMIT EACH PASS = FALSE COMMIT EACH TABLE  Optional parameter that specifies whether the process needs to do a commit after processing each base table.  Valid values are TRUE or FALSE.  Default value of this parameter is TRUE. This para parame mete terr shou should ld be set set to FALSE ALSE whil while e usin using g in dele delete te or merg merge e  This process.  The reason behind this is, since it commits after each pass, and in case of any failu failure res, s, the the data databas base e will will be left left corr corrup upte ted d with with dang dangli ling ng fore foreig ign n key key references, etc…

7/20/2009

Confidential

Page 6

Technical Document



•

•

•

•

•

•

•


Example: COMMIT EACH TABLE = FALSE

IGNORE BASE TABLES  Optional parameter that specifies which base tables has to be ignored for the process for the particular EIM table.  This parameter improves the EIM performance. S_PARTY_PER, S_ORG_BU  Example: IGNORE BASE TABLES = S_PARTY_PER, INCLUDE Optional al parame parameter ter that specif specifies ies which which other other sub-pr sub-proce ocess ss needs needs to be  Option included in the process in question.  Example: INCLUDE = Import Contacts LOG TRANSACTIONS  Optional parameter that tells whether to log transactions or not.  Valid values are TRUE or FALSE.  Default value depends on the DOCKING: TRANSACTION LOGGING parameter defined in the System Preferences.  Example: LOG TRANSACTIONS = FALSE ONLY BASE TABLES  Optional parameter that specifies which base tables has to be considered for the process for the particular EIM table.  This parameter improves the EIM performance. Example: e: ONLY ONLY BA BASE SE TABL ABLES ES = S_ S_PA PART RTY Y, S_ORG_E S_ORG_EXT XT,, S_ADDR S_ADDR_OR _ORG, G,  Exampl S_ORG_BU, S_ACCNT_POSTN S_ACCNT_POSTN ROLLBACK ON ERROR  Optional parameter that specifies whether a rollback has to be made if any failure occurs during the EIM process.  Valid values are TRUE or FALSE. FALSE.  Default value is FALSE.  This parameter should be set to TRUE while using in delete or merge process. In case of any failure, the database will be roll backed to the last commit point and thus avoiding the database da tabase corruption.  Example: ROLLBACK ON ERROR = TRUE SESSION SQL  Optional parameter that specifies a user defined SQL statement to be sent to database for execution before any other SQL statements.  This parameter can be used to set tracing for performance analysis.  Only one SESSION SQL parameter is allowed per process section.  This parameter cannot be used to insert or update any data in Siebel tables. Example: SESSION SESSION SQL = "UPDATE "UPDATE EIM_CONT EIM_CONTACT ACT SET PP_ST PP_START_DT ART_DT =  Example: SYSDATE WHERE IF_ROW_BATCH_NUM = 200” SKIP BU_ID DEFAULT  Optional parameter that specifies whether to use the default BU_ID defined in the repository.  Valid values are TRUE or FALSE. FALSE.  Default value is FALSE.

7/20/2009

Confidential

Page 7

Technical Document

   

•

 

•

TYPE  

•

•

Required parameter that specifies the EIM table to be used for the process. Example: TABLE = EIM_ACCOUNT For performance reasons, the number of tables should be limited to fewer than five for a merge or export process.

TRANSACTION SQL  Optional parameter that specifies a user defined SQL statement to be sent to database before other SQL statements are processed and immediately after every commit or rollback operation.  If both SESSION SQL and TRANSACTION SQL parameters are specified, the latter gets executed immediately after the former. former.  Only one TRANSACTION SQL parameter can be defined per process section.



•

If set set to FALSE ALSE,, the the BU_I BU_ID D defi define ned d in the the repo reposi sito tory ry is used used for for BU_I BU_ID D columns in the base tables. If set to TRUE, the values set in the interface table is used. This parameter is only limited to insert, delete and merge process, because the foreign key must be resolved for these processes. Example: SKIP BU_ID DEFAULT = TRUE

TABLE 

•


Required parameter that specifies the type of EIM process. Typical process types are IMPORT, EXPORT, MERGE, DELETE and SHELL. SHELL process defines that this process has other sub-processes that needs to be executed.

USE ESSENTIAL INDEX HINTS  Optional parameter specified for MS SQL Server and Oracle databases.  Valid values are TRUE or FALSE. i t enables a subset of index hints for MS SQL server. server.  When set to TRUE, it  Default value is TRUE. USE INDEX HINTS  Optional parameter specified for Oracle databases only.  Valid values are TRUE or FALSE.  When set to TRUE, EIM issues optimizer hints to the database in order to improve the performance. FALSE.  Default value is FALSE.  Example: USE INDEX HINTS = TRUE USING SYNONYMS Optional al parame parameter ter that specif specifies ies whethe whetherr to use the acc accoun ountt synony synonyms ms  Option during the import process.  Valid values are TRUE or FALE.  Default value is TRUE.  In order to improve the performance, set this parameter value to FALSE, but care must be taken while specifying this parameter. parameter.  Refer FAQs for further explanation on Account Synonyms.

4.3 Import Process Parameters:

7/20/2009

Confidential

Page 8

Technical Document


This This sectio section n descr describe ibes s the paramet parameters ers that that are specif specific ic to EIM impor importt proces processes ses.. The The parameters defined in this section have a scope limited to this process only. only. •

•

•

•

•

•

COMMIT OPERATIONS Optional parameter parameter that specifies specifies the commit commit frequency frequency while logging logging the  Optional transactions.  Value specifies the number of records processed before a commit is done. Applic icab able le only only if DOCK DOCKING ING:: TRAN TRANSA SACT CTIO ION N LOGG LOGGIN ING G is set set in Syst System em  Appl Preferences.  Default value is 0, which means commit has to be made at the end of EIM process.  Example: COMMIT OPERATIONS = 100 FILTER QUERY Optional al parame parameter ter that lets lets the user user to specif specify y an SQL statement statement which  Option filters the records that needs to be processed.  This query runs before the import process starts. IF_ROW_ST _STA AT col column umn of those those record records s that that fails fails this this query query for the  The IF_ROW par particu ticula larr batc batch h as spec pecifie ified d in the the proc proces ess s sect sectio ion n will will be set as “IMPORT_REJECTED”.  Example: FILTER QUERY = (IF_ROW_STAT <> “IMPORTED”) IGNORE BASE COLUMNS  Optional parameter that specifies which base columns has to be ignored for the particular EIM process.  This parameter improves the EIM performance.  You should not ignore the user key columns or required columns. S_ORG_EXT.HOME_PH_NUM  Example: IGNORE BASE COLUMNS = S_ORG_EXT.HOME_PH_NUM ONLY BASE COLUMNS  Optional parameter that specifies which base columns has to be considered for the particular EIM process.  This parameter improves the EIM performance.  You should include all the user key columns and required columns.  Example: ONLY BASE COLUMNS = S_ORG_EXT.NAME, S_ORG_EXT.LOC UPDATE ROWS  Optional parameter that specifies whether to update the column values in the base table with that of in EIM table.  Valid values are TRUE or FALSE.  Default value is TRUE.  Example: UPDATE ROWS = S_ORG_EXT, FALSE ATTACHMENT DIRECTORY Option onal al par paramet ameter er that that spec specif ifie ies s the the dir directo ectory ry path path cont contai aini ning ng the the  Opti attachments to be imported.  This directory path should exist on one of the Siebel server machine.  In case of remote machine, map the path to a drive and specify the drive name to this parameter. \INPUT  Default value is \INPUT  Example: ATTACHMENT DIRECTORY = X:

7/20/2009

Confidential

Page 9

Technical Document

 •

•

•

•

•

•


Where X: is mapped to \\CRMDEV02\siebsrvr\INPUT

DEFAULT COLUMN  Optional parameter that lets you to specify a value for the EIM table column.  This parameter applies only to Import process.  This value will be only used if the EIM table column is null.  Example: DEFAULT COLUMN = ACCNT_BU, “HP Americas” FIXED COLUMN  Optional parameter that lets you to specify a value for the EIM table column.  This parameter applies only to Import process. any.  This value will be used overriding the value in the EIM table column if any.  Example: FIXED COLUMN = ACCNT_BU, “HP Americas” INSERT ROWS  Optional parameter that specifies whether to insert records into the particular base table or not.  Valid values are TRUE or FALSE.  Default value is TRUE.  Example: INSERT ROWS = S_ORG_EXT, FALSE  INSERT ROWS = EIM_ACCOUNT, FALSE   If the specified table is an EIM table, the value applies to all the base tables that were mapped to this EIM table. MISC SQL  This parameter is used to specify the type of primary need to be set while updating the primary child. primary.  Types of primaries are explicit primary and implicit primary.  If set as explicit primary, EIM will consider the record where the primary child column in the EIM table is set.  Example: MISC SQL = EXPR_S_POSTN_PR_EMP_ID  If set as implicit primary, the first child record of the parent as specified in the EIM table will be considered as child record.  Example: MISC SQL = IMPR_S_POSTN_PR_EMP_ID  In cases, we might tell EIM to use explicit primary whenever specified and implicit primary if not. This can be set as shown below.  MISC SQL = EXPR_S_POSTN_PR_EMP_ID, IMPR_S_POSTN_PR_EMP_ID NET CHANGE Optiona nall param paramet eter er that that spec specif ifie ies s how how to hand handle le the the non non user user key key null null  Optio columns in the EIM table.  Valid values are TRUE or FALSE.  Default value is TRUE.  If set to TRUE, the null values in the non user key null columns will be ignored.  If set to FALSE, the null values in the non user key null columns will be applied to the base tables also. FALSE  Example: NET CHANGE = FALSE  This parameter applies only to Import process. TRIM SPACES

7/20/2009

Confidential

Page 10

Technical Document

    


Optional parameter that specifies whether to remove the trailing spaces from the column values before importing. This parameter applies only to Import process. Valid values are TRUE or FALSE. Default value is TRUE. Example: TRIM SPACES = FALSE

4.4 Export Process Parameters: This This sectio section n descri describes bes the parame parameter ters s that that are specif specific ic to EIM export export proces processes ses.. The parameters defined in this section have a scope limited to this process only. only. •

•

•

•

ATTACHMENT DIRECTORY  Optional parameter that specifies the directory path to where the attachments have to be exported.  This directory path should exist on one of the Siebel server machine.  In case of remote machine, map the path to a drive and specify the drive name to this parameter. \OUTPUT  Default value is \OUTPUT  Example: ATTACHMENT DIRECTORY = X: Where X: is mapped to \\CRMDEV02\siebsrvr\OUTPUT  CLEAR INTERFACE TABLE  Optional parameter that specifies whether to remove the old records from the EIM table whose batch number corresponds to, as defined in the IFB file.  Valid values are TRUE or FALSE.  Default value is TRUE.  Example: CLEAR INTERFACE TABLE = FALSE  If this parameter is set to false, and some rows already exist in the EIM table for the specified batch, EIM will throw a warning. EXPORT ALL ROWS  This parameter specifies whether to export all rows from the Siebel base tables that were mapped to the particular EIM table specified in the IFB file.  Valid values are TRUE or FALSE. Default value is FALSE. FALSE  Example: EXPORT ALL ROWS = FALSE  When you do an export on party EIM tables, set this parameter to FALSE. If not EIM will export all party records. EXPORT MATCHES  This parameter optionally specifies criteria that need to be satisfied before exporting the data from the base tables.  Example: EXPORT MATCHES = S_ORG_EXT, (NAME LIKE “HP%”)  The literal specified, is the condition that occurs after the WHERE clause in an SQL statement.

4.5 Delete Process Parameters: This section describes the parameters that are specific to EIM Delete process only. only. •

CASCADE DELETE ONLY

7/20/2009

Confidential

Page 11

Technical Document

    

•

•

•

•

•


Optional parameter that tells whether to delete the child records when a parent record is deleted. Valid values are TRUE or FALSE. Default value is FALSE. FALSE. If set set to FALSE, ALSE, EIM deletes deletes the parent parent record record and sets sets the foreign foreign key column in the child records to NULL. N ULL. Example: CASCADE DELETE ONLY = TRUE

CLEAR INTERFACE TABLE  Optional parameter that specifies whether the existing rows for the particular batch number should be deleted.  Valid values are TRUE or FALSE.  Default value depends upon the type of delete being done.  For DELETE EXACT, default value is FALSE, for others, it is TRUE.  Example: CLEAR INTERFACE TABLE = FALSE DELETE ALL ROWS  Optional parameter that specifies whether to delete all the rows from the base table.  Valid values are TRUE or FALSE. FALSE.  Default value is FALSE.  Care should be taken while setting this parameter to TRUE in case of party EIM tables. case e of party party EIM tables, tables, always always use DELETE DELETE MATCH MATCHES ES when when deletin deleting g  In cas records of particular party type.  Example: DELETE ALL ROWS = TRUE DELETE EXACT  This parameter tells the EIM to use user key matching algorithm with rows in the EIM table to perform deletion.  Valid values are TRUE or FALSE. FALSE.  Default value is FALSE.  Example: DELETE EXACT = TRUE  Always use this parameter to delete records from the non target base tables. DELETE SKIP PRIMARY  This parameter tells whether to do a cascade update on the primary child column in the parent table.  If the primary address record is deleted, then the primary address column in the parent table is set to NULL, when this parameter is set to TRUE. set to FALSE ALSE,, NO_MATCH_ROW_ID gets gets sto stored red in the primar primary y child child  If set column of the parent table.  Valid values are TRUE or FALSE.  Default value is TRUE.  Example: DELETE SKIP PRIMARY = FALSE DELETE MATCHES Optional al parame parameter ter that specif specifies ies a condit condition ional al expres expressio sion n to select select the  Option records which has to be considered for deletion.  Example: DELETE MATCHES = S_ORG_EXT, (NAME LIKE “HP%”)  The literal specified inside the parentheses is the condition that occurs after the WHERE clause in any SQL statement.

7/20/2009

Confidential

Page 12

Technical Document



•

•


This parameter can be used only to delete records from the target base table.

DELETE ROWS  This parameter specifies whether rows from the particular table should be deleted.  Example: DELETE ROWS = S_ADDR_ORG, FALSE  This example restricts the deletion of records from the S_ADDR_ORG table.  Care should be taken while setting FALSE value, because it might result in dangling foreign key references. UPDATE ROWS  This parameter specifies whether foreign references should be updated.  Valid values are TRUE or FALSE.  Default value is TRUE.  Example: UPDATE ROWS = S_CONTACT, FALSE  This above example restricts any update including the updating of foreign key references in the S_CONTACT table while performing the EIM Delete.  Care should be taken while setting FALSE value, because it might result in dangling foreign key references.

4.6 Merge Process Parameters: This section describes the parameters that are specific to EIM Merge process only. •

•

SET BASED LOGGING  This parameter specifies whether to enable the set based logging.  When enabled, a separate log entry will be generated for all the rows in each table that were processed by EIM. This allows greater performance.  When disabled, each and every row processed by EIM will have an entry in the transaction table.  Valid values are TRUE or FALSE.  Default value is TRUE.  Example: SET BASED LOGGING = TRUE UPDATE ROWS This parame parameter ter specif specifies ies wheth whether er foreig foreign n refere reference nces s in the named named table table  This should be updated.  Valid values are TRUE or FALSE.  Default value is TRUE.  Example: UPDATE ROWS = S_CONTACT, FALSE  This above example restricts any update including the updating of foreign key references in the S_CONTACT table while performing the EIM Merge.  Care should be taken while setting FALSE value, because it might result in dangling foreign key references.

4.7 Extended Parameters: Apart from the parameters defined at the component level and IFB file, it is also possible to define define dynami dynamic c parame parameter ters s at task task level. level. These These parame parameter ters s are called called as extend extended ed parameters. Extended parameters are of two types namely:

7/20/2009

Confidential

Page 13

Technical Document

• •


User-Defined Extended Parameters Predefined Extended Parameters

4.7.1 User-Defined Extended Parameters:

User can define dynamic parameters and values either through the GUI or command line inter interfac face. e. Commo Common n uses uses of this this type type of paramet parameters ers includ include e settin setting g EIM batch batch number number dynamically, dynamically, setting values to the variables defined i n the SQL query used in the IFB file. To define the parameters through GUI, follow the steps: • • •

•

•

•

•

Click on site map, select Server Administration. Select Enterprise Operations  Component Requests view tab. Create new record and select “Enterprise Integration Manager” in the component request form. In the Component Request Parameter list applet, create new record and click on the select button in name field. Select Select Extended Extended Parameters Parameters in the Job Parameter Parameters s window window and click OK, which will return to the Component Request Parameter list applet. In value field, type parameter and value in the following format: name1=value1,name2=value2……name_n=value_ name_n=value_n n  name1=value1,name2=value2…… Example: FIRST_NAME=Hary

To define the parameters through command line interface, follow the steps: •

•

In the command line, use the keyword ExtendedParams to define the name=value format. Example: ExtendedParams=\"BatchNum1=200001 ExtendedParams=\"BatchNum1=200001,BatchNum2=200002-200003\" ,BatchNum2=200002-200003\"

4.7.2 Predefined Extended Parameters:

Some extended parameters are predefined in Siebel applications which follows the same name=value format. Below is a list of predefined extended parameters: • • • • • • • • • • • •

CURRENT_USER – Logon name na me of the current user. user. PASSWORD – Password of current user. CURRENT_DATETIME CURRENT_DATETIME – Current date & time information. ROOT_DIR – Home directory of Siebel server. SIEBEL_FILE_DIR – Siebel File System directory. LANGUAGE – Language of Siebel server installation. TABLE_OWNER – Name of the table owner. ODBC_DATA_S ODBC_DATA_SOURCE OURCE – Connect string for the ODBC data source. MAX_NEST_SUBST – Maximum level of nesting in parameter substitution. NUM_IFTABLE_LOAD_CUTOFF IfbFileName TraceFlags

The parameter NUM_IFTABLE_LOAD_CUTOFF is used to tell EIM whether to load all the schema mappings. The feature is disabled by default in which the value is set to -1. When enabled, EIM loads all schema mappings. To enable this parameter, set the value to a

7/20/2009

Confidential

Page 14

Technical Document


positive positive number that is one less than the number of EIM tables used in the process. process. If the parameter value is set to the number equal to the number of EIM tables used in the process, it effectively disables this feature. Example: ExtendedParams=\”NUM_IFT ExtendedParams=\”NUM_IFTABLE_LOAD_CUT ABLE_LOAD_CUTOFF=-1” OFF=-1”

7/20/2009

Confidential

Page 15

Technical Document


5.0 Import Process: This section describes the steps involved in an import process, manual procedure to be follow followed ed to acc accomp omplis lish h an EIM import import,, differ different ent value values s of IF_ROW IF_ROW_ST _STA AT col column umn and recommended EIM importing order for different entities. 5.1 Import Process Steps: Whenever an EIM import process runs, it internally performs a sequence of tasks. Each task involves multiple passes where one pass is defined as an execution of one SQL statement to achieve a certain internal operation. Step1: This step is the initialization initialization step where where EIM compares the IF_ROW_BA IF_ROW_BATCH TCH_NUM _NUM with the batch number specified in the configuration file. If no records are found for the particular batch number, EIM gets terminated. It also sets all the temporary columns to NULL. When EIM starts starts at this this step, step, the IF_ROW IF_ROW_ST _STA AT colum column n value value is set to “IN_PR “IN_PROGR OGRESS ESS””. In addition, addition, it also checks whether whether required required columns are null. If any of the required columns columns was found null, the whole EIM process stops at this point. Step2: EIM EIM appl applie ies s any any DEF DEFAUL AULT COLU COLUMN MNS S and/ and/or or FIXE FIXED D COLU COLUMN MNS S valu values es defi define ned d in the the configuration (IFB) file. Step3: EIM applies the filter queries defined for this import process. Those records which were elimin eliminate ated d are set with with value value “IMPOR “IMPORT_R T_REJE EJECTE CTED” D” in the IF_ROW IF_ROW_ST _STA AT col column umn.. The IF_ROW_STAT_NUM and __STA columns for these records are set with a value of current EIM pass number. Step4: EIM resolves and generates the foreign key references for the rows that already exist in the Siebel base tables. Once resolved, it writes these foreign key values into the temporary columns of the interface table. If the foreign key fails for any required columns, then EIM elim elimin inate ates s thos those e reco record rds s from from furt furthe herr proc proces essi sing ng and and sets sets thei theirr IF_R IF_ROW OW_S _ST TAT to “FOREIGN_KEY” and IF_ROW_STAT_NUM & _STA columns with the corresponding EIM pass number. In additio addition n to foreig foreign n key key genera generatio tion, n, EIM also valid validate ates s for the list list of value values s for for the bounde bounded d pick pick lists. lists. The failed failed rows rows are elimin eliminate ated d from from furthe furtherr proces processin sing g and their their IF_ROW_STA IF_ROW_STAT column is set to “PICKLIST_VALUES”. “PICKLIST_VALUES”. Besides the EIM pass number is stored in the IF_ROW_STAT_NUM and _STA columns. Step5: EIM fetches the appropriate ROW_ID of the records which already exist in the base table and writes those values into the corresponding _RID temporary column of those records. During this step, it also sets _EXS temporary column of these records to Y. Y.

7/20/2009

Confidential

Page 16

Technical Document


Step6: EIM generates ROW_ID for new records. These records are those whose _EXS temporary column is set to N. Once ROW_ID is generated, these values are stored in the corresponding _RID temporary column of the records. Step7: EIM eliminates rows whose user keys contain invalid values. A scenario like missing value in one of the user key columns comes under this classification. EIM eliminates those failed records and set their IF_ROW_STAT value to “REQUIRED_COLS” and IF_ROW_STAT_NUM & _STA temporary columns to the current EIM pass number. EIM also resolves and generates foreign key references for the new rows that do not exist in the base table. Once resolved, it writes these foreign key values into the temporary columns of the interface table. If the foreign key fails for any required columns, then EIM eliminates those records from further processing and sets their IF_ROW_STAT to “FOREIGN_KEY” and IF_ROW_STAT_NUM & _STA columns to the corresponding EIM pass number. Step8: EIM updates the contents of existing base table rows with the values from the interface table that have successfully passed all the previous steps. In case of any update done, EIM writes the transaction detail to the master transaction log if and only Docking Transaction Logging parameter is enabled in the System Preferences. Preferences. If duplicates were found in the EIM table, then the row which EIM encounter first will be considered for updating and rest other duplicate rows are ignored. These duplicate rows were set value “DUP_RECORD_EXIST” in IF_ROW_STAT column and the current EIM pass number in IF_ROW_STAT_NUM and _STA temporary columns. Step9: EIM inserts the new records from the interface table into base tables. It writes these new rows to the master transaction log if and only Docking Transaction Logging parameter is enabled in the System Preferences. If duplicates were found in the EIM table, then the row which EIM encounter first will be considered for updating and rest other duplicate rows are ignored. These duplicate rows were set value “DUP_RECORD_EXIST” in IF_ROW_STAT column and the current EIM pass number in IF_ROW_STAT_NUM and _STA temporary columns. Step10: EIM update updates s the primar primary y child child col column umns s in this this step. step. An exampl example: e: Settin Setting g the primar primary y address for an account. To achieve this, the column ACC_PR_ADDR should be set to Y for the particular record in the EIM table. Step11: EIM runs MISC SQL parameters as last step of import process. There are certain primary child columns which should be set using both MISC SQL and primary child column in the EIM table.

7/20/2009

Confidential

Page 17

Technical Document


Those columns are: • • • • • •

S_CONTACT.PR_OU_ADDR_ID S_CONTACT.PR_HELD_POSTN_ID S_ORG_EXT.PR_BL_PER_ID S_ORG_EXT.PR_SHIP_PER_ID S_POSTN.PR_POSTN_ADDR_ID S_POSTN.PR_EMP_ID

These are the only six columns in Siebel Horizontals, where MISC SQL has to be specified, in order to update the primaries. If MISC SQL is not set, though the EIM import succeeds, the primary child won’t be set in the Siebel base table. For further information on MISC SQL, SQL, check the Import process parameters section defined in the previous pages. 5.2 Manual Importing Procedure: EIM import is a substantial effort where many things should be planned, many prerequisites to be met before performing the process itself. itself. Identify and Validate Data: Determine whether the data to be imported already exist in the database. Check for the completeness of the data. If required, do some data massaging before importing. Determine the count of entities so that it might help in estimating the time and resources needed. Identify column mapping and User Keys: Determine the base table where the data will get stored finally. Now identify the mapping between the data and Siebel base columns. Identify Identify the EIM table to achieve achieve this process process and the mapping between between the data and EIM table columns. Identify the user keys and required columns that need to be populated. Hardware and Software Environment: Check whether the Siebel application is properly installed and all the required components for EIM are in running state. Check whether ample memory resources are available. Check whether the currently available disk space will be able able to sto store re the data after after import import process process.. Speak Speak to your your DBA regardin regarding g the future future database size. Backup existing database: Before performing any significant change to the underlying database, it is recommended to take a backup of database. This will ease the recovery in case of any major failure. Copy File attachments to Server directory: Copy all the attachments to be imported to the input directory under the Siebel server root directory. Optionally you can set different directory path, by specifying it in the ATTACHMENT DIRECTORY parameter in the IFB file. The default file attachment path is \INPUT, which can be changed using the above parameter. Load and verify EIM tables: Use any data loading tools like SQL Loader, Loader, DTS, etc… to load the legacy data into the EIM tables. Once the data is loaded verify check the number of loaded rows against the legacy database. Also check the contents of data loaded into the EIM table.

7/20/2009

Confidential

Page 18

Technical Document


EIM Config Configura uratio tion n File: File: Crea Create te the the conf config igur urati ation on file file for for this this impo import rt proc proces ess. s. Use Use default.ifb in the \ADMIN directory as a template. Test the Import Process: Test your EIM import process for a small batch of 100 records. If required, change the parameter settings in the configuration file. Run the Import Process: In case of huge volumes of data, split the data into multiple batches with approximately 1000 to 5000 records per batch. Smaller the batch it is easier to identify the problems. Run the EIM process either through GUI or command line interface. Verify Verify Results: Results: There There are different different types types of error error that that might might occ occur ur during during an import import process. Identify these errors, debug them and run EIM once again as a separate batch, by changing the batch number for the failed rows. 5.3 Different IF_ROW_STAT Values: Depending upon the type of failure, the IF_ROW_STAT column of the interface table records are set with different kinds of values. AMBIGUOUS: Two rows in the base table have same user key but different conflict IDs. DUP_RECORD_EXISTS: One or more rows that exist in the interface table exactly matches with base table record. DUP_RECORD_IN_EIM_TBL: One or more rows have the same user key of another row within the interface table. FOREIGN_KEY: When a required foreign key column in the target table could not be resolved. IMPORTED: The rows got successfully imported for both target and not-target base tables. IMPORT_REJECTED: Rows which were eliminated as a result of FILTER_QUERY. IN_PROGRESS: This value is set in the first step, the initialization step, of the import process. NOT_ALLOWED: This value is set when a particular operation is not permitted. PARTIALLY_IMPORTED: The rows got successfully imported for target table but failed for one or more non-target tables. PICKLIST_VALUES: Required bounded pick list value could not be resolved. REQUIRED_COLS: One or more required columns were found NULL. ROLLBACK: When EIM encounters an error during a roll back operation, this value is set. SQL_ERROR: This value is set, when EIM fails importing, importing, when the transaction transaction logging is enabled. 5.4 Recommended Importing Order:

7/20/2009

Confidential

Page 19

Technical Document


Whenever an import process is done, you should make sure that any reference data needed must already exist in the Siebel database. If this is not followed errors like FOREIGN_KEY, PICKLI PICKLIST ST_V _VALU ALUES ES might might occ occur ur.. So, So, any admini administr strati ative ve or refere referenti ntial al data should should be imported first, followed by other data. The recommended order is as follows: • • • • • • • • • • • • • • • • • • • • •

Administrative Data (Ex: List of Values) Business Units (Organizations) Positions Accounts Contacts Employees Products Opportunities Personal Accounts Quotes Documents Forecasts Fulfillment Marketing Campaigns CPG Promotion Management CPG Product Movement Service Requests Product Defects Activities and Appointments Notes File Attachments

In some some cas cases, es, the import import order order might might change change slight slightly ly depend depending ing upon upon the busine business ss requirements.

7/20/2009

Confidential

Page 20

Technical Document


6.0 Export Process: This section describes the steps involved in an export process, manual procedure to be followed to accomplish an EIM export process. 6.1 Export Process Step: Whenever an EIM export process runs, it internally performs a sequence of tasks. Each task involves multiple passes where one pass is defined as a n execution of one SQL statement. Step1: EIM performs an initialization process on EIM tables. It checks the CLEAR INTERFACE TABLE TABLE parameter defined in the IFB file. If this parameter is set to TRUE, it deletes all the rows in the EIM table for the specified batch number. If this parameter is set to FALSE and if some rows exist for the specific batch number, number, a warning is issued. Step2: In this step, EIM uses the export parameter settings to select the records. It checks if EXPOR EXPORT T ALL ROWS parame parameter ter is set to TRUE. TRUE. If so, so, it ignor ignores es any EXPORT EXPORT MA MATC TCHES HES parameters defined in the IFB file and exports all the rows. If EXPORT ALL ROWS is set to FALSE, it uses the EXPORT MATCHES parameters to locate those specific rows. Once the rows to be exported were identified, it exports all the data into the particular EIM table and sets the IF_ROW_STAT IF_ROW_STAT value to EXPORTED for rows that were successfully exported. Step3: In case of parent tables, EIM locates and exports the child data to their respective EIM tables. 6.2 Manual Exporting Procedure: Before you start the export process, it is necessary to take care of the following things. Prepare EIM Tables: Check whether your interface table doesn’t have any records with IF_ROW_BATCH_NUM value equivalent to the batch number specified in the IFB file. If it exis exists ts EIM EIM beha behave ves s in the the follo followi wing ng ways ways depe depend ndin ing g upon upon the the sett settin ing g of the the CLEA CLEAR R INTERFACE TABLE parameter. p arameter. •

•

If the parameter is set to TRUE, EIM deletes all those existing rows for the specified batch number. If the parameter is set to FALES, EIM issues a warning.

Edit Configuration File: Create your configuration file by defining your requirements. The modifications that should be made in the IFB file depend on different scenarios as shown below. • • •

Exporting all data rows Exporting selected data rows Exporting all columns or selected columns

7/20/2009

Confidential

Page 21

Technical Document


When you need to export all the rows set the value of EXPORT ALL ROWS parameter as TRUE. If you want to export specific rows, specify the EXPORT MATCHES parameter with appropriate condition expression. If you want to export particular columns use the ONLY BASE COLUMNS or IGNORE BASE COLUMNS parameter in the IFB file. BU Name Name Pullin Pulling: g: EIM export export proces process, s, export exports s only only the ROW_ID ROW_ID of the BU into into _BI columns of the EIM table. In order to fetch the values of the BU names, follow the steps: • • •

In the ADMIN directory, directory, open the file named eim_export_lookup_bu_name.sql. Locate the appropriate SQL statement for the base table that you are exporting. If necessary, necessary, modify the SQL statement as required and run it.

Verify Export Results: Check the count of records in the EIM table with IF_ROW_STAT value equivalent to EXPORTED against the count of the records you need to export.

7/20/2009

Confidential

Page 22

Technical Document


7.0 Delete Process: This section explains different types of deletes, the steps involved in a delete process, manual procedure to be followed to accomplish an EIM delete process. 7.1 Delete Types: There are three ways in which you can achieve the deletion of records through EIM. They are as follows: •

•

•

DELETE EXACT – Where EIM uses a user key matching algorithm to identify the rows that needs to be deleted. This is done by matching the user key of the record in the interface table with that of the records in the base table. Once a match occurs, EIM deletes this record. DELETE MATCHES – Where EIM deletes those records that match the conditional expression set in the IFB file using the parameter “DELETE MATCHES”. DELETE ALL ROWS – Where EIM deletes all the rows from the base table irrespective of any conditions or user key matching.

7.2 Delete Process Steps: Whenever an EIM delete process runs, it internally performs a sequence of tasks. Each task involves multiple passes where one pass is defined as a n execution of one SQL statement. Step1: EIM performs an initialization process on EIM tables. It checks the CLEAR INTERFACE TABLE TABLE parameter defined in the IFB file. If this parameter is set to TRUE, it deletes all the rows in the EIM table for the specified batch number. number. The default value of the t he parameter depends on the type of delete (as explained in previous section) being performed. Step2: In this step EIM performs the actual deletion of the records. If the DELETE EXACT parameter is set to TRUE in the IFB file, EIM performs a user key matching process to identify those records that need to be deleted and deletes the same. If the DELETE MATCHES parameter is defined in the IFB file, EIM uses this conditional expression and selects those records from the target base table and deletes them. If the DELETE ALL ROWS parameter is set to TRUE in the IFB file, EIM deletes all the rows from the target base table. Step3: As a final step, EIM sets the IF_ROW_STAT of those records to “DELETED” in the EIM table. Also, when there’s a reference to this record in some other table, that record is deleted if the referenced column is a mandatory, otherwise the reference column is cleared. Whenever Whenever a parent parent record record is deleted, deleted, the child records records are also deleted, if the foreign key column in the child table is mandatory, mandatory, otherwise the foreign column is cleared.

7/20/2009

Confidential

Page 23

Technical Document


The deletion of child record is decided based on the CASCADE DELETE ONLY parameter defined in the IFB file. 7.3 Manual Deleting Procedure: Before you start the export process, it is necessary to take care of the following things. Identify the Data: As a first step, identify the data you want to delete from the base table. Now check once or twice, twice, whether whether you have selected selected the right data for deleting. deleting. Once the data is identified, decide what type of delete (explained in previous section) needs to be used. Prepare EIM Table: Depending upon the type of delete, decide what data should be loaded into your EIM table. If you are achieving a delete through DELETE EXACT, EXACT, you must load the data into EIM table in the mandatory columns with IF_ROW_STAT IF_ROW_STAT set to “FOR_DELETE”, “FOR_DELETE”, the user key columns of the corresponding target and non-target table. Edit Configuration File: Depending upon the type of DELETE you use, make changes in the configuration file. The configuration parameters are explained in the previous section. Always Always set the COMMIT COMMIT EACH EACH PASS PASS & COMMIT COMMIT EACH EACH TAB ABLE LE parame parameter ter to FALSE ALSE and ROLLBACK ON ERROR to TRUE, to avoid dangling references in case if a failure occurs during an EIM delete. Verify Results: After executing the EIM task, verify the results by examining the EIM table. EIM stores stores the ROW_ID ROW_ID of the deleted deleted rows rows in the EIM table, table, in T_DELE T_DELETED TED_RO _ROW_I W_ID D column. Query for the count of records whose IF_ROW_STAT = “DELETED”. Verify this count is equal to the count of records considered for deletion earlier. earlier.

7/20/2009

Confidential

Page 24

Technical Document


8.0 Merge Process: EIM Merge uses the rows in the EIM table and the IFB file parameter settings to achieve the merge process. Apparently, EIM merge is a combination of deleting one or more rows and updati updating ng of foreig foreign n key refer referenc ences es where wherever ver requir required. ed. This This sectio section n explain explains s the steps steps involved in the merge process, manual procedure to achieve merging. 8.1 Merge Process Steps: Step1: EIM performs an initialization process on EIM tables. As in any EIM process, this steps checks whether the mandatory columns are loaded. Step2: EIM selects the records from base tables, whose user key match that of in the EIM table. Step3: EIM deletes the records from the target base table that are specified in the EIM table. It also updates the foreign key references in the child entities of the deleted records to point to the survived record. •

•

•

For deleted rows, EIM sets the T_DELETED_ROW_ID to the ROW_ID of the deleted row in the base table. EIM also sets the T_MERGED_ROW_ID of the deleted rows with the ROW_ID of the survived record. While merging, when duplicate child entities are found among the parents being merged, the child records of the deleted row is made to point to the survived record and CONFLICT_ID is set for those duplicate child records. Below example portrays the Account and Contact relationship before and after merge. Before Merge Winner Record

Loser Record

HP Regional

HP WW

Hary Potter

Peter Parker

Hary Potter

Oliver Twist

After Merge HP Regional

Hary Potter

Peter Parker

Hary Potter

Oliver Twist

CONFLICT_ID Set

7/20/2009

Confidential

Page 25

Technical Document


8.2 Manual Merging Procedure: Identify the Data: As a first step, identify the data you want to merge from the base table. Within, identify the survivor records and loser records. Prepare EIM Table: Load the user key of both the survivor records and loser records into the EIM table. Set the IF_ROW_MERGE_ID for the loser records with a value of the ROW_ID of survivor record in the EIM table. The following example explains the above statement: IF_ROW_BATCH_NUM 100 100 100

ACCOUNT NAME HP India HP Regional HP WorldWide

ROW_ID 201 202 203

IF_ROW_MERGE_ID NULL 201 201

Verify Results: After running the EIM process, test your results by examining the EIM table. Query for the records with IF_ROW_STAT = “MERGED”, which gives the count of the merged records. Query for the records with IF_ROW_STAT = “MERGED_INTO”, which gives the count of the survivor records.

7/20/2009

Confidential

Page 26

Technical Document


9.0 Running EIM: Before running an EIM process, make sure all the components required for EIM task are up and running, running, the configur configuration ation file is stored stored in the \A OME>\ADMIN DMIN directory directory and the data is loaded into the EIM table. EIM can be run in two ways: Through GUI and Command Line interface. 9.1 Running EIM through GUI: • • • • • •

•

Open Siebel Client. Go to Server Administration Screen. Select Enterprise Operations  Component Request view tab. In the Component Request list applet, create a new record. In the Component/Job field select Enterprise Integration Manager. Manager. In the Component Request Parameter list applet, add new record for setting the EIM parameter. Once all the parameters are added, click the menu button in the Component Request form applet, and click Submit.

9.2 Running EIM through Command Line interface: •

•

• • •

Start the srvrmgr program in the command line interface. Use the below syntax to connect to the server manager. srvrmgr srvrmgr –g -e e> -s -u -p Once connected to server manager, manager, execute the start task command as shown below. start task for component EIM with config= If required give the parameter (name=value) pair separated by commas.

The excel sheet embedded below gives all the parameters of the EIM component. It also gives the parameter alias to be used while specifying the parameters through the command line interface, description of those parameters. The other detail like Current Value, Value on Restart and Effective Immediately shows the out of box values of these parameters after a fresh installation.

EIM EIM Parameters.xls eters. xls

In order to trace the EIM task in terms of SQL statements, ODBC calls and other errors you can set the appropriate parameters to do this. The three important parameters responsible for this are: • • •

Error Flags SQL Trace Flags Trace Flags

Activating or setting these flags will have a direct effect on performance. Care must be taken, so that these flags are not used while running EIM task in production environment. Typically, these flags should be used in a testing or development environment only.

7/20/2009

Confidential

Page 27

Technical Document


9.3 Error Flags: These flags are set to log the details when some error occurs during the EIM process. Typical values used for setting this flag are 0 and 1. With a setting of Error Flag = 1, EIM records detailed explanation of rows that were not processed successfully during the EIM process. 9.4 SQL Trace Flags: Setting this flag will create a log of SQL statement shot during the EIM. Typical Typical values are 1, 2 and 4. A value of 8, will record all SQL statements that make up the EIM process. 9.5 Trace Flags: These flags are used to trace various EIM operations like component tracing, SQL summary, task configuration, worst queries that took lots of time, summary of whole EIM process, etc... To To use this flag, it’s necessary to configure the component event level as follows: • •

Server Administration  Component  Component Event Configuration view tab. Select the event type and set the appropriate log level.

Log level value ranges from 0 – 5. The table listed below gives more detail about the logging level. Level 0 1 2 3 4 5

Description Fatal Errors Warnings Informational Details Diagnostic

To set the event logging for EIM component, you should set the levels for the following event types as shown below: Event Type SQL Tracing SQL Summary Task Configuration Component Tracing

Log Level 4 4 4 3

Once Once you configur configure e the event levels levels accordin according g to your your requir requireme ement, nt, you can set set the appropriate value for the Tr Trace ace Flags and run the EIM. Typical values of trace flags are 1, 2, 4, 8 and 32. To activate multiple trace flags, set the value which is equivalent to sum of the trace flag values. Different trace flag values and their descriptions are given shown below: Trace Flag 1: Setting a value of 1 will create step-oriented log of the task. The log created thus can be used to determine the amount of time EIM spends on each step of the whole process or each EIM table processed.

7/20/2009

Confidential

Page 28

Technical Document


Trace Flag 2: Setting a value of 2 will record traces of all substitutions of user parameters. Trace Flag 4: Setting a value of 4 will record traces of all the user key overrides. Trace Flag 8: Setting a value of 8 will record traces of all interface mapping warnings. Trace Flag 32: Setting a value of 32 will record traces of all file attachment status. This trace log has 4 kinds of labels depending upon the file attachment process. •

•

•

•

Attachmen Attachmentt Imported Imported – Indica Indicates tes whethe whetherr the file file attachm attachment ent was encode encoded, d, compressed and copied to the Siebel file server with the new name. Attachment (Old) Deleted – Indicates whether an existing file was replaced and deleted. Attachment Not Found – Indicates that the file cannot be found in the INPUT directory. Attachment Identical – Indicates the attachment found in the INPUT directory is same as that of in the Siebel file server.

7/20/2009

Confidential

Page 29

Technical Document


10.0 Debugging EIM: Once Once an EIM process process has run, there might might be lots lots of surpri surprises ses waiting waiting for the Siebel Siebel Administrator in the form of errors. These errors might occur due to record failure, database failure, not enough tablespace in the database, etc… This section explains how to debug different kinds of errors that occurs in EIM. Debugging style differs from environment to environment, database to database, data base, and the kind of error. error. General EIM errors like AMBIGUOUS to REQUIRED_COLS can be resolved as shown in below steps: •

•

•

Check the IF_ROW_STAT column of the EIM table and understand what the problem actually is. Check the IF_ROW_STAT_NUM and identify the number in this column. This number is the EIM pass number where the failure occurred. Check the _STA temporary columns of the EIM table that has the same number value as in IF_ROW_STAT_NUM. Once this is done, you can identify the base table where the failure occurred. columns for each base table catered  In EIM_ACCOUNT table, you have 7 _STA columns by this EIM table.

Sieb Siebel el Base Base Table Table S_PARTY S_ACCNT_POSTN S_ADDR_ORG S_CT S_ CTLG LG_C _CA AT_OR T_ORG G S_ORG_BU S_ORG_EXT S_ORG_REL S_PARTY_PER

Temp Tempor orar ary y _STA _STA Co Colu lumn mn Name Name T_PARTY__STA T_ACCNTPOST__STA T_ADDR_ORG__STA T_CT T_CTLG LG_C _CA ATORG_ ORG__S _ST TA T_ORG_BU__STA T_ORG_EXT__STA T_ORG_REL__STA T_PARTY_PER__STA

If IF_ROW IF_ROW_ST _STA AT_NUM T_NUM and any of _STA _STA colum column n matche matches, s, then then you you can conclude the error has occurred in that particular ta ble. Once you have found out the error and the table in which it occurred, you need to identify the table’s column where it failed. To identify the column either you should inspect the EIM table rows or set the trace flags, error flags and SQL flags as explained in the previous section. Once you run EIM by setting these flags, it will generate a detailed log file using which you can identify the failed column. Once the column is identified, make necessary changes. The changes might be in the data in EIM tables or in the IFB file. 

•

•

•

•

Sometimes you can see your EIM task staying the Queued state. This is because, the component component Server Request Request Process Process is in unavailable unavailable state. To make this component component in running state, you should make sure that you have applied all the patches for your Siebel server. server. Then shutdown the component and startup again as shown below: • • •

Server Administration  Server  Server Components. Query for Server Request Processor in the Server Component Applet. Click on the Shutdown button and then Startup again.

7/20/2009

Confidential

Page 30

Technical Document

•


Once this is done, done, restart restart your Siebel Gateway and Siebel Siebel Server Server where the Server Server Request Processor component resides.

Now you will find your Server Server Request Request Processor Processor up and running. The workaround workaround for this scenario will be running your EIM task through Command Line interface. There There might be some scenario scenarios s where your EIM will stay in the Active state for long time and if you query your EIM table, you will find the IF_ROW_STAT value of the records set to IN_PROGRESS. This happens when a lock was acquired on the particular EIM table by some other applications. Common examples are, not committing the table once inserting the records into the EIM table. If you get any errors like invalid configuration file, then check the IFB file whether you have specified all the required configuration parameters. Most of the EIM errors can be able to resolve immediately like configuration file not found, invalid configuration file parameters, while there might be errors like insufficient space. This means not only enough space available in the disk, but also in the database. You should check whether enough space is available in the Siebel Data tablespace and also check whether auto extent option is enabled for this tablespace. This kind of error, sometimes, might be very difficult to judge and conclude. If your your databas database e gets gets shut shut down down automa automatic ticall ally y, when when your your EIM is runnin running, g, then then it’s it’s a proble problem m with with the databa database. se. Check Check whethe whetherr your your databa database se is applie applied d all the requir required ed patches, whether the windows service is configured properly for the database.

7/20/2009

Confidential

Page 31

Technical Document


11.0 FAQs: 1. When I tried to set the primary employee for the position through EIM, it is not getting updated. Why? While updating the primary employee for position, apart from setting the POSTN_PR_EMP column in the EIM table, you should also define the MISC SQL parameter in your IFB file. For further information on How to set MISC SQL, SQL, MISC SQL for other Primary Childs, Childs , refer previous section. 2. Although setting the trace flags, I’m not able to see log information in the log file. Why? You You should configure your component event levels as described in the previous section Trace Flags.. Flags 3. What does IFB stands for? IFB stands for InterF nterFace Builder. 4. Can I use more than one IFB file for an EIM process? No, it’s not possible. You can use shell process in case if you want to do more than one operation in a single EIM task. 5. What is the effect of setting Docking Transaction Logging parameter to TRUE in case of EIM export? Any transaction logging won’t take place, since export process doesn’t change any data values. Also any parameter setting for transaction logging is ignored. 6. What is the use of DELETE SKIP PRIMARY parameter in EIM DELETE operation? The default value of this parameter is TRUE which doesn’t update the primary child column in the parent record when the primary child record is deleted. If set to FALSE, the primary child column is set to “No Match Row Id”. Id”. 7. How will I get the position details when I need to populate these values in the EIM table? The query listed below will fetch all the positions positions and their details in the underlying underlying Siebel Siebel database. SELECT A.NAME "Position Name", Name", B.NAME "Position Division", Division", B.LOC "Position LOC", LOC", C.NAME "Position BU Name" FROM SIEBEL. SIEBEL.S_POSTN A, A, SIEBEL. SIEBEL.S_ORG_EXT B, B, SIEBEL. SIEBEL.S_BU C WHERE A.OU_ID = B.ROW_ID AND B.BU_ID = C.ROW_ID 8. Is it valid to set the configuration file in such a way to restrict inserts or updates in the target base table? Yes. You can disable the inserts or updates done to the target base table and still it won’t throw any error, provided the user keys and required column’s values are available. 9. Why Why am I gettin getting g “NOT_ “NOT_ALL ALLOWE OWED” D” in the IF_ROW_S IF_ROW_STAT TAT column column after EIM import? Possible reason could be the misuse of the INSERT ROWS or UPDATE ROWS parameter. When you set the INSERT ROWS parameter to false and you try to import new record, you will get this error. When you set the UPDATE ROWS parameter to false and you try to make changes to this particular record, you get this error. error. 10. My EIM Delete fails by setting the IF_ROW_STAT column to AMBIGUOUS. Why?

7/20/2009

Confidential

Page 32

Technical Document


Whenever EIM encounters more than one record with same user key values but different CONFLICT_ID values, EIM cannot proceed with the delete operation and hence it sets the value “AMBIGUOUS” in the IF_ROW_STAT column. 11. What is an Account Synonym? How it aff ects my EIM? A synonym is nothing but an alias name given for any account. These synonyms can be entered by the Siebel Administrator through Synonym Views. Whenever you do an EIM Import on accounts, EIM not only checks the user key in the base table but also checks the associated Synonyms for that account to find a match. This makes EIM time consuming. To suppress this behavior, you must set the USING SYNONYMS parameter to FALSE.

7/20/2009

Confidential

Page 33

Technical Document


Appendix A: Acronyms: CPG – C P G CRM – Customer Relationship Management DML – Data Manipulation Language EIM – Enterprise Integration Manager GUI – Graphical User Interface IFB – InterFace Builder ODBC – Open DataBase Connectivity SQL – Structured Query Language

7/20/2009

Confidential

Page 34

7078740 Eim Technical Doc

Recommend Documents