5Importing Data
Importing Data
Importing data into Siebel base tables is a multistep process that requires significant effort. You must first load data from an external database into the EIM tables. Subsequently, you need to run an EIM process to read the data in these EIM tables and import them into the appropriate Siebel base tables.
This chapter is organized into the following sections:
EIM Import Process
To import tables of data, EIM performs a sequence of tasks. Each task involves multiple passes; at least one pass is required for each EIM table included in the process. Depending on the type of import process, EIM may repeat several tasks.
This section describes the general tasks that EIM performs to import data into the Siebel database using EIM. To see the general steps that you take when using EIM to import data, see Import Data Process Flow.
To import data from EIM tables, EIM performs the following steps:
EIM initializes any temporary columns:
It compares values in IF_ROW_BATCH_NUM with the batch number provided by the Component task that initiated this import process. For information on IF_ROW_BATCH_NUM, see Mandatory Columns for EIM Processing.
It sets all temporary columns to NULL and counts the rows to be processed.
Note: If there are rows where required columns contain only blanks, the complete EIM process will fail at this step. Rows will not be imported or updated.
EIM applies any DEFAULT_COLUMN and FIXED_COLUMN values defined for this import process. For information on DEFAULT_COLUMN and FIXED_COLUMN, see Parameters Used for Imports in Both the Header and Process Sections.
EIM applies any filter queries defined for this import process. If a row fails the filter query, EIM eliminates the row from further processing.
EIM generates foreign key references for rows with corresponding existing rows in the Siebel base tables. It writes these foreign key values into EIM table temporary columns.
If foreign keys fail for required columns, EIM eliminates these rows from further processing. It also validates bounded picklist values against the List of Values table (S_LST_OF_VAL). For this validation to occur, the List of Values must be specified at the table level, and not just at the business component level. For more information on bounded and unbounded picklists, see Configuring Siebel Business Applications.
EIM writes the appropriate ROW_ID values in the EIM table rows’ temporary columns, for rows with corresponding base table rows. For information on ROW_ID, see Mandatory Columns for EIM Processing.
EIM creates a ROW_ID with a unique value in the base table for each EIM table row without a corresponding row in the base tables.
EIM eliminates rows with invalid values for user keys from further processing.
Note: You can use EIM to update only non-user key columns; EIM does not support modification of existing user key columns. To update user key columns in S_ORG_EXT, S_PROD_INT, S_PROD_EXT, S_PARTY tables use EIM_ORG_EXT_UK, EIM_PROD_INT_UK, EIM_PROD_EXT_UK, and EIM_PARTY_UK. The postfix UK denotes user key.Note: For more information, see Fields That Cannot Be Updated.It then generates foreign key references for rows without corresponding rows in the Siebel database tables, and writes these foreign key values into EIM table temporary columns:
If foreign keys fail for required columns, EIM eliminates these rows from further processing.
For EIM table rows with data that will reside in multiple destination tables, EIM fails rows with foreign keys that cannot be generated.
EIM updates contents of existing base table rows with contents from corresponding EIM table rows that have successfully passed all earlier steps:
If any rows contain content that differs from the existing base table row, EIM writes these rows to the Master Transaction Log (if Enable Transaction Logging is enabled).
If multiple EIM table rows have the same user primary key for a base table, EIM uses only the first EIM table row to update the base table, and ignores the data in other rows.
EIM inserts any new EIM table rows that have successfully passed all earlier steps in the Siebel database tables:
It writes new rows to the Master Transaction Log (if Enable Transaction Logging is enabled).
If multiple EIM table rows use the same user primary key for a base table, EIM uses only the first EIM table row to update the base table, and ignores the data in other rows.
EIM updates primary child relationships in the Siebel database tables as necessary. EIM populates all primary child columns with Primary Child Col property set to TRUE. For information on primary child relationships, see About Explicit Primary Mappings.
Caution: You may want to use the UPDATE ROWS = FALSE statement to preserve existing information. Suppressing updates prevents updating primaries in this step of the import process, so this setting should be used with caution. For more information, see Suppressing Updates.Finally, EIM runs optional miscellaneous SQL statements. For more information, see the section on the MISC SQL parameter in Parameters Used for Imports in Both the Header and Process Sections.
Import Data Process Flow
This section describes the general process flow that you must follow to import data into the Siebel database using EIM.
Identify and validate the data to be imported. To perform this task, you must:
Determine the data to load and whether it already exists in another database. You should review existing data for completeness. For example, the Siebel database may require both an area code and a telephone number, while your existing database may not.
Determine the number of opportunities, contacts, and accounts you plan to import. This information assists you in estimating the time and resources required to import, process, and store your data.
Note: If the data exists in a database that uses a different character set, the import process does not work properly until you recreate the database.
Identify the column mappings and user key columns of the data to be imported. To perform this task, you must:
Identify the mapping between the data and Siebel base columns. For information on Siebel base table columns, see Siebel Data Model Reference on My Oracle Support.
Identify the EIM table columns that map to these base table columns. To view mappings between EIM table columns and base table columns, see EIM Table and Column Mappings.
Identify the user key columns and make sure they are populated uniquely. For information on user key columns, see Siebel Data Model Reference on My Oracle Support.
Make sure that your hardware and software environments are ready. Before you use Siebel EIM tables to import data, the Siebel application must be properly installed.
Work with your Siebel representative and MIS personnel to verify that the required hardware and software resources are available. For information about resource requirements, see Importing Large Databases.
Back up your existing database. Before undertaking any significant change—such as installing a new application, importing data, or upgrading an installed application—you should first perform a comprehensive backup of your database. This facilitates an easy recovery if problems occur.
Copy file attachments to the Siebel Server subdirectory named “input.” If you want to import file attachments, you can:
Copy the files to the input subdirectory under the Siebel Server root directory.
Store file attachments in the location specified in the ATTACHMENT DIRECTORY .IFB file header parameter.
Siebel EIM tables support all file attachment formats, including common file types such as Word documents (.doc), Excel spreadsheets (.xls), and text files (.txt). For information on file attachment columns, see File Attachment Columns.
Load and verify the EIM tables. Your database administrator can use a database tool provided with your RDBMS (such as SQL*Loader, Bulk Copy Utility, or dbload) to copy data from your existing database to the Siebel EIM tables.
Note: Siebel EIM tables contain several special columns that must be populated before rows can be imported. For more information, see EIM Table Columns.After the EIM tables are loaded, check the number of loaded rows against your existing database to make sure that the appropriate rows were loaded.
Check the contents of several rows to make sure that the tables are ready for the import process.
For information on preparing the EIM tables for data import, see Preparing the EIM Tables for Import Processing.
Edit the EIM configuration file (default.ifb). This file customizes the behavior of EIM by defining the data you will import and identifying the batch number to use.
For information on editing the EIM configuration file for data import, see Using the EIM Configuration File to Define a Process.
Test your import process. Run a small test batch (perhaps 100 records) to verify that the EIM tables load correctly, and that the correct parameters are set in the configuration file and on the srvrmgr command line.
For information on testing your import process, see Siebel Performance Tuning Guide.
Run the import process. Although your batch sizes depend on the volume of data you must import, consider using multiple smaller batches (1,000 to 5,000 rows) rather than one large batch. Smaller batches place fewer demands on resources. Also, when using smaller batches, the fixing of problems is simpler. If a batch is not imported correctly, it is easier to isolate the condition, correct it, and rerun the batch.
For more information on this step, see Running an Import Process.
Verify results. EIM provides several diagnostic tools that let you verify the success of import processing. For information on these tools, see Checking Import Results.
You must test and run the import process and verify the results for each batch you are importing. If an import process failure occurs, see Troubleshooting Import Processing Failures for descriptions of problems that can cause failures.
EIM provides comprehensive status information about each import process. When a process ends, you should review the information as described in Checking Import Results.
Importing Legacy Data
This section describes the general concepts and procedures for importing legacy data into the Siebel database using EIM.
Recommended Import Order for Importing Legacy Data
The order in which legacy data is imported is critical to make sure that relationships between dependent data elements are established correctly. Siebel EIM tables do not map one-to-one with Siebel target database tables.
To make sure that the necessary data is present to establish relationships between data entities, use the following sequence to import data:
Administrative (for example, a List of Values for Currency or Zip Code)
Business Unit
Positions
Accounts
Contacts
Employees
Products
Opportunities
Personal Accounts
Quotes
Documents
Forecasts
Fulfillment
Marketing Campaigns
CPG Promotion Management
CPG Product Movement
Service Requests
Product Defects
Activities and Appointments
Notes
File Attachments
This import order reflects most import processes. In some cases, the import order for your import process may vary slightly depending on your requirements.
While the import order is most critical when performing the initial import of legacy data, this recommended order should be followed for all subsequent data imports as well.
Importing an Initial Batch of Legacy Data
When you are importing an initial batch of legacy data, you need to complete the following procedure.
To import initial batches of data
In the EIM table, assign a unique batch number to each batch of data in the IF_ROW_BATCH_NUM column.
Disable the Enable Transaction Logging preference.
Note: Typically, initial data loads require transaction logging to be turned off. Siebel Mobile Web Clients will receive their updates during this initial data load.Navigate to Administration - Siebel Remote screen, then the Remote System Preferences view.
Clear the Enable Transaction Logging system preference.
Click Save.
You can also change the transaction logging preference by changing the LOG TRANSACTIONS parameter in the EIM configuration file. For more information, see Process Section Parameters Generic to All EIM Processes.
Start an EIM task for each batch number.
For information on running an EIM process, see Running an Import Process.
Review your import processes by using the log file produced by EIM (EIM_task#.log).
This file contains comprehensive status and diagnostic information about the import processes. By default, this file is located in the Siebel Server log directory.
Using ACT! for Legacy Data Imports
One of the options for importing bulk data from a legacy system into the Siebel database is to use ACT!
ACT! 2.0 and ACT! 3.0 are the only versions that have File/Import functionality for data import into Siebel CRM.
You can use “Exporter for ACT!” to export ACT! 4.0 or 2000Contacts, Notes/History, Activity, Group, Sales and E-Mail data into comma-delimited files.
For information on ACT! products, visit their official Web site.
Importing Large Databases
Before importing a large database, such as a legacy database, you should thoroughly test your import processes. Once the test batches are loaded correctly and any data discrepancies that may affect other batches are resolved, you may want to consider importing large batches for the remaining data. Before doing so, first make sure that the Siebel database is capable of storing the volume of data, and that your resources are adequate to support the processing.
Memory Resources Needed for EIM
To achieve and maintain high performance, the database memory area needs to be large enough to hold most of the frequently accessed data in the cache. Because a very large EIM batch may flush all the data from the cache and cause performance degradation, limit EIM batch sizes so the most frequently accessed data can remain in memory.
Database Resources Needed for EIM
EIM uses database server space for the EIM tables, target base tables, secondary tables, and work areas. To make sure that an import process runs smoothly to completion, you must anticipate and plan for these space requirements. Actual requirements vary based on the RDBMS you are using and the size of the database you are populating. Work with your Siebel representative and database administrator to develop a database blueprint that addresses the following resource requirements:
Base tables and indexes. When establishing appropriate sizes for the Siebel base tables and indexes, consider not only current size, but also reasonable growth. You should plan for future changes that may affect the database, such as organization expansion, new product lines, and company acquisitions. For more information on table sizing, see the documentation for your RDBMS.
Secondary tables. You may be importing data from a single EIM table into multiple destination tables. For each EIM table (except EIM_note), there is a primary, or target, Siebel base table. In addition, there may be one or more secondary tables associated with the target table. Data from the EIM table may ultimately reside in one of these secondary tables.
Database manager transaction logging area. The database manager uses a disk area to log its transactions. If you fail to set an adequately sized logging area for this operation, the database manager halts when the area runs out of space.
Transaction rollback areas. Database resources are temporarily allocated to store intermediate results used to recover the original database state if a transaction is rolled back or aborted. Each RDBMS may use a different implementation. The amount of data processed in a transaction determines the amount of database resources required for rollback areas. Make sure that you allocate sufficient resources, or use smaller batch sizes, to handle the rollback requirements. Your database administrator can configure your database to allocate adequate transaction rollback areas.
After working with small batches to make sure that your import processes run smoothly, you may want to initiate an unattended session in which EIM runs multiple import processes to load a large database.
Updating the Siebel Database
After you have completed the initial import of enterprise data, you can periodically use EIM to update the Siebel database. For example, if you add a new product line, it may be efficient to load the data into your enterprise inventory management database and then import it into the Siebel database. Use the steps described in Import Data Process Flow, although the scope of the update import is usually significantly smaller than that of an initial data import.
By default, when importing information, EIM performs both inserts and updates based on the content of the batch set. EIM first examines the set of information to determine which rows in the batch already exist in the Siebel database:
Batch rows matching existing base rows are used to update the database.
Batch rows that do not match base rows are used to perform inserts.
See INSERT ROWS and UPDATE ROWS Parameters for further information.
In some circumstances, you may need to suppress inserts and updates. For more information on adjusting parameters to suppress an insert or update, see Suppressing Data When Updating Existing Databases.
Updating Siebel Database for Batches with Both an Insert and Update to the Same Record
You may need to update the Siebel database with a batch that contains a record to be inserted as well as an update to that same row. When you use EIM to do this, a record will be inserted, but the update will be flagged as a duplicate.
EIM processes a record once for each batch, so for each record, MIN(ROW_ID) is processed, and the other record is marked as a duplicate (IF_ROW_STAT is set to DUP_RECORD_IN_EIM_TBL for the duplicate record). If you enter the user key of a record with different attributes twice in the EIM table, only the record with the MIN(ROW_ID) will be imported or updated. The duplicate will be ignored.
To avoid this situation, analyze the input records before beginning the EIM task. If you find duplicate records, you can either combine them into one record, or specify a different batch number for the duplicate record so as to process the update in a separate batch. For more information, see Siebel Performance Tuning Guide.
Fields That Cannot Be Updated
You cannot update system fields. All Siebel system fields are fields reserved only for use by Oracle for internal Siebel processes. They are not to be populated with customer data.
The following are reserved system fields that cannot be updated:
CONFLICT_ID
CREATED
CREATED_BY
LAST_UPD
LAST_UPD_BY
MODIFICATION_NUM
ROW_ID
DB_LAST_UPD
DB_LAST_UPD_SRC
Preparing the EIM Tables for Import Processing
This section explains how to prepare the EIM tables for a subsequent import into a Siebel database. To import data, EIM reads data in the EIM tables and writes data in the appropriate Siebel base tables by making multiple passes through the EIM tables to:
Set initial values for some columns in the EIM tables
When importing new data, make sure to populate the columns marked Required in the EIM table.
When updating existing records you do not need to populate the Required columns, but the user key columns must be populated.
To find which columns are required, and which columns are user keys, generate a table mapping report. See Generating EIM Table Mapping Reports.
Apply filter logic to select rows for importing
Generate foreign key references and internal values
Add or update relevant Siebel database rows
Update each EIM table row to indicate its import status
For general information on EIM tables, see Siebel EIM Tables.
Required Initial Values for Special Columns
Each row to be imported must contain the data you want to import and the appropriate values in the following columns:
ROW_ID. This value, in combination with the nonempty contents of IF_ROW_BATCH_NUM, must yield a unique value.
IF_ROW_BATCH_NUM. Set this value to an identifying number for all rows to be processed as a batch.
IF_ROW_STAT. In each row to be imported, set this column to FOR_IMPORT to indicate that the row has not been imported. After processing, if certain rows were not imported due to a data error, do the following:
Change the IF_ROW_BATCH_NUM value in the EIM Interface Table for those rows on which the EIM task (import, update, delete) needs to be executed. This value must correspond to the BATCH value or values provided in the IFB-File.
To identify rows that are not imported, use the following SQL statement:
SELECT * from <EIM Interface Table>
where IF_ROW_BATCH_NUM = <BATCH NUMBER USED IN PRECEDING EIM TASK> AND IF_ROW_STAT <> 'IMPORTED'
Once you determine which rows have failed, change the batch number for those rows in the EIM table to another batch. For example, if the first run uses batch number 100, and 10 rows failed, run a SQL to update the 10 failed rows to a new batch number, such as batch 101. After the batch number is changed for the failed 10 rows, run EIM the Import operation again, set BATCH = 101 in the ifb file. When you rerun the EIM Import, the operation will pick up the 10 rows under batch 101.
For more information on the BATCH IFB Parameter see the table in Process Section Parameters Generic to All EIM Processes.
For more information on special columns, see EIM Table Columns.
Required Initial Values for File Attachment Columns
Each file attachment row must contain the filename reference to the files you want to import and the appropriate values in the following columns:
FILE_NAME. Set this column to the root filename of the file attachment.
FILE_EXT. Set this column to the extension type of the file attachment (such as DOC, XLS, or TXT).
FILE_SRC_TYPE. This column must be set to FILE.
For more information on file attachment columns, see File Attachment Columns.
Adjusting the Case of Values
EIM supports various case values defined for base table columns in Siebel Tools. EIM adjusts the case value of an EIM table column according to the Force Case property of the corresponding base table column.
Prior to importing data into base table columns, EIM also adjusts the case of values in EIM table columns as defined in the list of values. The available case modes include:
Upper (Makes all letters uppercase)
Lower (Makes all letters lowercase)
FirstUpper (Makes the first letter of each word uppercase and leaves other letters unchanged)
None (Has no effect)
If a requested case mode is not supported by the database, EIM performs a row-by-row pass through the EIM table to adjust the case of column values and update the row accordingly. If this occurs, you should expect slower import processing.
Editing the Configuration File for Import Processing
This section describes the header and process sections that you need in the EIM configuration file to properly configure EIM for an import process. For general information on the EIM configuration file, see EIM Configuration File.
Before import processing begins, you must change the configuration file to support this function. Such changes include:
Editing the header and process sections and parameters.
Adjusting settings in the configuration file for various purposes. See Special Considerations for Imports.
Header Section Parameters Used for Imports
Parameters in the header section generally apply to all types of processes. For a description of the necessary contents in the header section, see Header Section Parameters Generic to All EIM Processes.
Process Section Parameters Used for Imports
Parameters in the process section apply only to that specific process and override any corresponding value in the header section for the specific process. This section describes the parameters used in the process section that are specific to an import process. For generic parameters that can be used in all EIM processes, see Process Section Parameters Generic to All EIM Processes.
The following information lists the parameters specific to an import process that appear in the process section of the EIM configuration file. (For the parameters specific to an import process that can appear in both the process section and the header section of the EIM configuration file, see the information in Parameters Used for Imports in Both the Header and Process Sections.)
| Parameter | Description |
|---|---|
COMMIT OPERATIONS |
Docking Log row commit frequency; default is 0. |
FILTER QUERY |
SQL preprocess filter query fragment. Example: This parameter names a query that runs before the import process. The query prescreens certain rows in the import batch, using data values in the EIM tables. Rows that do not meet the filter criteria are eliminated. The query expression should be a self-contained WHERE clause expression (without the WHERE keyword) and should use only unqualified column names from the EIM table or literal values (such as name is not null). By default, the FILTER QUERY parameter is not used. |
IGNORE BASE COLUMNS |
Specifies base table columns to be ignored by the import process. Use commas to separate column names, which can be qualified with base table names. Required and user key columns cannot be ignored. Use this parameter to improve performance when updating all but a few columns. The default is to not ignore any base table columns. |
IGNORE BASE TABLES |
Specifies base tables to be ignored by the import process. Use commas to separate table names. Target tables for EIM tables cannot be ignored. The default is to not ignore any base tables. Use this parameter to improve performance when updating all but a few tables. This parameter affects all EIM tables used in the import process. |
ONLY BASE COLUMNS |
Specifies and restricts base table columns for the import process. Use commas to separate column names, which can be qualified with base table names. Include all user key columns and required columns. Use this parameter to improve performance when updating many rows but few columns. The default is to process all interface columns mapped to the base table. Example: |
ONLY BASE TABLES |
Specifies and restricts selected base tables for the import process. Use commas to separate table names. Target tables for EIM tables must be included. The default is to process all base tables into rows that can be imported from the EIM tables. Use this parameter to improve performance when updating only a few tables. This parameter affects all EIM tables used in the import process. Example: |
UPDATE ROWS |
Optional base table, TRUE/FALSE toggle; default is TRUE. For more information on the UPDATE ROWS parameter, see INSERT ROWS and UPDATE ROWS Parameters. |
Parameters Used for Imports in Both the Header and Process Sections
The following information describes the parameters that can appear in either the header section or a process section, and are specific to an import process. For generic parameters that can be used in all EIM processes, see Process Section Parameters Generic to All EIM Processes. (The information in Process Section Parameters Used for Imports lists the parameters specific to an import process that appear in only the process section of the EIM configuration file.)
| Parameter | Description |
|---|---|
ATTACHMENT DIRECTORY |
(Default = SIEBEL_HOME\INPUT) Specifies the directory to be used for importing attachments. Before specifying a directory, make sure the directory exists on a Siebel Server machine and you have read and write access to the directory. Example: |
COMMIT EACH PASS |
Specifies whether a separate transaction should be used for each EIM pass through each EIM table. The default value is TRUE, which invokes commits after each pass. This setting helps to reduce the database resources required for the import process and provides a checkpoint to which you can return in the event of unexpected results.
Note: COMMIT EACH PASS works cumulatively with COMMIT EACH TABLE. If you set both commit each pass and commit each Table to true, a commit will occur at the end of each pass and at the end of each table.
|
COMMIT EACH TABLE |
Specifies whether a separate transaction should be used for each EIM table. The default value is TRUE, which invokes commits after each table. This setting helps to reduce the database resources required for the import process.
Note: COMMIT EACH TABLE works cumulatively with COMMIT EACH PASS. If you set both commit each pass and commit each Table to true, a commit will occur at the end of each pass
and at the end of each table.
|
COMMIT OPERATIONS |
(Import only.) Specifies the number of insert and update operations to be performed before a commit is invoked. The value for this parameter, an integer greater than zero, prevents the transaction rollback space from overflowing when large data sets are imported. The default for COMMIT OPERATIONS is not set; a commit is thus invoked only at the end of the import by default. This setting is ignored if you have turned off Enable Transaction Logging.
Note: This parameter is useful only for row-by-row processing (with transaction logging on). It is not used for set-based processing operations.
|
DEFAULT COLUMN |
(Import only) Specifies a default value for an EIM table column. The syntax is column name, value. Example: The given value will be used only if the column is null in the EIM table. |
FIXED COLUMN |
(Import only.) Specifies the value for an EIM table column. The syntax is the same as for DEFAULT COLUMN. Example: The given value will be loaded into the Siebel base table, overriding the value in the EIM table column. |
INSERT ROWS |
Specifies that nonexistent rows in the EIM table be inserted into the Siebel base table. The default value is TRUE. A table name can be specified with insert rows as the first value, separated by a comma. Example: If the named table is an EIM table, as in the example, the setting applies to all Siebel base tables imported from this EIM table. If the named table is a Siebel base table, the setting is applied when data is imported from any EIM table.
Note: The INSERT ROWS parameter must be set to FALSE for any table with an EIM table that does not have mappings to all its required columns, such as S_ORDER for EIM_ORDER_DTL. In this example, when EIM is not able to resolve the EIM_ORDER_DTL row to an existing S_ORDER record, it attempts to insert it as a new S_ORDER record. Since EIM_ORDER_DTL does not have mappings to all the S_ORDER required columns, the process fails with a “Cannot insert null” error.
For more information on the INSERT ROWS parameter, see INSERT ROWS and UPDATE ROWS Parameters. |
MISC SQL |
Sets specific explicit or implicit primaries, as mentioned in Step 11 in EIM Import Process of the import process. Explicit is when you have specific values to set as primaries. Implicit is when any of a group of values is acceptable. For example, you are importing one account with nine addresses. If any of the addresses is acceptable as being the primary, then set primary to implicit. EIM then selects one of the addresses as primary. If a specific address should be the primary, then set primary to explicit and indicate the primary account by setting its flag column (EIM_ACCOUNT.ACC_PR_ADDR) to Y.
Note: MISC SQL is intended for initial data loading only (with
DOCKING TRANSACTIONS = FALSE), because when using MISC SQL to set primary child foreign keys, NO transactions are logged for mobile users.
For a list of fields that can be set using the MISC SQL parameter, see MISC SQL Parameter. |
NET CHANGE |
(Import only.) Specifies the handling of null (non-user key) column values when importing a row that already exists in the Siebel database table. If For more information on this parameter, see NET CHANGE Parameter. |
ROLLBACK ON ERROR |
Specifies whether the current transaction should be rolled back (aborted) when an error, such as an SQL database failure, is encountered. The default value is FALSE. If you set this parameter to TRUE, you should also set COMMIT EACH PASS and COMMIT EACH TABLE to FALSE, and make sure that the database transaction space is large. |
TRIM SPACES |
(Import only.) Specifies whether the character columns in the EIM tables should have trailing spaces removed before importing. The default value is TRUE. |
NET CHANGE Parameter
By default, EIM does not update non-user key columns—that is, columns with a null value. The NET CHANGE parameter specifies the handling of null (non-user key) column values when importing a row that already exists in the Siebel database table. If NET CHANGE = TRUE, the null value will be ignored. If NET CHANGE = FALSE, the column in the base table will be updated with NULL.
Effect of NET CHANGE = FALSE on IF_ROW_STAT
When NET CHANGE = FALSE, there are three possible outcomes:
For a null value, EIM updates the base table column to NULL and sets the EIM table’s IF_ROW_STAT to IMPORTED.
For a non-null value that is a duplicate, nothing is done to the base table column and the EIM table’s IF_ROW_STAT is set to DUP_RECORD_EXISTS.
For a non-null value that is not a duplicate, EIM updates the base table column with the value in the EIM table and sets IF_ROW_STAT to IMPORTED.
EIM only updates the non-user key columns with NULL if you set the NET CHANGE parameter to FALSE. Also note that when EIM updates non-user key columns with NULL for the columns that had a non-null value beforehand, then the status of IF_ROW_STAT becomes IMPORTED. This is because EIM has performed the update transaction for this table.
The second case mentioned shows, however, that if a column had a null value beforehand, and EIM has performed the update with all the same records (including this NULL column), then in effect, EIM has ignored this null value and has not performed an update transaction for this NULL column (regardless of whether NET CHANGE is set to FALSE). So in this case, EIM populates IF_ROW_STAT with DUP_RECORD_EXISTS.
If in cases like this you want to update certain columns with NULL, then you can specify the ONLY BASE COLUMNS parameter in the .IFB file.
Example of Using the NET CHANGE Parameter
The following example is part of a sample .IFB file that uses the NET CHANGE parameter:
[Siebel Interface Manager] USER NAME = "SADMIN" PASSWORD = "SADMIN" PROCESS = IMPORT ACCOUNT [IMPORT ACCOUNT] TYPE = IMPORT BATCH = 1 TABLE = EIM_ACCOUNT NET CHANGE = FALSE
MISC SQL Parameter
The following information lists the EIM tables that can be used with the MISC SQL parameter, as well as the values that can be set. The table lists the values of the MISC SQL parameter when you want to set a field explicitly. If you want to set the field implicitly, replace the letters EXPR (EXplicit PRimary) with IMPR (IMplicit PRimary). Note that all separators for values are underscores.
| Table and Primary Child Foreign Key | MISC SQL Parameter Value for Explicit Primary | Corresponding EIM Table | Comments |
|---|---|---|---|
S_PROJ.PR_OU_ADDR_ID |
EXPR_S_PROJ_PR_OU_ADDR_ID |
EIM_PROJECT |
No implicit primary |
S_OPTY.PR_OU_ADDR_ID |
EXPR_S_OPTY_PR_OU_ADDR_ID |
EIM_OPTY |
No implicit primary |
S_OPTY.PR_OU_INDUST_ID |
EXPR_S_OPTY_PR_OU_INDUST_ID |
EIM_OPTY |
None |
S_CONTACT.PR_HELD_POSTN_ID |
EXPR_S_CONTACT_PR_HELD_ POSTN_ID |
EIM_EMPLOYEE |
None |
S_CONTACT.PR_USERROLE_ID |
EXPR_S_CONTACT_PR_ USERROLE_ID |
EIM_USER |
None |
S_CONTACT.PR_OU_ADDR_ID |
EXPR_S_CONTACT_PR_OU_ ADDR_ID |
EIM_CONTACT |
None |
S_POSTN.PR_POSTN_ADDR_ID |
EXPR_S_POSTN_PR_POSTN_ ADDR_ID |
EIM_POSITION |
None |
S_POSTN.PR_EMP_ID |
EXPR_S_POSTN_PR_EMP_ID |
EIM_POSITION |
None |
S_ORG_EXT.PR_BL_PER_ID |
EXPR_S_ORG_EXT_PR_BL_PER_ID |
EIM_ACCOUNT |
None |
S_ORG_EXT.PR_SHIP_PER_ID |
EXPR_S_ORG_EXT_PR_ SHIP_PER_ID |
EIM_ACCOUNT |
None |
S_CONTACT.PR_AFFL_ID |
EXPR_S_CONTACT_PR_AFFL_ID |
EIM_CONTACT |
None |
S_ORG_EXT.PR_BL_PER_ID |
EXPR_SIS_S_ORG_EXT_PR_BL_ PER_ID |
EIM_ACCNT_CUT |
None |
S_ORG_EXT.PR_SHIP_PER_ID |
EXPR_SIS_S_ORG_EXT_PR_SHIP_ PER_ID |
EIM_ACCNT_CUT |
None |
S_ORG_EXT.PR_CON_ID |
EXPR_S_ORG_EXT_PR_CON_ID |
EIM_ACCNT_CUT |
None |
S_POSTN_CON.PR_ADDR_ID |
EXPR_S_POSTN_CON_PR_ ADDR_ID |
EIM_CONTACT1 |
None |
S_ORG_EXT.PR_BL_PER_ID |
EXPR_FINS_S_ORG_EXT_PR_BL_ PER_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_EXT.PR_SHIP_PER_ID |
EXPR_FINS_S_ORG_EXT_PR_ SHIP_PER_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_EXT.PR_CON_ID |
EXPR_FINS_S_ORG_EXT_PR_ CON_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_EXT.PR_BL_OU_ID |
EXPR_S_ORG_EXT_PR_BL_OU_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_EXT.PR_SHIP_OU_ID |
EXPR_S_ORG_EXT_PR_SHIP_ OU_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_EXT.PR_PAY_OU_ID |
EXPR_S_ORG_EXT_PR_PAY_ OU_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_EXT.PR_COMPETITOR_ID |
EXPR_S_ORG_EXT_PR_ COMPETITOR_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_EXT.PR_PRTNR_OU_ID |
EXPR_S_ORG_EXT_PR_PRTNR_OU_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_EXT.PR_EMP_REL_ID |
EXPR_FINS_S_ORG_EXT_PR_EMP_REL_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_BU.PR_BL_PER_ID |
EXPR_S_ORG_BU_PR_BL_PER_ID |
EIM_FN_ACCNT1 |
None |
S_ORG_BU.PR_SHIP_PER_ID |
EXPR_S_ORG_BU_PR_SHIP_PER_ID |
EIM_FN_ACCNT1 |
None |
S_CONTACT.PR_HELD_POSTN_ID |
EXPR_FINS_S_CONTACT_PR_HELD_POSTN_ID |
EIM_FN_CONTACT1 |
None |
S_ASSET.PR_ASSET_ID |
EXPR_S_ASSET_PR_ASSET_ID |
EIM_FN_ASSET1 |
None |
S_ORG_GROUP.PR_ADDR_PER_ID |
EXPR_S_ORG_GROUP_PR_ADDR_PER_ID |
EIM_FN_ORGGRP |
None |
S_PROD_INT_TNTX.PR_CATEGORY_ID |
EXPR_S_PROD_INT_TNTX_PR_CATEGORY_ID |
EIM_PRDINT_TNT |
None |
S_QUOTE_TNTX.PR_ORDER_ID |
EXPR_S_QUOTE_TNTX_PR_ORDER_ID |
EIM_QUOTE_TNT |
None |
If you always want to use explicit primaries, follow this syntax:
MISC SQL = EXPR_S_CONTACT_PR_OU_ADDR_ID
If you always want to use implicit primaries, follow this syntax:
MISC SQL = IMPR_S_CONTACT_PR_OU_ADDR_ID
The most flexible method is to use explicit primaries on the records for which you have specified a primary, and to automatically use implicit primaries on the records where you have not specified a primary. The following example shows this syntax:
MISC SQL = EXPR_S_CONTACT_PR_OU_ADDR_ID, IMPR_S_CONTACT_PR_OU_ADDR_ID
For more information on how to use the MISC SQL parameter, see the sample default.ifb file located in the Siebel Server/admin directory.
INSERT ROWS and UPDATE ROWS Parameters
The INSERT ROWS and UPDATE ROWS parameters have optional elements of their syntax. For both parameters, the default value is TRUE. To change this for all tables, use this syntax:
INSERT ROWS = FALSE
To change only one table, specify the table name as follows:
UPDATE ROWS = S_CONTACT, FALSE
To change multiple tables, specify each table in a separate line, as follows:
INSERT ROWS = S_CONTACT, FALSE INSERT ROWS = S_ADDR_ORG, FALSE
If you need the parameter to be FALSE for most tables, and TRUE for only a few, use this method:
UPDATE ROWS = FALSE UPDATE ROWS = S_CONTACT, TRUE UPDATE ROWS = S_ADDR_ORG, TRUE
Special Considerations for Imports
There are several issues you should be aware of when running import processes. These issues include the following:
Suppressing Data When Updating Existing Databases
By default, when importing information, EIM performs both inserts and updates based on the content of the batch set. However, situations may arise in which you want to perform only inserts or only updates.
Suppressing Inserts
When the batch is a superset of an existing table, you should suppress inserts. For example, you may have a batch set of employee information that includes every individual in your organization. However, your Siebel database contains only members of the sales organization. To ignore batch entries for nonsales personnel in this case, you may want to run the entire batch using this setting to perform updates to existing rows only. If EIM attempts to insert a new row with this setting, the IF_ROW_STAT column is updated to NOT_ALLOWED. This means that EIM has attempted to insert a new row, but the action is not allowed.
To suppress insertions
Set the INSERT ROWS parameter in the EIM configuration file to FALSE.
The following example shows how to suppress insertions of unmatched rows from the EIM_ACCOUNT table to the S_ORG_EXT base table.
[Import Accounts Details] TYPE = IMPORT BATCH = 1 TABLE = EIM_ACCOUNT INSERT ROWS = S_ORG_EXT, FALSE
Suppressing Updates
When the information in your database is already accurate and current, you should suppress updates. For example, opportunities and associated contacts might appear as a batch feed from an external application on a regular basis. You may only be interested in adding new opportunities while preserving the information in existing opportunities. Use the UPDATE ROWS = FALSE statement to preserve existing information.
To suppress updates to existing rows
Set the UPDATE ROWS parameter in the EIM configuration file to FALSE.
The following example shows how to suppress updates to existing rows in the S_ORG_EXT base table.
[Import Accounts Details] TYPE = IMPORT BATCH = 1 TABLE = S_ACCOUNT_DTLIF UPDATE ROWS = S_ORG_EXT, FALSE
Importing Customizable Products
If your data includes customizable products built in Siebel Product Configurator, you must use XML to load them. Customizable products cannot be loaded using EIM. Customizable products have rules, scripts, and resources associated with them, so in order to migrate customizable products, you must use XML import and export functionality. For information on exporting and importing products, see Siebel Product Administration Guide.
Importing Opportunities and Revenues
When importing opportunities and revenues, it is important to note that S_OPTY has some columns that are denormalized from S_REVN: the columns named SUM_*. These columns are not defined as type Denormalized, but nevertheless they need to be maintained as denormalized columns.
Maintaining Denormalized Columns
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.
Importing Marketing Responses
You must populate the CAMP_MEDIA_ID column in the S_COMMUNICATION base table with valid values from the S_SRC_DCP base table in order for the rows to be displayed in the Response views.
Importing Contacts
This topic provides information organized as follows:
For more information related to the importing of contacts, see:
ASGN_* Flags
When you import contacts and set positions using EIM, the flags ASGN_MANL_FLG, ASGN_DNRM_FLG, and ASGN_SYS_FLG are set so that the intersection records are not routed to remote users. The Contacts view on the local database will display fewer contacts than the same view for the same user on the server database.
S_POSTN_CON.ROW_STATUS Flag
The column S_POSTN_CON.ROW_STATUS is a flag that can have value Y or N. When a contact is imported with value Y for this column, the contact shows in the user interface with an asterisk [*] in the New column, which means it is a new contact.
Importing Private Contacts
Siebel applications do not support importing private contacts using EIM. The default.ifb file contains a section that sets the CON_PRIV_FLG column to a constant N to make sure that only public contacts are imported. Because EIM does not support importing private contacts, do not change the value of the PRIV_FLG column. Additionally, do not remove this section of the .IFB file. To import contacts, you must have the CON_PRIV_FLG section in the EIM configuration file.
Importing Contacts to Make Them Visible in the Contact List
You need to use EIM_CONTACT to import into S_PARTY, S_CONTACT, and S_POSTN_CON. Make sure S_POSTN_CON.POSTN_ID references valid positions and that there is at least one employee associated with each position. S_POSTN_CON.POSTN_ID is mapped by PC_POSTN_NAME, PC_POSTN_DIVN, PC_POSTN_LOC, and PC_POSTN_BU in EIM_CONTACT. PC_POSTN_BU does not map to S_POSTN.BU_ID and BU_ID is not among the user key columns of S_POSTN. Instead, PC_POSTN_BU together with PC_POSTN_DIVN and PC_POSTN_LOC are used to resolve the S_POSTN.OU_ID, which refers to the divisions the positions belong to.
Divisions are stored in S_ORG_EXT with user key columns NAME, LOC, and BU_ID. For divisions, S_ORG_EXT.BU_ID references Default Organization; therefore, PC_POSTN_BU should be populated with Default Organization.
Troubleshooting the Unique Constraint Error When Importing Accounts or Contacts
This topic documents the causes, diagnostic steps, and solutions for troubleshooting the unique constraint error received when importing data through EIM.
To resolve the problem, look for it in the list of Symptoms/Error Messages in the following table.
| Symptom/Error Message | Diagnostic Steps/Cause | Solution |
|---|---|---|
When importing Account or Contact data using EIM, the batch fails with the following error: EIM-00107: ODBC (SQL) error.
The log file displays an error message similar to the following error shown by an Oracle database: ODBC error 23000: [MERANT][ODBC
Oracle 8 driver][Oracle 8]ORA-
00001: unique constraint
(SIEBEL.S_CONTACT_U1) violated
ODBC error 23000: [MERANT][ODBC
Oracle 8 driver][Oracle 8]ORA-
00001: unique constraint
(SIEBEL.S_ORG_EXT_U1) violated
|
This unique constraint error usually occurs due to inconsistent data in the base tables or incorrect data populated in the interface tables. The inconsistent data may result when two different server tasks, such as Siebel EAI processes and an EIM process, are run at the same time to import the same data. For an example of this, see Example of Troubleshooting the Unique Constraint Error when Importing Accounts or Contacts. |
See Example of Troubleshooting the Unique Constraint Error when Importing Accounts or Contacts. |
Import of EIM contact data into the S_CONTACT table fails with this error. For diagnostic steps, see Example of Troubleshooting the Import of EIM Contact Data into the S_CONTACT Table. |
See Example of Troubleshooting the Import of EIM Contact Data into the S_CONTACT Table. |
|
Import of EIM account data into the S_ORG_EXT table. For diagnostic steps, see Example of Troubleshooting the Import of EIM Account Data into the S_ORG_EXT Table. |
See Example of Troubleshooting the Import of EIM Account Data into the S_ORG_EXT Table. |
Importing Party Records
There are columns in the S_PARTY table that must be populated when importing party records such as Contacts, Positions, and so on. The following are the required columns, with their possible values:
PARTY_TYPE_CD. Indicates the type of party data that is being imported. The PARTY_TYPE_CD column can have the following values:
| Value | Description |
|---|---|
Person |
For Contact, User, Employee, or Partner. |
Organization |
For Organization, Division, or Account. |
Household |
For a Household (or Group). A Household is comprised of a collection of Contacts, independent of Account affiliations. |
Position |
For an Internal Division Position. |
AccessGroup (OR) |
For bundling of Party entities. Relates a person to groups indirectly (through Positions, Organizations, Accounts, and so on). An Access Group can have Organizations, Accounts, Positions, and User Lists. |
UserList |
A User List contains Siebel persons as its members. User Lists are created on an ad-hoc basis, not restricted to the Organizations to which the persons belong or to the Positions they hold. |
The PARTY_TYPE_CD column must contain one the values described in this table. No custom values are allowed in the PARTY_TYPE_CD column.
PARTY_UID. PARTY_UID is populated by default through the Siebel upgrade process and the application UI with the ROW_ID of the party record (for example, Contact or Position) that is being created, but you maintain the value for this column. The value does not have to remain identical with the ROW_ID.
With EIM, the PARTY_UID gets populated with the value specified in the EIM table for this column. PARTY_UID may have a calculated value with logic, such as a combination of email and other data. For this reason, PARTY_UID is defined as VARCHAR100.
ROOT_PARTY_FLG. ROOT_PARTY_FLG supports performance for Oracle. The following are possible queries to retrieve high-level Positions, Organizations, or Access Groups. Try using the first query before the second one:
WHERE ROOT_PARTY_FLG='Y'. ROOT_PARTY_FLG is set to 'Y' for high-level Positions, Organizations, and Access Groups as it applies only to these party subtypes. It is set to 'N' for other party subtypes.
WHERE PAR_PARTY_ID IS NULL. Oracle cannot use an indexed access path because there are no index entries for NULL, so ROOT_PARTY_FLG was added.
Note: The PAR_PARTY_ID field needs to be populated only when the PARTY_TYPE_CD is set to Organization or Position. For Positions, if the record is a position that is the child of another position, then PAR_PARTY_ID needs to be populated with the ROW_ID of the parent position. In the case of Organizations, this field applies only to internal organizations. Similarly to Positions, the PAR_PARTY_ID needs to be populated with the parent organization if it has one.Note: Also note that Divisions and Accounts have PARTY_TYPE_CD set to Organization well, but it is not necessary to populate the PAR_PARTY_ID field.
Importing Solutions
The Solution business component has the following Search Specification property: [Solution Item=‘Y’]. For imported records of this type to be visible following an import process, you must import data from the EIM_SOLUTION interface table to the S_RESITEM base table with the value in the SOLUTION_ITEM_FLG column set to ‘Y.’
When importing into the S_RESITEM base table, you need to include the following columns in the ONLY BASE COLUMNS parameter in the EIM configuration file:
FILE_NAME
FILE_EXT
FILE_SRC_TYPE
If these columns are not included in the ONLY BASE COLUMNS parameter, a low-level error will be generated.
Another requirement is that the Internal Publish flag (INTR_PUBLISH_FLG) must be set in the parent record for imports to be visible in the Solution/Resolution Documents view.
Importing Call Lists
When importing into the S_CALL_LST base table, you need to include the following columns in the ONLY BASE COLUMNS parameter in the EIM configuration file:
FILE_NAME
FILE_EXT
FILE_SRC_TYPE
If these columns are not included in the ONLY BASE COLUMNS parameter, a low-level error will be generated.
Importing Positions and Employees
The Administration - Group views automatically maintain the internal organization hierarchy incrementally as you change your organization’s position hierarchy, minimizing transaction volume and therefore improving the performance of Siebel Remote. For more information on using the Administration - Group views for working with positions, see Siebel Security Guide.
When using EIM to import or update positions, you must generate reporting relationships after running EIM to maintain organization relationships. If you do not generate reporting relationships, then incomplete or inaccurate data will be displayed in views involving employees or positions. For example, the My Team View will fail to display all positions on the team.
To activate position hierarchy, see Activating Position Hierarchy. To generate reporting relationships, see Generating Reporting Relationships.
To import employees and positions
Before importing employees and positions, make sure that the Position and Department columns in the Employee table contain the correct data, as follows:
Data from the Hire Date column in the Employee table matches the data from the Emp_Start_Date column in the Position table.
Data from the Position Start Date column in the Employee table matches the data from the Position Start Date column in the Position table.
Position table contains the logons of all employees.
Data from the Employee Hire Date column in the Position table matches the data from the Hire Date column in the Employee table.
Import the Employee table.
You should import the Employee table first, because EIM searches for the foreign key of the Position table during its import and update of the Employee table.
Note: If you are importing employees and positions with S_CONTACT.PR_HELD_POSTN_ID and S_POSTN.PR_EMP_ID set as primary columns, import the Position table first. See the following procedure.Import the Position table.
If you want to import employees and positions using EIM and you also want to set the following primary columns, then you will have to run the import twice for the EIM_POSITIONS table:
S_CONTACT.PR_HELD_POSTN_ID
S_POSTN.PR_EMP_ID
For example, to import employees and positions with S_CONTACT.PR_HELD_POSTN_ID and S_POSTN.PR_EMP_ID as primary columns:
Import the Position table using the EIM_POSITION interface table.
Import the Employee table, associate positions, and set the primary held position (S_CONTACT.PR_HELD_POSTN_ID) with the use of the MISC SQL parameter.
Set the primary employee of Position (S_POSTN.PR_EMP_ID) by using the EIM_POSITION table and the MISC SQL parameter.
Activating Position Hierarchy
After importing or merging positions using EIM, or after merging positions through the user interface, it is necessary to generate reporting relationships to populate or rebuild the S_PARTY_RPT_REL. This happens automatically when you insert positions using the user interface.
The Generate Reporting Relationships process needs to be executed after the upgrade process and whenever the denormalized hierarchy structure (S_PARTY_RPT_REL) becomes out of sync with the data in the normalized tables (S_PARTY). As noted in Siebel Database Upgrade Guide, tables can become out of sync in the following cases:
-
After upgrading, the organizational hierarchy (even if there is only one organization) must be established to maintain appropriate visibility in the views.
-
When Siebel Enterprise Integration Manager is used to import or update any of the hierarchies (positions, organizations, or access groups).
Generating Reporting Relationships
If you want to modify your organization structure by importing or updating positions using EIM, you must generate reporting relationships after running EIM to maintain organization relationships. Before generating reporting relationships, you must first activate position hierarchy by completing the procedure in Activating Position Hierarchy.
For best performance, complete all organization changes before generating reporting relationships, because this operation generates a high number of transactions for mobile users. This operation generates reporting relationships for all organizations and divisions regardless of the organization or division you have selected in the GUI. For more information on organization administration, see Siebel Security Guide.
To generate reporting relationships
Navigate to the Administration - Group screen, then the Positions view.
In the Positions list, click Generate Reporting Relationships.
Click OK.
Importing Data with Parent and Child Relationships
Siebel applications support multilevel hierarchies for defining accounts, products, and product lines. For example, a product’s bill of materials may involve levels for components, assemblies, and sub-assemblies. Similarly, a parent account may have multiple child accounts for company divisions and wholly-owned subsidiaries. These child accounts may be further organized into subaccounts such as regions and offices.
Siebel applications support an unlimited number of levels within account, product, and product line structures. For a child entity to be successfully imported, its parent must first be successfully imported in a prior batch or in the same batch. For more information, see Example of Importing and Exporting Hierarchical LOVs.
Importing Industry Codes
Siebel applications support the use of Standard Industrial Classification (SIC) codes. For example, a company may want to categorize its customers by industry type using SIC codes. In Siebel applications, the SIC field holds values that map to specific industries. If you want to use SIC codes, you can import data from a third-party database that supports SIC codes using EIM.
Importing File Attachments
EIM can import file attachments in all formats, including common file types such as Word documents (.doc), Excel spreadsheets (.xls), and text files (.txt). EIM does not place a limit on the number or the total size of files that can be imported.
To import file attachments into Siebel database tables
Using Windows Explorer, navigate to the Siebel Server directory.
The default is c:\siebel.
Verify that the Siebel directory contains a directory named input.
If the directory does not exist, create it by choosing File, New, and then Folder, and then enter
input.Copy all file attachments to the input directory.
Siebel EIM tables support all file attachment formats.
Populate EIM tables with rows matching the file attachments.
Run EIM.
Note: All three file attachment columns (FILE_NAME, FILE_EXT, FILE_SRC_TYPE) must be populated in order to import file attachments. The FILE_SRC_TYPE column must be set to FILE. Although these columns can be listed as nullable in the EIM tables, the import process will return errors if you leave any of these columns as NULL.
Updating File Attachments
You can also update file attachments that have already been imported into the Siebel database.
In order to update file attachments, EIM deletes the old row pointing to the existing file attachment and then imports the new file attachment. After all file attachments have been updated, use the Siebel File System Maintenance Utility named sfscleanup.exe (during hours when the network is least laden) to clean the file attachment directory of any unused file attachments.
To update file attachments
Update the file attachment by completing the steps in Importing File Attachments.
Once all file attachments have been updated, run the Siebel File System Maintenance Utility named sfscleanup.exe to clean up the file attachment directory.
For information on using sfscleanup.exe, see Siebel System Administration Guide.
Importing Organizations That Contain the BU_ID Column
Base tables in the Siebel Data Model that are enabled for multiple organizations contain the BU_ID foreign key column. This column points to a business organization defined in the S_BU base table. Examples of such base tables include S_PROD_INT, S_PRI_LST, and S_DOC_AGREE.
During the import process, if the value supplied in the EIM table does not resolve to a valid business organization, EIM by default will continue to import the record with the BU_ID set to the default value defined in the base table. If you want EIM to report import failures for such instances, set the parameter SKIP BU_ID DEFAULT parameter to TRUE in the .IFB file (the default value for this parameter is FALSE).
If you have not implemented multi-org capability or if you will not be using organizations, then use the Default Organization, a predefined organization in the S_BU base table.
Importing Accounts Containing Multiple Team Members
You can import multiple team members for accounts using EIM_ACCOUNT. Accounts and team members are related through S_ACCNT_POSTN. You can import multiple team members for accounts at the same time and specify the primary positions by setting ACC_PR_POSTN to Y.
Importing Multiline Fields
When importing multiline fields, such as addresses, you should use CHR(13) and CHR(10) for the field to be displayed as a multiline field. Otherwise, the following warning may be displayed in the GUI:
You have tried to modify a group of fields that may have more than one value. To edit or add field values in this group, please open the first field in the group by clicking on the multivalue field control.
Importing Exported Rows Into Target and Secondary Tables
If user keys from the secondary tables are made up of foreign keys referencing the target table and additional user keys of nonrequired columns, note that:
If you export rows from both target and secondary base tables, one EIM table row will be created for every target table row, and a separate EIM table row will be created for every related secondary table row.
If you reimport the exported batch rows into both the target and secondary base tables, the exported target table rows will be imported into the secondary table as well. This is because the exported target table rows have NULL values in the secondary table interface columns, and the secondary table’s additional user keys allow NULL values to be imported. Additional rows will thus be mistakenly imported into the secondary base table.
To avoid this problem, after exporting the target and secondary base tables rows, you should split the secondary table rows out from the exported batch into another batch, and then import the target and secondary table rows separately.
Importing International Phone Numbers Using EIM
To import international phone numbers, the phone number must be prefixed with a plus (+) sign and the country code. For example, an international phone number with a country code of 44 should have the following format: +44123456789.
Any phone number without a preceding plus sign in the database is treated as a U.S. phone number. This leads to the display of +1 in front of the phone number, and the use of the corresponding PHONE_FORMAT if the regional settings of the client are different.
Importing URL Links Into the S_LIT Base Table
To import records as URL links into the S_LIT base table, the FILE_NAME column must not be NULL and the FILE_EXT column must be NULL for URLs.
Importing LOV and MLOV Data
When importing List of Values (LOV) data, whether into an LOV column or a multilingual LOV (MLOV) column, you must populate the EIM table column with the display value of a specific language. The difference between the two cases is the following:
When importing into an LOV column, the EIM engine puts the display value directly into the column.
When importing into an MLOV column, EIM translates MLOV values during the import process. The EIM engine looks up the Language Independent Code (LIC) of the display value in the EIM table column and populates the LIC into the MLOV column.
EIM runs in the same language as that of the Siebel Server installation. For example, if the Siebel Server installation is in German, the LANGUAGE parameter setting defaults to German. In this example, the following takes place:
To import into an MLOV column, you enter a German display value in the EIM table column. You can enter “Aktiv” to indicate an account status that is active. The EIM engine puts the corresponding LIC, "Active," into the MLOV column.
To import into an LOV column, the EIM engine puts "Activ" into the LOV column.
To find the specific steps for importing LOV data, see the example in LOV Validation.
LOV Validation
When importing data from EIM tables, you may encounter the following error message in your trace file:
[ERR00] Interface table: [ERR00] S_XXXX_XMIF (Interface for XXXX Built-In M:1 Extension Table) [ERR00] -------------- [ERR00] [ERR00] Base table: [ERR00] S_XXXX_XM (Account M:1 Extension) [ERR00] ------------ [ERR00] TYPE (Type) [ERR00] This column contains a bounded picklist value and the value given does not [ERR00] correspond to a value in the list-of-values table for the given picklist type.
This error message indicates that either a picklist has not been created for this column (TYPE) or the value in your EIM table for this column (TYPE) does not correspond to one of the values in the picklist for this column. To resolve this issue, you need to make sure that:
A picklist already exists for this column.
The value you are importing for this column corresponds to one of the values in the picklist.
The following procedure explains how to import data into an LOV table, using the S_ORG_EXT_XM table as an example.
To import data into an LOV table
To find the LOV type for a column in the S_ORG_EXT_XM TABLE, perform the following actions:
In Siebel Tools, select Types.
Click Table.
Select S_ORG_EXT_XM.
With the S_ORG_EXT_XM table highlighted, expand Column tree control, and find the Type column.
With the Type column highlighted, find the following two attributes in the Properties window:
Lov Bounded: TRUE
Lov Type: ORG_EXT_XM_TYPE
The TYPE column should contain the value as the VAL column in the S_LST_OF_VAL table.
Using the Siebel client, find S_ORG_EXT_XM_TYPE.
Navigate to the List of Values screen.
Query the Display Value column for ORG_EXT_XM_TYPE to make sure that the picklist already exists.
Using the Siebel client or EIM, add values for this bounded picklist.
If you are using the Siebel client:
In the List of Values view, create a new record.
In the Type column, type ORG_EXT_XM_TYPE.
In the Display value column, insert any value you want to use for this type.
Repeat Step c until you have created records for all values you want to have in this picklist.
If you are using EIM:
Populate the EIM_LST_OF_VAL table, set the TYPE column to ORG_EXT_XM_TYPE, and set the VAL column to any value you want to use for this type. Make sure to populate all the required fields in the EIM_LST_OF_VAL table.
Repeat Step a until you have inserted all records into the table for all values you want to have in this picklist.
Import data from EIM_LST_OF_VAL to S_LST_OF_VAL using EIM.
The VAL column in the S_LST_OF_VAL table should contain the same value as the TYPE column in the S_ORG_EXT_XM table.
EIM and Audit Trail
If the use of Audit Trail is a requirement in your Siebel implementation, set the following two system preferences to true. (Preferences are available in the Administration - Application screen, System Preferences view.)
EnableAuditing. Controls auditing for the system.
EnableEimAuditing. Controls auditing for EIM.
These preferences are false by default.
Running an Import Process
You can run an import process when you have:
Identified the data for import processing
Prepared the related EIM tables
Modified the EIM configuration file accordingly
Run the import process by completing the procedures in Running EIM.
Checking Import Results
When an import process ends, you should carefully check the results to verify that data was successfully imported. During each import process, EIM writes comprehensive status and diagnostic information to multiple destinations. This section explains how to use this information to determine the results of the import process and is organized as follows:
Viewing a List of Imported Rows
The first task you should perform to check the results of the import process is to view a list of the imported rows.
To view a list of imported rows
Query the appropriate EIM tables for rows whose if_row_batch_num equals the batch number for the import.
These columns in each EIM table indicate whether a row was imported successfully, and they identify the pass number on which a row failed. During various passes of import processing, EIM sets the IF_ROW_STAT value to one of the values shown in the following information.
If error flags, SQL trace flags, or trace flags were activated for the EIM process, you can also use the trace file to view the results of the EIM process. For more information on viewing the trace file, see Viewing the EIM Log File.
| Value | Comment |
|---|---|
AMBIGUOUS |
There are two rows in the base table that have the same user key but different conflict IDs. EIM cannot distinguish these rows. |
DUP_RECORD_EXISTS |
The row exactly matches rows that already exist in the destination tables. This error occurs in Step 8 in EIM Import Process. Note that a row may have a duplicate in the target base table, but not in other destination base tables. In this situation, EIM adds the new relation (a child or intersection table) in the other destination base tables, and does not mark the EIM table row as a duplicate. |
DUP_RECORD_IN_EIM_TBL |
The row was eliminated because it is a duplicate (has the same user key) of another row in the EIM table with the same batch number. In this case, MIN(ROW_ID) is the record processed, and the other records with the same user key are marked as DUP_RECORD_IN_EIM_TBL. Do not confuse DUP_RECORD_IN_EIM_TBL with DUP_RECORD_EXISTS. DUP_RECORD_EXISTS status indicates that the same record already exists in the base table, while DUP_RECORD_IN_EIM_TBL status indicates that there are two or more EIM table records having the same user key values. |
FOREIGN_KEY |
A required foreign key column in the target table could not be resolved. This error occurs in Step 4 in EIM Import Process. |
IMPORTED |
The row was successfully processed against all its destination base tables. This status is set after the import has been completed. You can check the import status by using database commands to query the appropriate EIM tables for rows whose IF_ROW_STAT value is not equal to IMPORTED. The result is a list of rows that were not successfully imported. |
MPORT_REJECTED |
A user-specified filter query failed for this row. This error occurs in Step 3 in EIM Import Process if the user has specified FILTER QUERY expressions. |
IN_PROGRESS |
In Step 1 in EIM Import Process, EIM sets IF_ROW_STAT to this initial value for all rows in the batch. If rows still have this status value after EIM exits, a failure occurred that aborted processing for this table. |
NON_UNIQUE_UKEYS |
The user key was not unique in all the user key specifications on the table. |
PARTIALLY_IMPORTED |
The row did not fail for the target table (although it may have been a duplicate), but did fail during processing of a secondary base table. This status is set after the import has completed. |
PICKLIST_VALUE |
A required picklist value in the target table could not be resolved. This error occurs for NULL or invalid bounded picklist values in Step 4 in EIM Import Process. |
REQUIRED_COLS |
One or more required columns for the target table were NULL. This error occurs for missing user key columns in Step 7 in EIM Import Process, or when inserting new rows in Step 9 in EIM Import Process. |
ROLLBACK |
EIM encountered an error, such as an SQL database failure, and rolled back the transaction. This status is only used when |
SQL_ERROR |
An SQL error occurred during an attempt to import this row. This error occurs for rows processed when Enable Transaction Logging is set to TRUE. |
Troubleshooting Import Processing Failures
EIM is designed to import large volumes of data. Most failures are caused by data errors. It is usually faster and easier to correct the data errors and resubmit the corrected rows as part of a subsequent batch than to reprocess an entire batch. EIM does not stop when failures occur.
Failures can occur at several steps during the EIM Import Process; each type of failure has a different cause. See the causes listed in the following table.
This section provides guidelines for resolving import processing problems. To resolve the problem, look for it in the list of Symptoms/Error Messages in the following table.
| Symptom/Error Message | Diagnostic Steps/Cause | Solution |
|---|---|---|
Step 4 Failures |
Step 4 (shown in EIM Import Process) processes foreign keys and bounded picklists. A row fails this step if the foreign key developed from values in the EIM table columns does not correspond to an existing row in the target Siebel database table. For example, a Step 4 failure on ACCNT_NAME indicates that the value in the ACCNT_NAME column of that row did not correspond to an existing name (S_ORG_EXT.NAME) or synonym name (S_ORG_SYN.NAME). |
Correct the data errors and resubmit the corrected rows as part of a subsequent batch. |
Step 6 Failures |
Step 6 (shown in EIM Import Process) failures generally indicate invalid user key values. For example, a contact with a NULL value for the LAST_NAME column will fail because this is a required user key. All user keys are required except MID_NAME for contacts (S_CONTACT.MID_NAME) and LOC (location) for accounts (S_ORG_EXT.LOC). |
Correct the data errors and resubmit the corrected rows as part of a subsequent batch. |
Step 7 Failures |
Step 7 (shown in EIM Import Process) evaluates the foreign key relative to the data being imported (whereas Step 4 as shown in EIM Import Process evaluates it relative to existing data). If the foreign key references a table that is imported from the same EIM table, Step 7 resolves foreign keys into the data to be imported. |
Correct the data errors and resubmit the corrected rows as part of a subsequent batch. |
Step 8 and Step 9 Failures |
Failures for Step 8 (as shown in EIM Import Process ) and Step 9 (as shown in EIM Import Process ) indicate columns that have NULL values for fields that are required but are not part of the user key. |
Correct the data errors and resubmit the corrected rows as part of a subsequent batch. |
Data not visible after import |
If you find that, after an EIM import, the data is not visible in some views or applets, it is probably because values required for a particular view or applet to display imported data may not have been imported. For example, the Sales Order Line Items applet’s product picklist will only display products with S_PROD_INT.SALES_SRVC_FLG value set to N. |
To determine which values need to be imported for a particular view or applet, do a client-side spooling and check the SQL conditions when selecting the record. |
Unable to edit quotes after import |
Users are unable to edit their quotes after importing quote information. |
Make sure that the APPROVED_FLG field is set to N or is blank for each quote. Setting APPROVED_FLG to Y makes the quote read only and not editable by the user. |