Best practices for Power BI performance & security: Part I
In the past three years, we have developed over 1,000 complex Power BI reports for our customers. Based on our extensive engineering experience with the Microsoft platform, we have developed a series of best practices for architects and developers that we’d like to share.
-
- Limit the number of visuals in dashboards and reportsAccording to the Microsoft Optimization guide for Power BI, placing many visuals in a single report slows report performance. To combat this, limit widget visuals to eight per report page, and grids to one per page. Limit pages to a total of 30 points (cards 1, gauges 2, charts 3, maps 3, grids: 5), and limit tiles to 10 per dashboard.
- To improve report performance, remove unnecessary interactions between visualsBy default, all visuals on a report page can interact with one another. For optimal report performance, interactivity should be minimized. Reduce the number of queries fired at the back end and improve report performance by disabling unnecessary interactivity.
- Enable row-level Security Row-level Security (RLS) 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 back end can result in substantial performance gains. Test all roles before rolling out to production.
- Use Microsoft AppSource certified custom visualsPower BI certified visuals are AppSource visuals that have passed rigorous quality testing, and Microsoft verifies that all certified custom visuals have robust, high-performance code.These are the only custom visuals that can be viewed in Export to PowerPoint mode and email subscriptions.
- Use preview feature of hierarchy slicers instead of custom visualIf you need to show hierarchy in slicers, enable the preview feature provided by the Power BI desktop instead of using of a custom visual.
- Provide data categorization for Power BI reports 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 sensitivity labels, you raise user awareness about required security and how reports should be shared inside and outside the organization.
- Use on-premises data gateway instead of Personal GatewayPersonal Gateway takes data and imports it into Power BI. Enterprise Gateway (on-premises data gateway) imports nothing, which is more efficient when working with large databases.
