Siebel EIM 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.

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.

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 an execution of one SQL statement.

Step1:
EIM performs an initialization process on EIM tables. It checks the CLEAR INTERFACE 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. The default value of the 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 a parent record is deleted, the child records are also deleted, if the foreign key column in the child table is mandatory, otherwise the foreign column is cleared.
The deletion of child record is decided based on the CASCADE DELETE ONLY parameter defined in the IFB file.

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, whether you have selected the right data for 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, you must load the data into EIM table in the mandatory columns with IF_ROW_STAT set to “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 set the COMMIT EACH PASS & COMMIT EACH TABLE parameter to FALSE 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 the ROW_ID of the deleted rows in the EIM table, in T_DELETED_ROW_ID 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.

------------------------END--------------------------

Siebel Interface Table

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, uniqueness of the records, whether the record already exists in the database. 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 with appropriate batch number
Set the 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___
Example: T_ORG_EXT__UNQ, T_ORG_EXT__EXS, T_ORG_EXT__STA, T_ORG_EXT__RID


There are few exceptions for this naming rule.
Examples: 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 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 where it failed. Example: If the EIM process failed while processing a record in the S_ORG_EXT table, the temporary column T_ORG_EXT__STA is set the EIM pass number at that point of time.

-----------------------------------END-----------------------------------------

Different IF_ROW_STAT errors for Siebel EIM Import Process

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: A user-specified filter query failed for this row. 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, when the transaction logging is enabled.

---------------------END-----------------------------

EIM Data Load Order for any new Siebel Implementation

Many a time we wonder what would be the ideal Data Load order for a new Siebel Implementation. There are different ways to load the Administrative data such as the LOVs, Responsibilities etc. like the use of an Excel loader. But for those who are not comfortable with VB coding, the best way is to use EIM.
Following is the load order that should be used:

1. Administrative Data (Ex:List of values,Responsibilities,Views)
2. Business Units (Organizations)
3. Positions
4. Account
5. Contact
6. Address
7. Employees
8. Products
9. Opportunities
10. Personal Accounts
11. Quotes
12. Documents
13. Forecasts
14. Fulfillment
15. Marketing Campaigns
16. CPG Promotion Management
17. CPG Product Movement
18. Service Requests
19. Product Defects
20. Activities and Appointments
21. Notes
22. File Attachments
In some cases, the import order might change slightly depending upon the business requirements.

-------------------------END-------------------------