OBIEE Setting up usage tracking

OBIEE has a very good usage tracking system, with low resource costs. Here is how you set up the Oracle variant.

Step 1: create a oracle schema:

CREATE USER obiee_usage_tracking IDENTIFIED BY "OBIEE_USAGE_TRACKING"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE DEFAULT
QUOTA UNLIMITED ON "USERS";
GRANT "CONNECT" TO obiee_usage_tracking;
GRANT "RESOURCE" TO obiee_usage_tracking;
GRANT CREATE SESSION TO obiee_usage_tracking;
GRANT CREATE TABLE TO obiee_usage_tracking;
GRANT CREATE VIEW TO obiee_usage_tracking;
ALTER USER obiee_usage_tracking DEFAULT ROLE NONE;

Step 2: Create the tracking table.

the Tracking table Script can be found in: ... \OracleBI\server\Schema
Use SAACCT.Oracle.sql for Oracle and put it in the relevant OBIEE_USAGE_TRACKING schema.

Step 3: Make it available for the public
GRANT SELECT ON S_NQ_ACCT TO PUBLIC;

Step 4: Setting up the additional tables
You can find the scripts in ...\OBIEE\OracleBI\server\Sample\usagetracking\SQL_Server_Time

Run the following scripts;
Oracle_create_nQ_Calendar.sql
Oracle_create_nQ_Clock.sql
Oracle_nQ_Calendar.sql (Warning this only goes to 2016!)
Oracle_nQ_Clock.sql

Step 5: Make it available for the public

GRANT SELECT ON S_ETL_DAY TO PUBLIC;
GRANT SELECT ON S_ETL_TIME_DAY TO PUBLIC;

Step 5b: Create an extra view

(They forgot this one in the documentation)

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;

Step 6: Merge the usage tracking RPD into your master RPD.

Open your master RPD offline.

Save as "master_temp.rpd"
From the file menu select merge.



Select you original master.RPD
Select the modified repository from ...\OracleBI\server\Sample\usagetracking\ Password is empty
Click MERGE If you get this one don't worry about it, just click ok
Save the merged RPD as your master.rpd

Step 7: Move the Usage Tracking to a native connection pool If you ommit this and use ODBC you can get "strange" errors.
Import 1 table from the OBIEE_USAGE_TRACKING schema.

Delete only the table and copy the connection pool

Drag and drop the table part from the imported Usage tracking rpd.

Rename the databasebase an connection pools

Check the connection pool data
Be sure to set both connection pools!



Step 8: Altering the NQSConfig.ini

The file can be found in: …\OracleBI\server\Config

Locate and Alter the following:

###################
# Usage Tracking Section
# Collect usage statistics on each logical query submitted to the
# server.
###################
[ USAGE_TRACKING ]
ENABLE = YES;
DIRECT_INSERT = YES;
PHYSICAL_TABLE_NAME = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT";
CONNECTION_POOL = "OBI Usage Tracking"."Usage Tracking Writer Connection Pool" ;
BUFFER_SIZE = 10 MB ;
BUFFER_TIME_LIMIT_SECONDS = 5 ;
NUM_INSERT_THREADS = 5 ;
MAX_INSERTS_PER_TRANSACTION = 1 ;

Step 9:
Restart the BI server. Check the server log for errors.

Step 10: Import the Usage Tracking Presentation Catalog into the existing Presentation Catalog. Extract the example catalog from the ZIP file found in ...\OracleBI\server\Sample\usagetracking\

Open one instance of the Oracle Business Intelligence Catalog Manager. Open the source Presentation Catalog from where we can copy the Usage Tracking content.

Open this Presentation Catalog in offline Mode.

Once you have opened the presentation catalog, click on the shared folder. In this folder you can find the folder Usage Tracking, which holds all related reports, filters and the dashboard page.


Open another instance of the Oracle Business Intelligence Catalog Manager. Open the destination Presentation Catalog in online mode to which you want to copy the Usage Tracking content to. Open the shared folder. As you can see in the example below, this presentation catalog doesn’t have the Usage Tracking folder yet.


Go back to the source Presentation Catalog for the Usage tracking. Click on the Usage Tracking folder and click on Copy button in the upper left of the screen.


Go to the destination presentation catalog. Be sure the shared folder is opened. Right click in the folder window and click on Paste. Close both catalog managers.

Note: Some people are worried that they don't see the entry directly in the reports. This is because OBIEE saves the usage tracking inserts them in a batch. The amount is influenced by the BUFFER_SIZE parameter. This specifies the amount of memory used to temporarily store insert statements. The buffer allows the insert statements to be issued to the usage tracking table independently of the query that produced the statistics to be inserted. When the buffer fills up, then subsequent queries’ statistics are discarded until the insert threads service the buffer entries. When you stop the BI-server it will try to issue an insert all remaining entries.

Till Next Time

This article is also published on: http://knowledge.ciber.nl/weblog/

Murat did some great extra comments:

http://muratdemirkiran.wordpress.com/2009/01/07/test/