OBIEE 10G/11G - (Usage Tracking|UT) (Query Log)

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs)

1 - 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.

Advertising

3 - Configuration Steps

3.1 - 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

3.2 - 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

3.3 - 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>" ;
Advertising

3.3.1 - 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.

3.3.2 - 10G

You can found all the configuration parameters and their descriptions in the system management.

4 - Support

If you have any problem, check the NQServer.log file.

Example of problem :

4.1 - 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".

4.2 - 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.

5 - Documentation / Reference

Advertising