When migrating data to or from Siebel Base tables, one of the main tasks is to identify the Interface table that would be used in the EIM process. There are cases in which a base table can be loaded from more than one EIM Interface tables. In such cases, the choice of interface table could optimize the EIM performance significantly. ROW_ID and IF_ROW_BATCH_NUM are mandatory columns for EIM processing.
Driving Factors while choosing EIM table
Here is a list of all factors that should be kept in mind while choosing the EIM Table.
Base table- Identify all the Base tables that need to be populated. This is driven by requirement.
EIM table- Identify all the EIM tables that contain mappings to the Base Table. The query that will help to determine all the EIM tables that populate a particular Base table is:
SELECT DISTINCT T2.NAME BASE,
T1.NAME EIM
FROM S_TABLE T1,
S_TABLE T2,
S_EIM_TBL_MAP MAP
WHERE T2.NAME=<<give Base Table Name here>>
AND T1.ROW_ID=MAP.IF_TBL_ID
AND T2.ROW_ID=MAP.DEST_TBL_ID
AND T1.INACTIVE_FLG='N'
AND T2.INACTIVE_FLG='N'
AND MAP.INACTIVE_FLG='N'
ORDER BY T1.NAME;
Target Table- In the IFB file, while specifying the base tables that a particular EIM table will load, it is mandatory to specify the target base table for the EIM table. If the target table is not mentioned, EIM will generate an error. The following query determines the target base table for an EIM table:
SELECT DISTINCT T1.NAME SOURCE,
T2.NAME TARGET
FROM S_TABLE T1,
S_TABLE T2
WHERE T1.TARGET_TBL_ID = T2.ROW_ID
AND T1.NAME =<<give EIM Table Name here>>
ORDER BY T1.NAME;
EIM Table Selection to load maximum base tables- When more than one related Base tables need to be populated, instead of selecting different EIM tables for each Base table, the optimal choice would be an EIM table that would contain mapping to all the related Base tables that need to be populated. The following query determines the Base tables that can be loaded from a particular EIM table:
SELECT T1.NAME EIM,
T2.NAME BASE
FROM S_TABLE T1,
S_TABLE T2,
S_EIM_TBL_MAP MAP
WHERE T1.ROW_ID=MAP.IF_TBL_ID
AND T2.ROW_ID=MAP.DEST_TBL_ID
AND T1.INACTIVE_FLG='N'
AND T2.INACTIVE_FLG='N'
AND MAP.INACTIVE_FLG='N'
AND T1.NAME =<<give EIM Table Name here>>
ORDER BY T1.NAME;
For example, consider a situation where both the tables S_PARTY_PER and S_POSTN_CON need to be populated for storing relationships between an Employee and a position. Since employee related data are usually migrated using EIM_EMPLOYEE, this interface table would seem like an obvious choice. But EIM_EMPLOYEE does not have mapping with S_POSTN_CON. So in this case we could
- Load S_PARTY_PER using EIM_EMPLOYEE and S_POSTN_CON using EIM_CONTACT.
- Alternatively, we could use only EIM_CONTACT, since this interface table loads both S_POSTN_CON as well as S_PARTY_PER.
The choice ii would be optimal since our purpose is getting solved using a single EIM table.
EIM Table Selection to load maximum columns- Among these Interface tables, select the Interface table that contains mappings to the maximum number of Base table columns. The ideal choice would be the interface table that would have mapping to all the base table columns that have been identified for population.
For example, the base table S_CONTACT can be loaded using both EIM_EMPLOYEE as well as EIM_CONTACT. But if our requirement indicates that a specific column, say, PR_HELD_POSTN_ID of S_CONTACT needs to be populated then we would have to choose EIM_EMPLOYEE because EIM_CONTACT does not have mapping to PR_HELD_POSTN_ID column of S_CONTACT.
However, if our requirement were to populate PR_POSTN_ID of S_CONTACT, then we would have to choose EIM_CONTACT and not EIM_EMPLOYEE because EIM_EMPLOYEE does not have mapping to PR_POSTN_ID column of S_CONTACT.