Import Data Through Siebel EIM
11 Steps in EIM Import Process:
1. EIM initializes any temporary columns:
2. EIM applies any DEFAULT_COLUMN and FIXED_COLUMN values defined for this import process.
3. EIM applies any filter queries defined for this import process. If a row fails the filter query, EIM eliminates the row from further processing.
4. EIM generates foreign key references for rows with corresponding existing rows in the Siebel base tables.
5. EIM writes the appropriate ROW_ID values in the EIM table rows’ temporary columns, for rows with corresponding base table rows.
6. 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.
7. EIM eliminates rows with invalid values for user keys from further processing. 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:
8. EIM updates contents of existing base table rows with contents from corresponding EIM table rows that have successfully passed all earlier steps:
9. EIM inserts any new EIM table rows that have successfully passed all earlier steps in the Siebel database tables:
10. 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.
11. Finally, EIM runs optional miscellaneous SQL statements.
Import Data Process Flow:
1. Identify and validate the data to be imported. To perform this task, you must:
2. Identify the column mappings and user key columns of the data to be imported. To perform this task, you must:
3. 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.
4. 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.
5. Copy file attachments to the Siebel Server subdirectory named “input.” If you want to import file attachments, you can:
6. Load and verify the EIM tables.
7. Edit the EIM configuration file. This file customizes the behavior of EIM by defining the data you will import and identifying the batch number to use.
8. 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.
9. Run the import process.
10. Verify results.
Data Load Hierarchy for Siebel Entities:
To make sure that the necessary data is present to establish relationships between data entities, use the following sequence to import data:
1. Administrative
2. Business Unit
3. Positions
4. Accounts
5. Contacts
6. Employees
7. Products
8. Opportunities
9. Personal Accounts
10. Quotes
11. Documents
12. Forecasts
13. Fulfillment
14. Marketing Campaigns
15. CPG Promotion Management
16. CPG Product Movement
17. Service Requests
18. Product Defects
19. Activities and Appointments
20. Notes
21. File Attachments
Updating the Siebel Database:
After you have completed the initial import of enterprise data, you can periodically use EIM to update the Siebel database.
Preparing the EIM Tables for Import Processing:
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:
1. Set initial values for some columns in the EIM tables
2. When importing new data, make sure to populate the columns marked required in the EIM table.
3. When updating existing records you do not need to populate the required columns, but the user key columns must be populated.
4. Apply filter logic to select rows for importing.
5. Generate foreign key references and internal values.
6. Add or update relevant Siebel database rows.
7. Update each EIM table row to indicate its import status.
Required Initial Values for Special Columns:
Required Initial Values for File Attachment Columns:
Editing the Configuration File for Import Processing:
Process Section:
COMMIT OPERATIONS: Docking Log row commit frequency; default is 0.
FILTER QUERY:
IGNORE BASE COLUMNS:
IGNORE BASE TABLES:
ONLY BASE COLUMNS:
ONLY BASE TABLES:
UPDATE ROWS: Optional base table, TRUE/FALSE toggle, default is TRUE.
Common Header and Process Section Parameters:
ATTACHMENT DIRECTORY:
COMMIT EACH PASS:
COMMIT EACH TABLE:
COMMIT OPERATIONS (IMPORT Only):
DEFAULT COLUMN (IMPORT Only):
FIXED COLUMN (IMPORT Only):
INSERT ROWS:
MISC SQL:
NET CHANGE (IMPORT Only):
ROLLBACK ON ERROR:
TRIM SPACES (IMPORT Only):
NET CHANGE:
Effect of NET CHANGE = FALSE on IF_ROW_STAT:
When NET CHANGE = FALSE, there are 3 possible outcomes:
1. For a null value, EIM updates the base table column to NULL and sets the EIM table’s IF_ROW_STAT to IMPORTED.
2. 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.
3. 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.
MISC SQL:
Special Considerations in Import Process: Not covered here, See Bookshelf (IMPORTANT)
Importing Party Records:
The PARTY_TYPE_CD column can have the following values:
PARTY_UID:
ROOT_PARTY_FLG:
only to these party subtypes. It is set to 'N' for other party subtypes.
2. 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.
IF_ROW_STAT values after Import:
AMBIGUOUS:
DUP_RECORD_EXISTS:
DUP_RECORD_IN_EIM_TBL:
IMPORTED:
IMPORT_REJECTED: A user-specified filter query failed for this row.
IN_PROGRESS:
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.
PICKLIST_VALUE:
REQUIRED_COLS:
ROLLBACK:
SQL_ERROR:
11 Steps in EIM Import Process:
1. 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.
- It sets all temporary columns to NULL and counts the rows to be processed.
- 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.
2. EIM applies any DEFAULT_COLUMN and FIXED_COLUMN values defined for this import process.
3. EIM applies any filter queries defined for this import process. If a row fails the filter query, EIM eliminates the row from further processing.
4. 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).
5. EIM writes the appropriate ROW_ID values in the EIM table rows’ temporary columns, for rows with corresponding base table rows.
6. 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.
7. EIM eliminates rows with invalid values for user keys from further processing. 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.
8. 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.
9. 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.
10. 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.
11. Finally, EIM runs optional miscellaneous SQL statements.
Import Data Process Flow:
1. 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.
2. 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.
- Identify the EIM table columns that map to these base table columns.
- Identify the user key columns and make sure they are populated uniquely.
3. 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.
4. 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.
5. 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 files 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).
6. Load and verify the EIM tables.
- 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.
7. Edit the EIM configuration file. This file customizes the behavior of EIM by defining the data you will import and identifying the batch number to use.
8. 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.
9. 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.
10. Verify results.
Data Load Hierarchy for Siebel Entities:
To make sure that the necessary data is present to establish relationships between data entities, use the following sequence to import data:
1. Administrative
2. Business Unit
3. Positions
4. Accounts
5. Contacts
6. Employees
7. Products
8. Opportunities
9. Personal Accounts
10. Quotes
11. Documents
12. Forecasts
13. Fulfillment
14. Marketing Campaigns
15. CPG Promotion Management
16. CPG Product Movement
17. Service Requests
18. Product Defects
19. Activities and Appointments
20. Notes
21. 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.
Updating the Siebel Database:
After you have completed the initial import of enterprise data, you can periodically use EIM to update the Siebel database.
- 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.
- 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.
- 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:
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:
1. Set initial values for some columns in the EIM tables
2. When importing new data, make sure to populate the columns marked required in the EIM table.
3. When updating existing records you do not need to populate the required columns, but the user key columns must be populated.
4. Apply filter logic to select rows for importing.
5. Generate foreign key references and internal values.
6. Add or update relevant Siebel database rows.
7. Update each EIM table row to indicate its import status.
Required Initial Values for Special 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.
Required Initial Values for File Attachment 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.
Editing the Configuration File for Import Processing:
Process Section:
COMMIT OPERATIONS: Docking Log row commit frequency; default is 0.
FILTER QUERY:
- SQL preprocess filter query fragment. Example: FILTER QUERY=(ACCNT_NUM = "1500")
- Runs before the import process.
- Prescreens certain rows in the import batch, using data values in the EIM tables.
- Rows that do not meet the filter criteria are eliminated.
- 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.
- Required and user key columns cannot be ignored.
- Improves 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.
- Target tables for EIM tables cannot be ignored.
- The default is to not ignore any base tables.
- Improves performance when updating all but a few tables.
ONLY BASE COLUMNS:
- Specifies and restricts base table columns for the import process.
- Include all user key columns and required columns.
- Improves performance when updating many rows but few columns.
- The default is to process all interface columns mapped to the base table.
- Example: ONLY BASE COLUMNS = S_ORG_EXT.NAME, S_ORG_EXT.LOC, S_ORG_EXT.BU_ID
ONLY BASE TABLES:
- Specifies and restricts selected base tables for the import process.
- 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.
- Improves performance when updating only a few tables.
- Example: ONLY BASE TABLES = S_CONTACT, S_ORG_EXT
UPDATE ROWS: Optional base table, TRUE/FALSE toggle, default is TRUE.
Common Header and Process Section Parameters:
ATTACHMENT DIRECTORY:
- Default = SIEBEL_HOME\INPUT
- Specifies the directory to be used for importing attachments.
- Make sure the directory exists on a Siebel Server machine and you have read and write access to the directory.
- Example: ATTACHMENT DIRECTORY = SIEBEL_HOME\INPUT
COMMIT EACH PASS:
- Specifies whether a separate transaction should be used for each EIM pass through each EIM table.
- DEFAULT is TRUE, which invokes commits after each pass.
- Reduces the database resources required for the import process and provides a checkpoint to which you can return in the event of unexpected results.
COMMIT EACH TABLE:
- Specifies whether a separate transaction should be used for each EIM table.
- DEFAULT is TRUE, which invokes commits after each table.
- Reduces the database resources required for the import process.
COMMIT OPERATIONS (IMPORT Only):
- Specifies the number of insert and update operations to be performed before a commit is invoked.
- Value is an integer > 0.
- 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.
DEFAULT COLUMN (IMPORT Only):
- Specifies a default value for an EIM table column.
- Example: DEFAULT COLUMN = CURCY_CD , "USD"
- 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.
- Example: FIXED COLUMN = ORG_CD, "Commercial"
- 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.
- DEFAULT is TRUE.
- Example: INSERT ROWS = EIM_ACCOUNT, FALSE
- 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.
MISC SQL:
- Sets specific explicit or implicit primaries.
- 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.
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 NET CHANGE = TRUE, the null value will be ignored; otherwise, the column in the base table will be updated with NULL.
- Ignored if UPDATE ROWS = FALSE.
- DEFAULT is TRUE
ROLLBACK ON ERROR:
- Specifies whether the current transaction should be rolled back (aborted) when an error, such as an SQL database failure, is encountered.
- DEFAULT 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.
- DEFAULT is TRUE.
NET CHANGE:
- By default, EIM does not update non-user key columns, i.e., 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 3 possible outcomes:
1. For a null value, EIM updates the base table column to NULL and sets the EIM table’s IF_ROW_STAT to IMPORTED.
2. 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.
3. 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 above 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.
MISC SQL:
- See Bookshelf for list of EIM tables that can be used with the MISC SQL parameter, as well as the values.
- The table lists the values of the MISC SQL parameter when you want to set a field
- For Explicit Promary mapping, use EXPR (EXplicit PRimary) and for Implicit Primary mapping use IMPR (IMplicit PRimary).
- Syntax: MISC SQL = EXPR_S_CONTACT_PR_OU_ADDR_ID, 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
Special Considerations in Import Process: Not covered here, See Bookshelf (IMPORTANT)
Importing Party Records:
The PARTY_TYPE_CD column can have the following values:
- Person: Contact, User, Employee or Partner
- Organization: Organization, Division, or Account
- Household: Household
- Position: Internal Division Position
- AccessGroup: Bundling of Party entities
- UserList: Siebel Persons as its members
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 that is being created.
- 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 top-level Positions, Organizations, or Access Groups.
only to these party subtypes. It is set to 'N' for other party subtypes.
2. 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.
IF_ROW_STAT values after Import:
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.
- 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.
- MIN(ROW_ID) is the record processed, and the other records with the same user key are marked as DUP_RECORD_IN_EIM_TBL.
- 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.
IMPORTED:
- The row was successfully processed against all its destination base tables.
- This status is set after the import has been completed.
IMPORT_REJECTED: A user-specified filter query failed for this row.
IN_PROGRESS:
- 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.
PICKLIST_VALUE:
- A required picklist value in the target table could not be resolved.
- This error occurs for NULL or invalid bounded picklist values.
REQUIRED_COLS:
- One or more required columns for the target table were NULL.
- This error occurs for missing user key columns or when inserting new rows.
ROLLBACK:
- EIM encountered an error, such as an SQL database failure, and rolled back the transaction.
- This status is only used when ROLLBACK ON ERROR = TRUE.
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.