Tips to increase the performance of your Power BI Reports

Increasing the performance of the reports are very important for the user experience when working with Power BI. In this article I will go through how you can increase the performance of your reports in regards to the data sources, data model, visualization and other aspects.

Photo by Marc Sendra Martorell on Unsplash

You can also find the article on my web page eliasnordlinder.com, along with other Power BI aspects such as Resources, DAX-snippets and more.

Introduction

Performance issues are something that you pay more and more attention to the more you work with Power BI.

When you are just starting out with Power BI, there are a lot of things that you have you to learn.

You explore different types of visuals, learning how to write different DAX measures, how to transform data in Power Query, how to build up the relationships and models, and all of the other million things you can do in Power BI.

Performance is often put in back row, even though it should not.

When you are getting more and more experienced with the tool, you understand that performance actually plays a huge role in how much impact your reports will have inside the organisation.

You also learn that performance does not have a “One size fit all approach”, but there are a lot of different ways in how you can solve performance issues in your report.

General Performance issues

• In this first part of this article I will go through some of the common Performance problems that often comes in mind when working with Power BI:

Performance Analyzer to increase performance

• In the second part I will go through a tool that helps to investigate and point out some of the specific ways different visuals are affecting the performance:

External Tools to increase performance

• In the third part of the article I will go through external tools that can play a big part in helping you with the different performance issues you read about in part one, sometimes with the help of the performance analyzer:

General Performance Issues

In this first section I will go through some of the general performance issues you can in your report and how you can deal with these.

Too many Visuals on a report page

One of the most common issues when people just start out with visualization tools is that they want to visualize everything at once.

First, this makes it difficult to understand what to you should look after when you are looking at the dashboard. Things looks cramped and too many things stands out at once.

From a performance point of view, this is also bad practice, as the more visuals you have the slower will your report render (load).

Some of the things you can do to reduce the number of visuals are:

Only keep necessary visuals

• Think through what you really want to show and only keep the necessary visuals

Use formatting tricks to show the same things, but with less visuals

• There are some formatting tricks you can use to decrease the number of visuals, while still showing the same things in the report.
• One of the famous ones is to change multiple measure cards into a single table, see this video from Guy in a Cube to see more about how you can do this

Too high cardinality of your columns

Another common problem that many people run into regarding to performance is to have too high cardinality in the columns, see Guy in a Cube here.

• High cardinality of a column means that you do have a lot of different unique values in a specific column.
• I will go through this more when I go through the Vertipaq analyzer which is a great way to investigate which columns that have a high cardinality in a model.
• Ways to deal with high cardinality are:
• Remove columns if they are not used in your model
• Disable Auto Date/Time and use a separate calendar table instead
• Change data types, optimal data types can increase cardinality and increase performance of a model

Using Direct Query with bad models

• Direct query is often slow if it is not modeled optimally for Power BI already in the source.
• If your model is very slow, you are using Direct Query, and you run Performance Analyzer on this you will see that most of the rendering time is from Direct Query.
• We can deal with Direct Query performance problems by:
• Optimize model design
• Remove the need for Power Query queries to apply transformations
• Move calculated columns to added columns in the sources, if they are needed
• Only use simple measures
• Avoid using bi-directional filters
• Switch off interaction between visuals

Not optimized DAX Code

• The common misunderstanding that DAX is an easy language can often lead to non-optimized DAX code
• You can often write DAX code in a lot of different ways which will lead to the same result, but some ways are actually optimizing how the engines running in the back of Power BI much more than other ways.
• I will discuss this more when I talk about the VertiPaq Analyzer and DAX Query duration, but try to understand how DAX really works, instead of just googling measures and you will learn how to optimize your DAX code for better performance.

Performance Analyzer to increase performance

The performance analyzer is a tool, built in to Power BI, that can help you to find different aspects that make your report to run slowly.

Below are two pictures from how it looks like when you use the performance analyzer. A great video of the Performance Analyzer by SQL BI can also be found here.

How do you get the results from Performance Analyzer?

To get the result above from the performance analyzer, use the steps below:

1. Create a new blank page (This is needed to not have any cache when running the tool)
2. Go to View -> Performance Analyzer and start recording (While inside the Report Tab)
3. Go to specific page that you want to see the performance of
4. Wait until the performance analyzer is done and you will get the information you see on the right picture above.
5. (Optional), Press Export to export this to an external program if you want to use the data from the performance analyzer. I will go into the external tools more in the next sections.

How do you interpret the results of the Performance Analyzer?

Let us go back to the picture above again, but now we try to interpret the result that we see.

Different parts of the Performance Analyzer

For each visual, we can see three headers as well as a number besides them:

• DAX query
• Visual display
• Other

Duration

Duration (ms) is how long it takes for the specific aspect above to render. Important to notice is that it is in milliseconds, 1384 milliseconds means 1.38 seconds.

DAX query

• The DAX query is what is generated automatically when a specific object/visual is gathering the data from the original source. All visuals according to the DAX query can be seen as tables, no matter visual they are.

Real World Example:

• You are at the restaurant and order food at a fine restaurant.
• There are some standard procedures that are almost always done by the restaurant
• Taking you to a place to sit
• Looking through the menu to decide what to eat
• Telling the waitress what orders you want
• Chefs start preparing the food after getting the information from the waitress
• The procedure over all is not that complicated and these four same aspects are being done at all the different restaurants.
• But different restaurants are much more experienced and customers centric when they are fulfilling these tasks, which leaves a better user experience.
• In Power BI, there are often some end result you and standardized procedures you are doing as well.
• You have some measures that you want to perform, and in the beginning you are just writing the measure so that you are getting the right result, like the inexperienced restaurant.
• When you are getting more used to DAX, you understand that you can write the DAX queries in a lot of different ways, and some ways will make the report run much quicker (More customer centric).

How to limit the time it takes to run the DAX query

• Change the DAX measures to use the Formula Engine and Storage Engine optimally
• DAX studio is the best way to analyze how you can minimize the DAX query timings, see this video where Marco Russo is going through how he usually optimizes the DAX query. You can see how much time it takes for the Formula Engine respectively the Storage Engine to run a specific query in there.
• I would also recommend to read up on the Formula Engine and Storage Engine more -> Here is a video by Curbal about that topic, and how you can optimize your DAX measures to limit DAX query timings.

Visual display

• The visual query on the other hand cares about how and what is contained in your visuals in your report.
• The more visual points you have in the report, the higher will the visual query be.

Real World Example:

• You have ordered the food in the example above and are now waiting for the chef to finish the food.
• The table besides you have ordered a simple appetizer and get their food after 10 minutes.
• You have, on the other hand, decided to order the most complicated dish on the menu, and have to wait 40 minutes before the dish is finished.
• You have decided, mostly in your own power, to take a food that takes longer to cook before you care about that the result will be more advanced than the appetizer.
• In Power BI Performance language this can be called the Visual query

Limit the time it takes to run the Visual query

• The visual query on the other hand cares about which kind of visual that you are creating.
• Depends on how advanced visuals you decide to create, the longer duration will the visual query take.
• If your end goal is to have great performance, you should not go for too advanced visuals, and not uncertified custom visuals, as these may have a longer duration for the visual query.

Other

• The “Other” cares about what other visuals you have on the report, the more visuals you have on a page the longer will the “other” be/the longer will it take for a specific visual to load.

Real World Example:

• You are 10 people that order food at the same time.
• On the table besides you only one person is sitting and order food.
• The person on the table beside you get their food 15 minutes after ordering.
• For your table on the other hand it take 40 minutes, even if you have ordered the exactly same food as the person at the table besides you.
• The reason for this is that you also have to wait for all the other people at your food to have their food prepared, and some of these people have ordered food that takes much longer time.
• The only way you can minimizing the time it takes for your food to be prepared in this specific instance, is to limit the number of people you are going to the restaurant with.
• The “Other people” in Power BI Performance language is called “other”.

Limit the time it takes to run the “other” query

• The main way you can limit the way it takes to run the “other” query is to minimize the amount of visuals that you have in your report.
• Really think through if you need all the visuals you have in your report, and if you do, see if you can do some formatting tips to show the same things but with less visuals.

External Tools to reduce performance

You can download multiple external tools as the Business Ops package on Power BI Tips here.
I will go through two external tools in this section regarding performance:

• DAX Studio
• Report Analyzer

DAX Studio

DAX Studio is a well averse tool, where a lot of people probably uses for evaluating different DAX measures, especially that are more complex or return tables -> Great video on why you should use DAX studio on Power BI

But DAX studio can also be used a lot in more specific performance aspects, as seen below.

Decrease DAX Query Timing

• You can utilize DAX Studio by reducing query timing, which I have touched upon a bit earlier.
• This video by SQLBI is great regarding this topic, where Marco analyses a slow report in DAX Studio.
• Server timings tells exactly how long the Formula Engine and the Server Engine takes.
• The Storage Engine is much quicker than the Formula Engine, but can handle much less complex formulas.
• By rewriting the different DAX measures, and testing these out in DAX studio, you can change the complexity of the measures, move them from the Formula Engine to the Storage Engine, and make the performance of the model much better.

Decrease Size of Model and granularity of columns with VertiPaq analyzer

• VertiPaq Analyzer is another great part of DAX Studio
• You can see the granularity of each column, and the size of each column

You can utilize this information from the VertiPaq Analyzer in a lot of ways, for example by:

• Remove columns if they are not used in your model
• Disable Auto Date/Time and use a separate calendar table instead
• Change data types, optimal data types can increase cardinality and increase performance of a model

Report Analyzer

Find the blog post by the creator of the Report Analyzer, Elegant BI.

If I would describe report analyzer with six words I would describe it as:

• Visual representation of the Performance Analyzer

Another way to say it is that it is a GUI that makes is much easier to understand and work with than the performance analyzer, as well as with the possibility to easy export it to other tools such as DAX Studio.

I am a person that likes to see things visually, and I think many people who works with business intelligence does, therefore this tool is a great tool to let you explore these performance aspects visually!

How to use Report Analyzer?

As the report analyzer is partly based on the performance analyzer, you have to download the log files from the performance analyzer to use it:

• Run the Performance Analyzer for all the pages in the report that you want to analyze (See my description of the Performance Analyzer in the section Above)
• Press Export for each page to export the data to a JSON-file.
• Save all the JSON-files as well as the Power BI Desktop file to a specific folder
• Launch the Power BI Report Analyzer
• Open the folder with all the JSON-files and the Power BI file in Report Analyzer.

Features I appreciate in Report Analyzer

Intuitive layout, which is based on the layout of the visuals you use in the report page in Power BI, but are instead overlaid with information from the performance analyzer, such as:

• Type of Visual
• DAX Query Time (ms)
• Render time (ms)
• Row Count

Visual GUI with filters on top of the page in the Report Analyzer, which let you select different aspects such as:

• Report Name (Select and only show everything from that report page)
• Visual Type: Slicer, Card, Chart, Bar Chart, Matrix etc (Highlights that Visual Type)
• Visual ID
• DAX Query Time (seconds)
• Possibility to get visual cues, visual filled with red color, by setting a threshold of DAX query time in seconds.
• Possibility to copy the information directly to DAX Studio by just simply clicking on a Visual representation on the Report Analyzer report page.

See recommendations about how you can increase the performance such as:

• Reduce number of visuals
• Reduce number of custom visuals
• Reduce “Show Items with no data”
• Reduce long pages with vertical scrolling
• Reduce usage of TopN filtering
• Reduce usage of filters on Measures

I would recommend to also see Guy in a Cubes video regarding the Report Analyzer called “Another Power BI Optimization Tool — Report Analyzer”.

Conclusion

In this article I have gone through the main aspects why the performance might be bad in a Power BI Model, how to use the performance analyzer to investigate this, and how to use different external tools that also can help with increasing the performance of your model.

General performance issues are:

Too many visuals on a report page

• Try to only keep necessary visuals, and format visuals to reduce the number if possible

High cardinality of columns which leads to a big model size

• Use VertiPaq Analyzer to analyse the different columns
• Remove columns, change data type, and create date tables to reduce the model size

Using Direct Query with bad models

• Build better models in the source data which are optimized for Power BI
• Reduce the need for Power Query and move these transformations to the source

Not Optimized DAX Code

• “DAX is simple, but it is not easy” — Marco Russo
• Understand how DAX really works, and you will learn to optimize your DAX code for performance.

Using Performance Analyzer you can see the performance in regards to:

DAX query

• How long it takes to render a visual in regards to how you gather data from the source.
• Optimize by writing better DAX code, with the help of VertiPaq Analyzer.

Visual display

• How long it takes to render a visual in regards to the complexity of the visual.
• Optimize by using more standardized visualization with less data.

Other

• How long it takes to load a visual in regards to the number of visuals on a report page.
• Optimize by not including too many visuals on one report page.

There are several External Tools that can help your performance such as:

DAX Studio

• Use DAX Studio to try out, and reduce the DAX Query timings.
• Use VertiPaq Analyzer in DAX Studio to reduce granularity of columns.

Report Analyzer

• Get a visual representation of the Performance Analyzer.
• Easily move the DAX Query from a visual to DAX Studio.

Questions or inquiries

If you have any questions or input please contact me on

LinkedIn: www.linkedin.com/in/elias-nordlinder
Email: Elias.Nordlinder@gmail.com
Webpage: eliasnordlinder.com

Have a great week everyone
//Elias