About
Usage Tracking is a functionality of BI Server to trace the execution of a Logical SQL in a log database table.
NqQuery.log file shows Physical SQL Query, logical execution plan where Usage tracking does not have this information.
Articles Related
Configuration Steps
- 10g: All the 10g installation instruction can be found in this article: Oracle By Example, a complete example step by step
- 11g: See the documentation: Setting Up Usage Tracking of this tutorial OBIEE 11g: How To Set Up Usage Tracking and Create Usage Reports To Monitor Queries ? A sample usage tracking dashboard implementation is provided with the Oracle Business Intelligence installation at: FMW_HOME/Oracle_BI1/bifoundation/admin/provisioning/OracleBIServerComponent/usage_tracking.rpd
Creation of the usage table
The table that store the data is the table s_nq_acc .
The files to install usage tracking are located:
- on 10g: BI_ORACLE_HOME/server/Sample/usagetracking
- on 11g: The Usage Tracking and Summary Advisor Tables are hold in the BIPlatform. You get in the following directory a webcatalog, a repository and optional scripts to complete the star schema: ORACLE_INSTANCE/bifoundation/OracleBIServerComponent/coreapplication_obisn/sample/usagetracking
Repository
To configure Usage tracking the usage table s_nq_acc must be accessible in the physical layer of the repository.
A 10g sample repository can be found on this location : OracleBI_home\server\Sample\usagetracking
Parameters
The parameters are in the NqsConfig.ini file. You must configure the database connection values in “Usage Tracking”.“Connection Pool” for database where usage tracking data would be collected
Example of the usage tracking section of OBIEE 11g.
###############################################################################
#
# Usage Tracking Section
#
# Collect usage statistics on each logical query submitted to the
# server.
#
###############################################################################
[USAGE_TRACKING]
ENABLE = YES; # This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
#==============================================================================
# Parameters used for writing data to a flat file (i.e. DIRECT_INSERT = NO).
#
# 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; # This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
#==============================================================================
# Parameters used for inserting data into a table (i.e. DIRECT_INSERT = YES).
#
# This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
PHYSICAL_TABLE_NAME = "EXAREPO"."EXA1_BIPLATFORM"."S_NQ_ACCT"; # Or "<Database>"."<Schema>"."<Table>"
# This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
CONNECTION_POOL = "EXAREPO"."EXA_USAGE";
BUFFER_SIZE = 250 MB;
BUFFER_TIME_LIMIT_SECONDS = 5;
NUM_INSERT_THREADS = 5;
MAX_INSERTS_PER_TRANSACTION = 1;
#
#==============================================================================
# This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
SUMMARY_STATISTICS_LOGGING = YES;
# This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
SUMMARY_ADVISOR_TABLE_NAME = "EXAREPO"."EXA1_BIPLATFORM"."S_NQ_SUMMARY_ADVISOR"; # Or "<Database>"."<Schema>"."<Table>" ;
11g
The configuration parameter can be change within the Weblogic Mbean browser if UsageTrackingCentrallyManaged is set to true. otherwise the usage tracking and summary parameters are managed using the NQSConfig.INI file on each Oracle BI Server computer.
10G
You can found all the configuration parameters and their descriptions in the system management.
Support
If you have any problem, check the NQServer.log file.
Example of problem :
Non-existent Usage Tracking table
[2015-10-26T22:53:56.430+01:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 99b264eb267f4da4:38a8c88a:15039c0f41e:-8000-000000000070d671,0:1:6] [tid: 4d878940]
[59049] Usage Tracking not started due to non-existent Usage Tracking table "LCM"."LCM"."S_NQ_ACCT".
[2015-10-26T22:53:56.432+01:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 99b264eb267f4da4:38a8c88a:15039c0f41e:-8000-000000000070d671,0:1:6] [tid: 4d878940]
[59049] Usage Tracking not started due to non-existent Usage Tracking table "LCM"."LCM"."S_NQ_DB_ACCT".
Usage Tracking table contained the wrong number of columns or a column with an inappropriate data type
[2015-10-26T20:33:46.332+01:00] [OracleBIServerComponent] [ERROR:1] [] []
[ecid: 99b264eb267f4da4:38a8c88a:15039c0f41e:-8000-0000000000703587,0:1:1:6] [tid: 4dbb2940]
[59053] Usage Tracking stopped because the specified Usage Tracking table contained the wrong number of columns or a column with an inappropriate data type.