Siebel Life Sciences Guide > Importing Data into Life Sciences >

Extracting Data for Mobile Users

Once the data is in the database, it needs to be made visible to the users. The data is visible by position and can be viewed on many chart and analysis views in Siebel Life Sciences. This is fine for connected users, but the typical user of such data will be the sales rep or sales manager of a particular territory, who are likely to be remote users, synchronizing with the database using Siebel Remote. Therefore, the data needs to be extracted from the database.

The application administrator extracts syndicated data according to data-routing (visibility) rules that the administrator defines. When you do this, a full export-process audit trail is generated through log files. You can run multiple instances of the extraction process on each Siebel Server to maximize hardware capacity. The resulting output files are written in a compressed form and then zipped again to minimize file transfer time.

The general steps for data extraction are:

1. Create a data extraction rule.
2. Associate the necessary nodes of mobile users with the rule.
3. Define the portion of the data you want to extract and specify whether any deletions should be performed on the extracted data.
4. Set the environment by running the siebenv.bat file at the command prompt under the Siebel Server bin directory.
5. Run the Syndexp.exe executable file to perform the extraction.

These general steps are detailed in the following procedures.

To create a data extraction rule

1. Navigate to the Syndicated Data Administration screen > Export Rules view.
2. Click to select the Rules list and create a new record.
3. Complete the fields described in the following table.

   Field	Comments
   Name	A descriptive name for the rule.
   Outfile Id	Enter a unique, three-character ID. This ID will be used as the name of a temporary data file created by the extraction process.
   Active	If the rule is not active, it will not be processed by the extraction process.

You can create multiple rules for different types of extractions. For example, you could create one rule for Rx data and another rule for Outlet Level data. You can also extract data by multiple criteria (for example, sales force or position).

The WHERE part of a SQL SELECT statement specifies the rows of the S_SYND_DATA table that should be exported. In most cases, this WHERE clause is

WHERE T.POSITION_ID = [Position Id]

When you specify the users to extract, it will match their position ID to the position ID on the syndicated data table and extract only the data that is visible to them.

To associate nodes with an extract rule

1. In the Syndicated Data Export Rules view, select the correct extract rule.

   If a different view is currently displayed, navigate to Data Administration > Export Rules and select the appropriate rule.

2. In the Nodes list, create a new record and complete the necessary fields.

   Some fields are described in the following table.

   Field	Comments
   Name	You can select multiple nodes in the Add Nodes dialog box.
   Group Code	A code used for the grouping of nodes within the same extraction rule. You can specify the Group Cd value as an argument for the extraction process that is run with the Syndexp.exe executable file. With this group code, multiple Syndexp.exe processes can be run for the same extraction rule on the same Siebel Server.
   Node Type Code	An attribute column for the nodes, used to identify the remote nodes. (The Syndexp.exe process services only remote nodes.)
   Active	Only nodes specified as active will be processed.

To specify the data that is routed to mobile users

1. In the Syndicated Data Export Rules view, select the correct extract rule.

   If a different view is currently displayed, navigate to Data Administration > Export Rules and select the appropriate rule.

2. Click in the Tables list and create a new record.
3. Complete the fields. The fields are described in the following table.

   Field	Comments
   Table Name	Generally, this value should be S_SYND_DATA. The exception to this general rule is if you have created an extension table or if you are using another table to store syndicated data.
   Where String	Enter a string that defines the data to be extracted. Generally, these strings use standard SQL WHERE statements to build the extraction scripts. In addition to using static column values and column names to specify the data to be extracted, you can use two case-sensitive variables, [Position Id] and [Employee Id]. An example WHERE string definition that uses the Position Id variable is provided below. This example extracts data where the position ID in the syndicated data is equal to the position ID of the user. Example: WHERE T.POSITION_ID = [Position Id] A second example, provided below, extracts data for all positions assigned to an employee. Note that <TableOwner> in the example should be replaced with the appropriate Siebel database TableOwner value. Example: WHERE EXISTS (SELECT 1 FROM <TableOwner>.S_PARTY_PER R WHERE R.PARTY_ID = T.POSITION_ID AND R.PERSON_ID = [Employee Id])
   Sequence	Enter a numeric value that defines the order in which tables will be extracted.
   Active	Only tables specified as active will be processed.

4. In the Syndicated Data Administration screen, from the Show drop-down list, select SQL Statements.
5. Click in the Pre SQL Statements list and create a new record.
6. Complete the fields described in the following table.

   Field	Comments
   SQL Text	This SQL statement cleans up the data in the remote database in preparation for importing newer data, by deleting the existing data that will be replaced by newer data. If the entire syndicated data table is to be extracted, use the DELETE FROM table function. If only a subset of the data is to be extracted (for example, the latest month's data), use a where clause to specify the data subset. NOTE:  Each SQL statement must be prefixed with the table owner SIEBEL. If you do not include the table owner, the syndicated data import process fails.
   Comments	Optional description of statement's purpose; for example, describe the portion of the data you want to extract.
   Sequence	Enter a numeric value that defines the order in which SQL statements will be executed.
   Active	Only statements specified as active will be processed.

   You can refresh the entire syndicated data table, or you can refresh only certain specified data periods.

   NOTE:  Do not use the TRUNCATE function. Use the DELETE FROM function instead.

To set the environment

1. In a DOS window, navigate to the siebel_server\BIN directory.
2. Run the siebenv.bat file.

   CAUTION:  If you do not run siebenv.bat, you may encounter the following error message: "Unable to start common api. Error in DATAExpStartApis function."

3. Set the environment variables.

   NOTE:  Syndexp.exe will not detect nodes in a clustered environment successfully unless you manually set CLUSTER_NETWORK_NAME in either siebenv.bat or syndexp.bat.

   Refer to the command line arguments listed in Table 24 for multiple variables that are specific to the user environment. For more information on administering data extracts, see Siebel Remote and Replication Manager Administration Guide.

   NOTE:  Arguments that contain path names or spaces should be enclosed in double quotes ("").

   Table 24. Command Line Arguments for Environment Variables

   	Argument and Meaning	Comments
   /A	Create Attachment	Default: Y
   /C	ODBC Data Source	Default Environment Variable: SIEBEL_DATA_SOURCE
   /D	Siebel Table Owner	Required
   /E	Extract Rule Name	Required
   /F	File Server Directory	Default Environment Variable: SIEBEL_FSRV_ROOT. This File System parameter can use the absolute path to the \att directory or the Universal Naming Convention (UNC). Examples: syndexp /f "d:\Siebel\FS\att" syndexp /f \\MACHINENAME\FS\att
   /G	Group Code	Required
   /L	Log File	Default: syndexp.log
   /N	Repository Name	Default Environment Variable: SIEBEL_REPOSITORY
   /P	Password	Required
   /Q	Log Frequency	Default: -1
   /R	Read Consistency	Default: N
   /S	Use Sequence Number	Default: N
   /T	Data File Type	Default: compressed
   /U	Username	Required
   /V	AppServer name	Required. This is the logical name of the Siebel Server (not the host name).
   /X	Appserver Home Directory	Default Environment Variable: SIEBEL_HOME

To perform the data extraction

1. In the siebel_server\BIN directory, run the Syndexp.exe executable file.

   Use the command line arguments listed in Table 24 to define multiple variables that are specific to the user environment. For example:

   syndexp /U sadmin /P sadmin /D phdemo /E "Rule One" /C siebsrvr_siebel

   NOTE:  Interruption of the extraction process at any point causes the system to fail and necessitates restarting the data extraction from the beginning.

2. If an interruption occurs, you must go into the Pharma Node Attachment Administration view (navigate to the Syndicated Data Files view) and delete the previously generated data files. You also must go into each node and delete the latest file that was created in the previous run.

   After the program executes the Syndicated Data Loading (SDL) process, it displays the name of the user whose data is being extracted. This process continues until it has completed the extraction process for each user defined in the Syndicated Data Export Rules.

3. After you successfully complete the extraction process, you may want to create a batch file for use with subsequent extractions.

The extracted files are output in a compressed form to minimize file transfer time. Files are transferred using Siebel Remote, which is described in Siebel Remote and Replication Manager Administration Guide. After mobile users receive their extracted files, they must import the data into their local databases.

Full and Partial Data Extracts

Because syndicated data loading does not use EIM to maintain synchronization between the server and the remote databases, you must force a resynchronization step at the end of each direct load. Force resynchronization either by re-extracting an entirely new remote database for each mobile user or by performing a partial extract that includes only the refreshed syndicated data. A new, full database extract creates a database snapshot file for a given mobile user. A partial extract contains only S_SYND_DATA table data.

Some users receive syndicated data updates once a week, while some may receive syndicated data updates monthly or quarterly. Therefore, partial extract is preferred.

Possible Data Loss

Sometimes remote users connect to Siebel Server using unstable dial-up connections that prevent the data file from being transferred correctly, so that the data file does not reach the remote server. Siebel Server does not verify that the data file reached its destination successfully. At the end of the transfer step, Siebel Server removes the data file from the server.

In the case in which a data file is lost and removed from the server, you must extract a new data file for users.

CAUTION:  The loss of data or files may occur without warning. You might not be able to automatically determine the success or failure of a particular transfer. As a result, sales representatives may be using incorrect data for up to several months without realizing it.