Power BI performance best practices

15-07-2019  0 Comment(s)

The more data that a visual needs to display, the slower that visual will be to load. While this principle seems obvious, it can be easy to forget. For example: suppose you have a large dataset. Atop of that, you build a report with a table of the table. End users use slicers on the page to get to the rows they wanted – typically they’re only interested in a few dozen rows.

A common mistake is to have the default view of the table be unfiltered - that is, all 100M+ rows. The data for these rows must be loaded into memory and uncompressed at every refresh. This created huge memory loads. The solution: reduce the max number of items that the table displayed using the “Top N” filter. The max item can be much larger than what users would need, for example, 10,000. As a result, the end-user experience was unchanged, but memory utilization of the report dropped multiple orders of magnitude, and performance improved accordingly.

A similar approach to the above is suggested for all visuals on your reports. Ask yourself, is all the data in this visual needed? Are there ways to filter down the amount of data shown in the visual with minimal impact to the end-user experience? Note that tables in particular can be expensive.

Limit visuals on report pages

The above principle applies equally to the number of visuals on a particular report. It's highly recommended you limit the number of visuals on a particular report to only what is necessary. Drill-through pages are a great way to provide additional details without jamming more visuals into the report.  

Optimize your model

Some best practices:

  • Tables or columns that are unused should be removed if possible. 
  • Avoid distinct counts on fields with high cardinality – that is, millions of distinct values.  
  • Take steps to avoid fields with unnecessary precision and high cardinality. For example, you could split highly unique datetime values into separate columns – for example, month, year, date, etc. Or, where possible, use rounding on high-precision fields to decrease cardinality – (for example, 13.29889 -> 13.3).
  • Use integers instead of strings, where possible.

Comment Here


No Comments to Show