Following on from the series of posts I wrote last month (starting here) about testing the performance of Power BI reports in the browser, I would now like to highlight one possible cause of slow report rendering performance: the over-use of custom visuals. Before we go any further, though, let me be absolutely clear about something:

I am NOT saying that custom visuals are bad and that you should never use them. Custom visuals are an incredibly useful feature of Power BI and be used to solve a wide range of problems.

However if you are using an older browser to view reports, have a slow PC and/or have a slow network connection, then having a large number of custom visuals on the same page can be bad for performance. In fact having a large number of any kind of visual on the same report page can be bad for performance, as Mike Carlo shows here. Even so there is an extra performance overhead when you use a custom visual instead of one of the built-in visuals.

Take for example, the following report. The dataset it uses has one table with one column and one row containing the value 1, so retrieving data from it is going to be super-fast. This page has 26 Advance Card custom visuals on it displaying the value 1 from the dataset:

Using Chrome DevTools and the functionality on the Network tab to simulate a cold cache and a fast 3G network (as I show here), this page takes 45 seconds to render. Another page on the same report has 26 instances of the built-in card visual to show the same data:

Under the same simulated network conditions, Chrome DevTools shows this page takes 25 seconds to render. So the version of the page with the custom visuals is 20 seconds slower to render than the version with the built-in visuals, although this is obviously an extreme example. Without the network throttling (but with the cold cache) the version with the Advance Card visuals loads in 8.5 seconds and the version with the built-in cards takes 5.5 seconds; still a noticeable difference but maybe not one that your users would complain about.

Let me be clear about something else here:

I am NOT saying that the Advance Card visual is badly implemented – it’s a great visual and one that is used by a lot of people without any problems. What I am saying is that any custom visual comes with some kind of performance overhead and the more custom visuals you use on a page, the more the overhead will be.

How much overhead there is will depend on a lot of different factors so you should test to see if there is any noticeable impact from using custom visuals in your own reports – it’s very likely that there won’t be one. I would go as far as saying that 99% of the time you won’t see any impact. What’s more, there are improvements being made to the Power BI Service all the time and it’s highly probable that some optimisations for the loading of custom visuals will be made in the future.

One last tip for you. If you have imported a custom visual in Power BI Desktop but are not using it, you should always delete it (as shown here). There is another, smaller overhead associated with even having a custom visual imported into a report – although, again, this may well change in the future.

In part 1 of this series I talked about why you might want to test the performance of your Power BI report in the browser; in part 2 I showed how you could test report performance in the browser; and in this blog post I’ll walk through an example of a report that is slow and show how to find out what the problem is.

Let’s say you are building reports for a chain of fast food restaurants and you have just created a new one showing sales of your products. It consists of one page with one rather smart-looking column chart visual on it:

Performance Analyzer in Power BI Desktop shows that the report is nice and fast:

…so you go ahead and publish. You view the report after publishing and it still seems fast. Then the complaints start coming in: the report is slow!?! It seems to be users who are viewing the report on their phone who are having the most problems. So, following the instructions in my last post, you open up Chrome DevTools and run an audit using a simulated slow 4G connection:

You get the following results:

They look pretty awful – 30.5 seconds for the report to render! Scrolling down on the audit results you also see the likely cause:

A large network payload, with the number one culprit a large jpg? So next you go to the Network tab and do a hard refresh of the report, disabling the cache and simulating a fast 3G connection:

Here’s what the report render looks like:

As you can see, it not only takes over 30 seconds to render the report, but worst of all the column chart is only visible right at the end. The waterfall shows something is being downloaded that is 1.2MB in size and that this takes 7.23 seconds:

Hovering over this request displays a tooltip that gives the full filename which, again, is a jpg file. The only image on the report is the background image used in the column chart and it turns out it is 1.2MB in size:

So it’s the background image used in the column chart that is the main problem! You remove the background image from the visual:

…and as expected, when you re-run the audit using the same settings as before the report renders seven seconds faster, taking 23.4 seconds:

Still not great, I know, but remember this is worst-case performance: not only does the audit recreate slow network and hardware, but it also recreates a cold cache – something your users will rarely encounter.

Hopefully this fairly simple example shows how useful the tools and techniques shown in the first two parts of this series are for troubleshooting certain types of report performance problem. It also goes to show how important it is to make sure any images you use in your report are as small as possible – something that, in my experience, many Power BI developers don’t always do.

In the first part of this series I talked about why you might want to test the performance of a Power BI report in the browser, as opposed to using Performance Analyzer in Power BI Desktop, and the different factors you have to take into account when doing this kind of testing. In this post I’ll describe how you can find out how long it takes for your report to run in a browser.

You can think of Power BI as a low code/no code tool for creating web applications for reporting and analytics, and as such you can use the same tools that web developers use for measuring web site performance to measure Power BI report render time. Now I am not a web developer (I guess most of you aren’t either) so I’ve had a lot to learn about this subject, and I’m all too aware of how much more there is still to learn, but I wanted to write up what I have learned so far because I think it’s so useful. Please excuse any mistakes, misconceptions or over-simplifications!

I’m going to use Google Chrome as my browser in this post. Yes, I know, I work for Microsoft but this is the new Microsoft and I have used Chrome as my browser of choice for the last few years; anyway, Edge is moving to be Chromium-based so everything I show in this post works in exactly the same way in the current Edge beta I have installed on my PC. As I said last time, it’s also important to test performance in whatever browser your end users use to understand what their experience is, but any optimisations you make in your report that work for Chrome or Edge beta are likely to work for other browsers too.

Here’s the methodology that I have come up with for measuring report performance in the browser:

Choose the page you want to test

You can only test the performance of one page in a report at a time, so choose the page that is either the most used, used by the most important users, or takes the longest time to render.

Add a blank page to your report

Before you publish your report, in Power BI Desktop add a blank page with no visuals on to it. It doesn’t need to be the page that is opened when the report opens and you will be able to delete it later. Why do this? When you’re testing how long it takes for your report page to render, you’re probably doing so because you want to improve performance. Some things in the report page that influence performance you have the power to change, such as the design of the dataset, the DAX in the measures, the number and type of visuals on a page; some things will always happen when a report runs and you have to accept that overhead. Testing how long a blank page takes to render will give you an idea of how long this latter category of “things that always happen” takes, and you can subtract this time from the time your chosen report page takes to run.

Get the embed url for your report page and the blank page

After you have published your report, get the secure embedding urls for your chosen report page and the blank page. You can find out how to do this here. Getting the embed url for the report takes a few clicks, but you will need to do a bit of copy-and-pasting to get the embed urls to open the report on the blank page and your chosen report page.

Why do this? Once again, it’s all down to focusing on what you have control over. When a user views a report they normally do so in the Power BI Service portal and the time this takes consists of the time taken to render the portal plus the time taken to render the report. Since you don’t have any control over how long the portal takes to render you might as well ignore this in your testing, so you can get a clearer picture of the amount of time taken to run the report. When you paste the embed url for the report into a browser tab the portal will not be displayed and you’ll only see the report.

Create a new user profile in Chrome

This is something I do anyway for each customer I work with, because it makes handling multiple Power BI logins much easier, but for performance testing you should create and use a new user profile in Chrome. This allows you to delete any Chrome extensions just for this profile (apparently they can affect page render times) and delete the cache if you want, without affecting your main profile. You may also need to test from an incognito window if there are some extensions you can’t delete.

Run your report pages to warm the SSAS/Power BI cache

Copy the embed urls for the blank page and the report page into new tabs and let them load before you do anything else, to warm the SSAS/Power BI cache. I discussed testing performance on a cold or warm SSAS/Power BI cache in my last post, and as I said then, while I believe it is extremely important to understand the impact of SSAS/Power BI caching on report rendering performance, it’s only really feasible to do the kind of DAX performance testing that requires this in Power BI Desktop. For testing report page rendering performance in the browser it’s easier to assume that you are running on a warm SSAS/Power BI cache.

Create an audit

The easiest way to measure report load time is to open Chrome DevTools (you can do this by clicking Control+Shift+j in Chrome) while on the browser tab with the report page you want to test and use the functionality on the Audits tab. To get an understanding of what the Audits tab does I recommend you read this page and watch the accompanying video, paying particular attention to the steps in the section on how to establish a baseline. To keep things simple I suggest you choose the Desktop option in the Device section (choosing Mobile does not seem to render the Phone layout of your report if you have created one); check the Performance box in the Audits section; and then choose to apply one of the throttling options if you want:

When you click the Run audits button various tests will be run, and after a short wait you’ll see the results:

You can read more about what these results mean here but there is one number that a Power BI developer should be particularly interested in: Time to Interactive. As far as I can see this is pretty much the amount of time it takes for the report page to render (because, of course, defining what “render” means is complicated). Subtract the amount of time it takes to render the blank page from the amount of time it takes to render your chosen report page and you will know how much of the report page render time you can actually influence with any changes you make. I strongly recommend you run tests both with and without network throttling applied, because applying throttling really highlights any inefficiencies in your report design.

Use the Network tab

If you want to peak behind the scenes and see more detail about what happens when your report page renders, as well as have more control over things like network throttling, you can use the Network tab in Chrome DevTools. You can find out how to use it here (with more details here) but all you really need to do is:

• Open up a new tab with the embed url of the report page you want to test
• Open Chrome DevTools and go to the Network tab
• Make sure the Disable Cache box is checked and set up any network throttling options you want to use for your tests. The ability to add custom network configurations is particularly useful:
• Press and hold the refresh button on the browser toolbar and a dropdown menu will appear; click on Empty Cache and Hard Reload to refresh the page and simulate a user’s first visit to the report. This will automatically start data capture in the Network tab.
• When your report page has finished rendering, click on the red stop button in the top left hand corner of the Network tab. You need to be quick but not that quick, for reasons I’ll explain in the next bullet.
• Down at the bottom of the Network tab you’ll see some useful metrics, including the number of requests made, the amount of data transferred and most importantly, the finish time. This last metric is not the time elapsed from the start of the refresh until the point you clicked the stop button; it’s the time elapsed from the start of the refresh until the last request before you clicked stop, so this is another good way of finding the render time of your report. You’ll see that requests do get made after the report has finished rendering which is why you need to be fairly quick clicking the stop button, but this is still a lot more accurate than using a stopwatch to time report page rendering.
• The majority of the pane will be taken up with a list of requests made and a waterfall chart that visualises how long each request took.

Don’t get too clever!

You’ll see a lot of really interesting information on both the Audits tab and the Network tab and I’m sure most of you will be able to interpret what you see there and use it to tune your reports. I hesitated about mentioning the waterfall chart of the Network tab in this post but decided to do because I have found things on there that have helped me while performance tuning that I would not have found any other way. However, remember everything that you see on the waterfall chart in the Network tab is internal, undocumented and subject to change at any time. As a result please, please, please do not send me messages like “I’ve seen x happening on the Network tab, can you tell me what it means?” or “I’m a web developer and I can’t believe the stupid stuff I can see Microsoft doing here, why are you doing this?” because I’m not able to provide any answers.

As always, if you have any feedback or suggestions please leave a comment. In the next part of this series I’ll show you a worked example of how you can use all this to solve a performance problem that does not manifest in Power BI Desktop.

If your Power BI report is slow, most of the time the reason is something to do with the way you have modelled your data or the way you have written the DAX in your measures. There are plenty of resources out there to help you troubleshoot this kind of problem (Marco’s video “My Power BI report is slow: what should I do?” is a great place to start) but it’s important to understand that there are several other possible reasons why a Power BI report is slow, and these types of performance problem may only manifest themselves after the report has been published when a user is viewing it in the browser. As a result, it’s important to be able to test how long a report takes to render in a browser. If you can’t do this, how can you know if any changes you make help or hurt performance?

It turns out that testing performance of a report in the browser is not as straightforward as it seems. In this post I’m going to describe some of the factors you have to take into account when doing this type of testing; in the next post I’ll go into more detail about how you actually measure report rendering times in the browser and how to see what happens when the report is rendered.

Power BI is always changing

The first thing to remember is that the Power BI Service is always being updated and improved, which means that even if everything else in your environment stays the same the performance of any given report may vary (and hopefully get better) from day to day. Most changes to the Power BI Service are not publicised and there’s no way of knowing how much impact they will have on your report.  You need to take this into account when you do testing: don’t create a benchmark and then wait a week to see if any changes you have made have improved performance. Instead create a new benchmark every day you are testing and compare the changes you make to it.

Use the browser your users are using

Report performance can vary a lot depending on the browser you’re using. Before you ask, I don’t have any recommendations for the best or fastest browser to use for Power BI but generally speaking the more ‘modern’ your browser the better; this tweet from my colleague Chris Hamill showing how much faster the new Chromium-based version of Edge is compared to regular Edge is really striking. If you’re going to test performance of a report, though, you need to use the browser(s) your users are using, especially if that browser is IE.

Client hardware

The spec of the PC or mobile device running the browser is also a major factor in performance. As a Power BI developer it’s likely your hardware will be far superior to whatever device your users have to view your report with. It may be a good idea to borrow a PC, laptop, phone or tablet that is equivalent to what your users use, just for testing purposes.

Network factors

Users in different parts of the world with different internet connections will experience different report performance, and network conditions and therefore report performance may also vary by time of day. Power BI has some basic functionality to show the network latency that users are experiencing but it’s better to get someone who really knows what they are doing to help you understand what’s happening at the network level (I am absolutely not an expert in this). To a certain extent you can reproduce a different network conditions in the browser for testing purposes, but ideally you should to try to reproduce any problems and do any testing wherever the users are based and at the times of day they are using Power BI.

Power BI query caching

Datasets in Power BI Premium have an option to cache queries and improve performance for the initial page of a report. If you have this turned on, it means that the initial report load might be faster than subsequent interactions such as changing a filter or a slicer, so you should not just test the initial render time.

The Power BI/Analysis Services cache

Even if your dataset is perfectly modelled and your DAX is tuned to perfection, the caching that takes place within the Power BI engine (or Analysis Services, if you are using a Live connection) will almost always mean that the first time you run a query it will be slower than subsequent executions.

With Analysis Services Live connections you can clear the cache, and get worst-case cold-cache execution timings by running and XMLA ClearCache command from SQL Server Management Studio or by using DAX Studio’s Clear Cache button. You should also ensure that you are running your tests on an instance of Analysis Services that no-one else is connected to or doing anything on: this ensures that other users’ queries do not populate the cache, or use resources that affect the performance of your queries.

Things aren’t so simple with Power BI Import mode datasets – I’m pretty sure you can use XMLA to clear the cache of a Premium capacity via XMLA endpoints although I’ve not tried it, but even then it’s not likely you can ensure that you’re the only user on that capacity. With Shared capacity there’s no way to clear the cache. As a result you should instead accept that you can only test warm cache performance in the browser by loading your report once before you test performance. In any case, I think the kind of DAX-focussed testing that requires a cold cache can be done more easily in Power BI Desktop.

DirectQuery

If you’re using a DirectQuery dataset then you also need to worry about caching, security and other users running queries in the underlying data source. Again, as far as possible you should try to ensure that no-one else is running queries or doing anything else in the data source that could have an effect on the performance of your queries.

Simulating activity from other users

With DirectQuery connections, Live connections and Import mode, it might be that other users’ activity or processing/refreshing of cubes/models/datasets is causing the performance problem. I still think it’s important to do any testing when there are no other real users around – if you want to simulate other users running queries while you do your testing there are tools that can do that, including the Premium capacity load testing tool.

Do what your users do

Sometimes performance problems only occur in very specific circumstances, for example when a specific item has been selected in a filter or more than one item is selected, so it’s really important to create tests that reflect real-world patterns of interaction. Don’t forget that users might also be subject to row-level security that you as a developer are not affected by.

Conclusion

As you will have realised by now, creating realistic, repeatable tests for Power BI reports in the browser is not an easy task because there are so many different things to take into account. I’m not going to pretend this is an exhaustive list (I haven’t mentioned factors such as using a dataset that has the same data volumes as your production dataset, because that’s not specific to testing reports in the browser) but if you think I have forgotten something please leave a comment below – I would be interested to hear your thoughts.

Click here for part 2 in this series, where I show how to measure report render time in the browser. Click here for part 3, where I show a worked example of how to use the tools and techniques described in part 2.