Strained Relationship Between Quantity of Visuals & Performance in Power BI.

Performance of PBI report v/s number of visuals.

One of the best practices to improve the performance of a Power BI report is to limit the number of visuals on it. To understand this phenomenon better, I wanted to quantify the relationship between the quantity of visuals on a report and time a report takes to refresh. And, so, I did my little experiment and presented the results here. Give me your feedback please.

In order to establish meaningful cause and effect relationships and to maintain consistent repeatability of the experiment, I have documented as much details as possible including many images and kept variables such as data source and visuals, to minimum and as simple as possible.

My laptop

SYSTEM

Data Source

DATA SOURCE

MS Excel

Fields: Country & Salary

Rows: 200,000

Imported data

DATA MODEL & DAX

Sources: Excel Imported to Power BI.

‘Country’ is formatted to ‘Country’.

Single table in a data model.

One DAX measure, SUM().

Stacked bar chart with Performance Analyzer

VISUAL UNDER TEST

Stacked bar chart

Axis: Country

Value: SalaryM = SUM(Data_200K[Salary])

Other options are left at default values.

Number copies of ‘Stacked bar chart’s per page: 1 to 10.

PERFROMANCE DATA

1. Analyze above ‘Stacked bar chart’ in a report with a ‘Performance Analyzer’.

2. Every 5 second, report is refreshed with ‘Refresh visuals’ in ‘performance Analyzer’ for 6 times.

3. First set of ‘Duration (ms)’ of the refreshed visuals is ignored.

i.e. ‘SalaryM by country 778’ is ignored.

4. 2nd to 6th set of ‘Duration(ms)’s are documented as 5 samples of data sets.

5. Number of copies of the same Stacked bar chart were increased incrementally by 1 from 1 to 10.

6. In case of multiple visuals, values of first visual were used.

7. Collected these ‘Duration(ms)’ data in ‘PBIVisualAnalysis.xlsx’.

Performance Analyzer with three copies of the same chart.

‘Duration(ms)’ values are documented as below.

Documentation of data with three copies of the same chart.

‘SalaryM By Country’ as ‘Total_ms’, ‘DAX Query’ as ‘DAX Query_ms’, ‘Visual display’ as ‘Visual Display_ms’, and ‘Other’ as ‘Other_ms’ in PBIVisualAnalysis.xlsx.

Imported this dataset into Power BI for analysis and visualization.

ANALYSIS & VISUALIZATION: An average of five sets of data for the same number of visuals was obtained with ‘Group By’ in Power Query.

Power Query: M-code for average values with Group BY

RESULT

FINAL RESULT

CONCLUSION:

‘Other’ represents 72% of the time taken by display. For up to five visuals per page, ‘Other’ increases at a fairly consistent rate. However, ‘Other’ increases at a slower rate as number of visuals incrementally increases from 6 to 10.

‘Visual Display’ time which takes up almost 27% of the time, steadily increases as number of visual increases from 1 to 10. While ‘Other’ captures most of the time consumption, ‘Visual Display’ time is the reason why time to load a page increases as number of visuals per page goes up. ‘Visual Display’ consistently takes about 12ms per ‘Stacked column chart’.

Number of visual does not impact DAX Query time.

LIMITATIONS

Limitations of experiments.