Tuesday, November 11, 2014

Can we create siebel ROW_ID through EIM?

The plain answer is NO! We cannot create siebel Row Id using Enterprise Integration Manager (EIM). The main reason being siebel Row Ids are created using an algorithm internal to siebel. This algorithm is not exposed anywhere to the developers. What at least we can do is understand the construction of Row Ids and some aspects of it pertaining to uniqueness in the siebel database.

So lets have a basic understanding of Siebel Row Id.

What is ROW ID?

Row Id is a unique identifier associated with every record in Siebel databases. The column in which this value is found is ROW_ID and it is present on every table. For example S_CONTACT.ROW_ID will give the value something like "1-KR3YF" and this value uniquely identifies the contact record in the database. Siebel Row Id is not to be confused with the Oracle ROWID pseudocolumn which returns the address of the record in the database.

Is Siebel Row Id unique across tables?

Most of the beginners would say "Yes" to this question, but the fact is Siebel Row Id are not unique across tables but it is unique for each entity. For example, the ROW_IDs for the same person in S_PARTY, S_CONTACT and S_CONTACT_X are the same because they each refer to the same person.

Row Ids are used extensively throughout Siebel Enterprises to access specific records. Although users access records by a User Primary Key (such as Opportunity Name), it is more efficient for the Siebel Enterprise to store and access related data via the Row Id. The Row Id is a base-36 sequence number generated using a confidential, proprietary algorithm that ensures no duplication, thus protecting the referential integrity of the database. The ROW_ID column is VARCHAR(15) data-type, which may contain one or two non-numeric symbols (plus signs or hyphens, or both).

 The format of the ROW_ID is one of the following:

Records created by the user interface
Records created by Interface Manager (EIM)
Records created by EIM (Starting in Siebel versions 6.2 and higher, and Siebel version 7)


CP = Corporate Prefix, up to 2 alphanumeric characters
NP = Next Prefix, up to 6 alphanumeric characters
NS = Next Suffix, up to 7 alphanumeric characters

The maximum length of the ROW_ID is 15 alphanumeric characters. The corporate prefix will always be unique for any database (main or local). The server maintains its original value, and mobile databases created against that particular server database are always assigned a new, unique value.

The Siebel ROW_ID is a combination of the S_SEQUENCE_S and information from S_SSA_ID table. All connected users share the same prefix, which is obtained from the table S_SSA_ID on the server database. Remote Users are assigned a unique prefix when they are db-extracted. This value is also stored in the S_SSA_ID table on the local database.

Suffix is generated using an internal algorithm handled by the source code. When a new record is created through the user interface, the Siebel application reads the value of the current NS column from S_SSA_ID table and increments this value by a value more than 1 – for performance reasons, generally 50. The client caches these fifty potential ROW_IDs for future inserts. A new record entered from the user interface may result in many inserts to the underlying tables, depending on the business components used. When the client disconnects, cached ROW_IDs are lost.

The combination of the prefix and suffix generates a unique row_id. Since Siebel does not expose the algorithm behind generation of row ids, the ROW_ID generation is internal to the EIM process. Therefore Siebel provides user keys to map to a unique record in the base table.

During EIM, the EIM table columns, which map to the user keys of the base table, are populated with values so as to map to a unique record. If that record is to be updated by EIM later, the same user key values have to be populated in the corresponding EIM table columns. Otherwise, it will not resolve into the correct base table record.

For example, PERSON_ID and BU_ID constitute the user keys for S_CONTACT. The corresponding EIM_CONTACT columns are CON_PERSON_UID and CON_BU. When a new record is imported in S_CONTACT using EIM_CONTACT, the CON_PERSON_UID and CON_BU should uniquely identify a Contact record. Now, for updating some fields for this Contact using EIM_CONTACT, CON_PERSON_UID and CON_BU have to be populated with the same set of values that were used during the initial load in order to map to the same Contact record in the base table.

Similarly while loading a child table, which references the row id of the master table, the EIM table columns that map to the foreign-key column of the child table must be populated in the same way the 
user keys of the master table were populated.

For example, S_CONTACT_XM is a child table of S_CONTACT. The column PAR_ROW_ID of S_CONTACT_XM references the ROW_ID of S_CONTACT. S_CONTACT_XM is loaded by EIM_CON_DTL. The columns of EIM_CON_DTL that need to be populated to generate the PAR_ROW_ID of S_CONTACT_XM are CON_BU, CON_PERSON_UID and CON_PRIV_FLG. These EIM columns should be populated with the same set of values that were used to populate its parent S_CONTACT record through EIM_CONTACT. Note that though PRIV_FLG is not a part of the user key of S_CONTACT, but it is a required column and is part of the foreign-key mapping of its child table.


So, though siebel has made hidden from us the algorithm of generating Row Ids, it has given a beautiful way of identifying uniqueness of siebel records which is the "User Keys". The concept of User Keys is the heart of Siebel EIM. If one doesn't understands user keys he cannot understand EIM. And why I called it a beautiful way is because it shows Siebel's customer centric approach and implementing the abstraction concept by providing and promoting access to easier method (User keys) while hiding the difficult one (Mechanism of Row Id).

Keep reading :-)
Mohammed Arif