Is there a reference that explains how to call SQL queries that address Incorrect Syntax errors?

[MDKCLIRResearch]

I have been working on a Power BI report using DirectQuery. 

Due to constant issues with "Incorrect Syntax Near..." errors, we are considering switching to Import mode.

I really like the idea of DirectQuery.  This would help us work with a large database changes frequently.  Additionally, it will keep the size of the Power BI small.   

I am looking for references, blogs or courses that address the following questions.  I have been through two courses on Power BI in Udemy.  Both are good and worthwhile.  

The questions I have are below. 

1. Are there references or courses that discuss how Power BI wraps native queries, views, and stored procedures and the fixes/workarounds needed to make this work in Power BI in DirectQuery and Import modes?
   
   I have seen: Execute SQL Server Stored Procedure With User Parameter In Power BI (c-sharpcorner.com).
   
   However, I have a question on how to modify a Power Query call to a stored procedure that has no parameters.
   
   
2. What modifications are required to avoid the "Incorrect Syntax Near..." errors for all supported SQL objects in DirectQuery and Import modes.
   
   
3. I have a SQL query that calls a SQL View. 
   
   The SQL View as implemented in SQL does not contain ORDER BY or TOP clauses. 
   
   I have an ORDER BY on the native query entered in Power BI that calls the SQL View. 
   
   This promptly tells you that ORDER BYs cannot be used with SQL Views.
   
   
   • Again, there is no ORDER BY in the SQL View being called.
     
     
   • The error occurs when an  ORDER BY is included in the outer native query that calls the SQL View (The view that has no ORDER BY clause).
     
     
   • I am guessing that this is caused by the way Power Query wraps the native query that is sends to SQL.
     
     
4. How would I see the M and DAX wrappers that Power BI sends to SQL? 
   
   If I add a native query as a data source in Power BI, how would I see the DAX or M wrappers around the Native Query?  This would give me some useful info and may explain why native queries are treated as Views. 
   
   
5. If I order columns from a  SQL View by specific columns in Power Query, does this modify the wrapper sent to the SQL Server? 
   
   Does this simply move the sorting to Power BI?  
   
   
6. For large datasets, I would think that the best strategy is to do sorting via ORDER BY on the SQL Server. 
   
   Otherwise SQL delivers an unordered bag of rows that must be  sorted by Power BI.

[Daryl-Lynch-Bzy · Accepted solution]

@MDKCLIRResearch - do you use DAX Studio?  DAX Studio - The ultimate client tool for working with DAX queries

This tool give you visibility over the Direct Query executed by Power BI.  This will help you to see the SQL created.  This code can be run with performance benchmarks in Dax Studio and also copied to SSMS to check performance.

This may help you to identify bottlenecks in either the Direct Query code created by Power BI or the SQL code in you database views.  The final bottleneck is the delay caused by the Data Gateway.

I wondering why you want Order By to be performed in SQL.  I am thinking that the Execution Plan generated creates an expense.  Ordering might be last step and potentially performed in the Power BI client rather than the server.  In fact you may find that the data is order twice, once in SQL by the Server DB and once in Power BI.

I don't think you should include any M transformation in your Power BI Direct Query.  Just connect to tables, views and there related views.

I will always recommend importing the data into Power BI because it is optimised for reporting.  SQL is not optimised for reporting.  You may also want to consider options for incremental loads and hybrid tables to refresh the data more frequently.