Thursday, Jan. 18, 2018

Remove duplicate records using EIM merge

Written By:


July 9, 2012


Posted In:

EIM merge is the process used to remove the duplicate rows (or very similar content rows) from Siebel base table. It deletes one of the rows and updates the foreign key references wherever required. It can be explained by these simple steps:

Step 1: Export the duplicate data into interface table.

Step 2: Select a criteria based on which you want a record to survive or be deleted. For e.g. If the activities of a particular account gets duplicated with duplicated activity status as “Not Started”, then your activity status is the criteria which you are going to use.

Step 3: Populate the IF_ROW_MERGE_ID of the rows:

For the survivor rows: Set IF_ROW_MERGE_ID to be NULL.

For records to be deleted, set IF_ROW_MERGE_ID to the ROW_ID of the record in which you want it to be merged (Survivor Record).

Step 4: While merging, when duplicate child records 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. For example, there are two duplicate records with child records like:

Before Merge:
Original_Record–> Child A
Original_Record–> Child B

Duplicate_Record–> Child A
Duplicate_Record–> Child C

So Child A is duplicated record in child table. After merge:

1. Duplicate record will be deleted.
2. All child records will point to original record.
3. Duplicate child record of duplicate record will have its CONFLICT_ID set:

Original_Record–> Child A
Original_Record–> Child B
Original_Record–> Child A(CONFLICT_ID set)
Original_Record–> Child C

Step 5: Verify Results: After running the EIM process, query for the records with IF_ROW_STAT = “MERGED”, which will give the count of merged records and the records with IF_ROW_STAT = “MERGED_INTO”, which will give the count of the survivor records.

Ifb file parameters specific to merge process:

This parameter specifies whether to enable the set based logging. If enabled, a separate log entry will be generated for all the rows in each table that were processed by EIM. If disabled, each and every row processed by EIM will have an entry in the transaction table. So setting this parameter as true can help enhance the performance of the process. But the default value of this parameter is TRUE, which already points to better performance.

This parameter has a different meaning than it has in other EIM processes. In merge, this parameter specifies whether foreign references in the named table should be updated. Default value is TRUE.

Important things to remember while merging data:

1. EIM merge process can be used to merge data from target base tables only, not from the secondary tables. So EIM_ACTIVITY can be used to merge S_EVT_ACT only and not the other secondary tables.
2. Keeping UPDATE ROWS parameter as FALSE can leave a lot of orphan records in child table. So it should be kept TRUE unless you have some criteria to delete the child records of duplicate record.

Share This Article

About Author


Siebel Technical Consultant

  • Balaji Rajasekar

    How and where do set the critiria ? Is it at the ifb file?