Tracing a Power BI DirectQuery Refresh in Oracle

 In today's video I have demonstrated how Power BI can use DirectQuery to query an Oracle database and refresh reports without actually storing the data in the Power BI file (as would be done if "Import" was used instead of DirectQuery).

I have used SQL Tracing in the Database Instance to identify the SQL statement that Power BI executes

For the first visual in Power BI which shows total salary by Department, the Power BI module and SQL statement are identified as :

MODULE NAME:(msmdsrv.exe) 
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)

sqlid='c8a9qd2dzks8h'

SELECT * FROM (
SELECT
*
FROM 
(

SELECT
"t1"."DEPARTMENT_NAME" "c6", SUM ( "t4"."SALARY" )
 "a0"
FROM 
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
    "$Table"."FIRST_NAME" as "FIRST_NAME",
    "$Table"."LAST_NAME" as "LAST_NAME",
    "$Table"."EMAIL" as "EMAIL",
    "$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
    "$Table"."HIRE_DATE" as "HIRE_DATE",
    "$Table"."JOB_ID" as "JOB_ID",
    "$Table"."SALARY" as "SALARY",
    "$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"

 LEFT OUTER JOIN 

(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
    "$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on 
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)

GROUP BY "t1"."DEPARTMENT_NAME"
)
 "MainTable"
WHERE 
(

NOT(
(
"a0" IS NULL 
)
)

)

ORDER BY "a0"
DESC
,"c6"
ASC
 ) WHERE ROWNUM (lessthan) 1001

For the second visual in Power BI which shows count of employees in each Department, the Power BI module and SQL statement are identified as :

MODULE NAME:(msmdsrv.exe)
CLIENT DRIVER:(ODPM.NET : 23.6.0.0.0)

sqlid='dh7nbfqsy942q'

SELECT
"t1"."DEPARTMENT_NAME" "c6",
COUNT("t4"."EMPLOYEE_ID")
 "a0"
FROM 
((
select "$Table"."EMPLOYEE_ID" as "EMPLOYEE_ID",
    "$Table"."FIRST_NAME" as "FIRST_NAME",
    "$Table"."LAST_NAME" as "LAST_NAME",
    "$Table"."EMAIL" as "EMAIL",
    "$Table"."PHONE_NUMBER" as "PHONE_NUMBER",
    "$Table"."HIRE_DATE" as "HIRE_DATE",
    "$Table"."JOB_ID" as "JOB_ID",
    "$Table"."SALARY" as "SALARY",
    "$Table"."COMMISSION_PCT" as "COMMISSION_PCT",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID"
from "HR"."EMPLOYEES" "$Table"
) "t4"

 LEFT OUTER JOIN 

(
select "$Table"."DEPARTMENT_ID" as "DEPARTMENT_ID",
    "$Table"."DEPARTMENT_NAME" as "DEPARTMENT_NAME",
    "$Table"."MANAGER_ID" as "MANAGER_ID",
    "$Table"."LOCATION_ID" as "LOCATION_ID"
from "HR"."DEPARTMENTS" "$Table"
) "t1" on 
(
"t4"."DEPARTMENT_ID" = "t1"."DEPARTMENT_ID"
)
)

GROUP BY "t1"."DEPARTMENT_NAME" 

Thus, every refresh runs a number of queries -- some to synchronise the schema from Oracle to Power BI and others to refresh the numbers to present in the Visuals. This proves that the actual load of computing the GROUP BY and aggregations is in the *database instance* (because that is where the data actually resides) and not in the Power BI file (because no data is copied into the Power BI file)