Everything is SQL – the story of OBIEE

As one can imagine, SQL is the main engine to start loading a report in OBIEE. In principle, one has two kinds of queries. One query runs against the Oracle DBMS. This is the physical query that we know from any Oracle client. It can be found on the main main. Choose New > Analysis > Create Direct Database query. See image:

If one enters the page where such query may be formulated, one sees a rather Spartan looking page where the query may entered. It is very rudimentary, but, heck, what do you want more. Look at my interface page:

The name of the so-called connection pool can be found in the Oracle BI Administration Tool. The actual results from the query can be found in the “results” tab.

Another type of SQL is the logical SQL. This can be found in Administration > Manage Sessions. To avoid, results being returned from cache, one may think of starting the Call SAPurgeAllCache() command. This can be run from Administration > Issue SQL.
Under Administration > Manage Session, one may find the so-called logical SQL. This may look like:

SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/prodney/TomFinaal6',PREFERRED_CURRENCY='USD';SELECT
   0 s_0,
   "1_TOM"."WINKEL1"."COUNTRYID" s_1,
   "1_TOM"."WINKEL1"."FILNR" s_2,
   "1_TOM"."WINKEL1"."ID" s_3,
   "1_TOM"."WINKEL1"."OPGEBNR" s_4,
   "1_TOM"."WINKEL1"."PLAATS" s_5,
   "1_TOM"."WINKEL1"."REGIO" s_6,
   "1_TOM"."WINKEL1"."REGIONR" s_7,
   "1_TOM"."WINKEL1"."SOORT" s_8,
   "1_TOM"."WINKEL1"."WINKELGROEP" s_9,
   "1_TOM"."WINKELWEEKOMZET"."OMZETAH" s_10,
   AGGREGATE(NTILE("1_TOM"."WINKELWEEKOMZET"."OMZETAH",4) BY "1_TOM"."WINKEL1"."COUNTRYID") s_11,
   AGGREGATE(NTILE("1_TOM"."WINKELWEEKOMZET"."OMZETAH",4) BY "1_TOM"."WINKEL1"."OPGEBNR") s_12,
   AGGREGATE(NTILE("1_TOM"."WINKELWEEKOMZET"."OMZETAH",4) BY "1_TOM"."WINKEL1"."REGIONR") s_13,
   CAST(NULL AS INTEGER) s_14,
   REPORT_SUM("1_TOM"."WINKELWEEKOMZET"."OMZETAH" BY "1_TOM"."WINKEL1"."COUNTRYID") s_15,
   REPORT_SUM("1_TOM"."WINKELWEEKOMZET"."OMZETAH" BY "1_TOM"."WINKEL1"."ID") s_16,
   REPORT_SUM("1_TOM"."WINKELWEEKOMZET"."OMZETAH" BY "1_TOM"."WINKEL1"."OPGEBNR") s_17,
   REPORT_SUM("1_TOM"."WINKELWEEKOMZET"."OMZETAH" BY "1_TOM"."WINKEL1"."REGIONR") s_18
FROM "1_TOM"
WHERE
(("JAAR"."JAAR" = 2015) AND ("WINKELWEEKOMZET"."OMZETAH" > 0))
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 5000001 ROWS ONLY\

This can be run from Administration > Issue SQL.

In the log, one sees the logical SQL, followed by a translation into a physical SQL.

This Physical SQL needs a little rewriting and it can be fired directly from any SQL client.

This translation is very useful as it may help to identify the fields that are used to create a report from.