Siebel Enterprise Integration Manager (EIM) Refresher

If it’s been a while since your last Siebel EIM load project, the steps below will help you run your EIM job successfully.

At a high level, here is the process:

1. Document the mapping for each field
2. Move the data from the source to the staging table (using SQL Insert statements)
3. Move the data from the staging table to the Siebel base tables (using EIM Process in Siebel App or CLI)
4. Review the log files / verify the import

Now, let’s dive into the details.

Document the Mappings

First, make sure you understand the source data and what Siebel Entities you will be importing. Next, you’ll need to identify where your source data will reside (e.g., Siebel database, CSV files, other DB).

After that, you should document the EIM table mapping for the source columns to the target columns. If the mapping is comprehensive, the creation of the SQL insert statement will be easier.

1. First, identify which EIM table to leverage for the import, search for the destination base table in Tools Flat View (e.g., go to “EIM Table Mapping” and query for S_PTCL_SITE_LS in the [Destination Table] field, after which two EIM tables would be returned).
2. Query for the above tables in the Tools Tree View in object “EIM Interface Table.” (In my example, the query for the tables is: EIM_PTL_SITE_LS or EIM_CLSITE_DTL.)
3. Review the “EIM Table Mappings” for each of tables found from step 2. Verify that all the base tables that will be needed are available. Most data that is imported will need Team and/or Organization data, in order to be available in the GUI. (So, in this example, verify the table S_PTL_ST_POS_LS exists (this table is where Team data is kept). The Team and/or Organization table names will have *POS* or *ORG* in the table name.)

Once you’ve identified the EIM table that will be leveraged, document the mappings. (I use Excel for this.)

1. First, document the mapping for the data/columns to be imported into Siebel (e.g., the source data to the target Siebel EIM tables).
2. Identify all the required columns in the EIM table and document those mappings.
3. Identify all the required columns in the base Siebel table. To do so login into Siebel Tools, and query the base table in the Tools “Table -> Column” object using “Nullable = N and Required = Y”. Map the columns returned in the query. You can use the default value, if appropriate for the design. Flag columns can be set in the IFB file – see step 6 below. (Note: you might also consider if it’s applicable to map the other columns where Required=Y, but as long as they are nullable, it is not technically necessary to map them).

Below is a sample mapping sheet:

Move the Data: Source to Staging

After the mapping documentation task is complete, write SQL Insert statements utilizing the mapping sheet to populate the EIM tables. I test the SQL insert statements with minimal data rows to verify the insert statement. (Note: Use INSERT INTO SELECT statements to perform the insert. This allows you to preview the data to be inserted as you write the SQL for each column. Once you feel comfortable with the SELECT statement, the INSERT INTO can be prepended to it for a test import. We use where rownum<=x to specify the number of rows we want to preview.)

Below is a sample INSERT statement

–Site EIM insert

INSERT INTO SIEBEL.EIM_PTL_SITE_LS (ROW_ID, IF_ROW_STAT, IF_ROW_BATCH_NUM, CLPTCL_PGM_NAME, CLPTCL_PTCL_NAME, PTCLRG_PGM_NAME, PTCLRG_PTCL_NAME,                          SITEOR_ACCNT_BI, SITEOR_ACCNT_BU,       SITEOR_ACCNT_LOC, SITEOR_ACCNT_NAME,                                                                                    PLST_SITE_NUM,                            INVES_CON_BI,     INVES_CON_BU,          INVES_CON_PRIV_FLG, INVES_PERSON_UID, PR_POSTN,  PLST_SITE_UID, POS_ROW_STATUS, POS_POSTN_BI, POS_POSTN_BU,           POS_POSTN_DIVN,          POS_POSTN_LOC, POS_POSTN_NAME, PLST_STATUS_CD )

select                            ROWNUM, ‘FOR_INPUT’,  300,              ‘Solti Program’, STUDY,        ‘Solti Program’, CONCAT(CONCAT(STUDY,’/’),REGION),       ”,        ‘Default Organization’, ”,              (select t10.ACCOUNT_NAME from Account_Input t10 where t10.ACCOUNT_ID=t1.ACCOUNT_ID) as ACCOUNT_NAME, replace(replace(SITE_NUM,'(‘,”), ‘)’, ”),       ”,     ‘Default Organization’,        ‘N’,              CONTACT_ID,         ‘Y’,       SITE_ID,      ‘N’,            ”,     ‘Default Organization’, ‘Default Organization’,         ‘INTERNAL’, ‘Siebel Administrator’, ‘Planned’

from sadmin.SITE_INPUT t1

where rownum <=3;

–View the results of the insert…

SELECT ROW_ID, IF_ROW_STAT, IF_ROW_BATCH_NUM, CLPTCL_PGM_NAME, CLPTCL_PTCL_NAME, PTCLRG_PGM_NAME, PTCLRG_PTCL_NAME, SITEOR_ACCNT_BI, SITEOR_ACCNT_BU,       SITEOR_ACCNT_LOC, SITEOR_ACCNT_NAME,                                                                                    PLST_SITE_NUM,   INVES_CON_BI,     INVES_CON_BU,          INVES_CON_PRIV_FLG, INVES_PERSON_UID, PR_POSTN,  PLST_SITE_UID, POS_ROW_STATUS, POS_POSTN_BI, POS_POSTN_BU,           POS_POSTN_DIVN,          POS_POSTN_LOC, POS_POSTN_NAME FROM SIEBEL.EIM_PTL_SITE_LS;

commit;

Note: Notice the *BI*, *BU*, *LOC*, *DIVN*.  These columns are required for Team and Organization assignment.

Now, you can create/update the IFB file (configuration file with IFB extension). This file should be created in the <SIEBEL SRV ROOT>\ses\siebsrvr\ADMIN folder. There are many parameters that can be used to streamline the process. Check Siebel bookshelf for details on those parameters.

Below is a sample IFB file. This file specifies the EIM Tables that will be imported into Siebel Base tables during the EIM process. In this example config, only one table is specified.

[Siebel Interface Manager]

USER NAME = “SADMIN”

PASSWORD = “*******”

PROCESS = IMPORT SITE

; This imports the Contacts records

[IMPORT SITE]

TYPE = IMPORT

BATCH = 3000

TABLE = EIM_PTL_SITE_LS

;      NET CHANGE = FALSE

;      ROLLBACK ON ERROR = TRUE

;      COMMIT EACH PASS = FALSE

;      COMMIT EACH TABLE = FALSE

ONLY BASE TABLES = S_PTCL_SITE_LS, S_PTL_ST_POS_LS

;required at S_PTCL_SITE_LS table

FIXED COLUMN = EIM_PTL_SITE_LS.PLSTACTIVATESYNCFL, “N”

FIXED COLUMN = EIM_PTL_SITE_LS.EXT_CDMS_INT_FLG, “N”

FIXED COLUMN = EIM_PTL_SITE_LS.PLST_NO_SUBINFOFLG, “N”

FIXED COLUMN = EIM_PTL_SITE_LS.PLSTRESCHONLSTVSTF, “N”

Note: Before running the EIM job, you should increase logging on the EIM component. This will help with troubleshooting.

The below “Component Event Configuration” log level settings should be set to 4. (See Siebel bookshelf for info on how to set these.)

• EIM SQL
• EIM Trace
• Task Configuration
• SQL Tracing
• SQL Error
• SQL Summary

Move the Data: Staging to Siebel

You made it! Now, you’re ready to run the EIM job. This can be done via the “Jobs” view or the command line.

Below are some sample parameters for a GUI EIM job:

Review the Logs and Data

After you run the job, you’ll want to review the log files to verify that there were no errors, and then you should review the data in the GUI.

Below is a partial sample EIM log file – you’ll notice the errors, these error did not prevent the data from being imported. In this case, the Protocol Site record was inserted.  However, the Region was not associated because the Region value did not exist. (Note: If there are errors that require re-importing, then you need to configure the IFB file to delete records. Details of this is a subject for another blog.)

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:58:58               > Initializing

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:58:58               >   Loading configuration file               Site-insert.ifb                 0s

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:58:58

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:58:58

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:58:58               DB_TOKEN_ITABLE_NAME_MODIFIER: ” and IT.NAME in (‘EIM_PTL_SITE_LS’)”

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:00               >   Opening server database                  SBA_82_DSN                      2s

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:00               8/4/16 10:59 Status: Loading SSE application dictionary from DB.

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               8/4/16 10:59 Status: Loaded 1 IF tables and 1053 base tables (time: 4.9s).

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               >   Loading Siebel dictionary                                                5s

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               < Initializing                                                               7s

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               8/4/16 10:59 Status: Beginning import [IMPORT SITE], batch 3000.

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               > IMPORT SITE                                3000

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               >   Importing                                EIM_PTL_SITE_LS

DBCLog    DBCLogError             1              000014d9578914c4:0             2016-08-04 10:59:05               SQL Warning, SQL State 01000, 0, [tp][ODBC Oracle driver]The currently active transaction was committed before changing the AutoCommit connection option.

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               >     Step 1: initializing IF table                                          0s

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               >     Step 2: applying default values                                        0s

EIMTrace  EIMTraceSubEvent    3              000014d9578914c4:0             2016-08-04 10:59:05               >     Step 1_2: resolve organization                                         0s

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               Process [IMPORT SITE] had 1 row fail

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               on EIM_PTL_SITE_LS for batch 3000 in step 4, pass 106:

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06                 Failed to resolve foreign key value. (severity 8)

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               PTCLRG_PGM_NAME (Name)

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               PTCLRG_PTCL_NAME (Ptcl Name)

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               Base table:

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               S_PTCL_SITE_LS (Clinical Protocol Site)

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               PTCL_RGN_ID (Ptcl Rgn Id)

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               This is a foreign key value in the base table and the values in the interface

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               table did not resolve to existing values. Verify that the IF columns correspond

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               to existing base table rows.

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               This failure did not eliminate the rows from further processing. Processing

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               will continue for other columns in the same and in other destination base

EIMError   EIMErrorSubEvent    0              000014d9578914c4:0             2016-08-04 10:59:06               table.

I hope this quick guide helps refresh your EIM skills and gets you started on your next EIM job import. If you need any help, fill out the form below and we’ll get back to you.