Most of the developers working on Microsoft Power BI face issues in two ways. First, the Microsoft Power BI Application installed on the machine is not working properly and the developer is facing problems in performing any task. Second, the issue is related to report optimization. Generally, it is the responsibility of the developer to make an optimized report so that report loads quickly and also report refresh time is quite less.
Here, in this article, we will discuss the second issue that is report optimization. So, Why Microsoft Power BI is Slow? Microsoft Power BI is a tool that can consume a very large amount of data so it is very necessary to follow the steps of report optimization while developing the report. Using a lot of images, multiple visuals, improper DAX formula, consuming unnecessary columns into the data model, not taking into consideration query folding are some of the reasons due to which Microsoft Power BI Report is slower.
Best Practices for Microsoft Power BI Report Optimization
It is very important to know about various steps of report optimization while developing a Microsoft Power BI Report for the end users/client. Optimizing your Power BI report make report load faster and also help in loading data faster during refresh interval.
Let`s see in this article Microsoft Power BI report optimization steps.
Step 1: Using Query Folding
According to Microsoft Docs, Query Folding is the ability of Power Query to generate a single query statement to retrieve and transform source data.
When Query folding takes place it helps to process and compute the transformation steps applied in power query on the database side which helps to improve the query performance. Mainly, it helps to load data faster while performing refresh operations.
During Incremental refresh as well, query folding help to perform efficient report operation and help in reducing the refresh duration.
Some of the sources which support query folding are relational database, OData Feed, Sharepoint List, Active directory, etc. It is very essential to know whether query folding is taking place for a particular query or not. If the native query option is enabled for a query then Query Folding is taking place if not enabled then Query Folding is not taking place. Please see the below screenshot for reference.
Step 2: Disable the Enable Load Option for a Query
Whenever a table is loaded in the form of query in the Power Query then by default enable load option is active and that particular query is loaded into the data model. But there are certain tables that act as a supporting table and are not required in the data model.
We can disable the Enable Load option for such supporting tables so that they do not load into the data model and increase its size since we know there is a data size limitation for free, pro licenses, and also in premium capacity.
Please find the below screenshot showing how to disable the enable load for a query (table).
Step 3: Use Power BI Dataflow instead of Power Query
It has been tested that using Power BI dataflow is better for applying transformation steps instead of Power Query.
Important thing to note here is that Power BI dataflow is available with Premium capacity. So if you are having Premium workspace then make sure to use Power BI dataflow to applying transformation steps for achieving better performance.
Step 4: Reduce Number of Images used in the Report
In some of the reports there is a requirement for using multiple images but as a developer we should use lesser number of images as using lot of images in the report reduces the performance of the report and report take a lot of time to render the image.
Also, you can convert the image to base 64 if the image file size is more. You will be facing difficulty in using image of larger data size so converting image to base64 is a good choice.
Step 5: Reduce number of Visuals in the Report
For better performance of Report, it is essential to reduce the total number of visual in the page of the report. There are two options for one is the use a visual which can replace multiple visuals such as small multiple visuals in Power BI which is now generally available in July 2021 update.
So, main point is to explain is to use single visual instead of multiple visual as multiple visual take time to render while loading in Power BI Service.
Step 6: Use Star Schema for Better Performance
It has been proved practically that best schema for preparing data model on the Power BI side is Star Schema. Please check out Guy in a Cube vedio for more detail.
Step 7: Evaluate Custom Visual Performance
Sometime we have to use Custom Visual into the report for meeting the expectation of the client which is not possible through standard visuals available in Power BI.
But there is constraint that all custom visual available in the marketplace are not optimized for best performance. Using such visual can cause performance issue in the report.
So, it is essential to be cautious while using custom visual into the report.
Step 8: Use Optimized DAX Formula
It is important to use appropriate dax function while creating measure and calculated columns. Please check out DAX best practice guide for more details.
Step 9: Limit Data Model
You can limit the data model by restricting unnecessary columns either at the database level or power query level.
Another method is to use row level security which will restrict rows based on the filter condition set in the rule while setting up the row level security. One more method of limiting data model in power bi is to disable the enable load in power query. This method I have explained in the Step 2.
Step 10: Use Measure instead of Calculated Column
Measure are calculated on the runtime while calculate column consume memory for processing information so it is best practice to create measure instead of calculated column.
Step 11: Use Variable instead of Measures
If there is any formula which is not required to be used in any other measure then it is a suggested to use variable as it help keep the code clean and also make code structured.