For better performing reports and dashboards, it is very essential that they be built keeping in mind the best practices. Below is a consolidated list of best practices. And as we go along this list will have more additions.
1. Visuals:
Keep a tab on the number of visuals you use. The more the number of visuals, the slower the Dashboard will load. Only add the metrics really required by the audience. If metrics are more, divide them based on their use and add them to multiple different dashboards. Also provide drill-down features on certain reports which need more detailing, rather than giving all the information on the main report.
All the OOB visuals are rigorously tested for their performance, however, if you have to add your custom visual, please make sure you test its performance to make sure it is following the standards and is up to the mark, otherwise it will impact the performance of the report. Otherwise, you could use AppSource certified custom visuals which are tested to satisfaction.
2. Query Performance:
Incase where we are using DirectQuery or LiveConnection: if the report is slow to load, then often this would be a problem with the underlying data source since these queries get sent in real-time to the underlying data source. You can use data source specific tools to figure out the issue and improve performance.
If you are using on-premise data gateway
Monitor your gateway performance to figure out if there is a bottleneck, if so, either Scale-up using better hardware or Scale out by splitting the datasets into different gateways.
In order to monitor the performance of your report and figure out which visuals are taking longer to load; you can attach the SQL profiler to your Power BI desktop and get a good view of the performance of your queries.
3. Optimization:
Make sure to optimize your data model for better performance. Keep the model as lean as possible.
4. Filters:
The performance of the visual depends hugely on the amount of data it needs to load. So make sure that you only query the data that is absolutely required in the visual and that cannot be filtered for the user experience.
Enable RLS (Row level security) where applicable. That will only pull the data that the user has access to.
If the performance is slow while you use Hierarchical filters, try and replace them with regular multiple filters.
5. Network factors:
Each PowerBI tenant is assigned a Home Region. This is where your requests are routed to. And they can be further re-routed to the region of the underlying data source. This adds the network factor to the report performance. In order to minimize this factor, try to keep the home regions of your tenant and data sources as close to each other as possible.
You can use Azure SpeedTest tool to find out the network latency. This tool gives you an average latency of a data center at any given time.
And if you are looking for lower network latency networks in order to better the performance, use the Azure ExpressRoute which is a private connections service to Azure with more reliability, faster speeds, and lower latency.
6. Dashboards vs. Reports:
Retrieving data from the query cache is always better in terms of performance. So, it is better to have the dashboard as the first page to load for your users. Pin often used visuals to the dashboards. Users can further click and drill for details.
However, when loading a report, the queries are made on the fly to the data source. So that becomes a bottleneck for the performance if it is a heavy query.
7. Data Categorization:
Use Power BI data categorization to make the High Business Impact data more secure. That way the user will need to get a policy exception in order to share the data externally.
8. Use separate Gateways:
Create separate gateways for Live connection and Scheduled refresh because live connection performance slows down when scheduled data refresh is active.
Dashboards are a single window to show the current state of your most important data. And we should make sure that the information that is required, should stand out and speak for itself. Ask yourself questions as mentioned below, before developing a dashboard:
“Who is going to use this dashboard?”
“What are the key metrics the audience wants from this dashboard?”
“What level of detail is really required for the audience”
And based on the answers to the questions above, follow the below approach:
Many enterprises have prioritized digital transformation for being future ready as they are moving towards a data-driven, decision-making milieu that will change their products, services, and processes. AhaApps’ consulting and staffing solutions will help you take the blindfold off and get new insights from your data that you couldn’t have fathomed ever. Are you interested in seeing how Microsoft Power BI can help transform your business? We’d love to chat about what you are working on. Connect with us today and let the change begin!
Sunil Raheja has been associated with AhaApps as a Dynamics Practice Head since 2019. He is extremely passionate about Dynamics and loves to solve the challenges posed in the domain using his logical ability and expertise. If he isn’t being a Dynamics superhero, in his spare time, he likes to take up a new hobby-currently it’s woodworking. Sunil is a meditator and is a staunch believer in maintaining a healthy work-life balance.