Keeping existing 10g Usage

Keeping existing 10g Usage Tracking Customization in 11g after upgrade

This post will assist in implementing usage tracking in OBIEE 11g and keep the customization that might have been done in the 10g instance.

This document assumes that RCU utility has run successfully. Also, a standing up repository that was migrated to 11g instance with customize 10g Usage Tracking Subject Area.

Login in your database where you created WT_BIPLATFORM schema with a user that has read/write privilege and run the following. Copy the code into a file save it as .sql and run it using sqlplus.

spool run.log

@Oracle_create_nQ_Calendar.sql

@Oracle_create_nQ_Clock.sql

@Oracle_nQ_Calendar.sql

@Oracle_nQ_Clock.sql

@Oracle_create_nQ_UserGroup.sql

GRANT SELECT ON S_NQ_ACCT TO PUBLIC;

spool off

Setting up the additional tables before loading data, run the following scripts in the following order; These scripts can be found under {instance}/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking/SQL_Server_Time

Oracle_create_nQ_Calendar.sql
Oracle_create_nQ_Clock.sql

Now load the data using the following scripts located

{instance}/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking/SQL_Server_Time

Oracle_nQ_Calendar.sql
Oracle_nQ_Clock.sql

Create an extra view, Copy the code into a file save it as .sql and run it using sqlplus, match the name of the view with the physical table object in the 10g repository

CREATE OR REPLACE VIEW nq_login_group AS SELECT DISTINCT user_name AS login,user_name AS resp FROM s_nq_acct;

Grant select on nq_login_group to public;

Update NQSConfig.ini using the System MBEAN browser in Enterprise Manager

Note: Ensure to lock changes

Change the following Parameters

Apply changes and restart Enterprise Manager

Go to Repository in Online Mode, update the both Usage Tracking connection
pool with correct username and password to point to WT_BIPLATFORM schema.

Manually add 3 Columns to N_SQ_ACCT in Physical Layer

QUERY_KEY Varchar (1024)

QUERY_BLOB Long Varchar (2000)

ID Varchar (50)

Rename RUN_AS_USER to IMPERSONATOR_USER_NAME in N_SQ_ACCT in Physical Layer

Update USER to LOGIN in NQ_LOGIN_GROUP in Physical Layer

Update Usage Tracking Initialization Block SQL to point it to correct schema

In Usage Tracking BMM, open User table and verify if the below fields are mapped map them accordingly.

USER > LOGIN

GROUP > RESP

Check Global Consistency Check and SAVE Repository

Influences from gerardnico, rittmanmead, oracle_documents and other folks on the web.

Labels: OBIEE, Usage Tracking