OBIEE - Logical Sql
About
The logical SQL is the SQL that understand BI Server. It's a full Ansi 92 SQL (including subqueries and derived tables), plus special functions (SQL extensions) like AGO, TODATE, EVALUATE, and others.
When you have a connection with BI Server through:
- or Jdbc
a client tool such as :
- presentation service (which use an ODBC connection)
can send it a Logical SQL to retrieve a data set.
The Oracle BI Server transforms the Logical SQL into physical SQL through a process known as query processing that can be understood by source databases.
In Presentation Service, when you create an answer or a dashboard prompt, you create in fact a logical SQL that you can see in the Advanced Tab of the answer. Then you have to use its syntax in all their elements such as :
- formulas
- and filter
You can retrieve it in the “Logical SQL” column of usage tracking log table.
Join information, aggregation functions, and Group By clauses are not needed when querying through the Analytics server.
Article
Aggregation
Function
Other
Join Clause
The SELECT statement in Logical SQL differs from standard SQL in that tables do not need to be joined. Any join conditions supplied in the query are ignored because the join conditions are predefined in the Oracle BI repository.
But the join clause is supported between two SELECT statement: OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)
From Clause
SELECT "My Column" FROM "My SubjectArea"."My Table"
Is equivalent to
SELECT "My Table"."My Column" FROM "My SubjectArea"
