Siebel Life Sciences Guide > Importing Data into Life Sciences >

Importing, Extracting, and Routing Syndicated Data

The process described here for importing syndicated data takes advantage of the fact that syndicated data is read-only. Because the row ID on the S_SYND_DATA table is never referred to anywhere else in the Siebel data model, it can be populated with dummy values that differ from usual row IDs. The application administrator uses SQL*Loader to populate the ID field with a unique sequential value, a process which allows either full or partial table-level extraction. Using views in the Syndicated Data Administration screen, you can define complex routing rules for syndicated data.

NOTE:  Routing rules are used by Siebel Remote and Replication Manager to determine what data and transactions are routed to mobile client databases and regional databases. For more information on routing rules, see Siebel Remote and Replication Manager Administration Guide and Interface Tables Reference for Siebel Industry Applications.

The views that require syndicated data import are shown in Table 25.

The general steps for loading syndicated data and distributing it to mobile users are:

1. Load the data into the base tables of the Siebel Life Sciences database.

   If you choose to use EIM to load the data, turn off transaction logging.

2. Extract the data required by mobile users.
3. Transfer the extracted data files to mobile users.

Loading Data into the Siebel Life Sciences Database

Before you can view syndicated data in Siebel Applications, you must first populate the foreign keys fields in the S_SYND_DATA table. These keys are populated from the sales representative (POSITION_ID), territory (TERR_ID), product group (MARKET_ID), product (PROD_ID), contact (CON_ID), area (AREA_ID), and period (PERIOD_ID) fields. Each of these is a key to a record in another Siebel application table. Therefore, these fields need to be populated with valid row IDs for the corresponding position, territory, product group, product, and period business components.

To load syndicated data, the application must have the row IDs for the foreign key data that is stored in the S_SYND_DATA table. The data files used to load syndicated data are supplied in a format in which any key information is supplied as a textual description. This means that unless EIM is used to load the data, the textual descriptions need to be converted into their Siebel application table row ID equivalents.

Different data types need to be supplied to make a record visible in S_SYND_DATA. The only compulsory foreign key field is the PERIOD_ID. However, there is a unique index on the table that comprises all of the foreign key fields: S_SYND_DATA_U1. Table 23 lists all of the foreign keys fields, their foreign key table, and a description of what the field contains for Siebel Life Sciences.

Use a native data loader (recommended method) such as SQL*Loader in Oracle or BCP in SQL Server. For the purposes of example, Oracle is used as the target database, although the technique applies equally to SQL Server. Alternatively, you can use Visual Basic and Com.

Using SQL*Loader to Load the Data

Data records in Siebel applications do not refer to the row ID field in the S_SYND_DATA table. You can insert any value in this field if it is unique in the record. Therefore, you can use a native database utility to populate this table. This method discusses the use of the Oracle SQL*Loader utility. As with the Visual Basic and COM method, using SQL*Loader requires that you resolve the foreign key references either before or after the data has been loaded into the table.

To use SQL*Loader to load the data

1. Resolve the foreign keys before loading.

   Process the input file one line at a time and convert each foreign key value into a row ID. To save time, first load the data directly into the table as it is stored in the flat file, then update the foreign key fields using SQL statements.

2. Load the data file using a .ctl format file.

   The following is an example of loading a data file:

   options (rows=100)
   load data
   infile 'c:\myfile.txt'
   badfile 'c:\myfile.bad'
   append
   into table S_SYND_DATA
   fields terminated by ","
   trailing nullcols
   (ROW_ID SEQUENCE (MAX,1),
   CREATED SYSDATE, CREATED_BY CONSTANT "1-0",
   LAST_UPD SYSDATE, LAST_UPD_BY CONSTANT "1-0",
   MODIFICATION_NUM CONSTANT 0,
   CONFLICT_ID CONSTANT "0",
   POSITION_ID, TERR_ID,
   PERIOD_ID, DATA_SRC_CD CONSTANT "RXTer",
   MARKET_ID, PRDINT_ID, ATTRIB_01)

3. Run the SQL*Loader utility from the command line using the following syntax:

   SQLLDR73.EXE database/userid@pw control=c:\my.ctl log=c:\my.log

   NOTE:  The exact file name and syntax of the Loader utility is determined by the version of Oracle being used.

4. When the data has been loaded, resolve the foreign key references for each field. This step makes the data visible to the users, as in the following example.

   UPDATE s_synd_data
   FROM s_period p
   SET s_synd_data.period_id=p.id
   WHERE p.name=s_synd_data.period_id ;

This code updates the Period field in the Syndicated Data Table to the ID of the Period rather than to the textual description of the period. You must modify and run this code for each foreign key field referred to in Table 23. The code only needs to be run for each field that you load, not for all of the fields.

Alternative Method: Using Visual Basic and COM to Load the Data

Use Visual Basic and the Siebel COM interface to retrieve the Siebel table row ID information by searching for the field that contains the text and return the ID field.