Power BI Best Practice Guide
This Power BI best practise guide will help you improve the speed, security, and usability of your Power BI reports and dashboards. We are honoured for our experience in delivering business intelligence and analytics solutions as the Microsoft Power BI Partner of the Year for 2021.
Based on our expertise, we’ve produced this list of best practises, which includes:
- How Can Power BI Be Made More Secure?
- How to Speed Up Power BI
- How to Make Reports and Dashboards Have a Better User Experience (UX)
How To Improve Power BI Security?
1. Row-Level Security should be enabled (RLS)
Row-Level Security limits user access to certain rows in a database based on the user’s characteristics (role) when running a query. Power BI only imports data that the user is authorised to see using RLS.
Combining Power BI roles with back-end responsibilities can significantly enhance performance.
2. Use bespoke visuals that have been certified.
Custom graphics on AppSource that have undergone rigorous quality testing are known as Power BI certified visuals. Certified power bi custom visuals must contain solid, high-performance code, according to Microsoft. Only certified bespoke graphics can be viewed in the Export to PowerPoint mode and through email subscribers.
3. Sort the data in the report by the influence it has on the business.
To classify data as having a high, medium, or low business impact, use Power BI sensitivity labels. Users who want to share data that has a high business impact (HBI) must request a policy exception. Exceptions are not required for data with a low business impact (LBI) or a medium business impact (MBI).
You may enhance user knowledge about security and how reports should be shared inside and outside the business by utilising Power BI data sensitivity labels.
Create quick reports in Microsoft Power BI on the web
How to Speed Up Power BI
1. In dashboards and reports, keep the number of visuals to a minimum.
Using too many visuals on a single report causes it to run slowly. Limit the number of widget graphics per report page to eight, and grids to one per page. Limit the number of tiles per dashboard to ten. Limit pages to 30 points in general, assuming that each form of image is worth a different number of points:
Deck of cards: 1
Gauges: 2
Graphs: 3
Maps: 3
Grids: 5
2. Remove any visual interactions that aren't necessary.
All graphics on a report page can interact with one another by default. You can increase report performance by reducing the number of queries fired at the back end by removing superfluous interactions.
3. Instead of using Personal Gateway, use an on-premises data gateway.
Data is imported into Power BI via Personal Gateway. When working with huge databases, the on-premises data gateway (also known as Enterprise Gateway) imports nothing.
4. For live Power BI service connections and scheduled data refreshes, use distinct gateways.
If you utilise the same gateway for a scheduled refresh and a live connection, the live connection’s performance will be affected.
5. Before using, test the modified visual performance.
When dealing with enormous datasets or sophisticated aggregations, custom visualisations often struggle. The Power BI team does not typically test uncertified custom graphics. Consider replacing a custom graphic with a different one if it performs poorly.
6. In data models, keep complex metrics and aggregations to a minimum.
Computed measures should be used instead of calculated columns. Push calculated columns and measures to the source whenever possible. They are more likely to perform faster the closer they are to the source.
7. When possible, use the Star schema instead of the Snowflake schema.
The query structure of the Snowflake schema is complex, making it difficult to make modifications. The star schema is simple to understand, requires fewer joins, and reduces data redundancy.
Create a report from scratch
8. Slicers should be used sparingly.
Slicers are a terrific method for consumers to browse data, but they come at a cost in terms of performance. Each slicer sends out two queries: one to get the data, and the other to get the specifics of the selection. The performance of a system with too many slicers is severely slowed. Use the Filter pane to determine which slicers are used the least to eliminate unneeded slicers.
9. Ascertain that all reports and data sources are in the same location.
You may reduce the consequences of network delay by keeping the tenant and data source in the same location. Data transport and query execution are both sped up when a region is shared.
10. Instead of importing whole data sets, import only the columns and tables you need.
As much as feasible, keep the model narrow and slim. Columnar indexes are used by Power BI, thus tables that are longer and leaner perform better. When you need to import a large table, divide it into numerous partitions and process them all at the same time.
11. Instead of starting with an empty.PBIX file, use templates (.PBIT files) to expedite and standardise report creation.
You may create branded reports more quickly using templates. Custom colour palettes and themes can be saved as templates, ensuring that company identity is applied uniformly across all sites. Templates can also connect to regularly used data sources and provide DAX macros.
12. Reduce the number of inquiries
Query reduction options reduce the number of queries sent by Power BI. Select the “Add an Apply button to each slicer so you may apply modifications when you’re ready” option for slicers. Choose “Add a single Apply button to the filter pane to apply changes all at once (preview)” for filters.
13. When dealing with high cardinality columns, avoid bidirectional and many-to-many relationships.
Many-to-many and bi-directional relationships check more data points and travel more channels. Bi-directional associations against high-cardinality columns, as a result, have a negative impact on report performance.
14. Floating point data types should be avoided.
Floating point data formats can cause unpredictably large round-off mistakes and degrade report performance.
15. In your model, replace the auto-generated date table with a custom date table.
When you use a date table, you can use Power BI’s time-series function. The auto-generated date table, on the other hand, builds a separate date table for each date column, significantly expanding the model size.
To reduce model size, utilise a single date table and place all required relationships in fact tables. Split the date and time when creating your date table to optimise data compression. Here’s how to turn off the auto-generated date table and how to turn it back on.
16. On non-attribute columns, set Is Available in Mdx to false.
For measure columns and any other columns you don’t want end users to use, disable attribute hierarchy. This minimises the size of the data and the time it takes to load it.
17. Reduce the amount of data that is loaded when a page is loaded.
To limit the amount of data loaded on page load, use bookmarks, drill-through pages, and tooltips. This reduces the time it takes for landing pages to load.
18. For static pictures, use report backgrounds.
Use report backgrounds instead of different visuals for static images. This gives the end user the same information for a fraction of the expense of performance.
How to Make Reports and Dashboards Have a Better User Experience (UX)
1. Ensure that the cache refresh frequency matches that of the data source.
The Power BI cache refresh frequency is set to one hour by default. The frequency of cache updates should be set at intervals that are similar to the frequency of data source refreshes. If your data set, for example, only refreshes once a day, you should adjust the cache frequency accordingly. For end users, this increases report performance and accuracy.
2. Use a light or white backdrop colour.
White or light backgrounds are printer-friendly, allowing you to effortlessly publish your reports both online and offline.
3. Reduce the length of numbers
When showing numbers, only show up to four numerals at a time. Limit measures to two numerals to the right of the decimal point to maintain consistency across decimal points. Scale up to tens of thousands or millions of people if necessary.
4. To provide extra information on visuals and stats, use tooltips.
Report tooltips are a terrific method to share more information in a small amount of space. To avoid information overload, limit the number of visuals you use in report tooltips.
5. Use names that people will remember.
Report objects can be given aliases in Power BI. When naming columns and measures, use business-friendly names to avoid uncertainty for end users. Consider concealing unnecessary columns to avoid confusing users.
6. Allow users to customise report visuals.
When sharing reports with users, enable “Personalize graphics” in report settings. Users can then employ ad-hoc investigation to get further insights. Self-service BI can be enabled by combining this feature with personal bookmarks.
7. Within the image and on the page, avoid scrolling.
A bad user experience is created by several scrolling on a single page. When as all possible, keep your page sizes to the usual report sizes. Toggle the visibility of visuals using the Bookmark and Selection pane if necessary.
8. Instead of asking users to right-click on data points, utilise drill through buttons.
Drill through buttons are more natural than right-click buttons since they clearly indicate actions and outcomes. Use conditional formatting to make sure button wording is context-sensitive, which will improve the user experience even more.