When I first started with Siebel EIM <http://wiki.ittoolbox.com/index.php/Siebel_EIM> it was a struggle to determine how to get the data from a spreadsheet or external database mapped into the Siebel base tables through Siebel EIM. There are many steps involved and I became confused very quickly. Through these experiences I formulated an efficient process to take in order to map data to Siebel base tables through Siebel EIM.
The following is a step by step guide for mapping external source data to EIM tables in order to insert/import to a Siebel base table.
Siebel EIM Mapping Step By Step Guide
1. Determine the base tables that the data will be mapped to and you must understand the data models that the data will be converted into. I am assuming that you have already worked out the mapping and data conversion rules so you know what fields from excel file will be mapped to what base tables etc.
2. For each of the Siebel base tables to be populated, make a note of all the columns in the base tables that you will be mapping to with your source data. Also make a note of the columns that make up the user key for the table. If you are using Siebel EIM to update existing data in base tables then all that is required for EIM mapping is the mapping to user key and the columns being updated from source data. However if you are using Siebel EIM to insert new records you also need to also map to the non-system columns on the base tables that have the "Required" checkbox flagged and make a note of these. So you have 3 separate notes:
a. The required columns of the table (required for EIM inserts)
b. The columns mapping data to
c. The user key columns of the base table
3. In you need to determine a Siebel EIM table that is appropriate to load the data into the base tables, the appropriate Siebel EIM table would be that which has all the column mappings you need to populate the data. To do this, in the Siebel Tools <http://wiki.ittoolbox.com/index.php/Siebel_Tools> Object Explorer go to "Flat Mode" and select "EIM Table Mapping". In the list of table mappings, query in the "Destination Table" column for the base table you wish to populate make a note of some of the "Parent EIM Interface Table" tables. It is usually obvious which table(s) to use (i.e. EIM_ACTIVITY would map to S_EVT_ACT, but you would use EIM_ACTIVITY1/EIM_ACTIVITY2 etc for mapping to activity related tables etc.). When you have found the Parent EIM Interface Table, go to the "Types" view in the object explorer and select "EIM Interface Table" query for the table, drill into the table to the "EIM Table Mapping" and select the base table being mapped to then drill in to "Attribute Mapping" to verify that all the base columns you want to populate are there.
4. Lookup through Attribute Mapping in the "Base Table Attribute Column" field each of the columns being mapped to and make a note of what the corresponding interface table column is to populate with that field value. Do this for all the columns you wish to map to. It is good practice to maintain a mapping spreadsheet document to map fields from source data to interface table columns and Siebel base table columns.
5. In the same way as above lookup the required base table columns that you made a note of as these will need to be populated for the Siebel EIM insert/update operation to be successful. As part of this you need to determine what the value of the base table column that is required should be. Most of the time these are flag fields as part of the vanilla Siebel table in which you just need to ensure that you populate which that default value.
6. In the same way as the above step lookup the user key columns that you made a note of. You need to ensure that these columns are correctly populated with the unique identifier for each record that you wish to insert. Most of the time the user key will consist of the Name/LOC/BU, for example if for populating S_ORG_EXT, the user key is NAME/LOC/BU (not sure exactly what the BU column is called) where the NAME represents the unique name column of the Org, LOC is usually NULL and BU is based on the Organization the record belongs to "Default Organization" would be the value for a single org application.
7. In the Siebel Tools Object Explorer, go to EIM Interface Table > EIM Interface Table Column and filter for all records where "Required" = Y. Check if there are any columns here that have not been included to be mapped in the above mappings. If there are you need to ensure that these columns are also populated. Again it is a matter of determining what the column will map to and what the appropriate value for that column should be. Usually if the column does not mean anything to your application functionality specifically you would just map it to the default value that all other records in the base table are populated with.
8. If you populate the EIM table with all the above data for each record and run the EIM job with a configuration file that is correct you will get a successful EIM insert/update.
My next EIM article will provide a guide for mapping to EIM in order to import foreign key columns and setting the primary flag for MVG records.
/ Next Entry <http://www.siebel.com/blogs/siebel-answers/case-study-data-conversion-using-siebel-eim-26226>
Related White Papers
¡¤ Transferring data from one environment to another using Siebel EIM <http://Siebel.ittoolbox.com/r/cm.asp?r=857995>
¡¤ Anatomy of a Siebel Archiving Project <http://Siebel.ittoolbox.com/r/cm.asp?r=649199>
¡¤ URLMON.DLL Causes Intermittent Hangs in Siebel 7.0x <http://Siebel.ittoolbox.com/r/cm.asp?r=368278>
Related Jobs
¡¤ Siebel Designer / Siebel Developer with MedicalDevice and SOA Exp. <http://Siebel.ittoolbox.com/r/dcm.asp?r=2499198&type=D> (Dice)
¡¤ Siebel EIM Consultant <http://Siebel.ittoolbox.com/r/dcm.asp?r=2431755&type=D> (Dice)
Case Study: Data Conversion using Siebel EIM
Joshua Weir <http://www.siebel.com/people/joshuaweir/> posted 7/27/2008 | Comments <http://www.siebel.com/blogs/siebel-answers/case-study-data-conversion-using-siebel-eim-26226> (3)
This article presents a case study where a data fix is required on Siebel data. The data fix is performed using a combination of:
1. PLSQL to identify the data and populate the corresponding EIM table with the data to update.
2. Siebel EIM to load the data from EIM tables to Siebel base tables.
Let's say that a data problem exists where for all records in table S_ORG_EXT the MASTER_OU_ID column should be equal to the ROW_ID column however there are thousands of records where the MASTER_OU_ID columns is not equal to ROW_ID. Therefore we want to use Siebel EIM to bulk fix so that for all records in table S_ORG_EXT where MASTER_OU_ID <> ROW_ID that we set MASTER_OU_ID = ROW_ID.
I am going to use the same methodology that I discussed in the Siebel EIM Mapping - Step by Step Guide <http://blogs.ittoolbox.com/siebel-answers/archives/siebel-eim-mapping-step-by-step-guide-part-1-26075>. Please see this guide for more detailed information on the steps below.
1. Determine base table: S_ORG_EXT, S_PARTY (S_ORG_EXT and S_PARTY always go together as do S_CONTACT and S_PARTY and S_POSTN and S_PARTY when using Siebel EIM).
2. EIM mode is to update existing data no need to map required columns in EIM tables, only user key is required to identify the record to be updated.
User key columns for S_ORG_EXT: NAME, BU_ID, LOC.
User key columns for S_PARTY: PARTY_UID, PARTY_TYPE_CD
Data update columns: MASTER_OU_ID.
3. Siebel EIM table that can be used: EIM_ACCNT_CUT.
4. EIM to Base table mappings for data update columns:
MASTER_ACCNT_BU/MASTER_ACCNT_LOC/MASTER_ACCNT_NAME > S_ORG_EXT.MASTER_OU_ID
5. Required base table columns are not required to be populated as this is a Siebel EIM update not an insert.
6. EIM to Base table mappings for user keys:
AC_NAME > S_ORG_EXT.NAME
AC_LOC > S_ORG_EXT.LOC
AC_BU > S_ORG_EXT.BU
PARTY_TYPE_CD > S_PARTY.PARTY_TYPE_CD
PARTY_UID > S_PARTY.PARTY_UID
7. Other required EIM table columns:
ROW_ID = a unique row number
CREATED = default this to SYSDATE
CREATED_BY = default this to 'SADMIN' ?€¡° it doesn't matter
IF_ROW_BATCH_NUM = this is the batch number for the EIM job (417) can be any number
IF_ROW_STAT = this is defaulted to FOR_IMPORT if Siebel EIM insert/update
8. Now we need to create a SQL statement that will insert into EIM for all records in S_ORG_EXT where MASTER_OU_ID <> ROW_ID. Here is the SQL for Oracle database:
1: INSERT INTO EIM_ACCNT_CUT
2: (
3: ROW_ID
4: ,CREATED
5: ,CREATED_BY
6: ,IF_ROW_STAT
7: ,IF_ROW_BATCH_NUM
我的内容管理
展开
