Siebel EIM Mapping Part 1, 2

Part 1. 

When I first started with 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" checkboxflagged 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 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.

Part 2. 

This article is part 2 of the Siebel EIM Mapping Step by Step Guide. I will explain howforeign keys are populated using Siebel EIM. A foreign key is a column in a table that holds the same value as a column in another table. The foreign key references the record in the other table. A Siebel example of a foreign key is the S_EVT_ACT.TARGET_OU_ID column. This foreign key column references S_ORG_EXT.ROW_ID column. What is also important in understanding importing foreign keys with Siebel EIM is the use of user keys. Siebel EIM uses the foreign key table's user key to identify the foreign key record. This is best explained with an example, the following steps can be followed to populate foreign keys using Siebel EIM. I will use S_EVT_ACT.TARGET_OU_ID as an example. 

 1. Ensure that all required Siebel EIM columns are populated for the record that you wish to import using Siebel EIM. See Siebel EIM Mapping - Step By Step Guide - Part 1 for more information. You also need to verify that the foreign key can be populated through the Siebel EIM table that you select. To do this go to Siebel Tools and select "Flat Mode" on the Object Explorer. Click on the "Foreign Key Mapping" item in the Object Explorer. In the Siebel Tools list applet, query for the name of the foreign key column in the [Foreign Key Column] field (for my case this is TARGET_OU_ID) and the name of the base table in the [Parent EIM Table Mapping] field (for my case this is S_EVT_ACT). The records returned show all EIM tables that can import to the foreign key ([GParent EIM Interface Table] field).

 2. Once you have all required columns populated on the EIM table to import the record as discussed in Siebel EIM Mapping - Step By Step Guide - Part 1, in the Siebel Tools Object Explorer select the "Type" tab and select the "EIM Interface Table" Object Explorer item. Query for the EIM table and drill expand the "EIM Interface Table" Object Explorer item. Select the "EIM Table Mapping" Object Explorer item and select the record in the list applet with [Destination Table] = the base table. Then expand the "EIM Table Mapping" Object Explorer item and select the "Foreign Key Mapping" Object Explorer item. In the "Foreign Key Mappings" list applet, query for [Foreign Key Column] = the foreign key column. Then expand the "Foreign Key Mapping" Object Explorer item and select the "Foreign Key Mapping Column" Object Explorer item. Each of the records shown in the "Foreign Key Mapping Columns" list applet represents the EIM table columns required to resolve the foreign key value. The columns listed for a foreign key mapping is always the user key of the destination table. 

 3. The columns required to be populated are in the [Interface Table Data Column] field. There are some exceptions which I will explain next. For the example of S_EVT_ACT.TARGET_OU_ID, the user key of the S_ORG_EXT table which this foreign key refers to is: NAME, LOC and BU_ID. The foreign key mapping columns are listed as (notice the resemblance): TARGET_ACCNT_NAME - maps to the NAME of the S_ORG_EXT record. TARGET_ACCNT_LOC - maps to the LOC of the S_ORG_EXT record. TARGET_ACCNT_BI - maps to the BU_ID of the S_ORG_EXT record. There is an exception for the above EIM columns where the TARGET_ACCNT_BI represents the value in BU_ID, the TARGET_ACCNT_BU column represents the name of the org that BU_ID joins to in S_BU. For some reason the TARGET_ACCNT_BI when populated will not always resolve the foreign key, however I have found that the equivalent TARGET_ACCNT_BU column when populated will resolve the foreign key. Many EIM tables have BU/BI related columns, there will always be a pair with the same name but differing BU/BI. My rule of thumb is to always use the "BU" column first and populate with the name of the Organization. Then if this does not resolve the foreign key then try the "BI" column. 

 4. Now that the above EIM columns are populated, the foreign key should resolve when the EIM task is executed. You need to ensure that if you have an ONLY BASE COLUMNS section in the IFB file for the EIM task, then you need to include the foreign key column in the ONLY BASE COLUMNS section (eg. S_EVT_ACT.TARGET_OU_ID). 

 5. When you execute the EIM task, ensure to include parameters: SQL Trace Flags = 8, SQL Error Flags = 8. This will give a lot of information in the EIM log file and helps for debugging. 

 6. Once the EIM task has executed search for the foreign key column name in the EIM log file. There should exist a section in the log file which specifies whether the foreign key was imported or if it failed. If the foreign key import failed there will be an explanation of what was wrong in the EIM table. My next EIM article will discuss the EIM import of primary columns through explicit and implicit primary mappings.