Optimizing relationships
When report visuals are configured with fields from multiple queries or tables and expected results are not seen, then it’s possible there are issues with the relationships that have been set up (or autoconfigured by Power BI).
When optimizing relationships, it’s important to understand the true cardinality of the tables involved (this may require analysis using Data view) and how that has been configured in Power BI (properties on the connecting line between queries in Model view). It’s also important to verify that the filter direction supports propagation (shown by the arrows on the connecting lines in Model view).
Some other things to look out for that will make for suboptimal relationships include blank values matching columns between the tables and mismatching or incompatible data types for the matching columns between the tables.
Now, let’s look at how we can optimize visuals.
Optimizing visuals
When optimizing visuals, it’s important to think about all the ways visuals are used in Power BI. Visuals can be used in the following:
- Reports
- Dashboards
- Paginated reports
Let’s look at how we can uniquely optimize visuals in each case in the next sections.
Optimizing visuals in reports
To optimize visuals in reports, we need to follow a layered approach. First, review each visual and determine whether it is required. Limit the visuals used to only those required to meet business requirements. Adding additional visuals that are not needed will have a negative impact on report performance. Next, apply the most restricted data filtering to the visuals. This is accomplished by using filters for all pages, only the selected page, or only a specific visual. Lastly, you should consider performance when adding each visual to the report canvas. Each visual used has the potential to decrease the overall performance of the report, especially third-party or custom visuals, as they may not have gone through extensive performance testing before becoming available for use.
Optimizing visuals in dashboards
To optimize visuals in dashboards, you need to remember that Power BI keeps a cache of data in order to serve dashboards in the Power BI service. The cache used by the Power BI service helps to enable consistent performance across multiple users (unless row-level security is used, in which case a per-context cache will be built). Live report tiles and streaming tiles will not use a cache since they are used to serve very up-to-date information.
By default, the dashboard cache will be updated automatically by the service every hour, but this can be configured manually in the dataset settings. This should be set to the default setting or optionally configured per business requirements.
Optimizing visuals in paginated reports
The optimization of visuals in paginated reports centers around the performance of the data retrieval settings and Premium capacity memory allocation. The optimization of paginated report data retrieval settings includes concepts such as limiting data used by the paginated reports, using expression-based fields, and using filters (applied to a dataset in Power BI) or parameters (filtering injected to underlying data sources). All these techniques go back to the idea of limiting the amount of data in the dataset used by the report. More details on using paginated reports will be covered in the chapter on paginated reports.
Additionally, since paginated reports rely on the Power BI Premium service, it’s important to monitor the capacity resource usage using tools such as the Power BI Premium Capacity Metrics app. The use of the Metrics app is important for Power BI Premium administrators since it is possible to overload a Premium capacity with too many Premium workspaces for the specified SKU size. Paginated reports are run within a protected sandbox per Premium capacity to help ensure the isolation of resources. But it’s also important to ensure only trusted publishers in your organization have access to publish paginated reports.
Next, we will look at optimizing using group by and summarize aggregations.