Power BI Desktop - Resource Usage - CPU/RAM/Disk priorities

[lordtopcat]

Hi,

Forgive me as I'm not too "technical" but I will try and explain best I can.

Power BI Desktop runs quite slow on my PC (Surface Pro 7, i5, 8GB Ram) when my data source is connected to my on-prem SQL server. When connected to the Power BI service, it operates alot quicker.

My problem is, in my use case I can't always have my data pull from the service - I have to in certain cases connect direct to the server.

My IT team want to build me a virtual machine capable of running PBI desktop sufficiently while connected to the SQL server - what priorities should they be looking at? More CPUs, more threads, more RAM etc?

The report I'm currently working on is connected to around 15 SQL tables - total row count around 2.5m and growing by around 100k a day. Connection method direct query as this is used as a "semi-live" KPI dashboard. These # of rows are quite common amongst my previous reports and future reports I will build.

Any use cases or personal experiences would be helpful here to help my IT team figure out the best configuration.

Cheers

[TomMartens]

Hey @lordtopcat ,

please be aware, that quite often it's not the client machine that the bottleneck in directquery scenario.

Sometimes it's the report design, be aware that each visual creates it's own query, meaning, the more visuals the more queries.

Sometimes it's the database itself.

For this reason, I recommend this article

https://docs.microsoft.com/en-us/power-bi/guidance/directquery-model-guidance

that provides some hints on how to speed up your directquery solution.

Hopefully, this provides some ideas to tackle your challenge.

Regards,

Tom

[smearp]

If you are using Direct Query, then the processing moves from your PowerBI client directly to the SQL Server, so getting a faster computer won't help you out.

The fact that PowerBI works well when connected to the Power BI Service (and... I assume when you import the data?), the problem is not your local machine, but the SQL Server you are connecting to.

Here's the guidance on using Direct Query, https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about but to speed things up, you need to have a faster SQL Server, or have the IT department/DBA's look for opportunities to optimize the SQL server, or display fewer visualizations in your dashboards, or... you might consider talking to your stakeholders to determine how important the "semi-live" requirement is. 

If you are importing the data and refreshing in the service, you can setup refreshes to take place 8 times a day.  If they are okay with having data that is generally less than an hour old, in exchange for a much faster report... they might like that trade-off.