Performance Tips for Power BI

There are so many ways to improve the performance of the Power BI dashboard. In this blog, we’ll look at below mentioned performance tuning tips.

1.Query Reduction

2.Disable Loading

1.Query Reduction:

Query Reduction is primarily for Direct Query connections. you can use the direct query for large datasets or realtime reports. Whenever the user interacts with the Dashboard, Power BI will send queries to the database to retrieve the necessary data. There is a way to control when the queries have to hit the database. This can be controlled by enabling the Query reduction option. Also, we can minimize the number of queries that sends to the database using Query reduction.

In such cases, we can build the same “sales by segment” report for both south and East region by sending a single query to the database using Query Reduction instead of sending multiple queries so that we can reduce the refreshing time.

Figure 1.1

To Enable Query reduction option in Power BI Desktop, go to File > Options and settings > Options and select Query reduction. (Refer figure 1.2)

Figure 1.2

Once we enabled the Query reduction option, each of the filters/slicers in the dashboard will get the “Apply” button. Initially the Apply button is Gray (which means no change in this slicer selection). If you change the slicer selection to another value, you’ll see a change in the color of the Apply buttons (Yellow). Power BI will never send the queries to the database before selecting the “Apply “button on slicers/ filters.

In this scenario (Figure 1.3), once we select both south and east on region slicer, the dashboard will not get any change. So, we need to select the “Apply” button on the region slicer to get the updated dashboard. When we select the “Apply” button, Power bi will send the single query to the database that you can see in the below figure. (Marked by the red line in performance Analyzer pane).

By using Query reduction, we can control when queries go to the database. Also, we can minimize the number of queries go to the database, reduce the refreshing time.

Figure 1.3

1.1 Disable Cross Filtering:

Once we disable cross-filtering, visualizations on a report page can’t be used to cross-filter and cross-highlight the other visualizations on the page. In the below figure (Figure 1.4), having selected the “Consumer” Segment on the pie chart, the column chart is not cross highlighted to show the sales by category and segment.

Already we have disabled cross-filtering (Refer 1.2 figure). So, we can avoid the number of queries to go to the database.

   Figure 1.4

2. Disable Loading:

            Look at the below scenario (Figure 2.1). when you append Table-1 to Table-2, you will get the Append Table like Table-3. So, you don’t need Table-1 and Table-2 in your model because of Table-3 having all the data. In such cases, Power BI will generate 3 queries. By default, these queries from Query Editor will be loaded into the memory of the Power BI Model (Refer figure 2.2).

                                                                                            Figure 2.1

                                                                                           Figure 2.2

If you want to remove the unwanted tables (Table1, Table2) from the model, the best approach is to disable loading before closing the query editor (Refer figure 2.3).

Disable Loading doesn’t mean the query won’t be refreshed, it only means the query won’t be loaded into the memory. When you refresh the data, even queries marked as Disable Load will be refreshed, but their data will be used as an intermediate source for other queries instead of loading directly into the model.

To disable loading, go to Query Editor, then Right click on the query that you want to disable load and uncheck “Enable Load” you will get the message box that shows “Disabling load will remove the table from the report, and any visuals that use its columns will be broken.” Click on Continue, then you will see the load is disabled for that query.

                                                            Figure 2.3

Once you disabled the loading for both Table1 and Table2 queries, only the Append Table query will be loaded into the data model so that you can reduce the memory consumption. (Refer figure 2.4)

So, we need to remove every unused table from the Power BI model to reduce memory consumption. Less memory consumption leads to better performance

                                                                                   Figure 2.4

This blog helps to enhance the performance of your data model with query reduction and disable loading. Please feel free to reach Cittabase for more information. Visit our blogs for more topics on Power BI.