If your query is folding, it's a pretty good sign you need to rewrite your query properly. As a general principle there are usually benefits in pushing those changes as far back into the ETL as possible. Why? Firstly, when YOU write the TSQL you can tune it, comment it, make the WHERE clause SARGable and make the layout of the TSQL readable for the poor sod who is going to inherit your lazy drag and drop ETL. Secondly, query folding can blow out the plan cache on the source database server. For example, in TSQL you might use getdate(). The M query can instead think it is ever so helpfully doing the same thing by pushing a literal value back to the source server. The plan cache sees this as a different query EVERY TIME IT RUNS and fills up the cache with thousands of almost identical plans that are never going to be used. Finally, Power BI is just the presentation layer. If you're building business logic, sooner or later someone else is going to want the same logic surfaced a different way. If your complex calculation is pushed back to your centralised source of truth, everyone has the benefit of it. Not just those who happen to consume it through Power BI.
@@Greg0Connell then I think we're using "folding" in opposite meanings. Query folding means that all transformations fold into a single query. I think. Or that's what I understood from the video. Previously I had no idea what query folding means so I could be wrong.
@@BernatAgulloRosello Key point is that with query folding, Power BI is rewriting your TSQL query for you, the general assumption being that it's better for the Power BI server if that processing is pushed back to the source. The principle is fine. The execution is not always optimal though. I am yet to see a single example of query folding, where a human couldn't have done a better job writing the TSQL themselves. You can see this for yourself if you capture the code being executed on the source (eg with a profiler trace or extended events). Folding scrambles it into a completely avoidable cryptic mess. It cares naught for things like SARGability and plan cache bloat. But humans do. So by all means push the processing back to the source. Just be wary of letting Power BI do it for you.
@@Greg0Connell This dialogue is EXACTLY why I love the comments just as much as the original video. I learn so much from both. Thank you for the time/effort to post your comments. I don't understand all of it yet, but boy have you given me some stuff to think about.
I love this tips - However I would state that given all the effort to make using Power Query easy to learn and use (for 90% of tasks), it's a pity there has not been as much investment in making things like checking on Query Folding easy also
you prolly dont give a shit but if you guys are stoned like me atm then you can stream all the latest movies and series on instaflixxer. Been binge watching with my girlfriend recently :)
Hi there! I try to use this functionality but every time I try it opens a black window with sqldumper on it and it closes Power BI. do you know why this might happen? it worked once but not anymore... Thank you!!
I'm not exactly sure how to express what is going through my mind right now. i'm completely flabbergasted, dumb-struck, aghast. I have been using the Show Native Query to rebuild a number of my transformations and now I find out that it isn't quite the tool I and 100s of others thought is was. I AM so glad that you did this video and that I decided to watch it. I almost didn't since I expected it to be just more of the same. Lucky for me I clicked on it anyway.
@@cptdeadeye2112 I have a number of 'messy' data transforms on many of my data sources. The more I can push back on the server, the faster they process. Some of my data sources take 30+ transform steps, so the more I can offload, the better. I have documented as good as 2X less time to complete processing on a number of data refreshes. With this new knowledge I bet I can do even better.
I've always known "Query Folding" as "Pushdown" with the intermediate query engine even creating temporary stored procedures and temporary tables on the source where possible (and given permission to do so).
Awesome video, thanks. I do have a question though, I am running a simple stored procedure and applied step for top 100 rows. When I go to check the Diagnose Step, it doesn't add a separate folder like you have in your video for the diagnose step. I was wondering why I can't see that. Is that because my source is not odata feed and I am using sql server as source?
Hi Patrick - I am using SAP HANA database as my source, which tool shall I use to see the query folding logs similar to the Azure data studio which you are using for SQL server?
Really useful. But what if I have a chain of queries that reference earlier ones? How do they fold? E.g. Suppose I have a query (A) which can be fully folded back to the server. I then reference it as the source in query B. Can query B also fully be folded back to the server or does folding stop at the end of query A and mean query B always has to happen on my local laptop? Appreciate any advice you guys have - I'm an old-fashioned Excel guy who is still only learning to wield this new superpower :-)
Hi Patrick, wonderful video. when I load data from Azure Databricks it seems even the source step doesn't have query folding, is it the case that Databricks doesn't support any query folding? or Is there a way to "Activate it"
I am a beginner to query folding and would like to enable query folding to a data which is sourced to Power Query through OData. Please give your advice.
Patrick...one thing to add. I didn't see the "Tools" on the menu bar. I had to go to File --> Options --> Preview Features --> and select "Query Diagnostics". Restart app....you are done. For anyone looking. Great video!
Yes! Helps to make sure the transformations happen at the data source instead of getting all the data back and having to do that transformation in Power BI Desktop / Gateway / or the service. The data source usually can do it more optimally then on the Power BI side. Less overhead and faster operations.
What happens if the first few steps can be folded but subsequent steps cannot? Will it fold the first few steps, and let the mashup engine handle the rest, or will the mashup engine do all the steps?
Hi Patrick, I love these tips. I am trying with Azure Cost Management, but I am not sure I can get to select the diagnostic steps at all. I have not been able to say if the query folding is taking place actually
How do we check if Query folding is happening when connecting Power BI to Salesforce datasource using Power BI Salesforce Object connector ? Is incremental refresh from power Bi possible when connecting to Salesforce ?
We don't have access to salesforce. I don't know that it supports query folding to begin with. As the underlying connection with be API calls to the salesforce service, you could probably use Fiddler to see if the API calls are passing filters.
Haven't played with that data source specifically. Have you tried pulling in a table and renaming one column to see if that folds? If it isn't happening, there isn't really anything you can do. Maybe go to the ODBC driver, but that's pretty extreme.
The better question is how much query folding is supported. Not, if it is or not supported. Basic query folding may only let you do simple filters, while more complex ones will let you do things like grouping. A good indication that strong folding is supported is if the data source supports direct query.
"Insufficient permissions while starting the diagnostics listener. Consider running elevated." Is this a permanent aspect of the special tool or only while it's in preview?
If your query is folding, it's a pretty good sign you need to rewrite your query properly. As a general principle there are usually benefits in pushing those changes as far back into the ETL as possible. Why? Firstly, when YOU write the TSQL you can tune it, comment it, make the WHERE clause SARGable and make the layout of the TSQL readable for the poor sod who is going to inherit your lazy drag and drop ETL. Secondly, query folding can blow out the plan cache on the source database server. For example, in TSQL you might use getdate(). The M query can instead think it is ever so helpfully doing the same thing by pushing a literal value back to the source server. The plan cache sees this as a different query EVERY TIME IT RUNS and fills up the cache with thousands of almost identical plans that are never going to be used. Finally, Power BI is just the presentation layer. If you're building business logic, sooner or later someone else is going to want the same logic surfaced a different way. If your complex calculation is pushed back to your centralised source of truth, everyone has the benefit of it. Not just those who happen to consume it through Power BI.
I don't think it's a good general rule. For most transformations better on source than inside powerBI
@@BernatAgulloRosello That's exactly what I said.
@@Greg0Connell then I think we're using "folding" in opposite meanings. Query folding means that all transformations fold into a single query. I think. Or that's what I understood from the video. Previously I had no idea what query folding means so I could be wrong.
@@BernatAgulloRosello Key point is that with query folding, Power BI is rewriting your TSQL query for you, the general assumption being that it's better for the Power BI server if that processing is pushed back to the source. The principle is fine. The execution is not always optimal though. I am yet to see a single example of query folding, where a human couldn't have done a better job writing the TSQL themselves. You can see this for yourself if you capture the code being executed on the source (eg with a profiler trace or extended events). Folding scrambles it into a completely avoidable cryptic mess. It cares naught for things like SARGability and plan cache bloat. But humans do.
So by all means push the processing back to the source. Just be wary of letting Power BI do it for you.
@@Greg0Connell This dialogue is EXACTLY why I love the comments just as much as the original video. I learn so much from both. Thank you for the time/effort to post your comments. I don't understand all of it yet, but boy have you given me some stuff to think about.
SOOOOOOOO much easier to understand this concept from how you explained it. I couldn't make heads nor tails of the explanation Microsoft gave me.
I love this tips - However I would state that given all the effort to make using Power Query easy to learn and use (for 90% of tasks), it's a pity there has not been as much investment in making things like checking on Query Folding easy also
you prolly dont give a shit but if you guys are stoned like me atm then you can stream all the latest movies and series on instaflixxer. Been binge watching with my girlfriend recently :)
@Aryan Terrance Yup, I have been watching on instaflixxer for since november myself :D
Yet another fabuloustic video from you guys! I truly appreciate the form, your enthusiasm and that funny spice you add to it ;) This is bananas!
Hi there! I try to use this functionality but every time I try it opens a black window with sqldumper on it and it closes Power BI. do you know why this might happen? it worked once but not anymore... Thank you!!
I'm not exactly sure how to express what is going through my mind right now. i'm completely flabbergasted, dumb-struck, aghast. I have been using the Show Native Query to rebuild a number of my transformations and now I find out that it isn't quite the tool I and 100s of others thought is was. I AM so glad that you did this video and that I decided to watch it. I almost didn't since I expected it to be just more of the same. Lucky for me I clicked on it anyway.
I haven't even known about this, before.
What are you using it for?
@@cptdeadeye2112 I have a number of 'messy' data transforms on many of my data sources. The more I can push back on the server, the faster they process. Some of my data sources take 30+ transform steps, so the more I can offload, the better. I have documented as good as 2X less time to complete processing on a number of data refreshes. With this new knowledge I bet I can do even better.
I've always known "Query Folding" as "Pushdown" with the intermediate query engine even creating temporary stored procedures and temporary tables on the source where possible (and given permission to do so).
Awesome video, thanks. I do have a question though, I am running a simple stored procedure and applied step for top 100 rows. When I go to check the Diagnose Step, it doesn't add a separate folder like you have in your video for the diagnose step. I was wondering why I can't see that. Is that because my source is not odata feed and I am using sql server as source?
Hi Patrick - I am using SAP HANA database as my source, which tool shall I use to see the query folding logs similar to the Azure data studio which you are using for SQL server?
Patrick, These Diagnostic Tools do not appear in Power BI Services within a DataFlow. How could I execute this for a DataFlow Query?
Thanks Andrew
Really useful. But what if I have a chain of queries that reference earlier ones? How do they fold?
E.g. Suppose I have a query (A) which can be fully folded back to the server. I then reference it as the source in query B. Can query B also fully be folded back to the server or does folding stop at the end of query A and mean query B always has to happen on my local laptop?
Appreciate any advice you guys have - I'm an old-fashioned Excel guy who is still only learning to wield this new superpower :-)
What about Amazon redshift, does it allow query folding
I was just looking for an exemple on how to check if a query folding occures on non SQL Databases. Thank you Patrick
Always helpful, thanks so much!
How to do it in snowflake? It should be supported but in no circumstances can I get it to work😢
6:17 Comércio Mineiro, Família Arquibaldo... Meu cérebro bugou, por alguns minutos achei que eu não tava mais em um canal gringo.
Trying to keep it real! 👊
Hello sir i didn't understand please explain any other way
This is great, thanks Patrick!
Thank you! 👊
Awesome vid. Very helpful. I'm gonna go and see what kind of folding is taking place on some of my reports. Thanks!
Awesome! Thanks for watching! 👊
Hi Patrick, wonderful video. when I load data from Azure Databricks it seems even the source step doesn't have query folding, is it the case that Databricks doesn't support any query folding? or Is there a way to "Activate it"
How to do that on sql profiler
6:18 "Comércio Mineiro" 🤪😍
I am a beginner to query folding and would like to enable query folding to a data which is sourced to Power Query through OData. Please give your advice.
Great Video. Thank you! One question: How can I find out if query folding is happening when querying an Exchange Online mailbox?
Patrick...one thing to add. I didn't see the "Tools" on the menu bar. I had to go to File --> Options --> Preview Features --> and select "Query Diagnostics". Restart app....you are done. For anyone looking. Great video!
This vid should be embedded in the query folding section within MS Learn. Good stuff!
Hi
I am trying to apply incremental refresh where source is SAP Hana
but not able to do it,
can you make a video for it?
Hey Petrick, Thanks for video. I want know if MQuery is foldable query. I am using Mquery to extract my data from Azure Application Insights.
I understand that Query Folding is a good thing, right?
Yes! Helps to make sure the transformations happen at the data source instead of getting all the data back and having to do that transformation in Power BI Desktop / Gateway / or the service. The data source usually can do it more optimally then on the Power BI side. Less overhead and faster operations.
What happens if the first few steps can be folded but subsequent steps cannot? Will it fold the first few steps, and let the mashup engine handle the rest, or will the mashup engine do all the steps?
Great job, congrats! How to do this on direct query mode?
Hi Patrick, I love these tips. I am trying with Azure Cost Management, but I am not sure I can get to select the diagnostic steps at all. I have not been able to say if the query folding is taking place actually
Please tech slow
Thank :)
Excellent illustration! Thanks
Very helpful, thanks.
Glad it helped. Thanks for watching!👊
Muchas gracias por compartir esta información 💪🏼🥰
Yooooo
How do we check if Query folding is happening when connecting Power BI to Salesforce datasource using Power BI Salesforce Object connector ? Is incremental refresh from power Bi possible when connecting to Salesforce ?
We don't have access to salesforce. I don't know that it supports query folding to begin with. As the underlying connection with be API calls to the salesforce service, you could probably use Fiddler to see if the API calls are passing filters.
What are some blogs and forums you all use?
Thank you Patrick. If query folding is not happening for AWS Redshift as data source, anything can be done to enable it?
Haven't played with that data source specifically. Have you tried pulling in a table and renaming one column to see if that folds? If it isn't happening, there isn't really anything you can do. Maybe go to the ODBC driver, but that's pretty extreme.
Hey Patrick, where can I find a list of data sources that are supported by query folding?
The better question is how much query folding is supported. Not, if it is or not supported. Basic query folding may only let you do simple filters, while more complex ones will let you do things like grouping. A good indication that strong folding is supported is if the data source supports direct query.
"Insufficient permissions while starting the diagnostics listener. Consider running elevated." Is this a permanent aspect of the special tool or only while it's in preview?
and no way to run the Microsoft Store version as elevated. But you can succeed with the downloaded version.
thanks so much!
Really cool!
Appreciate that Pavel! Thanks for watching 👊
Excellent!