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.
On the Power BI reporting front, here are the main things to keep in mind
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
- Use the Enterprise mode rather than Personal mode.
- Recommended configuration is: 8 CPU cores and 16 GB RAM
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.
- Unused columns should be removed
- Indexing of the columns that are commonly filtered
- Keep a check on the precision of the fields, remove columns with unnecessary precisions.
- Some DAX functions get tested every row of the table, try to avoid them when possible.
- Use calculated measures instead of calculate columns for better performance. And push them to the source wherever possible.
- Use slicers sparingly.
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.
More best practices, for building better Dashboards
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:
- Most importantly: Keep it Clean and uncluttered.
- Don’t add too many details on the dashboard. Users can click and drill into the reports to find out the details if needed.
- Create different dashboards for different audience sets.
- The more important information you want the users to look at, make it bigger than the other tiles.
- Depending upon the size of the screen your dashboard is going to be displayed onto, add the number of tiles/units of information on the screen. If it is a small screen, do not make the users scroll down a lot.
- Depending upon the type of information you want to project, choose the right kind of visualization. If some information can be shown best by using a simple bar chart, then use a bar chart. Don’t overdo it. If more detailed info is required, then choose a list.
- Filter your information keeping in mind the amount of data that you expect being pulled. Showing 100 users’ data on a single chart would obviously cramp it and would not be easily readable. Either filter it or make the tile bigger.
- Sort the charts based on the required information. Either by the measure or by the data.
- Label your charts/tiles and your data appropriately so that it makes sense to the users and it is readable.
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!
Author’s bio:
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.