OBIEE Online Training

Thursday, August 18, 2011

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)

2)

3)


4)


5)



 Thanks and Regards,

Deva

1 comment:

  1. HI can you post the diagrams clearly as i dont see them for setting up the OBIEE 11g usage tracking. And also i dont see you defined the catlog for usage tracking in to the original catalog.

    Ravi

    ReplyDelete