Custom SQL vs Power Query

[AmBigsPBI]

Hello,

My organization basically wants everything to be done as an import with custom SQL in the advanced editor. I do not have a lot of experience with SQL, but I can use Power Query editor to bring in data tables and make joins and the required transformations. I am trying to get a better understanding of the limitations of Power Query or using custom SQL code. I have seen many posts that tell you to never use custom SQL code, but my guys are telling me to always use custom SQL code and I am left confused.

1. When choosing to import using PQ Editor and doing all the work in there, can that still be connected to a gateway to schedule refreshes to the dataset? If done correctly, hopefully keeping the query foldable, should this strain the gateway? They make it sound like only SQL is fast enough to not strain the gateway.

2. If I am bringing in a table and then making my transformations, does that always load the entire table even if I have used remove columns, or edited the PQ M Language to "Select Columns" at the source? They say that it brings in the entire table and so even if I remove the remaining fields I don't need it is too slow.

3. Many of the tables in my database also have equivilent "Views", though I have never used them because I didn't understand the difference. I have seen some posts saying that you should use the View instead of the table if you want to combine custom SQL with PQ, can someone expand on when Views would be useful?

4. When using Direct Query (I have only ever tried Imports), is there anything I need to know about using PQ that is different than with using Imports? Will using Direct Query, when published to the Web Service, automatically update itself when the report is opened online?

Thanks so much!

[BA_Pete]

Hi @AmBigsPBI ,

Your second sentence is exactly the reason why I personally never use custom SQL, and don't recommend it either: PBI devs tend to know M and DAX, but not necessarily SQL. This can cause difficulties for both current and future PBI devs and, in my opinion, creates a larger efficiency risk: It's more likely that someone who doesn't know SQL very well will create custom SQL that is really inefficient, as opposed to creating queries in a favoured and properly-understood language, such as M.

-1- Yes, M language queries can be refreshed through gateways just as well as SQL or anything else. You've already mentioned the key to this whole discussion here: query folding. If you're good enough with Power Query/M to maintain query folding, then PQ is sending optimised SQL to the source in the background anyway.

Is PQ's SQL code as well-optimised as a decent SQL dev's code? Possibly not, but PBI devs don't tend to be SQL devs either, so you'd be hard-pushed to scrape even a fraction of a second off the processing time of the auto-generated SQL.

-2- No. Again, this comes down to query folding. By default, PQ brings in the top 1,000 rows as a preview. Any foldable transformations that are subsequently done are converted into SQL before being sent to the source, so the whole data is NEVER imported as long you maintain query folding.

-3- Views are useful if you want to use a specific data combination from the server many times. For example, you may have two server tables that each have 20 columns, one for orders received, and one for employees receiving. You could write a view that just takes two columns from one table and joins it with three columns from the other, then this combination can just be queried as if it were a native table in the DB, and it will also support query folding in the same way. In terms of efficiency, a view may not be quite as efficient as doing the whole process in PQ and maintaining query folding, or writing custom SQL (as a view has to be materialised in full on the server before being queried), but you get efficiency in the reduced dev time because the work has already been done.

-4- If you understand query folding, then you understand Direct Query from the Power Query point of view. You can't do anything in PQ that will break folding as then you can't query the DB directly when you want to update your data.

There are also a number of DAX expressions that can't be used for the same reason (i.e. that everything you do MUST be convertable to SQL).

Direct Query will query the source when you first open the report, as well as any time the end user hits the 'Refresh Visuals' button.

If your organisation are so worried about gateway load to the point that everything must be custom SQL rather than folded M code, then they probably don't want to know what Direct Query does to the gateways!

Pete