OBIEE - Query Compiler

1 - 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.

3 - Steps

3.1 - Parsing

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

3.2 - 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)

3.3 - 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:

3.3.2 - 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.

3.4 - 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.

4 - 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 :

5 - Support

5.1 - 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

dat/obiee/obis/query_processing/query_compiler.txt · Last modified: 2017/09/13 16:16 by gerardnico