Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Best practice - SQL statement v Query editor

What is considered more performant when importing data from a SQL datasbase to PBI desktop - filtering with a SQL statement prior to query editor or using the query editor to filter data? I'm reading conflicting opinions - some say that using a SQL statement is kinder on our DBAs while others are saying the query editor allows for more effecient query folding. 

 

Has an official agreement been reached or can someone point me to some documentation?  Would like to understand for best practices going forward.

 

Thank you! 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

It really depends on the complexity of a query, supported query folding sources, and database drivers. If you have a lot of transformations/steps in your query, some of these steps might not be folded correctly, therefore SQL statements are prefered in this case. To make sure your query is folded, you can always check by right clicking on a step, and select View Native Query. If the option is greyed out, that step is not folded, which lead to the susequent steps not being folded as well.  

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

It really depends on the complexity of a query, supported query folding sources, and database drivers. If you have a lot of transformations/steps in your query, some of these steps might not be folded correctly, therefore SQL statements are prefered in this case. To make sure your query is folded, you can always check by right clicking on a step, and select View Native Query. If the option is greyed out, that step is not folded, which lead to the susequent steps not being folded as well.  

Anonymous
Not applicable

Thanks @Anonymous.  Maybe that's why I haven't been able to find a clear answer yet 🙂 

Hello @Anonymous 

If you can, work with your SQL folks and have them create views to feed your PowerBI model.  That way, if there are changes that come from the SQL side they will flow into your views.  It also makes tracking down issues easier because they can see what data you are using.

Take a look at this article from the SQLBI guys.

https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

Anonymous
Not applicable

Thank you @jdbuchanan71.  Trust me when I say I have advocated for making the changes in the views many many times!  But alas, we work with what we have.

@Anonymous With SQL sources, working from BI-optimized views is the ideal, but as you're seeing this can be a bottleneck if you can't author these views yourself and your IT doesn't have the desired responsiveness. I'd try to see if my entire PQ query folds, in which case you're probably fine.

 

If the query doesn't entirely fold, start by identifying the choking point(s). For instance Trim folds (=combination of rtrim and ltrim in SQL) but Clean doesn't. Push these non-folding steps as far down the process as possible. Usually you want to move your column and row filters up in your chain of PQ steps. Once that's optimized, consider porting the part that folds to dataflows, then make it your de facto source "view" (in reality it's a datalake-hosted CSV). You can then use that dataflow as the source for the remaining non-folding steps of your original query.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals
Anonymous
Not applicable

Thank you @otravers .  I haven't played around with dataflows yet - I'll check that out! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.