EIM Interview Questions

  1. What is Siebel EIM?
    • Siebel Enterprise Integration Manager (EIM) is a server component in the Siebel EAI component group that transfers data between the Siebel database and other corporate data sources.
  2. Why does Oracle does not recommend using native SQL to perform different operations on base tables?
    • A very common answer which people give to this "simple" question is that "Siebel doesn't allow inserts and updates, deletes etc directly into siebel database." or some may say this is what Siebel has recommended and so on..
    • Though direct inserts and updates in the siebel tables is highly not recommended, we can still do that if we are able to identify the direct tables and related tables in which data is to be inserted or updated.
    • The primary reason why we shouldn't be doing operation on siebel tables directly is for following reasons:
      • Table relationships in Siebel are highly complex. One table may be related to too many other tables, the foreign keys and the primaries are difficult to resolve if we perform the operations manually like through simple SQL statements.
      • Many organizations use mobile clients for siebel application. These mobile clients have to be synchronized after each data loads so that the data is up-to-date for mobile clients as well. Improper synchronization will cause data integrity issue between siebel server database and mobile user local database.
      • While updating data directly in siebel tables, system columns such as LAST_UPD, LAST_UPD_BY etc are not updated. This may mislead the actual record information and integrity will be lost. These updated data may not be synchronized properly in other parallel running servers as well like report DB server, Siebel Analytics etc.
    • Hence, Siebel has recommended that the in-built EIM technique be used for any type of data operations in the siebel tables.
  3. Why does Oracle does not recommend using native SQL to perform different operations on base tables?
    • The recommendation for not using native SQL statements to perform operation is because of following reasons:
      • Table relationships in Siebel are highly complex. One table may be related to too many other tables, the foreign keys and the primaries are difficult to resolve if we perform the operations manually like through simple SQL statements.
      • Many organizations use mobile clients for siebel application. These mobile clients have to be synchronized after each data loads so that the data is up-to-date for mobile clients as well. Improper synchronization will cause data integrity issue between siebel server database and mobile user local database.
      • While updating data directly in siebel tables, system columns such as LAST_UPD, LAST_UPD_BY etc are not updated. This may mislead the actual record information and integrity will be lost. These updated data may not be synchronized properly in other parallel running servers as well like report DB server, Siebel Analytics etc.
  4. What are the different EIM functions?
    • The different EIM functions are:
      • Import (Inserts and Updates)
      • Export
      • Delete
      • Merge
  5. What is IFB file?
    • IFB file is a configuration file that the EIM engine reads to identify details like what kind od process need to be performed, the batch numbers to identify the records and other parameters. Its an ASCII text file with .ifb extension. Before one runs the EIM job, this IFB file's parameters needs to be configured according to ones requirements.
  6. Where does the IFB file reside on server?
    • The IFB file resides in the siebsrv/ADMIN folder on the server. E.g. D:\sba81\siebsrv\ADMIN\Default.ifb
  7. What are the mandatory EIM columns that needs to be populated in different EIM processes?
      • The mandatory columns that need to be populated in an EIM table are:
          • ROW_ID: Part of EIM table user key
            • IF_ROW_STAT: Accepts any alphanumeric value but changes after EIM processing to show the status of the record.
              • IF_ROW_BATCH_NUM: Part of EIM table user key, specifies the batch number to identify the records for EIM processing.
                • IF_ROW_MERGE_ID: Required for Merge operation only, specifies the EIM ROW_ID of the record that will be merged into.
            1. Why does EIM uses temporary columns? Give an example.
              • EIM uses temporary columns to manipulate data during processing. For example, EIM might store the ROW_ID value for a Siebel base table in a temporary column. These column names begin with T_ and indicate the table or column for which they are used. Because EIM uses these columns internally during processing, these columns in EIM tables should not be manipulated.
            2. What is difference between CON_BU and CON_BI columns of EIM_CONTACT table?
              • EIM_CONTACT.CON_BU and EIM_CONTACT.CON_BI columns are column pairs to map organizations. These type of columns are present in other entities as well like ACC_BU and ACC_BI (for accounts), PROD_BU and PROD_BI (for products) etc.
              • CON_BU and CON_BI columns maps to the BU_ID column of S_CONTACT. In EIM Interface Table object (in tools) EIM_CONTACT has a foreign key mapping where you can see the CON_BI column mapped.
              • CON_BU column needs to be populated explicitly with the organization name whereas CON_BI column value is implicitly (which is the ROW_ID of S_BU table) picked by EIM based on the organization name in CON_BU. Never populate the CON_BI column by yourself, EIM handles it by itself.
            3. How can you identify which siebel base tables can be populated by an EIM table in Siebel tools?
              • Follow the below steps:
                • Open Siebel tools
                • Go to Types tab and select EIM Interface Table from the object explorer.
                • Query for EIM table in Name property
                • Expand the object and navigate to EIM Table Mapping
                • This will list all the base tables that can be populated by the EIM table.
              • Additionally there is a target table for each EIM table which you can see under EIM Interface Table object --> Target Table property.
            4. How can you identify which EIM tables can populate a Siebel base table in Siebel tools?
              • Follow the below steps:
                • Open Siebel tools
                • Go to Flat tab and select EIM Table Mapping from the object explorer.
                • Query for the base table in Name property
                • Navigate to the Parent Interface table name property to see all the EIM tables that can be used to populate the base table.
            5. What do you mean by non-target base tables?
              • Every EIM table has one or more base table mappings which can be viewed in siebel tools. Out of those base tables one would be the target base table which acts as primary base table for that EIM table. Rest of the base tables are called as non-target base tables. One can view the target base tables under EIM Interface Table object --> Target Table property.
            6. How can you manually create EIM column for an existing base table column?
              • You cannot manually create a EIM column for an existing base table column. The solution is to run the EIM mapping wizard which will map all the new extended columns and the old columns which have no EIM column mappings currently.
            7. How do you run EIM table mapping wizard in Siebel tools?
              • Lets take an example: Suppose a new column is extended in S_CONTACT and you need the corresponding EIM column to be generated.
              • Follow the below steps for running EIM mapping wizard:
                • Open siebel tools
                • Go to Table object and query for "S_CONTACT or EIM_CONTACT".
                • Lock the projects of both the tables. (By Menu Bar --> Tools --> Lock Project)
                • Right click on S_CONTACT record and select EIM Table Mapping. EIM mapping wizard will open.
                • Select the EIM table from list of EIM tables. (In our case its EIM_CONTACT)
                • Click next and then Finish.
                • After successful completion of the wizard, Click on Apply/DLL button for EIM_CONTACT record and furnish the details and click OK.
                • Finally activate the EIM_CONTACT table by clicking the Activate Button.
            8. What are (Primaries) Primary Foreign Keys? What is the use of primaries (Primary Foreign Keys) in Siebel data model?
              • The Siebel Data Model uses primary foreign keys (or primaries) to point from a parent base table to a child base table. Primaries enable business logic in the Siebel Data Model, such as identifying the primary position for an account. Moreover, primaries improve performance by eliminating repeating sub queries when data from both the parent table and the primary child table are displayed. If you do not use primaries, then you must execute a new query to identify any child records each time a parent record is displayed.
            9. Explicit primary mapping does not occur in each EIM table mapping. In which case can you find the explicit primary mapping defined in the EIM table mapping?
              • If both the parent table and the primary child table of a primary foreign key are mapped to the same EIM table, then once will see an explicit primary mapping for this primary foreign key under the table mapping of the primary child table.
            10. How will you identify the corresponding EIM column for a particular base table column? Say for S_PROD_INT.NAME, explain.
              • Follow the below steps:
                • Identify the corresponding EIM table for the base table S_PROD_INT from siebel tools (say its EIM_PROD_INT), see question no.11
                • Query for the EIM table EIM_PROD_INT in siebel tools in EIM Interface Table object
                • Expand the EIM Interface Table object and under EIM Table Mapping child object query the base table S_PROD_INT
                • Expand the EIM Table Mapping object and navigate to Attribute Mapping child object
                • Query the base table column name NAME in Base Table Attribute Column property and you can see the corresponding column name in the Interface Table data column property
            11. What is the difference between IF_ROW_STAT and IF_ROW_STAT_NUM columns?
              • IF_ROW_STAT column in the EIM table contains the status of each records after processing like IMPORTED, DELETED, NOT_ALLOWED, MERGED etc. whereas IF_ROW_STAT_NUM column contains a zero (0) if the row was successfully processed to completion or the pass number where the failure occurred.
            12. Name few header parameters in EIM configuration file generic for all EIM processes.
              • CONNECT, LOG TRANSACTIONS TO FILE, PROCESS, [Siebel Interface Manager], USERNAME, PASSWORD etc.
            13. Which parameter should be the first non-commented line in the IFB file?
              • [Siebel Interface Manager]
            14. In the IFB file, you have mentioned the credentials for database incorrectly but the proper credentials are stored at srvmgr level, will the job throw any error?
              • Credentials mentioned in IFB file are overridden by the credentials stored at srvmgr level, so in this case it would not throw error.
            15. Name few process parameters in EIM configuration file generic for all EIM processes.
              • BATCH, COMMIT EACH PASS, COMMIT EACH TABLE, INCLUDE, ONLY BASE TABLES, TYPE, TABLE etc.
            16. What does INCLUDE parameter mean in an IFB file?
              • INCLUDE is an optional parameter in the IFB file which indicates a subprocess to execute. INCLUDE names a process to be included as part of this process. More than one process may be included in another process. All included processes execute before the process itself. This parameter can be used only in shell processes. A shell process uses the INCLUDE statement to invoke a sequence of processes in a single run.
            17. In which EIM process ROLLBACK ON ERROR = TRUE parameter in the IFB file is highly recommended?
              • For Delete and Merge processes
            18. What is the purpose of SESSION SQL parameter in the ifb file?
              • This parameter specifies a user-defined SQL statement to be sent to the database server before other SQL statements for this process. This string is sent directly to the database and must be a single SQL statement suitable for immediate processing. This parameter can be used to set tracing for performance analysis. Only one SESSION SQL parameter can be used in each process section. This parameter cannot be used to insert or update data in Siebel base tables. EIM sends the SQL statement directly to the database and may cause data loss for Siebel Remote and Siebel Replication Manager.
            19. Why USING SYNONYMS = FALSE parameter is highly recommended for EIM performance tuning?
              • USING SYNONYMS = FALSE saves processing time because queries that look up synonyms (as in case of Accounts) are not used.
            20. What is the difference between DEFAULT COLUMN and FIXED COLUMN parameters?
              • DEFAULT COLUMN specifies a default value for an EIM column and it is used only when the column is null in the EIM table whereas FIXED COLUMN specifies a fixed value for an EIM column and the fixed value gets loaded in the base table overriding the value in the EIM table column.
            21. What are the required process parameters in the ifb file?
              • BATCH, TABLE and TYPE
            22. Can you enumerate the 11 steps for EIM import process?
              1. EIM initializes any temporary columns
              2. EIM applies any DEFAULT_COLUMN and FIXED_COLUMN values defined for the import process
              3. EIM applies any filter queries defined for the import process
              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
              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
              11. Finally, EIM runs optional miscellaneous SQL statements
            23. What will happen after processing if there were two duplicate records in the EIM table?
              • 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 i.e. 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.







            More to come...
            (Total more than 100 questions...)

            By Mohammed Arif