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

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.

SYSTEM

DATA SOURCE

MS Excel

Fields: Country & Salary

Rows: 200,000

DATA MODEL & DAX

Sources: Excel Imported to Power BI.

‘Country’ is formatted to ‘Country’.

Single table in a data model.

One DAX measure, SUM().

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’.

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

‘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.

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

Analysis of performance of Power BI reports v/s number of visuals.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Dinesh Patel

Dinesh Patel

1 Follower

Business Intelligence Analyst Expert in analyzing data to gain Business Intelligence and converting them into actionable visuals with MS Power BI & Tableau.