# Performance Optimization and Monitoring

> Chapter 18 of the Courseiva PL-300 curriculum — https://courseiva.com/learn/pl-300/performance-optimization-and-monitoring

**Official objective:** 4.4 — Monitor usage, performance, and optimize Power BI environments

## Introduction

Performance optimisation and monitoring. In Power BI, slow reports frustrate users and waste time — the difference between a dashboard that loads in two seconds versus thirty seconds can determine whether anyone actually uses it. For the PL-300 exam, you need to know how to measure and improve performance so your data solutions run efficiently in the real world.

## The Rooftop Solar Panel System Analogy

Your home has a 5-kilowatt rooftop solar panel system that generates electricity during the day. You have 10 appliances running, but the panels only produce enough power for 8 at once. If you run all 10, the system trips the breaker, and you lose power entirely.

To avoid this, you install a smart power monitor that tracks each appliance's draw in real time. You discover the old refrigerator uses 800 watts per hour — far more than expected — and replacing it with an energy-efficient model frees up capacity. You also realise that running the dishwasher, washing machine, and oven simultaneously during peak sunlight hours overwhelms the system, so you schedule them at different times.

Over months, you review the monitor's data: you see a daily energy usage report, identify which hours produce the most power, and adjust your habits. The system now runs without tripping, your electricity bill drops, and you even feed surplus power back to the grid. Power BI performance optimisation works the same way — you monitor resource usage (CPU, memory, query time), identify bottlenecks (a slow visual, a bloated dataset), and make targeted changes to keep your reports fast and reliable.

## Core explanation

Performance optimisation and monitoring is the practice of making Power BI reports and dashboards load and respond quickly, then keeping an eye on that speed over time. If a report takes too long to refresh data or to apply a slicer, users will abandon it. The exam tests your ability to identify performance problems and fix them.

Let's define the key technical terms you will encounter.

**Query time** is how many seconds (or milliseconds) it takes for Power BI to fetch data from its source — for example, from a SQL Server database or an Excel file. Every time you interact with a visual, like clicking a slicer to filter by year, Power BI sends a query to the underlying data model.

**DAX queries** are the formulas Power BI runs in the background to calculate measures and calculated columns. A poorly written DAX formula can dramatically slow down a report.

**Data model** refers to the structure of your tables and relationships. A star schema — one central fact table (like sales transactions) connected to dimension tables (like product names and dates) — is much faster than a single flat table.

**DirectQuery** is a connection mode where Power BI sends queries directly to the source database each time you interact with a report, rather than importing a copy of the data into memory. It is generally slower than Import mode.

**Import mode** loads all the data into Power BI's internal engine (VertiPaq) for fast, in-memory queries. It offers the best performance but requires scheduled refreshes to keep data current.

**Composite models** combine Import and DirectQuery, letting you bring some tables in fully and leave others connected live. This can improve performance for large datasets.

**Query reduction techniques** include applying filters at the source, reducing the number of visuals on a page, and avoiding excessive use of complex DAX.

**Performance Analyzer** is a built-in Power BI Desktop tool (on the View ribbon) that records how long each visual takes to render — including the DAX query time, the time to display the visual, and the time spent on the web browser rendering it.

**Resource consumption** refers to how much CPU (processor), memory (RAM), and network bandwidth Power BI uses when processing your report. In the Power BI Service (the cloud version), reports consume capacity resources which you share with other reports.

**Premium capacity** and **Premium Per User (PPU)** offer dedicated resources with higher limits than the shared capacity that comes with a free or Pro licence. Monitoring your usage against these limits is part of optimisation.

Why do all these details matter? Because a single slow visual can make an entire dashboard feel unresponsive. The exam expects you to diagnose problems: is the bottleneck in the data source (slow DirectQuery), in the DAX (calculation), in the report design (too many visuals on one page), or in the capacity (too many users hitting memory limits)?

There are three main areas you need to monitor and optimise:

- **Data source performance**: Use query folding (pushing transformations back to the source) and reduce the data volume brought into the model.
- **Data model design**: Use a star schema, avoid bidirectional filters unnecessarily, and ensure column cardinality (number of unique values) is as low as possible.
- **Report design**: Limit the number of visuals per page, use built-in date hierarchies instead of custom ones, and avoid very detailed tables that show thousands of rows at once.

The Performance Analyzer is your primary tool. You open it, start recording, then interact with your report. It shows a list of every visual, how long each part took, and a breakdown of the DAX query. You can even export the log to a JSON file for deeper analysis.

Another tool is **SQL Server Profiler** (or the built-in diagnostic traces in Power BI Desktop) which captures detailed query events. In the Power BI Service, the **Usage Metrics** report (on the workspace dashboard) shows how many users opened each report, how often, and on which devices.

**Monitor** means paying attention over time. You set up alerts for when the average query time exceeds a threshold (for example, two seconds). You check the refresh history to see if scheduled data refreshes are failing or running long. In Enterprise environments, the **Admin Portal** provides a **Capacity Metrics** app that shows how much CPU and memory your reports consume on Premium capacity.

The goal is not to make every report load instantly — some queries are inherently complex. Instead, you aim for a **acceptable user experience**: most interactions should respond within a few seconds, and heavy reports should not block other users from running their own reports on the same capacity.

## Real-world context

Meet Priya, a data analyst at a retail company. Her director complained that the monthly sales dashboard takes over thirty seconds to load every time someone clicks a region slicer. Priya needs to figure out why and fix it.

She starts by opening the report in Power BI Desktop and launching **Performance Analyzer** from the View ribbon. She clicks 'Start recording', then clicks the 'North America' slicer. The Performance Analyzer panel populates with rows for each visual on the page. She sees that the bar chart, the card showing total revenue, and the map all have high 'DAX Query' times — around 12 seconds each. The 'Visual Display' times are negligible. This tells her the bottleneck is in the calculations, not in rendering.

Priya then opens the **DAX query** associated with the bar chart by clicking 'Copy query' and pasting it into DAX Studio (a free external tool). She notices the measure uses a FILTER function scanning the entire 10-million-row fact table. That is slow. She rewrites the measure using CALCULATE with simpler filter expressions and uses a pre-calculated column to reduce the work. She also checks if the relationships between the fact table and dimension tables are set to single direction — bidirectional cross-filtering would force Power BI to look up both sides every time.

Next, Priya looks at the data source. The report uses DirectQuery to a SQL Server database. She examines the query folding: are all the transformations (like filtering out returns and aggregating totals) happening in the SQL query sent to the server, or is Power BI pulling every row and filtering locally? She finds that a merge step in Power Query is performing a join locally, which means all raw data gets downloaded before filtering. She moves the join to the SQL query using a native database query. That reduces the amount of data transferred by 70%.

She also checks the **Capacity Metrics** app in the Power BI Admin Portal. The company uses Premium Per User. The app shows that during peak hours (9 AM to 11 AM), the capacity's CPU usage spikes to 95%. Her report is one of the top consumers. She decides to change the report's scheduled refresh from 8 AM to 6 AM to avoid the peak.

Final step: she tests again with the same slicer and the same visual. The loading time drops from 12 seconds to under 2 seconds. She exports the Performance Analyzer log, attaches it to a change request, and documents the changes in her team's wiki. She also creates a monitoring dashboard using the built-in **Usage Metrics** report that tracks page views and average query times over weeks, so she can catch any future slowdowns early.

What Priya actually did matches the PL-300 exam objectives: she monitored usage (who uses what, when), analysed performance (which queries are slow), and optimised (changed DAX, data source connection mode, and refresh schedule).

## Exam focus

The PL-300 exam tests Performance Optimization and Monitoring in several specific ways. You can expect 5 to 8 questions on this topic, spread across the three areas: monitoring, diagnosing, and optimising.

**Question types you will see:**
- **Scenario-based multiple choice**: 'Your report takes too long to load when users apply a filter. What should you do first?' You choose the correct diagnostic step (e.g., use Performance Analyzer).
- **Tool identification**: 'Which tool shows you how long each visual takes to query?' Answer: Performance Analyzer.
- **Best practice selection**: 'Which data model design improves query performance?' Answer: star schema.
- **DirectQuery vs Import**: 'A report uses DirectQuery and is slow. What could improve performance?' Correct: reduce number of visuals, add aggregations, or switch to Import mode if data volume allows.
- **Capacity monitoring**: 'Where do you see CPU and memory usage for a Premium capacity?' Answer: Capacity Metrics app in the Admin Portal.
- **Troubleshooting**: 'Users see an error that a visual can't render because the query timed out. What is the most likely cause?' Answer: the DirectQuery exceeded the 225-second timeout limit (for shared capacity) or the DAX query is too complex.

**Traps Microsoft sets:**
- **Confusing Performance Analyzer with Power BI's data refresh history**. The Performance Analyzer measures query and render time for user interactions; refresh history shows how long it took to load data into the model. They are different concepts. The exam will present a scenario about a specific user complaint (slow interaction) and list options like 'Check refresh history' which is wrong.
- **Assuming all slow reports need more resources**. Often the real issue is a bad DAX formula or a poorly designed data model. Adding more Premium capacity only masks the symptom. The correct answer often involves analysing the query.
- **Suggesting 'Reduce the number of data sources' as a universal fix**. The exam expects you to identify the specific bottleneck first. Data source count rarely matters; the volume of data and query complexity do.
- **Treating Import mode as always faster**. While Import is generally faster than DirectQuery for small-to-medium datasets, for very large data (billions of rows) Import is impractical because the model becomes too large to fit in memory. The correct answer for a huge dataset might be DirectQuery with aggregations.

**Key definitions you must memorise:**
- **Performance Analyzer**: Records timeline of visual rendering, DAX queries, and external tools. Use it to identify which visual is slow.
- **Query folding**: Power Query pushing transformation steps back to the data source (e.g., SQL Server) rather than doing them locally. Always prefer query folding for performance.
- **Aggregations**: Pre-aggregated tables that summarise data at a higher level (e.g., monthly totals instead of daily transactions). Power BI can use them automatically when querying large datasets.
- **Timeouts**: DirectQuery has a 225-second timeout in shared capacity. If a query takes longer, the visual fails. Optimisation options include reducing data, adding aggregations, or switching to Import.
- **Star schema**: A central fact table linked to dimension tables with one-to-many relationships. Avoid many-to-many where possible.
- **Cardinality**: The number of unique values in a column. High-cardinality columns (like unique order IDs) are hard to compress and slow down storage and queries.
- **Bidirectional filters**: Slows down performance because Power BI must evaluate both directions. Only use when strictly necessary.
- **Capacity limits**: Premium capacity has specific CPU and memory limits per SKU. Monitoring these is part of the 'Monitor usage' objective.

The exam will also test your knowledge of **Dashboard vs Report performance**. A dashboard tile can be pinned from a slow report, but the tile itself may be cached and load fast. The exam question might present a scenario where tiles are fast but the underlying report is slow — the correct answer is to optimise the report, not the dashboard.

## Step by step

1. **Identify the Symptom** — A user reports that a specific visual or page loads slowly. Open Power BI Desktop and reproduce the issue. Note the exact action (e.g., clicking a slicer, selecting a date range) and how long it takes.
2. **Launch Performance Analyzer** — Go to the View ribbon in Power BI Desktop and click 'Performance Analyzer'. Click 'Start recording', then perform the slow action again. The panel will display each visual's query time, render time, and other metrics.
3. **Identify the Bottleneck** — Look for the visual with the highest 'DAX Query' time. That is likely the culprit. If 'Visual Display' time is high, the issue is in the web rendering, which could mean too many data points or a complex visual type.
4. **Analyse the DAX Query** — Click the 'Copy query' button next to the slow visual to copy the DAX formula. Paste it into DAX Studio or the built-in query editor. Look for expensive functions like FILTER scanning entire tables, or unnecessary nested calculations.
5. **Optimise the Data Model or Source** — Based on the analysis, rewrite the DAX measure to be more efficient. If using DirectQuery, check if query folding is happening. Consider adding aggregations or switching to Import mode for static data. Reduce column cardinality by dropping high-cardinality columns or using grouping.
6. **Test and Validate** — After making changes, run the Performance Analyzer again with the same action. Confirm the query time dropped below your target threshold (e.g., under 3 seconds). Export the log for documentation.
7. **Monitor in Production** — Publish the updated report. Use the Usage Metrics report in the Power BI Service to track page views and average interaction times over weeks. Set up alerts in the Capacity Metrics app if the report uses Premium capacity.

## Comparisons

### Performance Analyzer vs Refresh History

**Performance Analyzer:**
- Measures query and render time for user interactions with the report.
- Only available in Power BI Desktop.
- Useful for diagnosing why a specific visual is slow.

**Refresh History:**
- Shows how long it took to load data into the model from the source.
- Available in Power BI Service under dataset settings.
- Useful for diagnosing why scheduled refresh fails or runs long.

### Import Mode vs DirectQuery Mode

**Import Mode:**
- Data is copied into Power BI memory (VertiPaq).
- Fastest query performance for user interactions.
- Requires scheduled refreshes to keep data current.

**DirectQuery Mode:**
- Queries are sent live to the source database.
- Slower per query; subject to timeouts (225 seconds in shared).
- Data is always up-to-date; no refresh needed.

### Star Schema vs Flat Table

**Star Schema:**
- One fact table connected to multiple dimension tables.
- Optimised for compression and faster queries.
- Reduces data duplication and improves DAX performance.

**Flat Table:**
- All data in a single table with repeated values.
- Poor compression; results in large memory usage.
- Requires more complex DAX to filter and aggregate correctly.

### Usage Metrics vs Capacity Metrics

**Usage Metrics:**
- Shows who opened which report, on what device, and how often.
- Available in the workspace dashboard for any Power BI report.
- Focuses on user engagement, not resource consumption.

**Capacity Metrics:**
- Shows CPU, memory, and query failure rates per report or workspace.
- Only available for Premium capacity users via Admin Portal.
- Focuses on infrastructure resource usage and bottlenecks.

## Diagram

_Workflow for diagnosing and optimising a slow Power BI report, from user complaint through to ongoing monitoring._

```mermaid
flowchart TD
    A[User reports slow report] --> B[Open Performance Analyzer]
    B --> C[Identify slow visual by DAX Query time]
    C --> D{Is DAX inefficiency?}
    D -- Yes --> E[Rewrite DAX measure]
    D -- No --> F{Is data model flat?}
    F -- Yes --> G[Convert to star schema]
    F -- No --> H{Is DirectQuery slow?}
    H -- Yes --> I[Add aggregations or switch to Import]
    E --> J[Test with Performance Analyzer]
    I --> J
    G --> J
    J --> K[Monitor in Capacity Metrics / Usage Metrics]
```

## Common misconceptions

- **Misconception:** Adding more Premium capacity always fixes slow reports. **Reality:** Most slow reports are caused by inefficient DAX, poor data model design, or high data volume — not by capacity limits. Performance Analyzer should be the first step. (It is intuitive to think that throwing money at the problem (buying more capacity) will solve it, but the exam wants you to diagnose root causes first.)
- **Misconception:** DirectQuery is always slower than Import mode, so I should always use Import. **Reality:** DirectQuery is slower for repetitive user interactions but necessary for real-time data or very large datasets that cannot fit in memory. The best choice depends on the use case. (Beginners hear that Import is 'faster' and assume it is universally better, without understanding trade-offs like data freshness and memory limits.)
- **Misconception:** Removing visuals from a report always improves performance. **Reality:** Removing visuals helps only if the visuals themselves are compute-heavy (e.g., complex DAX). A simple table visual with 10 rows is not a problem. The bottleneck is usually the DAX queries, not the number of visuals. (It is a natural reaction to think 'less content = faster', but the exam tests understanding that the real bottleneck is computational, not visual.)
- **Misconception:** Performance Analyzer only works in Power BI Service, not Desktop. **Reality:** Performance Analyzer is available in Power BI Desktop (on the View ribbon). It does not exist in the Power BI Service. You use it in Desktop before publishing. (Many learners confuse desktop authoring tools with service monitoring tools. The exam may ask: 'Which tool do you use in Power BI Desktop to measure visual performance?')
- **Misconception:** Scheduling the data refresh for a later time fixes all slow report interactions. **Reality:** Data refresh timing only affects how current the data is, not how fast queries execute when users interact with the report. Interaction performance is about the data model and queries. (It is easy to conflate 'refresh speed' with 'query speed'. The exam separates these concepts clearly.)

## Key takeaways

- Performance Analyzer in Power BI Desktop is the primary tool for identifying which visual or DAX query is causing a slowdown.
- A star schema with a single fact table and dimension tables significantly outperforms a flat table model.
- Query folding in Power Query—pushing transformations to the data source—reduces local memory and CPU load.
- DirectQuery has a 225-second timeout in shared capacity; if your query exceeds that, you must either optimise the query or switch to Import mode.
- Bidirectional filters in relationships slow down performance and should only be used when required.
- The Capacity Metrics app in the Admin Portal shows CPU and memory usage for Premium capacity reports.
- Aggregations allow Power BI to respond to queries using pre-summarised data, improving speed for large datasets.
- Monitor not just the average query time but also the peak usage times to identify capacity bottlenecks.

## FAQ

**What is Performance Analyzer in Power BI?**

Performance Analyzer is a built-in tool in Power BI Desktop that measures how long each visual takes to load, broken down by DAX query time, visual display time, and other steps. You use it to find and fix slow report elements.

**Why is my Power BI report so slow?**

The most common causes are inefficient DAX formulas (like using FILTER on an entire table), a flat data model instead of a star schema, using DirectQuery when Import would be fine, or having too many visuals on a single page. Use Performance Analyzer to identify the exact source.

**What is query folding in Power BI?**

Query folding is when Power Query pushes transformation steps (like filtering or grouping) back to the source database instead of doing them locally. This reduces the amount of data transferred and improves performance. You can verify folding by right-clicking a step and selecting 'View Native Query'.

**How do I check if my Power BI report is using too much memory?**

In the Power BI Service, if you have Premium capacity, use the Capacity Metrics app to see CPU and memory consumption per report. For shared capacity, check the dataset size in the workspace settings—if it exceeds 1 GB (for Pro), it may impact performance.

**What is the 225-second timeout in DirectQuery?**

In shared Power BI capacity, a DirectQuery request that takes longer than 225 seconds (about 3.75 minutes) will time out and the visual will show an error. To avoid this, either reduce the query complexity, add aggregations, or switch to Import mode.

**Should I use Import or DirectQuery for performance?**

Import mode is faster for user interactions because data is already in memory. DirectQuery is slower per query but keeps data real-time and can handle extremely large datasets. Use Import for static or near-real-time data under 1 GB (or up to 10 GB with Premium). Use DirectQuery for huge datasets or when live data is critical.

**What does the Capacity Metrics app tell me?**

The Capacity Metrics app (available in the Power BI Admin Portal for Premium capacities) shows CPU usage, memory usage, and query failure rates per report or per workspace. You can identify which reports are using the most resources and plan optimisations.

## Check your understanding

1. **Which tool do you use to measure how long each visual takes to render in Power BI Desktop?**

   Answer: Performance Analyzer.

2. **What is the main advantage of a star schema data model over a flat table model for performance?**

   Answer: A star schema reduces data duplication, improves compression, and allows Power BI to generate more efficient DAX queries, resulting in faster load times.

3. **What does query folding mean?**

   Answer: Query folding means that transformation steps performed in Power Query are pushed back to the source database to execute, reducing the amount of data transferred and improving performance.

4. **In shared Power BI capacity, what is the timeout limit for a DirectQuery request?**

   Answer: 225 seconds.

5. **Where do you monitor CPU and memory usage for a Power BI Premium capacity?**

   Answer: In the Capacity Metrics app, available in the Power BI Admin Portal.

---

Interactive version with quiz and diagrams: https://courseiva.com/learn/pl-300/performance-optimization-and-monitoring