How-to: Upgrade Usage Tracking OBIEE 10g to OBIEE 11.1.1.x

Oracle has provided plenty of documentation on how to upgrade your OBIEE 10g environment to their new 11g platform. They've even outlined the process of configuring a new instance of Usage Tracking on a new 11g environment - outlined here. What I haven't been able to find is the ability to take an existing Usage Tracking environment in OBIEE 10g, and port it over to the new 11g environment. The upgrade assistance tool won't work because the data model for Usage Tracking has actually changed between the two versions.  This guide is going to cover the process of:

• Taking an existing OBIEE 10g Usage Tracking environment 
• Making the necessary changes to the Usage Tracking data model
• Applying the configuration changes in weblogic to enable Usage Tracking in OBIEE 11g

Step 1: Make changes to existing OBIEE 10g Usage Tracking RPD

The data types and nullable check box have changed for nearly every column in Usage Tracking's primary table: S_NQ_ACCT. Using the Admin Tool, modify each column as outlined below:

Step 2: Modify Physical Data Model

I've noticed on some engagements that the NQ_LOGIN_GROUP table may not always be deployed. I don't think this is an 'out of the box' table but rather something manually deployed to track the applicable group of an individual user. Note that as you move to the application role based security model you'll no longer need this table. In the event that you do need this backwards compatibility, you'll need to fix the broken link - which is made evident by the red line that appears when you attempt to display the physical data model:

You'll need to make the physical join on NQ_LOGIN_GROUP.LOGIN = S_NQ_ACCT.USER_NAME

Step 3: Enable Usage Tracking in Enterprise Manager

It's at this step where Usage Tracking implementation differs dramatically between OBIEE 10g and OBIEE 11g. In the 10g platform, Usage Tracking was enabled by modifying the nqsconfig and instanceconfig files, but with the advent of Oracle's Enterprise Manager you must now go through EM's MBeans configuration to enable the tool.  To Oracle's credit, they did provide a detailed guide on how to enable Usage Tracking in EM . Rather than re-invent the wheel, i'm going to defer everyone to the section titled 'Configuring Usage Tracking in Enterprise Manager'. We're past the 'OBIEE 10g' upgrade so Oracle's guide is more than appropriate.

Step 4: Deploy Usage Tracking Tables to Your 11g Database

Most implementations use the DEV_BIPLATFORM that come pre-populated with the S_ETL_DAY, S_ETL_TIME_DAY and S_NQ_ACCT tables, but if your version did not, or you're not using the default schema, run the following scripts located at the following directory:

instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\

1. Oracle_create_nQ_Calendar.sql
2. Oracle_create_nQ_Clock.sql
3. Oracle_nQ_Calendar.sql
4. Oracle_nQ_Clock.sql

Step 5: Confirm Usage Tracking is Enabled

I've created a sample report with the Usage Tracking subject area as outlined below:

 

When viewing the log generated by the query, you'll see that a query is generated against the Usage Tracking connection pool and no error is thrown!



 

keywords: obiee 11g, usage tracking,  upgrade usage tracking , enterprise manager