Why is it Important to Optimize Power BI Reports?
When developing reports in Power BI Desktop (or any software for that matter), good performance in terms of load time is important for gaining trust with stakeholders and ensuring user adoption. Long load times and poor user experience can deter users from adopting solutions as part of their everyday processes.Â
How to Optimize Power BI Reports
In this blog we are going to walk though some practical and easy tips on how to optimize your Power BI reports. These tips will be broken down into two sections, Data Model and Report View.
Lets get started:
Data Model
1. Use STAR Schema
Power BI is built to perform most efficiently when using a STAR schema. By breaking out your data model into fact and dimension tables, your queries will be optimized in your report. These fact and dimension tables should be related to one another, not joined (aka flat file format), which will result in more performant reports.Â
2. Limit Data Model
The fastest way to optimize your Power BI report is to limit the number of columns to only the ones you need in your data model. Go through your tables in Power Query and determine what fields are being used. Delete these columns if they are not being used in any of your reports or calculations. Another way to limit your data model is to use Row Level Security when applicable.Â
A side note, limiting columns can be far more effective than limiting the rows in your data set. As an example, if you have a data set with 1M rows and 20 columns, removing 10 columns will remove 10M data points from your model. If you were to parse down the number of rows in half, you would only be eliminating 5M data points. While filter down your dataset to only the applicable data is a good idea, eliminating unused columns will be much more effective in minimizing your dataset.Â
3. Push Data Transformations Upstream
Building on the previous step, if you are able to move your data manipulations to your query, this will help your performance. Whether you’re doing calculations, formatting, or grouping at the appropriate granularity in your SQL, this will eliminate Power BI spinning its wheels on these items. If you end up doing your transformations in Power Query, try Query Folding.
4. Move Row Level Logic to Power Query​
Move any logic that needs to be calculated at the row level to Power Query. Instead of using DAX to create calculated columns containing the more basic IF THEN calculations, use M to do the same thing. These fields will be included in your permanent data model brought in from Power Query instead of them being performed in Power BI Desktop.
5. Use Measures Instead of Calculated Columns
For beginning Power BI users, it can be tempting to create calculated columns instead of using measures as you’re able to see the row by row output for each calculated column. The issue is that calculated columns can be more burdensome for the data model as they actually create a new column of data. Instead, measures are simply aggregations of the fields in the data model, so they do not actually add new data to your model. Using measures where applicable will help reduce data model size as well as increase computation efficiency.Â
6. Convert Multiple Measures to Variables
Instead of creating metrics that require using multiple measures, use variables. You can perform multiple calculations within a single measure using the VAR and RETURN functions. This will minimize the number of measures you use as well as increase performance for the metrics being calculated. If you are going to be repeating a variable multiple times then you can still create an independent measure for that value. See example of variables below.Â
// Measure =
VAR num = SUM(revenues)-SUM(expenses)
VAR denom = COUNT(customer)
RETURN
DIVIDE(num,denom)
7. Amend Dates and Column Types
Although this is probably the least impactful of the ideas in the list, editing your data’s formatting can also help limit model size and performance. As an example, date will show as mm/dd/yyyy 12:00:00am by default in Power BI. To cut down on characters in the data model, change the date types to Date to eliminate the Time portion. If you have integers with a large amount of decimal points, reduce the decimal places showing. Lastly, if you have text showing TRUE or FALSE, you can change these to be binary, so 1 and 0.Â
Report View
1. Minimize Visuals
When creating your report, try and use as few visual elements in your report as possible. This will decrease the amount of calculations that Power BI is performing when rendering your report. An example of this would be using a multi-row card when showing multiple KPIs instead of using multiple single metric cards.Â
2. Don’t Use Slicers (if possible)
Use slicers only where needed. Otherwise, use the filters available to users in the Filter Pane. Slicers are less efficient at returning required data than the Filter Pane. If you do use a slicer, make it a single drop down instead of a list. A list will pre-populate some queries in it and make it less efficient.Â
3. Use Performance Analyzer to Analyze
Power BI has a built in way for you to analyze the performance of your reports. In the View ribbon you can find the Performance Analyzer. By opening this pane and clicking Record, this will show you how fast your report renders when performing different functions in your report. You can also see what specific sections of your report are causing performance delays so that you can work on improving those sections.
I hope that these techniques will help you optimize your Power BI reports and ultimately drive adoption of your reporting.Â
Have more Power BI questions? Reach out and have your questions answered by Power BI experts!
