OBIEE11G USAGE TRACKING
Setting Up Usage Tracking in OBIEE
This Document will used to set usage tracking and create usage
reports to monitor queries
Step1: Can use available User
in SQL schema otherwise create new one with all permissions.
CREATE USER OBIEE_USAGE_TRACKING IDENTIFIED BY
OBIEE_USAGE_TRACKING
D:\bi11g\instances\instance3\bifoundation\OracleBIServerComponent\coreapplication_obis1\schema\SAACCT.Oracle.sql
Run the script S_NQ_ACCT which is in SAACCT.Oracle.sql from
the user
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
D:\Obiee\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_Server_Time
Run the following scripts;
Oracle_create_nQ_Calendar.sql (To create table)
Oracle_create_nQ_Clock.sql (To create table)
Oracle_nQ_Calendar.sql (To Insert data into table)
Oracle_nQ_Clock.sql (To Insert data into table)
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 6:
Create an extra view
CREATE OR REPLACE VIEW nq_login_group AS SELECT DISTINCT
user_name AS login, user_name AS resp FROM s_nq_acct;
Step 7:
Make it available for the public
GRANT SELECT ON nq_login_group TO PUBLIC;
Step 8:
Merge the usage tracking RPD into your master_Rep RPD.
Open your master RPD offline.
then Save as "master_temp.rpd"
Click yes for Global Consistency
From the file menu select merge
Then select
Original Repository: master_Rep.rpd
Modified Repository: usagetracking_11115_BI0002
along with the Repository Passwords
Note: Can get the
correct usagetracking RPD and catalog from the following
then click on Next
Then click Next
Choose Modified option from Decision
Then Click on Finish option
Click No option for Global Consistency then Merged Rpd will be
Step 9: Upload
this new merged RPD and Catalog in the Enterprise Manager (EM)
Step
10: OBIEE 11g Usage Tracking Server
Configuration
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
[ 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 ;
Note:
PHYSICAL_TABLE_NAME = “<Database>”.”<Schema>”.”<Table>”
CONNECTION_POOL =“<Database>”.”<Connection
Pool>”
f)
Save and close the NQSConfig.ini configuration file (Run the services if
required using Opmnctl)
g) Copy the Usage Tracking Presentation
Catalog into the existing Presentation Catalog and Extract it
Merge Master Catalog and Usagetracking Catalog
using Catalog Manager
1. Go to Oracle Business Intelligence > Catalog
Manager to lunch Catalog
2. Open
Catalog - File >> Open Catalog , either in Online or Offline mode ,
provide the path, login credentials and click on OK.
3. Once the catalog
manager is expand the catalog and then select the “shared folder” as shown
below and then go to File >> select Archive
5. Once it is successful click on “Ok” and a file will be
created in the folder shown.
OBIEE HOME >> instances
>> instance4 >> bifoundation >>
OracleBIPresentationServicesComponent >> coreapplication_obips1 >>
catalogmanager >> mycatalog_archive
Archived file is shown below
Un-Archive – Catalog
1. Open Catalog - File >> Open Catalog , either in
Online or Offline mode , provide the path, login credentials and click on OK.
2. Once the catalog manager is expand the catalog and
then select the “shared folder” as shown below and then go to File
>> select unarchive
3. Click
on Browse and select the file which you want to un-archive and then finally
click on OK. Once done successfully click OK.
4. Refresh the
Analytics browser
Login
into User Interface(UI) and the check that the Usage Tracking Dashboard is added
