Steps to upgrade usage tracking when upgrading from OBIEE 11.1.7.1 to OBIEE 11.1.1.9

Recently usage tracking stopped working for one of my clients after upgrading to OBIEE 11.1.1.9 with the NQSERVER log showing below 2 errors.

[59053] Usage Tracking stopped because the specified Usage Tracking table contained the wrong number of columns or a column with an inappropriate data type.

[59049] Usage Tracking not started due to non-existent Usage Tracking table "Usage Tracking"."DEV_BIPLATFORM"."S_NQ_DB_ACCT")

Reason for the above errors is that there are quite a few changes introduced to usage tracking in this version.

Below are the changes In Summary:

1. Usage Tracking now includes the physical query information, which enables end-to-end tracing.
2. Couple of new columns introduced in S_NQ_ACCT
   • ECID
   • TENANT_ID
   • SERVICE_NAME
   • SESSION_ID
   • HASH_ID

 3. Session IDs are introduced for more precise tracking. SESSION ID here is the BI Server session (not presentation session).

4. Init blocks usage also recorded. A new table S_NQ_INITBLOCK is introduced to handle this

5. Events are logged in Timestamp. START_TS and END_TS are now timestamp data types. 

Steps to upgrade.

1. Take backup of existing S_NQ_ACCT table.
2. Create the tables S_NQ_ACCT and S_NQ_DB_ACCT <OBIEE_HOME>\ Oracle_BI1\rcu\integration\biserver\scripts\oracle\createtable-usagetracking.sql
3. Import both the tables into OBIEE RPD. Overwrite S_NQ_ACCT table. Join the tables by S_NQ_DB_ACCT.LOGICAL_QUERY_ID REFERENCES S_NQ_ACCT.ID
4. Alter both the tables in database and change START_TS and END_TS data types to timestamps. This change need to be made in RPD also.
5. Restart the services and you will notice below message in NQSERVER log

 [2016-02-12T16:54:18.898+08:00] [OracleBIServerComponent] [NOTIFICATION:1] [] [] [ecid: 00iYYKOYe8fFw00Fzzw0w000017C00001l,0] [tid: 15c4]  [59055] Usage Tracking started.

 Another important script I noticed in OBIEE 11.1.1.9 is, it comes with upgrade script using which we can import the existing S_NQ_ACCT table to the newly created table with additional columns. This will enable us to retain the usage history and the same can be found in <OBIEE_HOME>\ Oracle_BI1\rcu\integration\biserver\scripts\oracle\upgrade-usagetracking.sql