Upload of UPT and UPT-IRM Intersect Data

This chapter covers information about how to upload usage pattern tracking (UPT) data as well as its intersection with incremental repository merge (IRM) data to the Siebel CRM database to reduce the cost of testing post upgrade of Siebel CRM. It includes the following topics:

Overview of UPT and IRM Data

The UPT data allows administrators to review details about when and how often users access the features in a Siebel CRM application. The process of performing UPT yields a set of .csv files.

Note: The information in this chapter is applicable only when you run IRM after migrating from a prior Siebel CRM release in which UPT was enabled such as version 15.5 or later. For information about the specific releases (including applicable patchset releases) for which UPT can be enabled, see 2145521.1 (Article ID) on My Oracle Support. For more information about Usage Pattern Tracking, see Usage Pattern Tracking and for running IRM, see Siebel Database Upgrade Guide.

As a Siebel CRM database administrator, you also obtain a set of .csv files when you use IRM to apply Siebel Innovation Pack during database upgrade (see Siebel Database Upgrade Guide).

Execution of UPT is an optional but recommended process. IRM is a mandatory process for customers upgrading from a previous Siebel Innovation Pack to the current one. Both yield .csv files that you may upload to your Siebel CRM database for further analysis in the form of reports. The .csv files of UPT list the actual repository objects in use by end users. In the case of IRM, these files list the repository objects modified or newly added during the ongoing IRM process. The actual change to a repository object is termed direct change. This change may trigger an indirect change to other objects in its hierarchy. Both types of changes reflect in the IRM log (see Siebel Database Upgrade Guide) and the UPT-IRM Intersection report (see Analytical Reports for UPT and UPT with IRM Intersection).

Note: The objects exported to the IRM .csv file are Business Objects, Business Components, Applets, Views, Web Pages, Schema and Workflows. The export is performed during the Upgrade Repository (upgrep) process once IRM is completed.

You can choose to perform only UPT to monitor and report usage data or IRM to upgrade to a Siebel Innovation Pack or both.

Benefits of Intersecting UPT-IRM Merge

It is useful to compare the UPT .csv with the IRM .csv files so as to focus testing only on objects that are in use by the customer as well as modified by IRM and not on all modified objects. If you choose to compare the two sets of .csv files, the intersection will yield a list of repository objects that are used and modified. These objects are the ones most used by end users and, therefore, require more testing.

Note: To intersect UPT and IRM data, you need to migrate from Siebel CRM version 15.0 or 15.5 as UPT is enabled on the patchsets of these versions only.

When you intersect UPT data with IRM data, you obtain an accurate view of the actual product components used by a customer and the repository artifacts that IRM touches during the upgrade cycle. This targeted testing will help customers:

Obtain data-backed targeted and specific investments so as to get predictable results.
Reduce the cost of upgrades and cost of ownership through focused functional, integration, and performance testing post upgrade.

For example, UPT events may indicate that 10 applets across five views in two screens are the only objects used by a customer. IRM delta changes may be across 15 applets, 10 views, and 12 screens. The resultant intersection of UPT and IRM could be 6 applets, 3 views, and 1 screen. This would be generated as a report and be an output of the IRM Upgrade cycle. This will help the customer focus on testing the entities obtained solely from the intersection. The resultant saving will be on resources, time, and money.

Process of Uploading and Intersecting Data

At the broad level, the steps are to upload the data into the database and intersect it with IRM. To upload the UPT data and intersect it with the IRM data, perform the following tasks:

Installing the File Upload Utility

The UPT data upload utility is multi-purpose as it can be used to both upload batches of .csv files and perform their intersect. Its purpose is to:

Upload the UPT .csv files to the Siebel CRM database.
Compare UPT .csv with IRM .csv files.
Upload the intersected UPT-IRM .csv files to the Siebel CRM database.

For more information on how to install this utility, see Analytical Reports for UPT and UPT with IRM Intersection

Installing OBIEE

You use Oracle Business Intelligence Enterprise Edition (OBIEE) to view the uploaded data in the form of prebuilt or custom reports.

For more information on installing OBIEE, see Installing Oracle Business Intelligence.

Generating UPT and Performing IRM

You must generate UPT and perform IRM, producing a set of .csv files from each process.

To generate UPT-related .csv files, perform the steps in Usage Pattern Tracking
To generate IRM-related .csv files, perform the steps in Siebel Database Upgrade Guide.

Note: Place the generated .csv files in folders, such as C:\UPTFiles or C:\IRMFIles, on your local or remote machine.

Uploading UPT and UPT-IRM Intersect Data

Use this procedure to upload the data.

To upload UPT and UPT-IRM Intersect data to a Siebel CRM database

Create a folder on your local or remote machine, such as C:\UPT, to copy the database .jar and utility files.
Download the database-specific .jar file to this location as per your database as follows:
- Oracle: ojdbc6.jar
- MSSQL: sqljdbc4-2.0.jar
- DB2: db2jcc.jar
  
  Note: You can obtain the .jar files from the respective database clients.
Download your choice of data upload utility to the same folder.
- To execute the data upload utility from command prompt, download UPTFileUpload.jar
- To execute the data upload utility from the user interface, download UPTUtilities.jar
  
  Note: You can obtain the .jar files from the SIEBEL_ROOT/dbsrvr/common folder. You can execute them where Siebel Server is installed or copy them to another environment where Java 8 is installed. Ensure your system has JRE 1.8 or above.
Navigate to the folder where you downloaded the utility.
- To upload the UPT .csv files from the command prompt, enter as follows:
```
java -jar UPTFileUpload.jar /s [Database Server] /f [CSV files folder path] /d 
[Database Type – Oracle, DB2UDB or MSSQL] /u [Database User] /p [Database 
Password] /h [Host Name] /o [Database Port] /r [Data Purpose] /t [Table Owner]
```
  For example:
```
java -jar UPTFileUpload.jar /s dbixt1 /f c:/UPTFiles /d Oracle /u SMITH123 /p 
SMITH123 /h smith01ixt /o 1551 /r test /t SMITH123
```
  Note: To learn more about the arguments for the data upload utility, invoke its Help. You can run the script multiple times but it will load only newly added files or the files where user corrected errors shown in earlier parse. Ensure that the table owner has the table create, read, and write access.
- To upload from user interface, enter as follows at the command prompt to open the utility:
```
java -jar UPTUtilities.jar
```
  Note: Pass the parameters to the utility to enable upload of each .csv file.
The utility processes each .csv file and records its filename in any of the following three files as per processing status:
- ProcessedFiles.log: logs files processed successfully along with the row count per file.
- UnProcessedFiles.log: logs files that did not upload along with their error description.
- Fileupload.log: logs all steps in file execution, including technical issues, such as incorrect folder or connectivity to a database, along with error description.
The processed files are stored in the folder created in the Note at Step 2.

Intersecting UPT and UPT-IRM Data

To view and analyze the .csv files, you can view them in any supporting editor or upload them to the Siebel CRM database. For more information on how to view UPT and IRM data on the Siebel CRM database, see Analytical Reports for UPT and UPT with IRM Intersection .

Performing an intersect of the UPT and IRM data provides:

A list of objects from the UPT-IRM intersect indicating those that are used and modified and, therefore, require testing.
Prebuilt reports, such as UPT-IRM Intersection, after loading data on OBIEE.
UPT raw data to use with any analytics tool to generate custom reports.

To intersect UPT and UPT-IRM data

Perform the steps shown in Uploading UPT and UPT-IRM Intersect Data.

Note: Create a separate folder, such as C:\IRMFiles, in which to copy the IRM files.
- Download the IntersectUPTMrg.jar to the same folder if you want to use the command prompt to intersect the .csv files or use the data upload utility for user interface indicated in Uploading UPT and UPT-IRM Intersect Data.
Navigate to the folder where you downloaded the utilities.
Intersect the UPT and IRM files from the file folders where they are stored.
- To intersect the .csv files from the command prompt, enter the following command:
```
java -jar IntersectUPTMrg.jar /u [CSV files folder path] /m [IRM files folder 
path] /l compareCSV.log /o [output folder]
```
  For example:
```
java -jar IntersectUPTMrg.jar /u C:/UPTFiles /m C:/IRMFiles /l compareCSV.log /o 
upt_mrg
```
- To upload from user interface, invoke the data upload utility for user interface and navigate to the IRM tab.
The utility creates the upt_mrg folder in the same folder as the .jar of the utility and places the merged files in it.

Note: These files will be used for further reporting and analysis as outlined in Analytical Reports for UPT and UPT with IRM Intersection.