Power BI Desktop sql native queries vs Power Query Editor native queries performance differences

[PBIFanHook7]

I have read where the performance difference between using native SQL queries within Power BI Desktop's "Get Data" options verses using Power Query Editor (Get Data) and applying transformations in PQE, depends on several factors, primarily related to query folding. 

However, I have a simple Oracle SQL Native Query (like below) which I bring into Power BI Desktop's ("Get Data" options) and I DO NOT need to apply any PQE transformations to it, 

(1) Would this approach have a negative impact on performance?

(2) Would the below SQL Native Query (brought into Power BI Desktop) still execute on the source database and would the results be returned to Power BI Desktop (and not PQE)?

(3) If there is not a need to apply transformations in PQE, is it be okay to use SQL Native Queries within Power BI Desktop?

Simple SQL Native Query:

SELECT TT.*,
CASE WHEN TT."Color" = 'Red' THEN 1
WHEN TT."Color" = 'Blue' THEN 2
WHEN TT."Color" = 'White' THEN 3
WHEN TT."Color" = 'Green' THEN 4
ELSE 99
END AS "Color Sort Order"
FROM TestTable TT

Note the above SQL Native Query returns only about 10 columns (so I used the TT.*) and it returns about 5,000 rows. I am using DirectQuery Mode.

Thanks in advance for all help, suggestions, recommendations, etc.

[v-tejrama]

Hi  @PBIFanHook7 ,

Thank you @Ritaf1983  for the response provided!

Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Thank you for your understanding!

[PBIFanHook7]

Yes, my questions were answered by @Ritaf1983. 

[Ritaf1983 · Accepted solution]

Hi @PBIFanHook7 

It is generally best practice to push transformations as close to the source as possible. If you cannot create a proper view in Oracle, then using a native SQL query in Power BI is fine. Performance considerations will still apply in DirectQuery mode, since it is always slower compared to Import mode. The SQL query you wrote will execute on the Oracle database itself, and the results will be returned to Power BI.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[PBIFanHook7]

Thanks for the excellent response. You provided what I needed.