OBIEE - Usage Tracking S_NQ_ACCT Table

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics)

1 - About

The table S_NQ_ACCT is a database table that holds all data log of the usage tracking functionality.

Advertising

3 - Description of the Usage Tracking Data

3.1 - Functional

Group Column Description
BI Server COMPILE_TIME_SEC The time in seconds required to compile the query.
BI Server NODE_ID The host name of the computer where the Oracle BI Server is running. If this field has the value instance1:corea, see the bug 14083146
BI Server REPOSITORY_NAME The name of the repository the query accesses.
BI Server RUNAS_USER_NAME (IMPERSONATOR_USER_NAME 11g) “Default is Null. Varchar(128)
User Id of impersonated user. If the request is not run as an impersonated user, the value will be NULL.”
BI Server TOTAL_TIME_SEC The time in seconds that the Oracle BI Server spent working on the query while the client waited for responses to its query requests.
BI Server USER_NAME The name of the user who submitted the query.
Logical Query END_DT The date the logical query was completed.
Logical Query END_HOUR_MIN The hour and minute the logical query was completed.
Logical Query END_TS The date and time the logical query finished. The start and end timestamps also reflect any time the query spent waiting for resources to become available.
Logical Query QUERY_TEXT The logical SQL submitted for the query.
Logical Query QUERY_BLOB 11g Contains the entire logical SQL statement without any truncation.
Logical Query QUERY_KEY 11g - An MD5 hash key that is generated by Oracle Business Intelligence from the logical SQL statement.
Logical Query ROW_COUNT The number of rows returned to the query client.
Logical Query START_DT The date the logical query was submitted.
Logical Query START_HOUR_MIN The hour and minute the logical query was submitted.
Logical Query START_TS The date and time the logical query was submitted.
Logical Query SUBJECT_AREA_NAME The name of the business model being accessed.
Physical Query CUM_DB_TIME_SEC The total amount of time in seconds that the Oracle BI Server waited for back-end physical databases on behalf of a logical query.
Physical Query CUM_NUM_DB_ROW The total number of rows returned by the back-end databases.
Physical Query ERROR_TEXT “Default is Null. Varchar(250)
Error message from the back-end database. This column is only applicable if the SUCCESS_FLG is set to a value other than 0 (zero). Multiple messages will concatenate and will not be parsed by Oracle BI Server.”
Physical Query NUM_DB_QUERY The number of queries submitted to back-end databases in order to satisfy the logical query request. For successful queries (SuccessFlag = 0) this number will be 1 or greater.
Presentation Catalog PRESENTATION_NAME “Default is Null. Varchar(128)
Name of the Presentation Catalog in Oracle BI Presentation Services.”
Presentation Catalog QUERY_SRC_CD The source of the request, for example, Drill or Report.
Presentation Catalog SAW_DASHBOARD Path of the dashboard. If the query was not submitted through an Interactive Dashboard, the value will be NULL.
Presentation Catalog SAW_DASHBOARD_PG “Default is Null. Varchar(150)
Page name in the Interactive Dashboard. If the request is not a dashboard request, the value will be NULL.”
Presentation Catalog SAW_SRC_PATH The path name in the Oracle BI Presentation Catalog for the request.
Query Cache CACHE_IND_FLG “Default is N.
Y indicates a cache hit for the query, N indicates a cache miss.”
Query Cache NUM_CACHE_HITS “Default is Null. Number(10,0).
Indicates the number of times existing cache was returned.”
Query Cache NUM_CACHE_INSERTED “Default is Null. Number(10,0).
Indicates the number of times query generated cache was returned.”
Success SUCCESS_FLG The completion status of the query: 0 - The query completed successfully with no errors. 1 - The query timed out. 2 - The query failed because row limits were exceeded. 3 - The query failed due to some other reason.
ID 11g - The unique row ID (in order to link to the physical table S_NQ_DB_ACCT ?)
HASH_ID 11.9 ?
TENANT_ID 11.9 The name of the tenant of the user who ran the query ?
SERVICE_NAME 11.9 The name of the service.
Query ECID 11.9 The system-generated execution context ID.
User SESSION_ID 11.9 The ID of the session

OBIEE - Which Usage Tracking time measure should be used to gauge the performance of SQL retrieval time ?

Advertising

3.2 - Structure

Column Data Type Precision Scale Nullable
CACHE_IND_FLG CHAR 1 0 NOT NULLABLE
COMPILE_TIME_SEC DOUBLE 10 0 NULLABLE
CUM_DB_TIME_SEC DOUBLE 10 0 NULLABLE
CUM_NUM_DB_ROW DOUBLE 20 0 NULLABLE
ECID VARCHAR 1024 0 NULLABLE
END_DT DATETIME 1024 0 NULLABLE
END_HOUR_MIN CHAR 5 0 NULLABLE
END_TS TIMESTAMP 5 6 NULLABLE
ERROR_TEXT VARCHAR 250 0 NULLABLE
HASH_ID VARCHAR 128 0 NULLABLE
ID VARCHAR 50 0 NOT NULLABLE
IMPERSONATOR_USER_NAME VARCHAR 128 0 NULLABLE
NODE_ID VARCHAR 100 0 NULLABLE
NUM_CACHE_HITS DOUBLE 10 0 NULLABLE
NUM_CACHE_INSERTED DOUBLE 10 0 NULLABLE
NUM_DB_QUERY DOUBLE 10 0 NULLABLE
PRESENTATION_NAME VARCHAR 128 0 NULLABLE
QUERY_BLOB LONGVARCHAR 32768 0 NULLABLE
QUERY_KEY VARCHAR 128 0 NULLABLE
QUERY_SRC_CD VARCHAR 30 0 NULLABLE
QUERY_TEXT VARCHAR 1024 0 NULLABLE
REPOSITORY_NAME VARCHAR 128 0 NULLABLE
ROW_COUNT DOUBLE 20 0 NULLABLE
SAW_DASHBOARD VARCHAR 150 0 NULLABLE
SAW_DASHBOARD_PG VARCHAR 150 0 NULLABLE
SAW_SRC_PATH VARCHAR 250 0 NULLABLE
SERVICE_NAME VARCHAR 128 0 NULLABLE
SESSION_ID DOUBLE 10 0 NULLABLE
START_DT DATETIME 10 0 NULLABLE
START_HOUR_MIN CHAR 5 0 NULLABLE
START_TS TIMESTAMP 5 6 NULLABLE
SUBJECT_AREA_NAME VARCHAR 128 0 NULLABLE
SUCCESS_FLG DOUBLE 10 0 NULLABLE
TENANT_ID VARCHAR 128 0 NULLABLE
TOTAL_TIME_SEC DOUBLE 10 0 NULLABLE
USER_NAME VARCHAR 128 0 NULLABLE
Advertising

4 - Ibot

If the ibot are used for seeding cache then In the table which stores Usage Tracking data there is a column called “QUERY_SRC_CD”. A value of 'seed' in the Query source (QUERY_SRC_CD) is indicative of a cache seeding query. So you can differenciate the cache seeding query in usgae tracking based on the value in this column.

However in this case customer had set delivers destination as “dashboard”. In this case we cannot differenctiate between queries issued from the web versus ibot - Because ibot is run as impersonated user and there is no separate mechanism to track impersonation.

5 - Documentation / Reference