While Implementing Usage Tracking in OBIEE 11g,I came to know that the given default RPD and Catalog is a bug in 18.104.22.168.
So updating you all with correct RPD and Catalog for implementing Usage Tracking in OBIEE 11g.
you can get the correct RPD and catalog from here.
Step by Step procedure of Implementing Usage Tracking in OBIEE 11g:
Used to monitor system and ad hoc query performance and to provide input on usage details. Used for determining user queries that are creating performance, based on query frequency and response time.Usage tracking Presentation Catalog provides prebuilt requests and a dashboard built to provide common analysis—for example, analyzing usage patterns by users, groups, and queries; daily and weekly peak usage patterns and load variance;time series comparisons to help compare current usage over previous weeks or months; and major contention points for troubleshooting, including, for example, top long-running queries.
Before implementing Usage Tracking we need to run the following scripts.
Step 1 :
Create the following sql scripts:
- Oracle_create_nQ_Calendar—Create table scripts
- Oracle_create_nQ_Clock—Create Table Scripts
- Oracle_nQ_Calendar—Insert Scripts
- Oracle_nQ_Clock—Insert Scripts
- Also create S_NQ_ACCT table
- We need to even create NQ_LOGIN_GROUP view also using the following syntax:
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;
We can get the scripts from Middleware/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking/SQL_Server_Time
We can get the S_NQ_ACCT table from
Note : Don’t forget to commit the changes after creating the tables and inserting the data.
Step 2 : We can get the upgraded Usage Tracking RPD and Catalog from the below path after installing OBIEE 11g.
As mentioned it is a bug so you can get the RPD and Catalog from the above mentioned links.
Step 3 : Upload the RPD and Catalog in the Enterprise Manager (EM) to make it online.
you can get how to upload RPD and Catalog in to EM from https://bidevata.wordpress.com/2011/11/23/how-to-keep-rpd-and-catalog-in-online-mode-in-obiee-11g/
Step 4 : Open the RPD in online mode and change the Usage Tracking Writer Connection Pool settings to your database credentials.
Update the row count and check whether data was populating or not.
Step 5: OBIEE 11g Usage Tracking Server Configuration:
To configure the server configuration parameters, perform the following steps:
a) Open Middleware\instances\instance1\config\OracleBIServerComponent\
coreapplication_obis1\NQSConfig.ini in Notepad and edit.
b) Next, set the usage tracking options. Scroll to the Usage Tracking section. Set Enable = Yes; to enable usage tracking.
c) In the section that lists parameters used for inserting data into a table, set the
PHYSICAL_TABLE_NAME = “OBI Usage Tracking”.”Catalog”.”dbo”.”S_NQ_ACCT” ;
d) The table name is the fully qualified name as it appears in the Physical layer of the Server Administration Tool. The general structure of this parameter depends on the type of database being used. For Oracle, the structure is PHYSICAL_TABLE_NAME = “<Database>”.”<Schema>”.”<Table>”.]
e) Set the CONNECTION_POOL parameter to specify the connection pool to use for inserting records into the usage tracking table. The setting is CONNECTION_POOL = “OBI Usage Tracking”.“Usage Tracking Writer Connection Pool”. Leave the default settings for BUFFER_SIZE, BUFFER_TIME_LIMIT_SECONDS, NUM_INSERT_THREADS, and MAX_INSERTS_PER_TRANSACTION. Your configuration file should look like this:
The connection pool name is the fully qualified name as it appears in the Physical layer of the repository.
f)Save and close the NQSConfig.ini configuration file.
Step 6 : Login to analytics http://<hostname>: <port >/analytics/ by using Administrators credentials.
Check the Usage Tracking dashboards .