OBIEE11g Setting up Usage Tracking

Setting Up Usage Tracking in Oracle BI EE

This Document will used to Set up usage tracking and create usage reports to monitor queries.

1) It is used to monitor system and ad hoc query performance and to provide input on usage details

2) Usage tracking is particularly useful in determining user queries that are creating performance, based on query frequency and response time. When usage tracking is enabled, the Oracle BI Server collects usage tracking data for each query and writes statistics to a usage tracking log file or inserts them directly to a database table.

3) 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.

Step1: if you have user id in your SQL schema use it other wise create new one with all permissions.

Step 2: Create the tracking table.

the Tracking table Script can be found in:

D:\bi11g\instances\instance3\bifoundation\OracleBIServerComponent\coreapplication_obis1

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:\bi11g\instances\instance3\bifoundation\OracleBIServerComponent\coreapplication_obis1\sample\usagetracking\SQL_Server_Time

Run the following scripts;

SQLServer_create_nQ_Calendar

SQLServer_create_nQ_Clock

SQLServer_create_nQ_UserGroup

SQLServer_nQ_Calendar

SQLServer_nQ_Clock

And load the data.

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

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

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

GRANT SELECT ON S_ETL_DAY TO PUBLIC;

GRANT SELECT ON S_ETL_TIME_DAY TO PUBLIC;

GRANT SELECT ON S_NQ_ACCT TO PUBLIC;

Open your master RPD offline.

Usage Tracking RPD file

[USAGE_TRACKING]

ENABLE = YES; 

#==============================================================================

# Parameters used for writing data to a flat file (i.e. DIRECT_INSERT = YES).

#

# Note that the directory should be relative to the instance directory.

# In general, we prefer directo insert to flat files.  If you are working in

# a cluster, it is strongly recommended you use direct insert.  If there is

# only one Oracle BI Server instance, then you may use flat file data.

# The directory is then assumed relative to the process instance.  For

# example, "UTData" is resolved to

# "$(ORACLE_INSTANCE)/bifoundation/OracleBIServerComponent/<instance_name>/UTData

STORAGE_DIRECTORY = "<directory path>"; 

CHECKPOINT_INTERVAL_MINUTES = 5; 

FILE_ROLLOVER_INTERVAL_MINUTES = 30; 

CODE_PAGE = "ANSI";  # ANSI, UTF8, 1252, etc.

#

#==============================================================================

DIRECT_INSERT = YES; 

#==============================================================================

#  Parameters used for inserting data into a table (i.e. 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 = 250 MB; 

BUFFER_TIME_LIMIT_SECONDS = 5; 

NUM_INSERT_THREADS = 5; 

MAX_INSERTS_PER_TRANSACTION = 1; 

#

#==============================================================================

SUMMARY_STATISTICS_LOGGING = NO; 

SUMMARY_ADVISOR_TABLE_NAME = "OBI Usage Tracking"."Catalog"."dbo"."S_NQ_ACCT"; 

File path bellow,

Edit With Notepad--

Ammend USAGE Tracking Section in NQSCONFIG.ini Like So I have shown only sections relevent to

ammendment.

[USAGE_TRACKING]

ENABLE = YES;

After merging two rpd GPC_Datamart and Usage tracking will be show like below screen short.

Ammend both Connection pools to the Schema for Usage Tracking we created earlier. Also set permissions

for Writer connection pool to read and write.

Connection Pool & Usage Tracking Writer Connection Pool

Then ammended connection pool to connect to my Usage Tracking tables schema we created earlier.

Just do an Update Row Count to make sure you can connect ok ..

I have noticed that OBIEE 11g is pretty bugged up... So just keep trying with creating connection pools..

I had to copy the connection pool over from my Pre-Merged UsageTracking.rpd and then it started

working.. Weird but hey.

Thats it the RPD is working and merged .

Then log in dashboard page and check it out.

Reference:

1)

http://obiee101.blogspot.com/2008/08/obiee-setting-up-usage-tracking.html

2)

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/biee/r1013/usage_tracking/usage_tracking.htm

3)

http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/biee/r1013/usage_tracking/usage_tracking.htm

4)

http://gerardnico.com/wiki/dat/obiee/usage_tracking

5)

http://www.obieetalk.com/obiee11g-setting-usage-tracking

 Thanks and Regards,

Deva