Slow queries impact on Power BI user interaction

[highxsky]

Hi all,

So I was working on a Power BI project lately and encountered performance issues, more specifically thrashing (my computer was very unresponsive and memory & disk were running nearly 100%).

My issues were partially related to many queries, including unoptimised ones, in Power Query.

Unchecking the "parallel loading" option worked for me.

As I am working on documenting common performance issues, diagnosis tools and suggested solutions, I'd like know more about what's going on in Power BI and the relationship between Query and Visualization parts.

So my question is: what is the relationship between Power Query and Power BI (visualization screens)?

- Could unoptimized queries impact the visualization part of the solution? I mean, once the data is refreshed and loaded, should it work fluently?

- If performance issues are encountered (i.e. slow queries) in PQuery, can it impact the user interaction in PBI as well?

Thanks in advance for your replies and hints,

Regards,

Pierre

[v-alq-msft]

Hi, @highxsky 

I wonder what connection mode you are using. For import mode, upon load, all of the data defined by those queries will be imported into the Power BI cache. Upon building a visual within Power BI Desktop, the imported data will be queried. The Power BI store ensures the query will be fast. All changes to the visual are reflected immediately. Any changes to the underlying data aren't reflected in any visuals. It's necessary to Refresh to reimport data.

For DirectQuery mode, upon load, no data is imported into the Power BI store. Instead, upon building a visual within Power BI Desktop, queries are sent to the underlying data source to retrieve the necessary data. The time taken to refresh the visual depends on the performance of the underlying data source. Any changes to the underlying data aren't immediately reflected in any existing visuals. It's still necessary to refresh. The necessary queries are resent for each visual, and the visual is updated as necessary.

For further information, please refer to the following documents.

About using DirectQuery in Power BI 

Optimization guide for Power BI 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz]

Hi @highxsky 

Power Query is more like a ETL tool that transforms the data and loads it into the model ( VertiPaq Engine ).

So to answer your question it should not ( or else you are working in direct query mode ).

Designing your data model / dataset / DAX Measures correctly will have an impact on the performance.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[highxsky]

Hi Mariusz,

Thanks for the reply !

Regarding data model: I have some tables that I generate in DaX.

The effort is on Power BI and not Power Query then?

If so, then I may want to reproduce these tables, in Power Query only.

The reason I ask, is because, on many projects, refresh is only made once a week/month, thus users are totally OK with a longer refresh, as long as UX is reactive.

[amitchandak]

@highxsky , refer if these can help

https://www.thebiccountant.com/2016/11/08/speed-powerbi-power-query-design-process/

https://www.thebiccountant.com/speedperformance-aspects/

[highxsky]

Thanks for the reply!

I had already checked these links as part of my documenting work on performances, along with Chris Webb's (great) posts.
I may want to re-read more thoroughly these links though, as I assume they provide hints to my question on the relationship between PQuery and PBI.

To me, the relationship was not so obvious: it's different modules (PQuery, PBI interface), with different languages, but part of the same global tool (Power BI).

Furthermore, the thrashing issue I encountered clearly came from Power Query, but had an impact on the UX in PBI (and on my computer, big time...), thus I was tempted to make the connection between the two, as it still occurred when data was not refreshing.

[mahoneypat · Accepted solution]

When you refresh the data, any slowness you see is due to the queries, but also any calculated columns and calculated tables.  If you are seeing slowness when interacting with slicers, navigating pages, etc. that is due to unoptimized DAX measures.  Also add some of the SQLBI blogs/videos to optimize your DAX expressions too.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat