How to optimize your Power BI Solution

However, the size of a Power BI data model as well as the size of the pbix file can grow very fast, which can put a strain on memory consumption and thus result in compromising the power of its VertiPaq engine and slowing down the performance substantially.

To fully harness the benefits of Power BI, there are a few considerations to make that enable developers and administrators to produce and maintain optimized Power BI solutions. You can optimize your solution at different architectural layers. Layers include:

• The data model, including the DAX formula for calculated measures
• Visualizations, dashboards, Power BI reports
• The environment, including capacities, data gateways, and the network

Optimizing the data model

The data model supports the entire visualization experience. Data models are either hosted in the Power BI ecosystem or externally (using DirectQuery or Live Connection), and in Power BI they are referred to as datasets. It's important to understand your options, and to choose the appropriate dataset type for your solution. There are three dataset modes: Import, DirectQuery, and Composite. For more information, see Datasets in the Power BI service, and Dataset modes in the Power BI service.

There are several ways to improve the efficiency of a Power BI data model:

1. Remove Unnecessary Columns and Rows:
   • Trim down your dataset to only include the necessary columns and rows. This reduces the amount of data that needs to be loaded and processed.
2. Use Import Mode for Small to Medium-Sized Datasets:
   • For datasets that can comfortably fit into memory, use Import mode rather than DirectQuery to take advantage of the in-memory processing capabilities.
3. Utilize Query Folding:
   • Ensure that as much data transformation as possible is performed in the source system. Power Query should push the operations to the data source, rather than performing them in Power BI.
4. Avoid Creating Many-to-Many Relationships:
   • Many-to-many relationships can lead to complex DAX calculations and performance issues. Try to model your data in a way that avoids them.
5. Optimize Data Types
   • Use the most appropriate data type for each column. For example, use integers instead of decimals for whole numbers.
6. Partitioning and Aggregation:
   • For very large datasets, consider partitioning and aggregating data in the data source before importing it into Power BI.
7. Optimize Hierarchies:
   • Avoid creating overly complex hierarchies, as they can slow down report rendering and filtering.

There are also a few ways to improve the efficiency of the DAX formulas in the model:

1. Minimize Unnecessary Calculations:
   • Avoid creating measures that are not frequently used or needed in reports.
   • Use measures only when dynamic calculations are required.
2. Use SUMMARIZE and/or ADDCOLUMNS Sparingly:
   • These functions can be resource-intensive, especially when applied to large tables. Be selective in their usage.
3. Avoid Using Large, Unfiltered Tables in Calculations:
   • Applying DAX calculations to very large tables can significantly slow down performance. Consider filtering the data before performing calculations.
4. Leverage DirectQuery Mode for Large Datasets:
   • If your dataset is too large to be imported, consider using DirectQuery mode to query the data source directly instead of importing it into Power BI.
5. Use Relationships Instead of LOOKUPVALUE or RELATED:
   • When possible, establish relationships in the data model rather than using DAX functions like LOOKUPVALUE or RELATED, as these can be performance-intensive.
6. Optimize Complex Calculations:
   • Break down complex calculations into smaller, more manageable steps. Use variables to store intermediate results.

Optimizing visualizations

Power BI visualizations can be dashboards, Power BI reports, or Power BI paginated reports. Each has different architectures, and so each has their own guidance.

Dashboards

It's important to understand that Power BI maintains a cache for your dashboard tiles—except live report tiles, and streaming tiles.

Retrieving the data from the cache provides better and more consistent performance than relying on the data source. One way to take advantage of this functionality is to have dashboards be the first landing page for your users. Pin often-used and highly requested visuals to the dashboards. In this way, dashboards become a valuable "first line of defense", which delivers consistent performance with less load on the capacity. Users can still click through to a report to analyze details.

For DirectQuery and live connection datasets, the cache is updated on a periodic basis by querying the data source. Each cache update will send queries to the underlying data source to update the cache. The number of queries that generate depends on the number of visuals pinned to dashboards that rely on the data source.

Apply the most restrictive filters

The more data that a visual needs to display, the slower that visual is to load. While this principle seems obvious, it's easy to forget. For example: suppose you have a large dataset. Atop of that dataset, you build a report with a table. End users use slicers on the page to get to the rows they want—typically, they're only interested in a few dozen rows.

A common mistake is to leave the default view of the table unfiltered—that is, all 100M+ rows. The data for these rows loads into memory and is uncompressed at every refresh. This processing creates huge demands for memory. The solution: use the "Top N" filter to reduce the max number of items that the table displays. You can set the max item to larger than what users would need, for example, 10,000. The result is the end-user experience doesn't change, but memory use drops greatly. And most importantly, performance improves.

A similar design approach to the above is suggested for every visual in your report. Ask yourself, is all the data in this visual needed? Are there ways to filter the amount of data shown in the visual with minimal impact to the end-user experience? Remember, tables in particular can be expensive.

Limit visuals on report pages

The above principle applies equally to the number of visuals added to a report page. It's highly recommended you limit the number of visuals on a particular report page to only what is necessary. Drillthrough pages and report page tooltips are great ways to provide additional details without jamming more visuals onto the page.

Evaluate custom visual performance

Be sure to put each custom visual through its paces to ensure high performance. Poorly optimized Power BI visuals can negatively affect the performance of the entire report.

Optimizing the environment (for administrators)

You can optimize the Power BI environment on Power BI Service by configuring capacity settings, sizing data gateways, and reducing network latency.

Capacity settings

When using capacities—available with Power BI Premium, Premium Per User licenses, or Power BI Embedded — you can manage capacity settings and thus assign certain workspaces to certain capacities with different computing powers.

Gateway sizing

A gateway is required whenever Power BI must access data that isn't accessible directly over the Internet. You can install the on-premises data gateway on a server on-premises, or VM-hosted Infrastructure-as-a-Service (IaaS). A Power BI administrator can manage and size the gateway according to the workloads depending on the data source requirements.

Network latency

Network latency can impact report performance by increasing the time required for requests to reach the Power BI service, and for responses to be delivered. Tenants in Power BI are assigned to a specific region.

When users from a tenant access the Power BI service, their requests always route to this region. As requests reach the Power BI service, the service may then send additional requests—for example, to the underlying data source, or a data gateway—which are also subject to network latency.

Tools such as Azure Speed Test provide an indication of network latency between the client and the Azure region. In general, to minimize the impact of network latency, strive to keep data sources, gateways, and your Power BI capacity as close as possible. Preferably, they reside within the same region. If network latency is an issue, try locating gateways and data sources closer to your Power BI capacity by placing them inside cloud-hosted virtual machines.

Monitoring performance

You can monitor performance to identify bottlenecks. Slow queries—or report visuals—should be a focal point of continued optimization. Monitoring can be done at design time in Power BI Desktop, or on production workloads in Power BI Premium capacities.