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

Bi Server Architecture With Client

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.

Configuration Steps

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

Obiee11g Usage Tracking Physical Layer

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.

Obiee11g Mbean Usage Tracking

10G

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

Obiee Usage Tracking System Managment

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.

Documentation / Reference





Discover More
Obiee 11g Connection Pool Connection Script Timesten
OBIEE - Aggregate Persistence (Script)

The Aggregate Persistence functionality is the ability to: create and populate aggregate tables, and map them to the metadata repository in order to use the aggregate navigation capability of the...
Bi Server Architecture With Client
OBIEE - BI Server (OBIS|nqsserver|nqs)

OBI Server is also known as: OBIS (Oracle Business Intelligence Server) or nqsserver (The process name) OBI Server is principally a query and analysis server software: that can integrate data...
Obiee Security User Indexcol
OBIEE - IndexCol Function - Handle hierarchy Level security

The INDEXCOL function helps to select the appropriate column and behave as the IF THEN structure. It can then use external information to return the appropriate column for the logged-in user to see. ...
Obi Edition
OBIEE - Installation / Configuration

This page includes all articles on the installation topic of OBIEE. Installing...
Obiee Ceim
OBIEE - Logical Sql

The logical SQL is the SQL that understand BI Server. It's a full Ansi 92 SQL (including subqueries and derived tables), plus special functions (SQL extensions) like AGO, TODATE, EVALUATE, and others....
Obi Edition
OBIEE 10G/11G - Monitoring - (Performance|Perfmon) (Metrics|Counter)

The System and Performance Monitoring is managed through metrics that span over all the OBI layers: OS Application Server (Weblogic 11g or OC4J for 10g) BI Server instrumenteJMX technologinstrumenteDMS...
Obi Edition
OBIEE 10G/11G - Nqquery.log (Query logging)

It's the log file of BI Server showing information the process of a logical SQL query. It's located in the directory: 10g: BI_ORACLE_HOME/server/Log 11g: ORACLE_INSTANCE/diagnostics/logs/OracleBIServerComponent/coreapplication_obis1...
Obiee11g Bip Security Center Roles
OBIEE 11G - Installation Sample Application version 825

edit the “” Save the file. Login to the EM administration screen using the url Expand 'Business Intelligence' > Coreapplication > Deployment > Repository Click...
Obi Edition
OBIEE 11G - Upgrade From OBIEE 11.1.1.7 To OBIEE 11.1.1.9

When you move from one 11g release to another, you do not use the Oracle Fusion Middleware Upgrade Assistant. Instead, you use various other tools including the Patch Set Assistant. Whereas upgrading...
Obiee Summary Advisor
OBIEE 11g - Exalytics Summary Advisor

The Summary Advisor is a tool that evaluate the collected statistics (Cache hits are ignored) recommend aggregates and creates an aggregate persistence script After the Summary Advisor Statistics...



Share this page:
Follow us:
Task Runner