OBIEEintermediate
BI Server (NQSServer) Query Processing
Explain how the BI Server turns logical SQL into an optimized physical query plan, including navigation, aggregate awareness, and federation.
The BI Server is where the abstraction pays off. An analyst writes against the business model; the server decides how to answer it.
It's like a GPS route planner: you say "get me downtown," and it silently chooses the highway, a shortcut through side streets, or a combination — you never see the routing logic, only the fastest path taken.
Key Concepts
1
The stages.
logical SQL arrives
| parse against the RPD metadata
| resolve logical columns to their mappings
| evaluate every LTS that could satisfy the request
| apply navigation: content levels, fragmentation filters
| choose the sources
v
generate physical SQL, one statement per source
| execute, possibly in parallel
v
combine, aggregate, and return2
Navigation is the interesting part, and it is where aggregate navigation and fragmentation are applied.
The server compares what the query needs against what each LTS declares it holds, and picks accordingly.
3
Federation is the other. When a query spans two databases, the server issues a statement to each and
performs the join itself:
4
query: sales by product, where sales are in Oracle and the
product master is in SQL Server5
-> SELECT ... FROM oracle_sales GROUP BY product_id
-> SELECT product_id, name FROM sqlserver_product
-> join, in the BI Server6
That works, and the join happens outside the database, in memory. So it is correct and expensive, and a
federated query joining two large result sets is a common cause of a server under memory pressure.
7
The mitigation is to push as much as possible down — filter and aggregate in each source so the server joins
small results.
8
What the server does that the database cannot. Combining two facts at a common grain without a fan
trap; choosing an aggregate table transparently; applying row-level security from the RPD; serving from
cache.
9
The diagnostic tool is the query log. At a sufficient logging level it records, per query, the logical
SQL received, the physical SQL generated, the execution time of each, and how the rows were combined.
10
That is where you see what actually happened. "The report is slow" becomes "the physical SQL took 40
seconds" — a database problem — or "the physical SQL took 2 seconds and the server spent 38 combining" — a
model problem.
11
Reading the generated physical SQL is also how you verify that aggregate navigation chose what you expected,
which is otherwise invisible.
12
What the interviewer is probing.1. "Walk through what happens to a request." Probing: the mechanism. Stalls: "It runs SQL."
Moves up: logical SQL arrives, the server checks cache, picks logical table sources, generates
physical SQL, executes it, then joins and aggregates results.
13
2. "Where do you see the generated physical SQL?" Probing: the practical skill. Stalls: "You
cannot." Moves up: the session log at an appropriate logging level, or the nqquery log — which is
the first place to look for any performance question.
14
3. "What does it mean when the server does the join itself?" Probing: the diagnosis. Stalls:
"Nothing." Moves up: it had to pull both sets back and join in memory, which is far slower than a
database join and usually signals a modelling problem.
15
4. "What controls the logging detail?" Probing: the detail. Stalls: "A setting." Moves up:
the logging level on the user or session variable, with level 2 showing logical and physical SQL —
and it should not be left high in production.