Power BI Desktop - Performance Issues

[zfemmer]

I have been having some issues with creating Power BI reports. We use Power BI Report Server and the current report that I am using is currently about 60MB and the total amount of data for the datasets is about 1.5GB; somewhere around 15 million rows of data. I am importing the data from SQL Server rather than doing DirectQuery.

The report itself currently has 9 pages, 5 of which are just custom tooltips, 1 is a table of contents, and the other 3 have a pretty fair amount of visuals, slicers, bookmarks, etc, but as I said, the report is only 60MB.

The end users have absolutely no problems, it is just me as the creator of the report in Power BI Desktop. It is very sluggish and laggy. I thought it was my laptop which has about 16GB RAM and a pretty average CPU; i5-6200-U 2.30GHz. So we tried setting up a virtual machine with 64GB RAM and a pretty similar CPU, but with double the cores; 4 to 8.

Does anyone have similar issues or know of anything to make it better?

My only idea was to make one dashboard as a table of contents and then have that have links to the other pages so it splits up the visuals to different reports, but I am not sure how the end-users will like that and it will have duplicate datasets causing more space overall.

If I need to supply more information, let me know.

[Pragati11]

Hi @zfemmer ,

I think the scenario that you have mentioned is something, most of the people who work with Big Data, face these issues.

Recently, I have worked on something similar scenario and improved the Performance by making following chnages:

• Try to remove un-necessary columns from your tables. keep only the ones used for reporting. Delete remaining columns.
• In Query Editor enable the "Enable Load" option for the tables that are used for reporting. Some-times we create refrence tables for creating bridge tables in Power BI; which are further used for creating relationships between the tables.

• If you are using visuals like a table visual, and displaying most of the rows from your dataset, then try to limit these rows by giving them a default view like - Top 1000 rows, etc. You can check my blog on this similar functionality: https://community.powerbi.com/t5/Community-Blog/Controlling-Number-of-Rows-to-Be-Displayed-in-a-Tabl...
• The other option could be pre-set some page level filters on your report. This improves performance of the report from loading point of view.
• Avoid bi-directional relationships in your data model. Try to adopt the approach of a Star Schema Model.
• If you are creating many calculations, try to stick to MEASURES rather than creating Calculated COLUMNS.
• Use Performance Analyzer in Power BI to check performance of your reporting tabs. It tells you exactly where your problem is - whether your DAX query takes time or visual loading takes time.
• Try to limit number of visuals on a single tab. Rather than having 20 visuals on a single tab, split them into 2 tabs having 10 visuals each.

For now I can suggest the above ways based on my experience. There are many articles out there which give insights around how performance of the Power BI dasboard can be improved.

Let me know if these help.

Thanks,

Pragati

[zfemmer]

Thanks for the reply.

I have tried many of these solutions. I have very few columns in my datasets, just quite a bit of rows. I do have Enable Load turned on for each. I do not use any tables as visuals, bi-directional relationships, or calculated columns.

The performance analyzer doesn't really help because the issue is mainly when I am adjusting visuals, moving visuals, formatting, etc.

Do you know if a poor cpu and or not having a dedicated graphics card could be causing issues?

[zfemmer · Accepted solution]

So I ended up testing out my co-workers pretty beefy computer setup; AMD Ryzen 9 3900X 12-Core with 32GB ram and it for sure made a world of a difference, everything was much smoother.

I imagine that it is my CPU.

[amitchandak]

@zfemmer , refer these few, see if they can help

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

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-The-Do-s-and-Don-ts-of-Power-BI-Rel...
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-My-Power-BI-report-is-slow-What-sho...

https://www.c-sharpcorner.com/article/power-bi-best-practices-part-1/
https://www.knowledgehut.com/blog/business-intelligence-and-visualization/power-bi-best-practices