Story

How to use Denormalized Columns

Siebel uses Denormalized Columns to improve the performance of query operation in view where data visibility is restricted using Siebel View modes like Organization, Position etc. Denormalized columns are generally created for columns most frequently used for query like Contact First Name, Last Name etc.  These denormalized columns are created in Intersection tables of the base table with Position, Organization tables.  This provides an added improvement while querying for commonly used fields in My Contacts or All Contacts (Organization Visibility) views.

For example in My Contact View, there would be a by default where clause on S_POSTN_CON table on Position Id.  When user queries in the view for a particular first Name or Last Name, without the denormalized columns, SQL generated would have where clause on S_CONTACT as well as S_POST_CON table

By having the denormalized column, the SQL generated would have where clause only on Single table and performance of the query will be faster. For denormalized column, Siebel ensure that if you are updating the data using any standard Siebel method (UI, EIM, EAI) the value in denormalized columns are automatically kept in synchronization.  But if you are updating any such columns using direct SQL (not supported by Siebel), these denormalized column do not get updated.

When updating columns that are the source of denormalized columns in other tables, you must find the records related to the columns being updated and load them as well, in the same batch.
As an example, you are updating the S_SRC table using EIM_SRC. EIM_SRC maps to S_SRC, S_SRC_BU, and S_SRC_POSTN, among others. S_SRC_BU and S_SRC_POSTN both contain the column SRC_NAME, which is denormalized from S_SRC.NAME. So, S_SRC_BU.SRC_NAME and S_SRC_POSTN.SRC_NAME should match S_SRC.NAME.
 
You have a record in S_SRC, and you want to update its NAME to something else using EIM_SRC. When you load the data of this record with its new NAME into EIM_SRC and then run EIM to update the NAME, EIM does not automatically update the SRC_NAME in the records within S_SRC_BU and S_SRC_POSTN. In order for the EIM engine to update S_SRC_BU.SRC_NAME and S_SRC_POSTN.SRC_NAME with these related records, you must find these related records in S_SRC_BU and S_SRC_POSTN and load them into EIM_SRC as well. The batch number must be the same. Only the user key column data needs to be loaded for these related records.