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