Introduction

Report optimisation in Power BI refers to the process of improving the load speed of the visuals and the refresh time of the reports. There are various techniques and best practices to enhance the loading time, responsiveness, and overall user experience of Power BI reports.

The following are some common areas of focus for report optimisation in Power BI:

• Data Modelling Optimisation: This involves designing an efficient and optimized data model by reducing unnecessary relationships, removing redundant tables or columns, and optimizing data types and cardinality. A well-designed data model can significantly improve report performance.
• Query Optimisation: Power BI queries data from the underlying data sources to populate the visualisations in the report. Optimising the queries can involve reducing unnecessary columns, filtering data at the source, and using query folding to push data transformation steps back to the data source.
• Report Design Optimisation: Designing an efficient and user-friendly report layout is crucial for optimising user experience. This involves organising visuals, using appropriate visuals for the data, minimising the use of custom visuals, and optimising colours, fonts, and other design elements.
• Performance Monitoring & Optimisation: Regularly monitoring the performance of your Power BI reports and conducting performance testing can help identify bottlenecks and areas for improvement. Use tools like Performance Analyzer to review the report’s usage metrics to gain insights into performance.
• Hardware & Environment Optimisation: This involves optimising the infrastructure and settings to ensure the best performance and user experience.

Let’s look into what each of these features does in more detail.

Data Modelling Optimisation

The following are some common areas of focus for data modelling optimisation in Power BI.

Simplify data models:

• Data models should be simple and well-organised.
• Reduce the number of tables and relationships between the tables to improve performance. 
• Star Schema is recommended for the data model over any other schema.

Avoid bidirectional relationships: 

• Cardinality and cross-filter directions are important aspects of any data model. 
• Many to Many cardinality and Bi-directional filters should be avoided for better performance.

Use the right data types: 

• Choose appropriate data types for columns to reduce memory consumption and improve performance.
• Usually Power BI auto detects the correct data types but it should be checked and changed accordingly for every relevant column to improve the performance.
• Consider changing the data type from Date/Time to Date. When the data type is set to Date/Time, Power BI will automatically produce a small calendar table in the background including Month, Quarter and Year. When you change the data type to Date, the background table will not be produced.

Remove unnecessary columns: 

• Remove or hide unnecessary columns from the data set. It will lead to lower memory consumption and will improve the performance of the report.
• The column can be removed from Power Query before loading the data to the data view or can be hidden by right-clicking on it.

Removing a column in PowerQuery:

Hiding a column:

Query Optimization

The following are some common areas of focus for data modelling optimisation in Power BI. 

Use query folding

Query folding in Power BI is a technique used to optimise query performance by pushing data transformation steps back to the data source. When query folding is achieved, Power BI sends a modified query to the data source, which performs the required transformations and returns the results to Power BI. It can be used on most relational databases but if you want to know whether is applicable to your data source, use the below steps:

1. Go to Power query editor and click on any of the query in the actions tab.
2. If the View Native query option is enabled then query folding is possible else it is not applicable to the current data source.

Let’s understand the process to implement query folding to improve the performance of the report. We have created a report using SQL as a data source and the data table as per the below schema:

1. Open the Power BI desktop and click on ‘Get Data’. Select SQL Server as the data source:

2. Provide the SQL server details and server connectivity mode then ‘Click Ok’.

3. Select the required view or table from the list and click on transform data.

4. It loads the data to Power BI and will show it in the Power query.

5. In order to check whether this data source supports Query folding or not, follow the above steps. (Right-click on the Navigation or any applied steps and see the Native query option.)

6. So our database supports query folding.

Assume there is a requirement to show only the data with certain filters. In our report, we will have to show only data where YearOpened is greater than 1990 AND NumberEmployees is greater than 10.

We can easily achieve it by applying filters in the Power Query as follows:

Implementing Query Folding

After applying these filters, close and apply will load the filtered data to the data view. Since our database supports query folding then we can push these transformations(Filters) to the database instead of doing them at the Power Query level. Let’s understand the process to implement it using query folding:

1. Right-click on the Navigation and click on native query. It will show a SQL query.

2. Copy the query and double-click on the source item in the applied steps .

3. Click on the advance action of the window and paste the copied SQL query to SQL Statement box.

4. Add ‘WHERE’ clause to the query at the end with filters condition as per requirements. Remember we need to provide a database name which is mandatory for query folding.

5. Click ‘OK’ and see filtered data loaded to Power BI and there are no steps logged in the applied steps section.

This enables a better query performance of the data and will improve the data refresh time and report performance. 

Reduce unnecessary data loading

This is also another important method to optimise your report. You can disable loading the table if you are not planning to use it as a dimension or a fact table. This is commonly done for tables that are used for merge or append functions.

Assume we have 3 tables loaded to the Power BI and append these tables into a single table. Now if we load all tables then it would be a duplicate data instead disable the load of these tables and load only the final appended table to the data view.

 In order to enable or disable a load : 

1. Go to Power Query.
2. Right-click on the table.
3. Uncheck enable mode option to disable it.

Power BI will not load the selected table to the data view.

Report Designing Optimisation

The following are some common areas of focus for report designing optimisation in Power BI.

Limit visuals and elements

Minimise the number of visuals and elements on a report page to improve rendering and interaction performance.

Use slicers wisely:

Avoid using too many slicers on a page as each slicer can impact performance.

Optimise visuals: 

Use appropriate visual types, reduce unnecessary formatting, and avoid complex visual interactions to improve rendering performance.

Performance Monitoring & Optimisation

The following are some common areas of focus for performance monitoring and optimisation in Power BI.

Use Performance Analyzer

Utilize Power BI’s Performance Analyzer tool to identify and troubleshoot performance bottlenecks.

Monitor query performance

 Keep track of query execution times and identify slow-running queries to optimise them.

Utilise usage metrics

Analyse usage metrics and identify reports or visuals that are causing performance issues.

Optimise data refresh

Schedule data refresh during off-peak hours and optimise queries used in data refresh operations.

Hardware and Environment Optimisation

Optimise Power BI Gateway

Configure and optimise the Power BI Gateway for efficient data transfer between on-premises and cloud-based data sources.

Scale resources

Allocate appropriate resources to Power BI, including CPU, memory, and disk, to handle the workload efficiently.

Consider DirectQuery or Live Connection

If feasible, use DirectQuery or Live Connection instead of importing data to reduce memory consumption and improve performance.

Conclusion

This article has highlighted the benefits of using a calendar table and how to add the table using various methods. Adding a calendar table in Power BI is important for time intelligence calculations, data aggregation, visualisation, customisation, and improved performance.