EIM Data Import Steps

 Navigating the 11 Steps of EIM Data Import:

1. Getting Started: EIM kicks off by setting up temporary columns. It cross-checks the batch number from the initiating task with IF_ROW_BATCH_NUM values. All temporary columns start as empty, and the system counts the rows for processing. If any rows with empty required columns exist, the entire operation stops to prevent incomplete data.

2. Default Values in Play: EIM then applies predefined default and fixed values specific to this data import.

3. Filtering Out Unwanted Data: If specific filter queries are set for this import, EIM applies them. Rows that don't meet the filter criteria are removed from the process.

4. Foreign Key Generation: EIM generates foreign critical references for rows corresponding to existing Siebel base table entries. These references are stored in temporary columns. Those rows are removed from the pipeline if it can't create foreign keys for the required columns.

5. Assigning ROW_IDs: For rows corresponding to base table entries, EIM gives ROW_ID values into temporary columns.

6. Creating Unique ROW_IDs: Rows from the EIM table that don't have a match in base tables get unique ROW_IDs created for them.

7. User Key Validation: Rows with invalid user fundamental values are weeded out. EIM then works on generating foreign critical references for rows that don't have matches in base tables. If foreign key generation fails for required columns, those rows are removed. Rows aiming for multiple destination tables face rejection if their foreign keys aren't established.

8. Updating Existing Base Table Rows: EIM updates existing base table rows with data from corresponding EIM table rows that have passed the earlier stages. If there are differences between the new data and existing records, EIM logs these changes (if transaction logging is enabled). Only the first EIM table row sharing the same user primary key for a base table gets to update it; the rest are disregarded.

9. Inserting New EIM Table Rows: Fresh EIM table rows that successfully pass the previous steps are inserted into the Siebel database tables. This insertion is logged in the Master Transaction Log when transaction logging is enabled. If multiple EIM table rows have the same user primary key for a base table, only the first one is used for the update, while the others are left aside.

10. Handling Primary Child Relationships: EIM manages primary child relationships in the Siebel database tables as needed. It fills in primary child columns marked with the Primary Child Col property set to TRUE.

11. Extra SQL Touch: Lastly, EIM executes optional miscellaneous SQL statements if any are needed for further operations or customizations.

Import Data Process Flow:

1. Data Assessment and Validation: To initiate the import process, you must first identify and validate the data you intend to bring in. This involves figuring out what data must be loaded and assessing whether it exists in another database. Additionally, you should review the quality and completeness of your existing data. For instance, while your current database may lack specific data points like area codes, the Siebel database may require them. Understanding the scope of the data assists in estimating the time and resources needed for the import.

2. Column Mapping and User Keys: Next, you'll identify how the columns in your data correspond to the columns in the Siebel base tables. This step involves mapping data fields to their respective locations in the Siebel structure. You'll also identify user key columns and ensure they contain unique values.

3. System Readiness: Ensure that your hardware and software environments are prepared for the data import. This includes confirming that the Siebel application is correctly installed and collaborating with your Siebel representative and IT personnel to ensure all required hardware and software resources are available.

4. Database Backup: Before embarking on any significant changes, such as installing a new application or importing data, creating a comprehensive backup of your existing database is crucial. This precautionary step simplifies recovery if any issues arise during the import process.

5. File Attachment Handling: If your data import includes file attachments, you must manage them appropriately. You can copy the files to the designated "input" subdirectory under the Siebel Server's root directory or follow the location specified in the ATTACHMENT DIRECTORY.IFB file header parameter. Siebel EIM tables support various file attachment formats, including common types like Word documents (.doc), Excel spreadsheets (.xls), and text files (.txt).

6. EIM Table Loading and Verification: Load and verify the EIM tables to ensure the data is accurately loaded. Compare the number of packed rows against your existing database to confirm that the correct data has been loaded. Additionally, review the contents of several rows to ensure that the tables are prepared for the import process.

7. EIM Configuration File Customization: Customize the EIM configuration file to define the import data and specify the batch number to use. This file tailors the behaviour of EIM according to your import requirements.

8. Testing the Import Process: It's a good practice to run a small-scale test batch (e.g., around 100 records) to validate that the EIM tables load correctly and that the configuration file and srvrmgr command line parameters are set correctly.

9. Running the Import: Execute the import process. Depending on the volume of data, consider using multiple smaller batches (typically containing 1,000 to 5,000 rows) instead of one extensive collection. Smaller batches place fewer demands on resources and simplify troubleshooting if any issues arise.

10. Result Verification: Finally, verify the results of your import process to ensure that the data has been successfully brought into the Siebel database.

Data Import Sequence for Siebel Entities:

To ensure the smooth establishment of relationships between data entities, follow this step-by-step sequence when importing data into Siebel:

1. Administrative Data: Start by importing administrative data. This may include essential organizational Information.

2. Business Units: Next, focus on adequately importing business units to structure your data.

3. Positions: Import position-related data in order to define roles and responsibilities.

4. Accounts: Bring in account information, such as companies or organizations you interact with.

5. Contacts: Import contact data representing individuals associated with these accounts.

6. Employees: If applicable, load data for your employees.

7. Products: Introduce product information into your system.

8. Opportunities: Focus on opportunities which represent potential business deals or sales prospects.

9. Personal Accounts: Import data related to personal accounts, if relevant to your business.

10. Quotes: Handle quotations or proposal data in your import process.

11. Documents: Import any necessary documents or files.

12. Forecasts: If your business involves forecasting, import forecast data.

13. Fulfillment: Bring in fulfilment data, often linked to delivering products or services.

14. Marketing Campaigns: If applicable, import data related to marketing campaigns.

15. CPG Promotion Management: For specific industries, like consumer packaged goods, manage data related to promotions.

16. CPG Product Movement: Track product movement data for relevant sectors.

17. Service Requests: Import data regarding customer service requests or inquiries.

18. Product Defects: Handle product defect data.

19. Activities and Appointments: Import scheduled activities and appointments.

20. Notes: Manage notes and textual Information as needed.

21. File Attachments: If your data includes file attachments, ensure they are imported appropriately.

This sequence is a recommended guideline for most data import processes. However, remember that specific requirements may lead to slight variations in the import order.

Updating the Siebel Database: Once you've completed the initial import of enterprise data, you can use Siebel's EIM to update your database periodically.

By default, when EIM imports Information, it handles both inserts (adding new data) and updates (modifying existing data) based on the content of your data batch. EIM first examines the data set to determine which rows in the collection already exist in the Siebel database:

• Rows in the batch that match existing base rows are used to update the database.

• Rows in the batch that don't match base rows are used for insertion.

In some cases, you might need to update the database with a batch that contains a record for insertion and an update to the same row. A new record is inserted when you use EIM for this purpose, but the update is flagged as a duplicate.

EIM processes each record once for each batch. It prioritizes the form with the MIN(ROW_ID) value and marks the other as duplicates (setting IF_ROW_STAT to DUP_RECORD_IN_EIM_TBL). Suppose you enter the user key of a record with different attributes twice in the EIM table. In that case, only the MIN(ROW_ID) form will be imported or updated, and the duplicate will be ignored.

It's important to note that system fields cannot be updated. These fields are reserved for internal Siebel processes and should not be used for customer data. Some examples of these reserved system fields include CONFLICT_ID, CREATED, CREATED_BY, LAST_UPD, LAST_UPD_BY, MODIFICATION_NUM, ROW_ID, DB_LAST_UPD, and DB_LAST_UPD_SRC.

Preparing EIM Tables for Import Processing in Simple Terms:

When you want to bring data into your system using EIM (Enterprise Integration Manager), there are some critical steps it goes through:

1. EIM starts by looking at the data in its tables.

2. It then puts this data into the right places in your Siebel database.

3. Along the way, EIM follows specific rules and ensures everything is set up correctly.

Here are some key things it does:

 It sets up initial values for some columns in its tables.

 When adding new data, you must fill in specific columns marked as required in EIM's tables.

 If you're updating existing records, you don't need to fill in the required columns but must provide the critical Information.

 EIM also uses filters to decide which rows of data to bring in.

 It creates connections between different pieces of data (foreign keys) and handles some internal stuff.

 It adds or updates the correct rows in your Siebel database.

 Finally, it keeps track of the status of each row in its tables.

Here are some important things you need to make sure of:

 For special columns like ROW_ID, IF_ROW_BATCH_NUM, and IF_ROW_STAT, they must be set up correctly:

 ROW_ID needs to be unique, especially when combined with IF_ROW_BATCH_NUM.

 IF_ROW_BATCH_NUM should be a special number that identifies a group of rows being processed together.

 IF_ROW_STAT should be set to "FOR_IMPORT" to show that the row hasn't been brought in yet.

For file attachments, you need to set up these columns properly:

 FILE_NAME should have the primary name of the attached file.

 FILE_EXT should have a file type like DOC, XLS, or TXT.

 FILE_SRC_TYPE always needs to be set to "FILE."

Editing the Configuration File for Import Processing:

Process Section:

COMMIT OPERATIONS: This setting determines how often EIM will save data to the docking log. The default is 0, meaning it won't keep until the end.

FILTER QUERY: This particular query checks data before importing it. It helps screen out unwanted rows in your import batch based on your specific criteria. This query runs before the import process begins. You can think of it as a pre-import filter.

IGNORE BASE COLUMNS: You can use this to tell EIM which base table columns to ignore during the import. However, you can't overlook required or user key columns. This can speed up the process when you only update a few columns.

IGNORE BASE TABLES: Similar to the previous setting but for entire tables. You can specify which base tables should be ignored during import. Target tables for EIM tables cannot be overlooked. They can also improve performance when you're updating only a few tables.

ONLY BASE COLUMNS: This setting lets you specify and limit which base table columns should be processed during import. You should always include the user key and required columns. It's helpful when updating many rows but only a few columns.

ONLY BASE TABLES: Similar to the previous setting, but for tables. You can specify which base tables should be processed during import. Target tables for EIM tables must be included. This is useful when you're updating only a few tables.

UPDATE ROWS: This optional setting lets you choose whether EIM should update rows in the base table. The default is TRUE, meaning it will update rows by default.

Common Header and Process Section Parameters:

ATTACHMENT DIRECTORY: This is the folder where attachments will be imported from. Ensure this folder exists on the Siebel Server machine and that you have the correct permissions to access it.

COMMIT EACH PASS: This determines if EIM should save data to the database after each pass through an EIM table. The default is TRUE, which means it will be kept after each receipt. It helps reduce resource usage and provides a checkpoint if something goes wrong.

COMMIT EACH TABLE: Similar to the previous setting but for each EIM table. It also reduces resource usage by saving data after each table.

COMMIT OPERATIONS (IMPORT Only): This specifies how many insert and update operations should be performed before a save is made. It helps manage transaction space during large imports.

DEFAULT COLUMN (IMPORT Only): You can set default values for specific EIM table columns. These values will be used if the queue is null in the EIM table.

FIXED COLUMN (IMPORT Only): This lets you specify a fixed value for an EIM table column, which will override any value in the EIM table.

INSERT ROWS: This tells EIM to insert rows from the EIM table into the Siebel base table if they don't exist. The default is TRUE. You can specify this for EIM tables or Siebel base tables.

MISC SQL: This is used to set specific primary keys explicitly or implicitly. It helps define which records should be considered preliminary when multiple options exist.

NET CHANGE (IMPORT Only): Determines how null values in non-user key columns are handled when importing a row that already exists. If NET CHANGE = TRUE, null values will be ignored; otherwise, the column in the base table will be updated with NULL.

ROLLBACK ON ERROR: Specifies whether the transaction should be rolled back if an error occurs during the import process. The default is FALSE, but you can set it to TRUE.

TRIM SPACES (IMPORT Only): Decides whether trailing spaces in character columns in EIM tables should be removed before importing. The default is TRUE.

NET CHANGE: EIM doesn't update non-user key columns with null values by default. NET CHANGE determines how null values in these columns are handled 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.

Impact of NET CHANGE = FALSE on IF_ROW_STAT:

When you set NET CHANGE = FALSE, it affects the behaviour of EIM in three different scenarios:

1. For Null Values: If a column has a null value in the EIM table, EIM will update the corresponding base table column to NULL, marking the IF_ROW_STAT in the EIM table as IMPORTED.

2. For Non-Null Values (Duplicates): When EIM encounters a non-null value that already exists as a duplicate in the base table, it won't change the column. Instead, it will set the IF_ROW_STAT in the EIM table as DUP_RECORD_EXISTS.

3. For Non-Null Values (Not Duplicates): If a non-null value in the EIM table doesn't match any existing values in the base table, EIM will update the base table column with this new value. Simultaneously, it will set IF_ROW_STAT in the EIM table as IMPORTED.

It's important to note that EIM will only update non-user key columns with NULL when you set NET CHANGE to FALSE.

Furthermore, when EIM updates non-user key columns with NULL, those columns previously had non-null values, and the IF_ROW_STAT will be marked as IMPORTED. This is because EIM has executed the update transaction for these columns.

When a column previously had a null value, and EIM updates it with the same records (including the NULL column), EIM treats this as if it ignored the null value and didn't perform an update transaction for that NULL column. As a result, IF_ROW_STAT will be populated with DUP_RECORD_EXISTS in this scenario.

Suppose you want to ensure that certain columns are updated with NULL values in cases like this. In that case, you can specify the ONLY BASE COLUMNS parameter.IFB file.

MISC SQL:

The MISC SQL parameter allows you to specify how EIM should handle primary key mapping. You can find a list of EIM tables and their corresponding values for the MISC SQL parameter in the Bookshelf documentation.

For Explicit Primary mapping, you can use EXPR (EXplicit PRimary). For Implicit Primary mapping, you can use IMPR (IMplicit PRimary). This gives you flexibility in handling primary key mapping.

For example, you can use explicit primaries for records where you've specified a primary key and automatically use implicit primaries for records without a primary key. This offers a versatile approach to managing primary keys, as demonstrated in the following syntax:

MISC SQL = EXPR_S_CONTACT_PR_OU_ADDR_ID, IMPR_S_CONTACT_PR_OU_ADDR_ID

Importing Party Records:

In the process of importing party records, it's essential to understand the PARTY_TYPE_CD column, which can take on various values:

• For individuals, PARTY_TYPE_CD can be Person, which includes Contact, User, Employee, or Partner.

• For organizations, PARTY_TYPE_CD can be an Organization, Division, or Account.

• The value Household represents households.

• The Position subtype denotes positions within an organization, specifically Internal Division Positions.

• AccessGroup refers to a bundling of party entities.

• UserList represents Siebel Persons as its members.

Now, let's delve into the details of PARTY_UID:

PARTY_UID: PARTY_UID is automatically populated through the Siebel upgrade process and the application UI. Initially, it takes the value of the ROW_ID of the created party record. However, it's worth noting that this value doesn't have to stay the same as the ROW_ID. With EIM, you can specify the value that should go into PARTY_UID in the EIM table for this column. PARTY_UID may even have a calculated value based on certain logic, such as a combination of email and other data. That's why PARTY_UID is defined as VARCHAR100, providing ample space for various value calculations.

Now, let's discuss ROOT_PARTY_FLG and its role in optimizing performance for Oracle:

ROOT_PARTY_FLG: ROOT_PARTY_FLG plays a crucial role in enhancing performance for Oracle databases. It's beneficial when retrieving top-level Positions, Organizations, or Access Groups. In these cases, you can use queries like:

• WHERE ROOT_PARTY_FLG = 'Y' to retrieve top-level Positions, Organizations, and Access Groups. This flag is set to 'Y' only for these specific party subtypes, as it pertains solely to them. For other party subtypes, it's set to 'N'.

• Additionally, you can use WHERE PAR_PARTY_ID IS NULL to retrieve relevant data. Oracle encounters challenges when using an indexed access path for NULL values since there are no index entries for NULL. To address this issue, ROOT_PARTY_FLG was introduced to facilitate more efficient queries and data retrieval.

IF_ROW_STAT Values After Import:

After performing an import with EIM, you'll come across various IF_ROW_STAT values that provide insights into the import process and the status of each row. Here's what each of these values means:

AMBIGUOUS: This status indicates that two rows in the base table share the same user key but have different conflict IDs. EIM cannot distinguish between these rows due to the conflicting Information.

DUP_RECORD_EXISTS: When a row in the EIM table matches rows already existing in the destination tables, it's marked as DUP_RECORD_EXISTS. It's important to note that a row may have duplicates in the target base table but not in other destination base tables. In such cases, EIM adds the new relationship (like a child or intersection table) in the different destination base tables and does not mark the EIM table row as duplicates.

DUP_RECORD_IN_EIM_TBL: This status is assigned to a row that was eliminated because it's a duplicate, having the same user key as another row in the EIM table with the same batch number. The record with the minimum ROW_ID is processed, and the other documents with the same user key are marked as DUP_RECORD_IN_EIM_TBL. DUP_RECORD_EXISTS status indicates that the same record exists in the base table, whereas DUP_RECORD_IN_EIM_TBL status means multiple EIM table records with the same user fundamental values.

FOREIGN_KEY: This status is given when a required foreign key column in the target table cannot be resolved during import. It signifies an issue with linking the data to related tables.

IMPORTED: A row marked as IMPORTED has been successfully processed against all its destination base tables. This status is set after the import process is completed.

IMPORT_REJECTED: If a user-specified filter query fails for a particular row, it's assigned the IMPORT_REJECTED status. This means that the row did not meet the criteria set by the filter query.

IN_PROGRESS: EIM initially sets IF_ROW_STAT to this value for all rows in the batch. Suppose any rows still have this status value after EIM finishes processing. In that case, it indicates a failure that aborted the processing for that table.

NON_UNIQUE_UKEYS: This status is used when the user key specified for the table is not unique according to the user key specifications. It suggests that there might be issues with the uniqueness of the data.

PARTIALLY_IMPORTED: A row with this status didn't fail for the target table (although it may have been a duplicate) but did fail during the processing of a secondary base table. It implies that the import process encountered issues related to related tables.

PICKLIST_VALUE: When a required picklist value in the target table can't be resolved, this status is assigned. It typically occurs for NULL or invalid bounded picklist values.

REQUIRED_COLS: If one or more required columns for the target table were found to be NULL during import, the row is marked with this status. This can happen when user key columns are missing or inserting new rows.

ROLLBACK: EIM assigns this status when it encounters an error, such as an SQL database failure, and rolls back the transaction. It's used when the ROLLBACK ON ERROR setting is set to TRUE.

SQL_ERROR: If an SQL error occurs while attempting to import a row, it's assigned the SQL_ERROR status. This often happens for rows processed when Enable Transaction Logging is set to TRUE, indicating an issue with database operations.