Selective Update of Non-user Key Columns Using NET CHANGE and ONLY BASE COLUMNS.

By judiciously combining the two IFB file parameters, selective null updates of Siebel base table columns can be carried out using Siebel EIM

Let us consider 3 non-user key columns Col 1, Col 2 and Col 3 in a Siebel base table. Columns 1 and 2 should not be updated with NULL while column 3 be updated with whatever is there in the corresponding EIM column (NULL or non-NULL value)

1. To achieve this, when the EIM column corresponding to Col 3 always has a NULL, the parameters should be set as below:
NET CHANGE = TRUE
ONLY BASE COLUMNS = COL 1, COL 2

2. If the corresponding EIM column (for Col 3) has a combination of NULL and non-NULL values, the job should be run in two steps with the parameters set as below:

Step 1
NET CHANGE = FALSE
ONLY BASE COLUMNS = COL 3

Step 2
NET CHAGE = TRUE
ONLY BASE COLUMNS = COL 1, COL 2

Read More »

Siebel Tutorial on EIM and Invoking EIM.

Interface Table

• Staging area for data to be imported, updated or merged into base tables via EIM (used for exported data too)
• EIM does not perform data cleansing
• Delete table data after processing
• Have three columns that MUST be populated:

# IF_ROW_BATCH_NUM = number to id all records for this batch

# ROW_ID = unique id for record in batch (1, 2, 3)

# IF_ROW_STAT = i.e. FOR_IMPORT, FOR_DELETE.. updated during run

• IMPORTED = success for all target/destination tables

• PICKLIST_VALUES = failed b/c lov not found

• REQUIRED_COLS = faild b/c one require column was null

• DUP_RECORD_EXISTS = failed because a record already exists

• FOREIGN_KEY = failed b/c fk could not be resolved

• PARTIALLY_IMPORTED = success for target base table, not for 2^nd’ry
•  T_ column s (i.e. T_DELETED _ROW_ID) are temp. used during runs

How do we get data in?

# Load using non-Siebel vendor utilities (i.e. DTS, bcp, SQLLoader)

# Write programs (i.e. PLSQL to modify/cleanse data)

Base Table

• User data is stored in one or more based tables in Siebel
• Relationships between them depend on primary/foreign keys using Siebel’s Row_ID
• RowId = PK for EVERY table

User key

• Users keys used to uniquely ID a row for EIM (to prevent the duplication of records)

Enterprise Integration Manager (EIM)

• Server task that manages exchange of data between external data in interface tables and user data in base tables
• Uses Config file (*.ifb) to “know” where/what to do

# Imported, merged, deleted, exported

# Which int tables and which base tables

# Must specify:

• Batch Number (i.e. 200)

• Type (i.e. Import)

• Table (i.e. EIM_PROD_INT)

• Destination table (i.e. S_PROD_INT)

• Default values (i.e. DEFAULT COLUMN = SALES_FLG = “Y”)

For delete jobs, there are additional properties

• Delete Exact = deletes row in base tables with user key values specified in the EIMtable; delete is cascaded; should be used for non-target base tables
• Delete matches = rows based on a where clause
• Delete All rows = deletes all rows in a named table
• DELETE ROWS = FALSE (prevents deletes)

Verify:

(1) With SQL against EIM tables + base tables

(2) Log files

(3) Client application

Have a processing order (i.e. tables referenced via fk must be loaded first)

Referential Integrity

• User key column mappings (i.e. Person Id) are used to resolve ROW_Ids for base tables.. (i.e. is this an existing record or a new record.. then really uses row-Ids for maintaining referential integrity)
• Cannot use SQL to populate user data in base tables because relationships are complex and you cannot auto-generate a unique row id with SQL (Siebel application generates this)

Server Manager

• EIM runs as an OS process writing log entries, reads ifb file, loads dictionary and executes EIM step.
• Invoked via server manger in GUI or Command Line

Read More »

The below post describes how can file attachments be imported via EIM (Enterprise Integration Manager).

In Siebel versions 5.x ,6.x and 7.x, users can run Enterprise Integration Manager (EIM) to import file attachments. For example, there are already some Service Request records in S_SRV_REQ table. Please follow several steps to import file attachments to Service Requests:

Siebel version 6.x and 7.x:
1. Put the file to be attached in the Siebel_Server\input directory (i.e. ABC.doc)

2. In the EIM_SR_DTL table, populate:

SR_SR_NUM
SR_BU
ATT_FILE_NAME (’ABC’)
ATT_FILE_EXT (’DOC’)
ATT_FILE_SRC_TYPE (’FILE’)

3. Run EIM to import file attachment to S_SR_ATT table.

4. A new .saf file will be generated in the file system after EIM loading.
In version 5.x:

• Put the file to be attached in the Siebel_Server\input directory (i.e. ABC.doc)
• In the S_SR_DTLIF table, populate:

SR_NUM
SR_FILE_NAME (’ABC’)
SR_FILE_EXT (’DOC’)
SR_FILE_SRC_TYPE (’FILE’)

• Run EIM to import file attachment to S_SR_ATT table.
• A new .saf file will be generated in the file system after EIM loading.

Read More »

Siebel Tutorials on Data Mapping.

Which base tables will store external data and which interface tables will be used to get the data there.

• Key inputs:

Which Attributes (i.e. fields) need to be stored?

What Entities do the collection of these attributes represent?

What is the relationship between these entities?

Which Siebel BC represents entity?
# I.e. Dept of Ed Borrower = Contact, Student Loan = Asset

Analyze Siebel destination tables + columns using:
# Siebel Tools for available tables + columns + BCs + relationships

• Using Source-to-Base table mappings:

Determine EIM-to-base mappings with Bookshelf and/or tools
# I.e. for S_ORG_EXT, locate IF tables that support S_ORG_EXT, like EIM_ACCOUNT, etc
# Best of those tables is the one that allows you to import the most data
# Determine required columns

• Required if there is a mapping from it to one of the required, non-system default columns on the base table
• Could include “defaults” from tools

# Verify column mappings match applet and BC mappings

• Use Data Map for cross-team communication

Interface tables may populate more than one base table.

Base table can be populated by more than one interface table.

Additional Party considerations
# BU column holds name of organization, BI column is populated automatically

# PARTY_UID, PARTY_TYPE_CD, ROOT_PARTY_FLG required

Read More »

Siebel EIM Interview Questions, Answers and Explanation.

Can you tell me what are the different processes in EIM?
Answer : Import, Export, Delete and Merge.

Can we do update through EIM? If so, what process should be used to do update?
Answer : Yes. The process is Import.

How does EIM identify if the record is for insert or for update?
Answer : Through user keys. If the record for the same user key exist, then EIM does an update else insert.

Can we generally update user keys through EIM?
Answer : No. But for Account (S_ORG_EXT table) and Product (S_PROD_INT table) we can even update user keys.

What are the columns must be populated in any EIM table before running EIM task?
Answer : ROW_ID, IF_ROW_STAT and IF_ROW_BATCH_NUM.

What are the different values that we may get in IF_ROW_STAT column once EIM run is over?
Answer : IMPORTED, DUP_RECORD_EXIST, PARTIALLY_IMPORTED, FOREIGN_KEY, PICKLIST_VALUES, REJECTED, AMBIGUOUS etc.

When do we get ‘AMBIGUOUS’ as IF_ROW_STAT?
Answer : When there are duplicate records with same user key and different conflict_id in the base table.

When do we get ‘PARTIALLY_IMPORTED’ as IF_ROW_STAT?
Answer : When EIM is able to populate target base table and fails to populate any of the non-target base table.

Check for IF_ROW_STAT_NUM and search in the log file to find out partially imported table. Else check Temporary columns to get partially imported table name.EIM uses temporary column internally to facilitate EIM process. We do not populate T_COL.

What are different delete parameters we need to use in DELETE EIM process?
Answer : ‘DELETE EXACT’, ‘DELETE MATCHES’ and ‘DELETE ALL RECORDS’.

DELETE EXACT use user keys to delete records. EIM table needs to be populated first.

What column is to be populated while performing MERGE operation? This is used to identify the record to be merged.
Answer : IF_ROW_MERGE_ID is to be populated MERGE operation to identify the record to be merged.

T_MERGE_ROW_ID stores the ROW_ID of the surviving record. T_DELETE_ROW_ID stores ROW_ID of deleted records.

Read More »

This article describes how to run multiple SESSION SQL statements for EIM Processes.

The method used is to break up the original EIM process into 2 separate processes, each with a SESSION SQL. To add another SESSION SQL, create a dummy process that contains the other SESSION SQL. Users can either set the BATCH NUMBER for this dummy process to an unused batch number, or use an unused EIM table. Create a shell process to call both processes.

For example, to import only the activities of the type 'In Store Visit',the following SESSION SQL is used to delete all the other activities from the EIM table:

delete from EIM_ACTIVITY where act_todo_cd <> 'In Store Visit' and if_row_batch_num = 1 and if_row_stat = 'FOR IMPORT'

To update Activity type code as 'Activity' for all activities, the following SESSION SQL is used :

update EIM_ACTIVITY set act_cal_type_cd = 'Activity' where if_row_batch_num = 1 and if_row_stat = 'FOR IMPORT'

To achieve this 2 SESSION SQLs, configuration file should look like this:

[SESSION SQL Step]

SESSION SQL = "delete from EIM_ACTIVITY where act_todo_cd <> 'In Store Visit' and if_row_batch_num = 1 and if_row_stat = 'FOR IMPORT'"
TYPE = IMPORT
BATCH NUMBER = 9999
TABLE = EIM_ACTIVITY

[Activities Import Step]

SESSION SQL = "update EIM_ACTIVITY set act_cal_type_cd = 'Activity' where if_row_batch_num = 1 and if_row_stat = 'FOR IMPORT'"
TYPE = IMPORT
BATCH NUMBER = 1
TABLE = EIM_ACTIVITY

[Import New Activities]

TYPE = SHELL
INCLUDE = SESSION SQL Step
INCLUDE = Activities Import Step

In the first Process there are no rows in the interface table which corresponds to this batch number. This means that no rows exist to be imported from this interface table.

EIM processing will continue with the second process. The SESSION SQL defined in [Activities Import Step] will be executed and the resulting Activities rows will be imported into the Siebel base tables.

Read More »

Latest collection of Siebel EIM Interview Questions and Answers collected from job interviews.

What is mean by implicit primaries?
Answer : Implicit primary means the following

• Allow EIM to implicitly set primaries. 
• Use the external system does not know the primary. 
• Sets the primary to be the child with the lowest ROW_ID. 
• Mimics auto primary in the application.

How many type of Section in EIM (.ifb) files?
Answer : There are Two Types: 
A) Header Section and B) Process Section

What is .ifb? Mention its use?
Answer : The ‘ifb’ is a configuration file, which is used when EIM Component is running. It contains information such as user name, password, process and many other important parameters.

What is called an interface table and why we use interface table in EIM?
Answer : Interface table is a staging area for EIM process. Data from legacy is transferred to EIM table first using non-siebel process. For this, we need interface table in Siebel.

How do you import List of Values?
Answer : It depends on any Hierarchies, but using EIM_LST_OF_VAL (for EIM load) import parents first then children, otherwise you will receive failure message. COM objects can also be used to import the LOVs from a flat file to the application.

How will you find the particular interface table for a base table?
Answer : You need to do the following as below. This article discusses in detail about Siebel EIM Data Mapping

• Go to Tools-> In Object Explorer select "Flat Tab".
• And select EIM Table Mapping-> Query in OBLE in Destination Table (Base Table Name) and get the corresponding EIM Table Name in Parent EIM Interface Table.

How do you find the status of EIM process?
Answer : The status of EIM Process could be seen in IF_ROW_STAT column in EIM Interface table.

Name the Mandatory Columns for EIM Processing ?
Answer : The mandatory columns in EIM processing are listed below.
ROW_ID
IF_ROW_BATCH_NUM
IF_ROW_MERGE_ID
IF_ROW_STAT
IF_ROW_STAT_NUM

Will modifications to a BC affect an EIM import process?
Answer : The answer is No, Enterprise Integration Manager (EIM) always works at the table level.
Data in the EIM table columns are imported directly into the base tables. Hence, any changes to the configuration of the BC will not affect the EIM process.

Read More »

Siebel Enterprise Integration Manager Interview Questions Answers, Siebel EIM Interview Questions Answers.

Why we need user key column mapping?

Answer : User key column mapping in interface tables are used to resolve ROW_ID s for base tables, maintaining referential integrity.

What is the relationship between base tables and interface tables?

Answer :  The relationship is cited below.

• An interface tables may populate more than one base table.
• A base table may be populated by more than one interface table.

What are the three important fields (mandatory columns) in interface tables?

• IF_ROW_BATCH_NUM: Number to identify all records that should be processed in a batch.
• ROW_ID: Unique number to identify the record in the batch.(Here is the not generated ROW_ID used on base tables).
• IF_ROW_STAT: Non-Null character value which is reset during the run.

What is the use of EIM_T columns?

Answer : All interface tables having EIM_T columns used to hold temporary values and status during processing setup.

Can you use EIM to populate added extension columns to a base table?

Answer : Yes. But you must add extension columns to the appropriate interface table and map the extension columns to the base table.

Why we set trouble shooting flags?

Answer : Task parameters can override component level event logging to create additional information in the log.

Trace Flag = 1 à Record Processing steps.

Error Flags = 1 à Record details regarding failed rows.

SQL Trace Flags=8 à Record summary SQL.

Should only be done in test environment.

Dramatically impeds EIM Performance and generate large log files

How will you verify EIM task?

Answer : Verify EIM Task Using

• Server Manager Logs
• SQL against the EIM and the Base tables
• Client Application to view data.

What is mean by explicit primaries?

Answer :

• Use the primary flag to set explicit primaries through EIM
• Use when the external system knows which child should be primary.

What is mean by implicit primaries?

• Allow EIM to implicitly set primaries.
• Use the external system does not know the primary.
• Sets the primary to be the child with the lowest ROW_ID.
• Mimics auto primary in the application.

Read More »

Siebel EIM questions has been answered by Siebel professionals in various forums.

What is EIM?
Answer : EIM is a server task that manages exchange of data between external data in interface tables and user data in base tables.

What is User Data?
Answer : User data only managed and populated by you. Installation populates seed data and repository data.

What is the difference between EIM and EAI?
Answer : EIM: It is batch mode Integration. When data volume is large then we have to go to EIM.
EAI: It is real time Integration. When data volume is small then we have to go to EAI.

How many type of Siebel Data model extensions are there?
Answer : Standard and Custom extensions

What is Archiving?
Answer : EIM Deletes are useful for archiving old data and deleting obsolete data. During delete processing the deleted rows are written to interface tables from which they can be archived in a format allowing easy re impact if necessary.

Where the user data is stored?
Answer : The User data is stored in one or more base tables in the Siebel database. Relationship between the base tables relay upon PK & FK based on row ids.

• ROW_ID is the system primary key for every base table.
• ROW_ID is the system-generated value. 

What are interface tables?
Answer : Interface tables store external data inside the Siebel database.
These tables are:
Staging area for data to be imported, updated merged in to base tables via EIM.
Staging area for data exported by EIM.
Staging area for data to be deleted in the base table using EIM’s DELETE EXACT CLAUSE.

What is Data Cleansing?
Answer : During imports, EIM attempts to import data from the interface table to the base table. It is important to note that if the data loaded in the interface table is faulty, it will remain faulty in the base tables. EIM Does not perform data cleansing.

What is data mapping?
Answer : Data Mapping determines

• Which Siebel base table columns will store external source data
• Which interface table columns will be used to import from source to destination

What is user key?
Answer : PK&FK based on ROW_ID are used for system wide referential integrity. Based on multiple columns user keys are used to uniquely identify a row for EIM

Read More »