Updating the Usage Analytics Database Format

The format of the Microsoft SQL database used to collect the Usage Analytics data may change when you upgrade from one Coveo Platform version to another. When your Coveo implementation takes advantage of the Usage Analytics module features, in the Coveo migration process, you therefore need to update the Usage Analytics database to the latest format.

To update the Usage Analytics database format

1. Referring to the following table, select the migration scripts appropriate for your migration. The scripts files are distributed in the [.Net_Front-End_Path]\Web\Analytics\Scripts\Migration\ folder.

   Script file	CES version
   MIGRATE_CES_ANALYTICS_DB_FROM_V7_TO_V7-1.SQL	7.0
   MIGRATE_CES_ANALYTICS_DB_FROM_V6_TO_V7.SQL
   MIGRATE_CES_ANALYTICS_DB_FROM_V5_TO_V6.SQL	6.5.4616+
   MIGRATE_CES_ANALYTICS_DB_FROM_V4_TO_V5.SQL	6.5.4332+
   MIGRATE_CES_ANALYTICS_DB_FROM_V3_TO_V4.SQL	6.5 up to release 4280
   MIGRATE_CES_ANALYTICS_DB_FROM_V2_TO_V3.SQL Note: You also need to run ReviseAnalytics method (see the corresponding Step ).	6.2
   MIGRATE_CES_ANALYTICS_DB_FROM_V1_TO_V2.SQL	6.1

   Important: Pay attention to not use the creation script also available in the Creation folder. Running the creation script permanently erases any existing Usage Analytics data as it creates blank Usage Analytics database tables.

   Example: You are migrating from CES version 6.5 build 4332 in which the Usage Analytics module database is in version 5. You need to migrate the Usage Analytics database to version 7-1. To do so, you must successively run the following scripts:

   • MIGRATE_CES_ANALYTICS_DB_FROM_V5_TO_V6.SQL

   • MIGRATE_CES_ANALYTICS_DB_FROM_V6_TO_V7.SQL

   • MsIGRATE_CES_ANALYTICS_DB_FROM_V7_TO_V7-1.SQL

2. On the Coveo server, copy the appropriate script file from the [.Net_Front-End_Path]\Web\Analytics\Scripts folder.

   Note: The script uses the Usage Analytics database default name (CoveoAnalytics). If your implementation of the Usage Analytics database uses a different name, edit the first line of the script to replace the default database name with the name of your database.

3. On the Microsoft SQL Server:

   1. Paste the script file to a temporary folder of your choice.

   2. Start Microsoft SQL Server Management Studio (on the Windows taskbar, select Start > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio).

4. In Microsoft SQL Server Management Studio:

   1. On the menu, select File > Open > File.

   2. In the Open File dialog box, browse for the script file that you pasted, and then click OK.

   3. On the SQL Editor toolbar, select the appropriate database (CoveoAnalytics by default), and then click Execute to run the script.

      

      A message appears at the bottom of the Microsoft SQL Server Management Studio window to indicate that the execution of the script completed successfully.

      

      A migration log is also added in the Version table of the Usage Analytics database.

      

5. When you migrate the Usage Analytics database from version 2 to version 3, immediately after running the MIGRATE_CES_ANALYTICS_DB_FROM_V2_TO_V3.SQL script, you also need to invoke the ReviseAnalytics Usage Analytics web service method to populate the newly created Keywords and ActionKeywords tables with your existing Usage Analytics data:

   1. Using a browser, on the Coveo Master server, access the Usage Analytics web service page with the following URL: 

      http://localhost:8080/Coveo/Analytics/Analytics.asmx?op=ReviseAnalytics

   2. In the page that appears, click Invoke.

      

      The ReviseAnalytics method performs the required operations the background.