Power BI Best Practices-Part 1
There are dozens of Power BI best practices. But you may not find guidance on applying those best practices. By that we mean, do you know Power BI best practices to enhance Security? Or do you know this specific Power BI best practice, you are implementing, is meant to fall under performance optimization?
Jumping straight to the main topic, this blog is all about Power BI best practices. However, we i.e., Calculate Data, have categorized all those dozens of Power BI best practices Under 8 labels.
- DAX Expressions
- Error Prevention
- Naming Conventions
So, you can know well when you need to implement these best practices or you know how to combat any problem regarding security, report performance, design, formatting, etc. Since they are too many, we shall be covering all these in 2 parts. Therefore, this blog is all about Power BI best practices for Security, Performance and Design.
Power BI Best Practices for Security, Performance and Design
We’ll go through a list of 26 practices while digging into the required explanations as reasoning. Let’s get started with Security.
What are the Best Practices for Power BI Security?
- Enable Row-Level Security (RLS)
For protecting sensitive data, Power BI has a powerful data governance capability i.e., Row-level Security (RLS). Enabling it in Power BI reports lets you limit the data and its visibility to an end-user by applying instructions to rows in a table and removing access to certain data.
- Use Microsoft AppSource-certified custom visuals
The Power BI-certified visuals are custom visuals which have passed rigorous quality testing and are verified by Microsoft to have robust and high-performing code. These custom visuals can be viewed in Export to PowerPoint and email subscriptions.
- Categorize report data by business impact
Use Power BI data sensitivity labels to improve user knowledge about security and sharing of reports inside and outside the organization. This will let you classify data as high, medium, or low business impact. Data labelled under High Business Impact (HBI) demands users to request a policy exemption for sharing it externally. While data under Low Business Impact (LBI) and Medium Business Impact (MBI) do not involve exemptions.
What are the Power BI Best Performance Practices?
- Use an on-premises gateway instead of a Personal Gateway
The personal gateway imports data into Power BI while the on-premises (Enterprise) gateway imports nothing and works best for large databases.
- Use separate gateways for Power BI service live connection and scheduled data refresh
Using the same gateway for both slows down the performance of the live connection during the scheduled refresh. So, create separate gateways to avoid such issues.
- Limit complex measures and aggregations in data models
The closer to the source, the faster the performance. So, create calculated measures instead of calculated columns. Where feasible, push calculated columns and measures to the source.
- Ensure reports and data sources are in the same region
This limits the effects of network latency and ensures faster data transfer and query execution.
- Use templates (.PBIT files) instead of starting with an empty .PBIX file
Templates let you save custom colour palettes and themes. Thus, enabling you to develop and standardize branded reports more rapidly by pre-applying templates on all pages.
- Avoid bi-directional and many-to-many relationships against high-cardinality columns
Because they pass through more pathways and check more data points as compared to high-cardinality columns. So, better to avoid them.
- Replace the auto-generated date table with a custom date table in your model
Auto-generated date table creates each date column. Thus, expanding the model size. Instead use custom date tables and split the date and time.
- Set IsAvailableinMdx to false on non-attribute columns
Disable attribute hierarchy for measure columns or any columns, you don’t need the end user to use it. This reduces data size and load time.
- Align the cache update frequency with the data source refresh frequency
If the data source updates once a day, it’s better to set the cache update frequency accordingly. This enhances both the performance and the accuracy.
7 Bonus Performance booster Practices for reaching so far 😉
- Use slicers sparingly
- Reduce queries
- Use report backgrounds for static images
- Remove unnecessary interactions between visuals
Visuals on a report interact with one another by default. So, too many of them will slow down the report’s performance. For optimal performance, either limit visuals or modify interactions between them. To limit the visuals try this practice i.e.
- Keep widget visuals to eight per report page
- Maintain grids to one per page
- Keep tiles to 10 per dashboard
- Use Star schema instead of Snowflake schema when possible
Snowflake schema has a complex query structure while Star schema has a simple structure and is easy to read. This tends to reduce data redundancy.
- Avoid using floating point data types
They cause unexpected round-off errors that drop report performance.
- Reduce the amount of data loaded on the page load
Instead use tooltips, drill-through pages, and bookmarks.
Power BI Best Design Practices for Reports and Visuals
We have already covered this in detail in one of our recent blogs. So, in this category, we’ll only go through the points instead of digging into the reasoning.
- Use white or light background colours
- Shorten numbers
- Use tooltips to provide more information on visuals and metrics
- Use names that are meaningful to users
- Allow users to personalize visuals in a report
- Avoid scrolls within the visual and on page
- Use drill-through buttons instead of expecting users to right-click on data points
If you wish to learn more about best designing practices, read our blog Top 7 Tips for designing effective Power BI Dashboards. This blog not only provides in-depth details for the above but also covers the top and best 7 tips for dashboard designing, implemented by us.
In a Nutshell
We’ve gone through the Power BI best practices for security, performance, and design. We tried to cover as much as possible while delivering a proper explanation. And we hope you implement them as well and get to know the difference in your security, performance, and design.
In the next series of our blog, we’ll cover more practices to strengthen our DAX Expressions, Formatting and Maintenance. Till then have happy learning.