OBIEE - Query Compiler

Bi Server Architecture

About

The query compiler is responsible of the compilation of a logical sql in the query processing process of BI Server.

The query compilation is composed of the following five phases:

  1. parsing,
  2. navigation,
  3. rewrites,
  4. and code generation.

The final output of the query compiler is an executable code. This code is passed to the execution engine that is then responsible for executing the code in parallel.

You can then have two SQL statement at the same time against one database.

Steps

Parsing

In the first compilation phase, the multi-threaded parser accepts the logical sql and generates a parse tree as its output.

Logical Request Generation

Subsequently, the logical request generation component is responsible for instantiating the inferred aggregation in the simplified SQL supported by the Oracle BI Server. OBIEE - Logical Request (before navigation)

Two major phases: Navigation and Rewrite

The navigation and rewrite phases do the bulk of the work in compiling a query. The output of these two major phases is an execution plan that is then fed into the code generation phase.

The navigator is responsible for the content-aware data federation capabilities.

The input to the navigator is a logical request tree describing the precise semantics of the requested data while its output is the initial physical execution plan. The navigator exploits knowledge of content to eliminate the predominant majority of traditional multi-database joins.

It also has built-in support for common business analytics such as:

Rewrite

Once the navigator generates the initial physical execution plan, the rewrite phase of the compiler is responsible for distributed relational query optimization and optimal native SQL generation.

This phase covers:

  • (i) Multidatabase join plan generation
  • (ii) Function shipping
  • (iii) Functional compensation analysis
  • and (iv) Optimized SQL generation.

The Oracle BI Server’s join engine is seamlessly invoked when necessary. Join plans are constructed to maximize collective function shipping down to the source databases.

Optimal function shipping reduces loads on the source database and the network. The most important query processing elements to function ship include:

  • GROUP BY and aggregation;
  • Filters
  • and Multi-pass SQL operations.

Equivalence Processing/Preserving

Aggregate and filter rewrites through a fragmentation design may push aggregates and filters through the tree (past operators such as joins, union alls, etc.) down to the database, thus reducing database load and network traffic. Both WHERE and HAVING filters may also be pushed to the database, depending on the GROUP BY clause.

Code Generation (Compiler)

Code generation is responsible for producing the native query processing language directives to communicate with heterogeneous, remote databases (i.e. physical SQL generation).

It is also responsible for generating the code for any remaining query processing that has not been function shipped to remote databases.

Which function is shipped, is configurable through the Features tab of a database in the physical layer.

This includes the insertion of parallel execution directives for the Analytics execution engine.

For instance:

  • OBIEE includes “DISTINCT” clause in SQL queries as soon as the primary key of a table is not selected.

The statistics

The Oracle database otpimizer rely on statistics to find the best way to execute a SQL.

OBIEE have also some statistical information such as :

Support

None of the fact tables are compatible with the query request

Error Details
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
State: HY000. Code: 2053177616. [NQODBC] [SQL_STATE: HY000] 
[nQSError: 10058] A general error has occurred. 
[nQSError: 14020] None of the fact tables are compatible with the query request Table.Column. 
(HY000) SQL Issued: SELECT Table.Column saw_0 FROM MY_SUBJECT_AREA ORDER BY saw_0

Check that for the logical level of the logical column (Table.Column), you have a logical table source with the same logical level





Discover More
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 Database Features
OBIEE - Database Features Parameters

When you create a database in the OBIEE physical layer some databases features parameters are automatically checked depending on the type of your database. database section This features are mostly...
Obiee Query Log
OBIEE - Physical SQL

The physical SQL is the SQL that send the BI Server to the data sources in order to retrieve data. This SQL is most of the time generated by the query compiler during the query processing of the logical...
Bi Server Architecture
OBIEE - Query Processing (What happens when you issue an Logical SQL Request (through Answers for instance) ?

Query processing in OBIEE Logical SQL Request Logical Request Generation (Logical SQL Request with security) Query compilation Cache service. The server will check to see if this request can...
Obiee Combine With Similar Request
OBIEE 10G - Reporting on Multiple Subject Area (Advanced Logical SQL)

In 11G, you can easily joins two subject areas. This article shows advanced logical SQL statement for the sake of knowledge and was written on a 10G because within an answer, it was possible through the...



Share this page:
Follow us:
Task Runner