cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MDKCLIRResearch
Helper III
Helper III

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

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.

 

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Solution Sage
Solution Sage

@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. 

View solution in original post

1 REPLY 1
Daryl-Lynch-Bzy
Solution Sage
Solution Sage

@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. 

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!