SBL-EIM-00205: Failed to load the application dictionary.

Hi,

Today while running Employee skill load on dev environment I got the error "SBL-EIM-00205: Failed to load the application dictionary.". Though this error is not very common, it may occur after upgrade to new siebel versions.

The solution to this problem what I found is:

1. Checked the log file, got below error message:

2. In Siebel tools, I queried for EIM_PAYMENT table and its base table mappings.

3. The foreign key column mapping had one more record entry. This record was added in Upgrade process from 8.1.1.10 to 8.1.1.11. It was a duplicate entry for the same EIM table column. I made the new foreign key mapping inactive as below:

In tools, Query for EIM_PAYMENT in the EIM Interface table attribute, navigate to EIM Table Mapping, Query for S_SRC_PAYMT_ITM, navigate to Foreign Key Mapping and under WRNTY_CLM_ITM_ID, there were two entries for the EIM column. One had changes property ticked.

4. The changed record was made inactive, followed by Apply DDL and activation of the EIM table.

5. Ran the EIM job again.

Results: EIM job successfully completed :-)

User, Contact and Employee in Siebel

Difference Between a User, Contact and an Employee in Siebel:

Employee: An employee is user who can login to the application and has an employee profile associated to it. He is also present as the Contact in Application. So, we can say that every employee is also a contact.

User: In application like eCommunication, ePharma you can provide anybody with ability to register and use application with limited access that is a User in Siebel. A user can also be a contact but a User is not an Employee.

Contact: Contact is a person who has least of privileges when it comes to application. A contact is not a part of organization but is usually associated with Accounts (Customers) as a touch point to reach customer. This is a basic detail of all three.

Main point of differences that I can think about these is:

Application use: A Contact has least privileges and he cannot login to application where as a user and employee can login and use Siebel Application.

Screen: The Screen to access Contact records is Contacts. Employee and User share the same screen that Administration – User but they have different view.

Table: Base table for Contacts is S_CONTACT whereas User and Employee are based in S_USER. In previous version of Siebel there was table called S_EMPLOYEE which was used but in Siebel 7.8 that table in inactive. General information of an Employee and Users such as first name, last name etc. are stored in S_CONTACT and other information is stored in S_USER.

Employee Flag: Employee Flag is true for Employee but false for a User and Contact.

EIM Performance Tuning Tips

Some Useful Performance tuning tips for Siebel EIM:

Click Here to download in PDF Format.

1. ONLY BASE TABLES or IGNORE BASE TABLES parameters
List only those tables that are relevant for a particular EIM task

2. ONLY BASE COLUMNS or IGNORE BASE COLUMNS parameters
List only those columns that are relevant for a particular EIM task

3. Separating Insert and Update Statements

• Always better to separate the Inserts and Updates in different batches
• EIM has to perform additional processing to determine whether to insert or update

4. USE INDEX HINTS Parameters usage

• If FALSE, EIM does not generates hints during processing
• EIM processing should be tested with both settings (TRUE and FALSE) to determine which provides better performance

5. Additional Indexes on EIM tables
Sometimes it is recommended to create additional indexes on EIM tables to improve performance of time-consuming SQL statements.

7. Controlling size of batches

• Siebel recommends to use a batch size no more than 5000 rows.
• Using batch ranges (x-y) allows us to run with smaller batch size and avoid the startup overhead on each batch.

8. USING SYNONYMS parameter usage

• When set to FALSE, it saves processing time because queries that look up synonyms are not used (for Account load)
• Should not be set to FALSE if multiple addresses are used for Accounts.

9. Transaction logging
When set to FALSE during initial load, reduces transaction activity to the siebel docking tables

10. Parallel execution of EIM jobs
Run mutually exclusive EIM processes concurrently

11. Optimizing SQL
Running EIM job with the following flag settings
Error Flags: 1
SQL Trace Flags: 8
Trace Flags: 3

12. UPDATE PRIMARY KEYS and PROMARY KEYS ONLY parameter usage
UPDATE PRIMARY KEYS and PROMARY KEYS ONLY parameters should be avoided in IFB file

13. Regular Table maintenance (GATHER TABLE STATS)
Running gather stats would help faster processing of batches

14. Clear EIM tables
Delete old batch records before running fresh EIM task

Compiled By

Abu Maryam Mohammed Arif

Organization, Account and Internal Division

Relation between Organization, Division and Account:

A division, internal or partner, is also an organization if its internal organization flag is TRUE (S_ORG_EXT.INT_ORG_FLG = 'Y') and it has an associated S_BU record.

• Every division is associated with one org, either itself or the closest ancestor division that is also an org.
• Every position is associated with a division. The position is then also automatically associated with one org, the org with which the division is associated.
• Persons (Contacts), Users, Employees, Partner users are instances of the Person party type.
• Typically you associate each employee and partner user with one or more positions. The employee or partner users have only one active position at a time. The employee or partner user is automatically associated with one division and one org at a time: the division and org associated with the active position.

• Relations between Person type and other party entities are stored in S_PARTY_PER table. E.g. Relation between Users and Accounts or Users and positions.
• This relation controls the data visibility for a person.
• S_PARTY_REL table stores ad-hoc informational relationships between parties. E.g. Relationships between a Company and its Accounting firm (both are stored as Accounts)
• NOTE: Whenever you want to associate a person type (Contact, User, Employee etc.) entity with other party type, use S_PARTY_PER table.

Compiled By

Abu Maryam Mohammed Arif

Why use EIM to load data in siebel?

Why Use EIM?

A very common question may arise in our minds, "Why do we use EIM to load (insert, update, delete etc.) data in Siebel database?"

A very common answer which people give to this "simple" question is that "Siebel doesn't allow inserts and updates, deletes etc directly into siebel database." or some may say this is what Siebel has recommended and so on..

Though direct inserts and updates in the siebel tables is highly not recommended, we can still do that if we are able to identify the direct tables and related tables in which data is to be inserted or updated. The primary reason why we shouldn't be doing operation on siebel tables directly is for following reasons:

1. Table relationships in Siebel are highly complex. One table may be related to too many other tables, the foreign keys and the primaries are difficult to resolve if we perform the operations manually like through simple SQL statements.

2. Many organizations use mobile clients for siebel application. These mobile clients have to be synchronized after each data loads so that the data is up-to-date for mobile clients as well. Improper synchronization will cause data integrity issue between siebel server database and mobile user local database.

3. While updating data directly in siebel tables, system columns such as LAST_UPD, LAST_UPD_BY etc are not updated. This may mislead the actual record information and integrity will be lost. These updated data may not be synchronized properly in other parallel running servers as well like report DB server, Siebel Analytics etc.

Hence, Siebel has recommended that the in-built EIM technique be used for any type of data operations in the siebel tables.



Written By
Abu Maryam Mohammed Arif

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

A Basic Introduction to Siebel Enterprise Integration Manager (EIM)

About Siebel Enterprise Integration Manager (EIM):

• Siebel EIM is a server component in the Siebel EAI component group that transfers data between the Siebel database and other corporate data sources.
• Exchange of information is accomplished through intermediary tables called EIM/Interface tables.
• The EIM tables act as a staging area between the Siebel application database and other data sources.
• EIM is the primary method of loading mass quantities of data into the Siebel database.
• EIM can be used to perform bulk imports, updates, merges, and deletes of data.

How does EIM Works?

• Siebel applications uses Siebel Base tables for storing and retrieving data. The data that is to be imported can come from various sources like legacy database, external database etc.
• The external data cannot be directly imported into siebel base tables. This is due to the reason that database structure of the external databases, their formats, schema etc would differ from siebel database structures. In fact Siebel does not allows this.
• For the consistency of data and load process, the external data first stages into the EIM tables and then subsequently loaded into siebel tables through EIM jobs.
• So the data exchanges between the Siebel database and external databases occurs in two parts:

1. Load data into EIM tables.
2. Run Siebel EIM jobs to import the data from the EIM tables into the Siebel base tables.

• It is worthy to note that the first part of this data-exchange process involves only the EIM tables from siebel side, only the second part of the process involves the functionality of Siebel EIM.
• Also, EIM should be used to perform bulk imports, exports, merges, and deletes, instead of direct SQL. Oracle does not allows this.

EIM Process/Functions:

There are 4 different process/functions in Siebel EIM namely:
1. Import
2. Delete
3. Merge
4. Export
Details of these process can be found in later blog posts.

Process Flow between EIM and Non-Siebel databases:

For each EIM process, you need to complete the following sequence of steps.

1. Prepare the EIM tables:

• For delete, merge, or import operations, the EIM tables require loading with representative data that allows EIM to identify the specific Siebel base table on which to operate.
• You can use either an SQL program utility or native SQL to perform this function.
• The EIM export processes require minimal preparation of the EIM tables.
• When an export operation takes place, the EIM tables are populated with data from the Siebel base tables. Therefore, you can use either an SQL program or native SQL to transfer data from the Siebel application to a non-Siebel application.

2. Edit the EIM configuration file:

• An ASCII or Unicode (binary) text file of extension type .IFB that resides in the Siebel Server/admin directory allows you to define the type of EIM processes to be performed: export, delete, merge, or import.

3. Run EIM:

• EIM is submitted as a Siebel Server batch component task (EIM job) either from the UI i.e. Administration - Server Management views or from the Server Manager command line interface.

4. Check results:

• The EIM component task produces a log file, which provides tracing information about the process.
• The tracing information produced is variable dependent upon the EIM component task parameters used and the Siebel Server event logging deployed for the EIM component.
• As always, during testing operations you should check the EIM processes using increased tracing information, and then reduce tracing when the process is deployed to production.

Compiled By
Abu Maryam Mohammed Arif