Overview of Siebel EIM Tables

EIM Tables Overview:

• Siebel EIM tables are intermediate database tables that act as a staging area between the base tables in the Siebel database and other databases.
• EIM tables are designed to be simple and straightforward so they can be loaded or read by way of external programs.
• Before EIM can be used in a merge, update, or import process, EIM tables must be populated with data, using any method supported by the database.
• Once EIM tables are populated, EIM job can be invoked to process this data.
• Each EIM table usually supports a group of base tables that can be imported or exported in a single batch.

EIM Table Columns:

• In EIM tables, several columns are mandatory, others are conditionally mandatory, depending on the conditions of your import.

ROW_ID: 

• For an EIM table row to be eligible for processing, you must initialize its ROW_ID. 
• The ROW_ID, in combination with the value of IF_ROW_BATCH_NUM, must yield a unique value. 
• The ROW_ID values in the EIM tables are not the ROW_ID values that are assigned to the row when it is loaded into the base table.
• An EIM-generated ROW_ID has a XX-XXX-XXX format.
• A regular Siebel row ID that is assigned to the row has a X-XX format.

IF_ROW_BATCH_NUM:

• You must set the values in this column to the same integer, greater than or equal to 0, as an identifying number for all rows to be processed as a batch.
• The maximum value is 2147483647.
• Use this column as the first key of any new indexes created on an EIM table.

IF_ROW_MERGE_ID:

• You can set this column to one of two values:
• NULL- This value identifies the surviving or merged-into-row.
• ROW_ID- This value identifies the ROW_ID number in the EIM table where the row will be merged.

IF_ROW_STAT:

• EIM updates this column after processing the row to indicate the status of the record.
• When populating the EIM tables, you can set this column to any value except NULL. E.g. FOR_IMPORT, indicating that the row has not been processed yet.
• After processing, if certain rows were not imported due to a data error, you should change:

IF_ROW_STAT_NUM:

• After processing, this column contains a zero (0) if a row was successfully processed to completion.
• If processing failed, this column contains the pass number where the pass failed.

Temporary columns:

• EIM uses temporary columns to manipulate data during processing.
• These column names begin with T_ and indicate the table or column for which they are used.
• Because EIM uses these columns internally during processing, do not manipulate these columns in the EIM tables.

EIM Table and Column Mappings:

• EIM uses EIM table mappings to map columns from EIM tables to Siebel base tables.
• Siebel predefined EIM mappings are fixed and cannot be remapped.
• Some base tables may not be mapped to a corresponding EIM table. In such case, EIM Table Mapping Wizard can be used to add missing mappings.

Creating New EIM Table Mappings to Existing Base Tables:

• You can create new EIM table mappings from an EIM table into a base table if either of the following conditions is true:

             1. Mappings already exist from the EIM table to the base table.

          2. The base table is an extension table and mappings already exist from the EIM table to the corresponding base table.

• For example, you could create a new column in EIM_ACCNT_DTL and map this either to a new extension column in S_ORG_EXT or to an existing column in the extension table S_ORG_EXT_X.

• If you create an extension column to a base table, then run the EIM Table Mapping Wizard, the Wizard creates the following mappings:

           1. The mapping for the newly added extension column

         2. The mappings for all unmapped columns in the base table, including unmapped Siebel base columns.

Explicit Primary Mappings:

• The Siebel Data Model uses primary foreign keys (called as primaries) to point from a parent base table to a child base table.
• Primaries enable business logic in the Siebel Data Model, such as identifying the primary position for an account.
• Primaries improve performance by eliminating repeating subqueries when data from both the parent table and the primary child table are displayed.
• Primary foreign keys are columns that have names usually beginning with PR_ and are defined as primaries in the data model.
• If both the parent table and the primary child table of a primary foreign key are mapped to the same EIM table, then you should see an explicit primary mapping for this primary foreign key under the table mapping of the primary child table.
• E.g. Parent Table: S_PROD_INT Child Table: S_PROD_LN_PROD Explicit Primary Mapping: S_PROD_INT.PR_PROD_LN_ID (Product Line Id)

How to check which Base tables are mapped to a particular EIM table?

1. Start Siebel Tools.

2. In the Object Explorer, click the Types tab.

3. Click EIM Interface Table.

4. In the EIM Tables window, select the EIM table for which you want to view the mappings.

5. In the Object Explorer, expand EIM Interface Table.

6. Click EIM Table Mapping.

The EIM Table Mappings window displays all base table mappings for the selected EIM table.

Expand EIM Table Mapping and click on Attribute Mappings to find the base table column mappings to EIM table columns.

How to check which EIM tables can populate a particular base table?

1. Start Siebel Tools.

2. In the Object Explorer, click the Flat tab.

3. Click EIM Interface Table.

4. Query for the base table in the Target Table properties.

5. The Name property will give the list of all EIM tables that can populate the base table.

EIM Table Mappings to Base Tables without User Keys & Related Topics:

• Some EIM tables contain table mappings to base tables not having any user keys. Most of them are those starting with S_NOTE*

Things to keep in mind when performing EIM processes involving base tables without user keys:

• IMPORT (Insert): Import works but EIM does not check and prevent duplicate records from being imported into the base tables without user keys.
• If an import batch is executed repeatedly, the same records will get imported repeatedly because EIM cannot check whether the records to be imported already exist in the base table without user keys.
• IMPORT (Update): Update on base tables without user keys cannot work, because EIM cannot uniquely identify the record to update.
• EXPORT: Exporting data from base tables without user keys is done the same way as exporting data from base tables with user keys.
• DELETE: DELETE ALL ROWS and DELETE MATCHES can be used to delete data in target base tables.
• If a table without a user key is the target table, then delete works as it does for base tables with user keys.
• In most cases, however, a table without a user key is a secondary table and its data can only be deleted with the table as a child of its parent table.
• MERGE: Merge does not work on base tables without user keys.

Compiled By

Abu Maryam Mohammed Arif