PBI Report - Very slow performance

[niyati_61]

Hi @d_gosbell,

My Power BI Report has become very slow. When I open my report, in context of charts, they take too long to appear. 

And pages where I've buttons (bookmarks) to switch between charts - it takes really long. 

And, currently making this on PBI Desktop, saving it, then uploading it to Power BI Report Server Web portal.

For context,

- I have 110 pages in my PBI report with visuals. As I'm creating a progress report on the status of 4-5 different departments. (Added buttons to navigate to the depts data)

Edit here: When I say 110 pages, it's because I've some drill downs implemented (so hidden secondary and final pages), and a lot of other hidden pages too (Eg: tooltops, and not required pages, etc). So basically, my report finally has around 20-30 pages (not hidden).

- I've noticed the charts where I've added buttons (using bookmarks) - when I click a button - the charts take longer to appear.

- Mostly I've created tables in PBI itself (and keep updating it), and have some excel data files imported in too. 

So, currently data is all manually updated in PBI/ loaded in excel files weekly. No live data connections yet.

- Data Modelling - Mostly have one to one or one to many relationships between tables. Don't have many to many relationships.

What solutions can I implement to make my Power BI report performance much better?

Is it slow because I have a lot of data imported in? Or there are 110 pages with slow visuals? Due to the type of visuals I have?

Thanks in advance for your support!

[TomMartens]

Hey @niyati_61 ,

providing specific advice is difficult without more details.

Here are some general recommendations

• Separate content from the data
  It's often a good idea to separate the data (the data model) from the content (the data visualizations) by using two pbix files. The data.pbix is used to create the data model, the pbix will be used to publish the data model to the workspace. Publishing a pbix always creates a report inside the service, for this reason I create "some" data visualizations, but I use these visuals for some housekeeping information like number of rows inside the tables. The 2nd file content.pbix connects to the Power BI dataset and not to the original data sources. This creates a "thin" report, as it does not contain data.
  Separating content from the data will directly impact the performance issues you have mentioned, but I assume that working with a pbix file that consists of 110 report pages comes with its challenges.
• Performance in Power BI is related to two aspects the data model and the rendering of data visualizations.
  • You can identify performance issues of the data model using DAX Studio. These issues are related to not optimal DAX measures, and the number of unique values inside a column.
  • The rendering of content. Each visual (that contains data) creates a DAX query, each DAX query comes with some overhead. The more queries the more overhead, the slower the report. Even if a object does not issue a query it has to be rendered. These objects are used to support the understanding/the reading of the report. The general advice - reduce the number of elements that create queries. This can be achieved by using the Multi-row card visual instead of multiple card visuals. There are some visuals that allow specific design like the Zebra BI visuals or the Pureviz visual.

Personally, I have to admit that I never heard of a report that contains more than 100 report pages. For this reason, I can not confirm that the number of pages has an impact on the overall report performance, but I never do that. Consider distributing the content across multiple reports.

Hopefully, this provides some ideas on how to tackle your challenges.

Regards,

Tom

[amitchandak]

@niyati_61 , first of all, automate data loading using on-premise gateway. replace excel in the folder and power bi service should take it up.

Reason can be

1. DAX - Measures - You need to test them using performance analyzer and DAX studio

2. Too many visual on page

3. Custom visual may also slow down

This is going to be step by step approach ruling out what is working and  not working.

Remove all columns and measure, not in use.

If the report can be split into multiple reports then do that