All topics
6 / 55
OBIEEintermediate

Usage Tracking & Query Auditing

Explain how OBIEE captures query-level usage statistics for performance tuning, adoption analysis, and auditing.

Usage tracking records every logical query the BI Server executes. It is the only reliable source for which reports are used, by whom, and what they cost.

It's a car's trip computer silently logging every drive — speed, distance, fuel used — so that later you can see which routes are wasting gas, without having to remember or guess.

Key Concepts

1
Enabled in NQSConfig.INI, writing either directly to a table or to files loaded later:
NQSConfig.INI
2
    [USAGE_TRACKING]
    ENABLE = YES;
3
    direct insert   simple; the write is on the query path
    file mode       safer under load; decoupled, loaded by an ETL
4
Under heavy use, direct insert makes the tracking part of every query's latency, which is the argument for files.
5
S_NQ_ACCT is the table, and its useful columns:
S_NQ_ACCT
6
    USER_NAME             who
    START_TS, END_TS      when, and how long
    TOTAL_TIME_SEC        elapsed
    NUM_DB_QUERY          how many physical queries it generated
    ROW_COUNT             rows returned
    SUBJECT_AREA_NAME     which model
    CACHE_IND_FLG         did it hit cache
    QUERY_TEXT            the logical SQL
7
What it answers that nothing else does.Which reports are actually used. A catalog of four hundred analyses, of which sixty have run this year. That is the evidence for retiring the rest, and it is otherwise unavailable.
8
Where the cost is. Sort by total time — calls multiplied by duration — not by the slowest single query. A dashboard taking 4 seconds and run 8,000 times a day costs far more than a monthly report taking 3 minutes.
9
Whether caching is working. CACHE_IND_FLG gives you the hit rate. A rate near zero after seeding means the seeding queries do not match what users run.
CACHE_IND_FLG
10
Who is affected before they complain. A query that started failing or slowing is visible here before the support call.
11
Adoption is the other use. Usage by user and department tells you whether a deployment landed. Reports built and never opened are a different problem from reports that are slow, and only this distinguishes them.
12
The practical note. Build a small subject area over S_NQ_ACCT so the BI team analyses usage with the tool itself. Querying it by hand means nobody does it regularly.
S_NQ_ACCT
13
And the caveat. The table grows continuously and is rarely purged. On a busy deployment it becomes one of the larger tables in the warehouse, so a retention policy belongs in the setup rather than being added after somebody notices.
14
What the interviewer is probing.1. "What does usage tracking give you?" Probing: the value. Stalls: "Statistics." *Moves up:* who ran what, how long it took, rows returned and whether cache was hit — which is the evidence base for tuning and for retiring content.
15
2. "How do you use it before a migration?" Probing: the practical application. Stalls: "You do not." Moves up: to find which dashboards are actually used, so you migrate the few that matter instead of all of them.
16
3. "What do you look for to find tuning targets?" Probing: the method. Stalls: "The slowest." Moves up: total time across all executions — a 5-second report run 10,000 times a day matters more than a 5-minute monthly one.
17
4. "What is the setup consideration?" Probing: the operational angle. Stalls: "None." *Moves up:* it writes a row per query, so the table grows fast and needs partitioning and a purge policy.
Use cases:usage-trackingauditingperformance-monitoring