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.
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.
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!
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).
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 :)
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 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!!
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"
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 - 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?
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.
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
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?
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?
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.
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.
