Back to Subject
Practice MCQ

Unit 6 - Notes

INT374

Unit 6: Optimizing Power BI Performance

1. Overview of Power BI Optimization

Performance optimization in Power BI is the process of minimizing the resources (memory and CPU) required to refresh data and render reports, thereby improving the end-user experience. Slow reports lead to low user adoption. Optimization occurs at three distinct layers:

  1. The Data Source: Optimizing SQL queries or source files.
  2. The Data Model: Schema design (Star Schema), relationships, and calculated columns.
  3. The Visual Layer: Efficient DAX measures and rendering choices.

2. Optimization Tools

To optimize effectively, developers must identify bottlenecks using specific tools. These fall into two categories: Native (Built-in) and External (Community).

Native Tools

  • Performance Analyzer: The primary built-in tool for measuring how long each visual takes to load.
  • Query Diagnostics: Used within Power Query Editor to troubleshoot slow data transformation steps (ETL).
  • The Optimize Ribbon: A dedicated UI tab for controlling report behavior during the authoring process.

External Tools

While not built-in, these tools are industry standards for deep optimization:

  • DAX Studio: Used to analyze the execution plan of DAX queries captured by the Performance Analyzer. It tells you how the engine is calculating a number (Server Timings).
  • Tabular Editor: Used to implement the "Best Practice Analyzer" (BPA) which scans the model for rule violations (e.g., missing format strings, inefficient relationships).
  • VertiPaq Analyzer: used to inspect memory consumption of columns to reduce model size.

3. The Optimize Ribbon

Introduced to help report authors working with large data models or DirectQuery sources, the Optimize Ribbon allows developers to pause visual rendering while making changes to avoid unnecessary query execution.

Key Features

A. Pause Visuals

  • Function: Stops visuals from sending queries to the data engine whenever a change is made on the canvas.
  • Use Case: Without this, adding a column to a table, changing a format setting, or resizing a chart triggers a refresh. "Pause Visuals" allows you to make multiple configuration changes (e.g., drag in three different fields and change a color) without waiting for the visual to load between every single action.
  • Visual Indicator: Visuals will display a "Visual has pending changes" banner while paused.

B. Refresh Visuals

  • Function: Manually triggers a query update for the visuals on the page.
  • Use Case: Used in conjunction with "Pause Visuals" to verify the output once all configuration changes are complete.

C. Optimization Presets

(Detailed in Section 4 below).

4. Optimization Presets

Optimization Presets are pre-configured settings accessible via the Optimize Ribbon. They primarily target Query Reduction settings, which are crucial for DirectQuery models where every user interaction sends a query back to the SQL database.

The Three Presets

1. Interactivity (Default)

  • Behavior: Features all standard Power BI behaviors. Cross-highlighting is on; slicers filter immediately upon selection.
  • Best For: Import mode models or highly performant DirectQuery sources where user experience requires instant feedback.

2. Query Reduction

  • Behavior: Adjusts settings to minimize the number of queries sent to the source.
    • Disables cross-highlighting/cross-filtering between visuals by default.
    • Adds an "Apply" button to slicers and filters.
  • Impact: The report does not update until the user explicitly clicks "Apply" on a slicer. This prevents intermediate queries (e.g., if a user wants to select "2023" and "Region A", the report won't try to load "2023" first, then reload for "Region A").
  • Best For: Slow DirectQuery sources or extremely large datasets.

3. Customize

  • Behavior: Opens the "Options" menu, allowing the developer to manually toggle specific query reduction settings, such as:
    • Disabling cross-highlighting.
    • Adding Apply buttons to Slicers only.
    • Adding Apply buttons to the Filter Pane only.

5. Performance Analyzer

The Performance Analyzer is the diagnostic "black box" recorder for Power BI Desktop. It breaks down the duration of every visual's loading time into specific components.

How to Use

  1. Navigate to the Optimize ribbon (or View ribbon in older versions).
  2. Click Performance Analyzer. A pane opens on the right.
  3. Click Start Recording.
  4. Interact with the report: Click "Refresh Visuals" to test the initial load, or interact with slicers to test interactivity performance.
  5. Click Stop.

Interpreting the Metrics

The analyzer breaks down the duration (in milliseconds) into three distinct categories:

Metric Definition Optimization Strategy
DAX Query The time required for the tabular engine to calculate the numbers (evaluate measures). The bottleneck is the DAX code or the data model. Use variables, avoid iterator functions (FILTER, SUMX) over large tables, and check relationships.
Visual Display The time required to render the visual elements (draw the chart) on the screen after the data is available. The bottleneck is the rendering. Reduce the number of data points (e.g., a scatter plot with 50k points), remove shadows/gradients, or switch to a simpler visual type (Card vs. Custom Visual).
Other Time spent waiting in the queue, background processing, or network latency. Usually indicates too many visuals on a single page. The engine can only evaluate a limited number of queries in parallel. Reduce the number of visuals per page.

Analyzing the Query

Inside the Performance Analyzer pane, you can expand a specific visual to see the breakdown.

  • Copy Query: This button copies the actual DAX query generated by the visual.
  • Workflow:
    1. Identify a slow visual with a high "DAX Query" time.
    2. Click "Copy Query".
    3. Paste the query into DAX Studio.
    4. Run "Server Timings" in DAX Studio to see specifically which part of the calculation (Storage Engine vs. Formula Engine) is slow.

6. Summary of Best Practices

To achieve optimal results using the tools above, adhere to the following core principles:

  1. Star Schema Design: Always strive for a One-to-Many relationship model with Fact tables and Dimension tables. Avoid Bi-Directional filtering unless absolutely necessary.
  2. Limit Visuals: Do not crowd a single report page with 20+ visuals. Use bookmarks or drill-throughs to spread content.
  3. Integers over Strings: In the data model, use integers for keys and comparisons. They compress better and calculate faster than text strings.
  4. Remove Unused Columns: High cardinality columns (like unique IDs or GUIDs) consume vast amounts of memory. If they aren't used for relationships or reporting, remove them in Power Query.
  5. DirectQuery Caution: Only use DirectQuery when real-time data is mandatory or the data volume exceeds Import capacity. Import mode is almost always significantly faster.