Power BI Best Practices | MAQ Software

Best Practices Guide

Power BI Best Practices

July 26, 2018 3 Minutes to Read

To improve the performance and security of enterprise-grade Power BI implementations, we share our best practices for architects and developers. We manage hundreds of Power BI based dashboards that are used every day to drive operations and marketing by thousands of enterprise users. We would love to learn from your experience to further improve the guide.

  1. Limit the number of visuals in dashboards and reports
    • According to Microsoft Power BI Performance Best Practices, placing many visuals in a single report slows report performance.
    • Limit widget visuals to no more than eight per report page and grids to no more than one per page. Limit pages to no more than 30 points (cards: 1, gauges: 2, charts: 3, maps: 3, grids: 5).
    • Limit tiles to no more than 10 per dashboard.
  2. To improve Power BI report performance, remove unnecessary interactions between visuals
    • By default, all visuals on a report page can interact with one another. For optimal performance, interactivity should be controlled and modified.
    • Disabling unwanted interactivity reduces the number of queries fired at the backend and improves report performance.
  3. Enable Row-Level Security (RLS) where applicable
    • Row Level Security restricts user access to certain rows in a database depending on the characteristics (role) of the user executing a query. With RLS, Power BI only imports data the user is authorized to view.
    • Combining Power BI roles with roles in the backend can result in substantial performance gains.
    • Test all roles before rolling out to production.
  4. Use Microsoft AppSource certified custom visuals where applicable
    • Power BI certified visuals are AppSource visuals that have passed rigorous quality testing. Microsoft verifies that certified custom visuals have robust and well-performing code.
    • Certified custom visuals are the only custom visuals that can be viewed in Export to PowerPoint and email subscriptions.
  5. If you observe poor performance in Power BI, do not use hierarchical filters
    • If you observe high page load times when using hierarchical filters, remove the hierarchical filters and instead use multiple filters for the hierarchy.
  6. Provide data categorization for Power BI reports (HBI, MBI, LBI)
    • High Business Impact (HBI) data requires users to get a policy exception to share the data externally. Low Business Impact (LBI) and Medium Business Impact (MBI) data do not require exceptions.
    • By using Power BI data classification, you raise user awareness about the security level that should be used and how reports should be shared inside and outside the organization.
  7. Use the On-premises data gateway instead of Personal Gateway
    • Personal Gateway takes data and imports it into Power BI.
    • Enterprise Gateway imports nothing, which is more efficient when working with large databases.
  8. Use different Power BI gateways for “Direct Query” and “Scheduled Refresh
    • If the same gateway is used for Scheduled Data Refresh and Live Connection, Live Connection performance will slow down when Scheduled Data Refresh is active. Avoid this issue by creating separate gateways for Live Connection and Scheduled Refresh.
  9. Test each custom visual on a report for performance to ensure fast report load time; use an alternative visual if the chosen visual performs poorly
    • Custom visuals that are not certified are generally not thoroughly tested by the Power BI team. They might perform poorly when handling large datasets or complex aggregations. If a custom visual performs poorly, consider changing it to different visual.
  10. Limit complicated measures and aggregations in data models
    • Push calculated columns and measures to the source where possible. The closer they are to the source, the higher the likelihood of improved performance.
    • Create calculated measures instead of calculated columns.
    • Use star schema for designing data models.
  11. Use slicers sparingly
    • Slicers are a great way of allowing users to navigate data, but they come at a performance cost. Each slicer generates two queries: one to get the data, and another to fetch selection details.
    • Creating too many slicers impacts performance negatively.
  12. Ensure that the Power BI report and the data source are in the same region
    • With the tenant and data source in the same region, network latency is reduced. This results in faster data transfer and faster query execution.
  13. Import only necessary fields and tables instead of entire datasets
    • Keep the model as narrow and lean as possible.
    • Power BI works on columnar indexes; longer and leaner tables are preferred.
  14. Ensure the cache update frequency aligns with the data source refresh frequency
    • Report cache refresh should be set to intervals similar to the data source refresh frequency.
    • By default, the Power BI cache frequency is set to one hour. If, for example, your dataset refreshes only once per day, you should update the cache frequency accordingly.

References

Microsoft offers additional documents that provide a high-level framework for best practices. We strongly encourage you to review the following documents: