Forum Discussion
Using Function.InvokeAfter() function to stop the SQL connection query being executed multiple times
the first query is obviously running on the server, but as it's a custom SQL Power BI doesn't create it's own 'native query'
now imagine following scenario:Query1 - connection to the SQL database, select a table
Query2 - reference Query1, select 2 columns, filter and group by with sum (all done using the UI)Query3 - reference Query1, select 3 different columns, group by and count (again, only using the UI)
In this scenario actions in Query2 and Query3 can both be performed by PowerBI and SQL. As long as the query folding is active the actions are happening on the server - that means that PowerBI 'writes' the actual SQL that is executed on the server (the 'native query'), as there was no SQL provided in the beginning. Does this clarify?
Ad. 2 Native Query is the SQL that is automatically generated by Power Query when you shape your query with the UI - if you provided custom SQL as the source, then query folding is not happening, see this article
https://devinknightsql.com/2016/07/03/power-bi-checking-query-folding-with-view-native-query/
I just want to clarify something.
Query1 is a SQL Server connection. This connection has a SQL Statement (a complex statement). I tested this statement in SSMS first to make sure it's correct, then copy and paste into the Power Query SQL Statement box.
When I execute Query1 I can see it is executing against the source SQL Server from SSMS. To me, this means Query1, at the point of the SQL Statement, is being query folded. - Am I correct here?
The SQL Statement step is the 'Source' step in the Applied Steps. The 'Source' step is the very first Applied Step. No other steps are before it which could make it not query fold, and also taking into consideration SSMS is showing the query is being executed, this 'Source' step IS being query folded. However, when I right-click on the 'Source' step the View Native Query is greyed-out. This greyed-out part tells me it is not being query folded, but it's the first step and I can see it executing on the source SQL Server via SSMS - Is this SQL Statement/first step being query folded or not? (if it's not being queried folded then how is the data getting into Power Query?)
Thanks.
the first query is obviously running on the server, but as it's a custom SQL Power BI doesn't create it's own 'native query'
now imagine following scenario:Query1 - connection to the SQL database, select a table
Query2 - reference Query1, select 2 columns, filter and group by with sum (all done using the UI)Query3 - reference Query1, select 3 different columns, group by and count (again, only using the UI)
In this scenario actions in Query2 and Query3 can both be performed by PowerBI and SQL. As long as the query folding is active the actions are happening on the server - that means that PowerBI 'writes' the actual SQL that is executed on the server (the 'native query'), as there was no SQL provided in the beginning. Does this clarify?Yes, thank you.