OBIEE - Query Compiler
> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Server (OBIS|nqsserver|nqs) > OBIEE - Query Processing (What happens when you issue an Logical SQL Request (through Answers for instance) ?
Table of Contents
1 - About
The query compilation is composed of the following five phases:
- 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.
2 - Articles Related
3 - Steps
3.1 - Parsing
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.
3.3.1 - Navigation
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;
- and Multi-pass SQL operations.
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.
- OBIEE includes “DISTINCT” clause in SQL queries as soon as the primary key of a table is not selected.
4 - The statistics
OBIEE have also some statistical information such as :
- Row Counts
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