1. Building Semantic Models in Oracle Analytics Cloud
  2. Before You Begin
  3. Introduction to Semantic Models
  4. How Does a Semantic Model Query Data?

How Does a Semantic Model Query Data?

The Oracle Analytics query engine interprets Logical SQL queries and generates optimized Physical SQL queries to data sources as specified in a semantic model.

Oracle Analytics Query Engine

The Oracle Analytics query engine is the backbone of Oracle Analytics' governed and self-service analytics functionality. The query engine provides centralized data access, computes calculations, and enables data governance by creating a pipeline through which anyone can consume information specific to their application roles across their enterprise. The query engine is central to data visualizations, dashboards, ad-hoc queries, mobile access, enterprise reporting, data flows, and more. The semantic model functions as the brain of the query engine.

The Oracle Analytics query engine maintains the logical data model and provides client access to the model using ODBC connectivity or native APIs, such as OCI for the Oracle Database.

Logical SQL Queries

The Oracle Analytics query engine uses the semantic model's metadata to translate Logical SQL queries from workbooks, dashboards, and analyses into physical SQL queries against the mapped data sources that supply the data. The Oracle Analytics query engine also transforms and combines the physical result sets and perform final calculations.

This diagram shows how a Logical SQL query traverses the layers of a semantic model to query the data sources.


Semantic Model SQL Request

Logical Request Transformation Example

This example shows how the Oracle Analytics query engine interprets and converts a Logical SQL query to a Physical SQL query.

The Oracle Analytics query engine receives the following simple client request: 

SELECT
"D0 Time"."T02 Per Name Month" saw_0,
"D4 Product"."P01 Product" saw_1,
"F2 Units"."2-01 Billed Qty (Sum All)" saw_2
FROM "Sample Sales"
ORDER BY saw_0, saw_1

The Oracle Analytics query engine converts the Logical SQL query into a Physical SQL query: 

WITH SAWITH0 AS (
select T986.Per_Name_Month as c1, T879.Prod_Dsc as c2,
   sum(T835.Units) as c3, T879.Prod_Key as c4
from
   Product T879 /* A05 Product */ ,
   Time_Mth T986 /* A08 Time Mth */ ,
   FactsRev T835 /* A11 Revenue (Billed Time Join) */
where ( T835.Prod_Key = T879.Prod_Key and T835.Bill_Mth = T986.Row_Wid)
group by T879.Prod_Dsc, T879.Prod_Key, T986.Per_Name_Month
)
select SAWITH0.c1 as c1, SAWITH0.c2 as c2, SAWITH0.c3 as c3
from SAWITH0
order by c1, c2