SIEBEL EIM
Enterprise Integration Manager.
By Sukesh Tokshiya
Objectives • After completing this module you will Know following things: – SIEBEL 7 Data Model. – Different EIM Processes and their Use. – Input to Base table Column Mappings. – IFB creation and Optimizing the EIM process. – Use of UNIX shell Script NT Batch Files,SQL Loader and PL\SQL procedures while designing EIM solutions – Automation and Scheduling the execution of EIM job. • Why you need to know – Enables you to design EIM solutions for Import,Export,Update, Delete and Merge data into/from the SIEBEL database
By Sukesh Tokshiya
SIEBEL DATA MODEL • •
Over 2000 tables in the database Three major types: Data, Interface, and Repository
Data Tables • • • • • •
• •
Store the user data Business data Administrative data Seed data Transaction data for mobile users Are populated and updated: - By the users through the SIEBEL eBusiness applications - By server processes such as Enterprise Integration Manager for bulk importing and exporting of data Have names prefixed with S_ Are documented in the SIEBEL Data Model Reference
By Sukesh Tokshiya
SIEBEL DATA MODEL
rominent Data Tables •
Prominent tables storing data for the major business entities Internal Product
S_PROD_INT
Service Request
S_SRV_REQ
RESOLUTION_CD
OWNER_EMP_ID
DESC_TEXT
SR_NUM
ROW_ID
UOM_CD
PART_NUM
NAME
ROW_ID
Contact
Opportunity
S_OPTY
S_CONTACT
STG_NAME
PROG_NAME
NAME
BDGT_AMT
ROW_ID
MID_NAME
FST_NAME
LAST_NAME
ROW_ID
By Sukesh Tokshiya
SIEBEL DATA MODEL Interface Tables • • • • •
Are a staging area for importing and exporting data Are used only by the Enterprise Integration Manager server component Are named with prefix EIM_ Are documented in the Interface Tables Reference One EIM Table Can target Many Base Tables.
Repository Tables •
Contain the object definitions that specify one or more Siebel applications – Client application configuration – UI, business, and object definitions – Mappings used for importing and exporting data – Rules for transferring data to mobile clients
Column • •
Each table has multiple columns to store user and system data - Defined by the Column child object definitions Columns determine the data that can be stored in that table
By Sukesh Tokshiya
SIEBEL DATA MODEL User Key • • • •
Specifies columns that must contain a unique set of values Prevents users from entering duplicate records Is used to determine the uniqueness of records during data import operations Cannot be edited ( Exception in specific cases)
INDEX
•
• •
Is a separate data structure that stores a data value for a column and a pointer to the corresponding row - Are used to retrieve and sort data rapidly Can be created by configurators (to produce a custom index) Should be inspected to assess performance issues for query and sort operations
By Sukesh Tokshiya
SIEBEL DATA MODEL 1:M Relationships •
Are captured using foreign key table columns in the table on the many side of the relationship
M:M Relationships •
Are captured using foreign key table columns in a third table called the intersection table
1:1 Extension Table • •
Is a special table that has a 1:1 relationship with a base table Foreign key for the relationship:Is located in the extension table Is named PAR_ROW_ID. Provides additional columns for business components referencing the base table – A base and extension table can be considered as a single logical table Are used: – To provide flexibility for both SIEBEL engineering and customer use • Known as standard 1:1 extension tables – To support multiple business components referencing the S_PARTY table
By Sukesh Tokshiya
SIEBEL DATA MODEL Party Entity •
•
Refers to instances of entities that have access to records – Person-related entities – Organization-related entities – Groupings created for access to master data Allows for grouping of instances of different types of entities
Contact
Account
Employee
Position
User Partner
Person-related entities
Access Group User List
Grouping for access control
Division Organization
Organizationrelated entities By Sukesh Tokshiya
Enterprise Integration Manager (EIM Enterprise Integration Manager (EIM) •
Server task that manages exchange of data between external data in interface tables and user data in base tables
•
EIM reads a configuration file to determine: - Whether data should be imported, merged, deleted,or exported. - Which interface table(s) and base table(s) are used and affected.
• User key column mappings in interface tables are used to resolve ROW_IDs for base tables, maintaining referential integrity
•You cannot use SQL to populate user data in base tables
By Sukesh Tokshiya
Enterprise Integration Manager (EIM
terprise Integration Manager (EIM) Process Import Export Merge Delete By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM Import Process • Import Process is basically used to Insert or Update the data from external system. • Identify and validate the data to be imported. • Identify the column mappings and user key columns of the data to be imported.
Recommended Import Order 1 Administrative NOTE: An example of administrative data would be a List of Values for Currency or Zip Code. 2 Business Unit 3 Positions 4 Accounts 5 Contacts 6 Employees 7 Products 8 Opportunities 9 Personal Accounts 10 Quotes 11 Documents 12 Forecasts By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM Import Process Continued………. Recommended Import Order Continued…. 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
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. By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM Import Process Continued………. •Updating record Using EIM process. •EIM table should be populated with proper User keys to Update Base table records. •EIM table column is having value in it which is different from base table then and then only the base table value will be Updated. •If EIM table column is having NULL value it will not affect the Base Table column. •User Key Values in Base table Column cannot be Updated ( Exception with some base table as new EIM table Introduced Specifically for updating User Keys). •Insert and Update of Base table can be achieved by Specifying the proper Parameters in configuration file (IFB).
By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM Export Process • Export Process is basically meant for exporting the data from base table into and populating the EIM tables. • Exported data can be copied into Other data bases • Preserved Column Values - The values for the LAST_UPD and CREATED columns in the interface tables will always contain the values for the LAST_UPD and CREATED columns from the target base table. You can verify export results by checking IF_ROW_BATCH_NUM equals to the batch number for the export. The value of IF_ROW_STAT should be EXPORTED.
By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM Delete Process •To delete any record from SIEBEL we will delete the record by using SIEBEL DELETE process. •EIM initializes the interface tables for deletion. •It applies filter logic to select rows for deleting, insert interface tables rows that correspond to matching base table rows, or select rows with matching user keys in the interface tables. •EIM updates other tables with rows containing foreign keys that point to newly deleted rows. •Deletion process will delete/Update the corresponding record/s from tables where the row_id of record is referred as foreign Key
By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM Delete Process Continued………. Deletion Methods Supported EIM uses a combination of interface table row contents and configuration file parameter values to determine the method for selecting rows to be deleted. The following methods are supported: Delete rows in a SIEBEL base table with user key values specified in the corresponding interface table. Delete rows in the base table where the contents of a named column match those specified by a WHERE clause expression in the configuration file. Delete all rows in the base table regardless of interface table row contents or configuration file WHERE clause expressions. Caution: Do not use EIM to delete organizations. Using EIM to delete data from the Products base tables is also not recommended and can lead to inadvertent data integrity loss.
By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM Merge Process •Merge process is basically meant for Merging two records. •During Merge Process Identity the Parent record and Child records. •All the references (row_id) of Child records will be replaced by parent record and Child records will be deleted. While Populating the EIM ,IF_ROW_MERGE_ID is used to distinguish between parent and child records by EIM process. •For Parent Record IF_ROW_MERGE_ID is kept NULL and for Child record the row_id (EIM tables) of parent is populated. •Can be used only two merge data from primary base table and not the secondary tables.
By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM
New Features in SIEBEL 7.5 •S_PARTY Table. S_PARTY table has been introduced into SIEBEL Data Model in SIEBEL 7. The S_PARTY table is the target base table, while S_ORG_EXT. S_CONTACT, S_USER, and S_POSTN now become extension tables of the S_PARTY table. The S_EMPLOYEE table is obsolete in version SIEBEL 7. These schema changes have a direct effect on EIM behavior. •MISC SQL Parameter. SIEBEL 7 introduces a new parameter, MISC SQL. This is used to set certain Primary Child Foreign Keys, such as S_CONTACT.PR_OU_ADDR_ID and S_POSTN.PR_EMP_ID. When using MISC SQL in SIEBEL 7 to set Primary Child Foreign Keys, EIM does NOT log any transactions for mobile users. This parameter should only be used for initial data loading. For more information, see “Header and Process Parameters”. •utleimdiff.exe Utility. The Siebel data model changes from release to release. Schema changes have an effect on EIM behavior. Use the utleimdiff utility to find differences in all interface tables between two repositories. It also generates a selective report of the EIM tables. The results can be used to help you prepare interface tables for EIM data loading.
By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM
ew Features Continued….. • Previously you could not delete from the S_NOTE* and S_*_SKILL_IT tables because they did not have a primary user key. Now you can delete records from S_NOTE* and S_*_SKILL_IT tables without deleting records from the parent tables using EIM_NOTE_DEL and EIM_SKLI_DEL, respectively. •Delete and Merge performance is improved if you create some specific temporary indexes first. •Oracle INSERT APPEND MODE. This new parameter helps avoid deadlocks when running parallel EIM processes.
•ATTACHMENT DIRECTORY. Specifies the directory to be used for importing attachments. •CASCADE DELETE ONLY. This new parameter determines how child records are handled when the parent record is deleted. •EIM SCHEMA CACHE. This caches the column relations.
By Sukesh Tokshiya
1
Enterprise Integration Manager (EIM
New Features Continued….. •EIM Table Mapping Wizard. SIEBEL Tools includes an EIM Table Mapping wizard to assist in adding extensions to the Data Model: • Add new customer columns to existing SIEBEL tables. • Add new extension tables. • Add new intersection tables. •EIM_PROD_INT_UK. EIM_PROD_INT_UK in SIEBEL 7 can be used to update user key columns in S_PROD_INT, such as NAME and VENDR_OU_ID. INTEGRATION_ID is an alternative user key in S_PROD_INT. The EIM engine uses this new user key to update traditional user key columns. •LOG TRANSACTIONS TO FILE. SIEBEL 7 introduces a new parameter, LOG TRANSACTIONS TO FILE. EIM now logs transactions into DX files stored in the File_System\EIM directory. A marker transaction is created in the S_DOCK_TXN_LOG table. For more information, see “Header Parameters” •DELETE MATCHES and EXPORT MATCHES Behavior Changed. The behavior of these parameters has changed as part of the new S_PARTY model. These parameters can now affect extension tables. These parameters also have a new argument.
By Sukesh Tokshiya
2
EIM Column Mappings •Column Mapping is very Important process for designing EIM Solution. •Determine the Entity which it is going to affect. •Identify the INPUT column , corresponding EIM and Base Table and column it is going to affect. •Identify the Intersection table ( if needed) for incoming data to be loaded. •Identify the User key Values from INPUT file required to populate the EIM tables. •Determine the column required for visibility of records and populate the values in it. •Prepare a EXCEL document once you have identified the Mappings.
By Sukesh Tokshiya
2
EIM Configuration File EIM reads a configuration file that Specifies the EIM process to perform. There are two sections in IFB file Header and Process Section. IFB general Parameters Header Section. CONNECT LOG TRANSACTIONS TO FILE PASSWORD PROCESS [Siebel Interface Manager] TABLEOWNER USERNAME
ODBC System Data Source Log transactions to file or table; TRUE/FALSE toggle;default is TRUE Database password Initial/main process section to run Header section must use this reserved name Database tableowner, defined during installation Database/Employee logon
By Sukesh Tokshiya
2
EIM Configuration File Process Section. General Process Parameters for the EIM Configuration File Command Description TYPE BATCH TABLE COMMIT EACH PASS COMMIT EACH TABLE IGNORE BASE COLUMNS IGNORE BASE TABLES INCLUDE LOG TRANSACTIONS ONLY BASE TABLES
IMPORT, EXPORT, DELETE, MERGE, SHELL IF_ROW_BATCH_NUM Value to run against Name of EIM table. Commit after each EIM pass; default is TRUE Commit after each base table; default is TRUE Do not process these columns Do not process these tables Subprocess to execute Default value depends on system preference Process only these tables By Sukesh Tokshiya
2
EIM Configuration File TRANSACTION SQL
Post-commit SQL statement
USE SYNONYMS
TRUE/FALSE toggle; default is TRUE Error rollback behavior; default is FALSE
ROLLBACK ON ERROR [Siebel Interface Manager] USER NAME = "SADMIN" PASSWORD = "NOKIA321" PROCESS = Import Campaign Data [Import Campaign Data] TYPE = SHELL INCLUDE = "Import Campaign LE" [Import Campaign LE] TYPE = IMPORT BATCH = 1001-1200 TABLE = EIM_ACCNT_SRC COMMIT OPERATIONS = 2000 USING SYNONYMS = FALSE ONLY BASE TABLES = S_ACCNT_SRC DEFAULT COLUMN = AS_ACTIVE_FLG, "N" DEFAULT COLUMN = X_CMP_LOGIN_ID, "Unlocked"
By Sukesh Tokshiya
2
EIM Configuration File Import Process Specific Parameters. COMMIT OPERATIONS DEFAULT COLUMN FILTER QUERY SQL FIXED COLUMN INSERT ROWS MISC SQL NET CHANGE ONLY BASE COLUMNS PRIMARY KEYS ONLY SET BASED LOGGING TRIM SPACES UPDATE ROWS
Docking Log row commit frequency; default is 0 Default for IF column; DEFAULT COLUMN=ORG_CD, “Federal” pre-process filter query fragment; FILTER QUERY=(ACCNT_NUM“1500”) Set IF column to this literal; FIXED COLUMN=ORG_CD,“Commercial” Optional base table, TRUE/FALSE toggle; default is TRUE. Set primaries in import process Do not update with NULL values; default is TRUE Process only these columns Refresh only primary foreign keys for base tables; does not work In set-based mode; default is FALSE Specifies whether set based logging is enabled; default is TRUE Toggles space trimming; default is TRUE Optional base table, TRUE/FALSE toggle; default is TRUE. By Sukesh Tokshiya
2
EIM Configuration File Export Process Specific Parameters. Command
Description
EXPORT ALL ROWS
Export all rows in tables; default is FALSE
EXPORT MATCHES
WHERE Clause fragment; EXPORT MATCHES=(NAME LIKE “GEN%”)
CLEAR INTERFACE TABLE.
Specifies whether existing rows in the interface table for the given batch number should be deleted. Valid values are TRUE (the default) and FALSE.
[Siebel Interface Manager] USER NAME = "SADMIN" PASSWORD = "SADMIN" PROCESS = Export Employees [Export Employees] TYPE = EXPORT BATCH = 12345 TABLE = EIM_EMPLOYEE ONLY BASE TABLES = S_EMPLOYEE EXPORT ALL ROWS = TRUE
By Sukesh Tokshiya
2
EIM Configuration File Delete Process Specific Parameters. Command
Description
DELETE ALL ROWS
Used for deleting all rows in table; use with caution; default is FALSE
DELETE EXACT
Delete using user key matching algorithm with rows in IF table; default is FALSE
DELETE MATCHES
SQL WHERE fragment deletion criteria; DELETE MATCHES = EIM_ACCOUNT, (NAME LIKE “TST_ACCT%”)
DELETE ROWS
Prevents deletion of rows; DELETE ROWS=S_ADDR_ORG, FALSE
UPDATE ROWS
Prevents updating of foreign key references; UPDATE ROWS=S_OPTY,
FALSE.
By Sukesh Tokshiya
2
EIM Configuration File SAMPLE IFB file for DELETE Process. [Siebel Interface Manager] USER NAME = "SADMIN" PASSWORD = "NOKIA123" PROCESS = DeleteContact [DeleteContact] TYPE = SHELL INCLUDE = "DeleteContact1061" INCLUDE = "DeleteContact1062" [DeleteContact1061] TYPE = DELETE BATCH = 1061 TABLE = EIM_CONTACT ONLY BASE TABLES = S_CONTACT DELETE EXACT = TRUE [DeleteContact1062] TYPE = DELETE BATCH = 1062 TABLE = EIM_CONTACT ONLY BASE TABLES = S_CONTACT DELETE EXACT = TRUE
By Sukesh Tokshiya
2
Optimizing EIM Process • EIM process can be optimized to get Maximum performance. •Populate 2000 records for one batch records. •Set specific base table name in IFB file if you want process that base table only.If possible set specific columns also. •Use batch range for each process. •Start parallel EIM jobs ( you can start 5 instances of EIM on one SIEBEL server). •Set USING SYNONYMS Parameter in IFB file to FALSE for faster processing
By Sukesh Tokshiya
2
Use of UNIX shell Script NT Batch Files,SQL Loader and PL\SQL procedures while designing EIM solutions. Unix shell script are used to Initialize the environment variables and such as ( assuming database is on UNIX server) LOGON_ID PL_HOME PL_SQL PL_TEXT PL_UTL_FILE_DIR HOSTNAME USERNAME PASSWORD REMOTE_DIR files are
: Oracle username and password where the Products are to be loaded. : Home for the products load environment : Location where the PL/SQL scripts reside : Location where the text files are stored : Path where the .IFB and .TXT files are written by the PL/SQL scripts : Machine IP where the SIEBEL Gateway server is installed : User name for accessing the share on the SIEBEL Server : Password for accessing the share on the SIEBEL Server : Location on the SIEBEL Server where the .IFB and .TXT ftp'ed
ORACLE_HOME PATH HOME
: Path where oracle is installed. : Path up to bin directory of installations. : Path where ORACLE_HOME is there.
Unix shell script are used to start the SQL LOADER to get the input file. Unix shell script are used to run the PL\SQL script to load the data up to EIM table.
By Sukesh Tokshiya
3
Use of UNIX shell Script NT Batch Files,SQL Loader and PL\SQL procedures while designing EIM solutions.
.PL\SQL procedure is written to validate
the data, to populate the data in EIM table.
PL\SQL procedure can be used to generate the proper batch ranges and IFB file. PL\SQL procedure is written to validate the data, to populate the data in EIM table. PL\SQL procedure can be used to generate the proper batch ranges and IFB file. Sequences can be used to populate various unique column in EIM table. PL\SQL procedure can be used to update audit log tables.
By Sukesh Tokshiya
3
Use of UNIX shell Script NT Batch Files,SQL Loader and PL\SQL procedures while designing EIM solutions. Unix shell script are used the generate the LOG files. Unix shell script are used do processing on input ,processed files. Unix shell script will start a process in background which will always keep polling for Input file and whenever it come it will start the Data Load process by invoking one more shell script. NT batch file can be used to start the EIM process by executing srvrmgr.exe from DOS prompt with suitable parameters. NT batch file can ftp UNIX server and get the files to be processed on SIEBEL servers. ( Note :- SIEBEL server are on NT box , if they are on UNIX server than things will change) NT batch file can start the EIM process which will start SQL LOADER, to load the data in temp table.After this it will invoke PL\SQL procedure to populate the EIM table.Logging can also be done by NT batch file. SQL Loader can be invoked with proper parameter and CTL file name. SQL Loader is responsible for loading the data in TEMP table from input file.
By Sukesh Tokshiya
3
Automation and Scheduling the EIM Jo The entire execution of EIM DATA load can be achieved in automated way without user intervention and also it can scheduled. Automation can achieved in following manner. 1) The Shell script/NT batch file will be scheduled in CRONTAB/NT Scheduler 2) Shell script/NT batch file will start and invoke SQL LOADER with CTL file name specified.The Input file will be at fixed location and with fixed name. 3) After data is loaded in temp table , It will invoke PL\SQL script. 4) PL\SQL script will populate the EIM table and generate the IFB file. 5) REMSH command (specific to UNIX ,should be installed on SIEBEL NT box separately to connect to that NT server) will invoke NT batch file on target SIEBEL Server. 6) NT batch file in turn will call one more batch file which will download the IFB file created by PL\SQL procedure. 7) After IFB file is downloaded it will start the EIM job on the SIBEL server using the parameter set in the batch file and also move the IFB files on different locations
By Sukesh Tokshiya
3
Thank you By Sukesh Tokshiya
3