Optimizing Enterprise-Scale Semantic Models

Introduction

This lesson completes the domain Implement and manage semantic models (25–30% of the DP-600 skills measured) with the group "Optimize enterprise-scale semantic models": improve query and report visual performance, improve DAX performance, configure Direct Lake, including default fallback and refresh behavior, choose between Direct Lake on OneLake and Direct Lake on SQL analytics endpoint, and implement incremental refresh. This course targets the English syllabus effective October 19, 2026. Microsoft does not publish the question count or the question-type mix, so OnLevelUp mock exams use a 50-question practice length and an unofficial 80% practice target, which is not equivalent to the official 700/1000 scaled passing score.

Optimization questions are diagnostic: the exam describes a slow visual, a failed refresh or a silent fallback, and asks which tool to use or which setting to change. After this lesson you will be able to find the cause of a slow report with the right tool, reduce model size, rewrite slow DAX, configure Direct Lake behavior and design a refresh strategy.

Learning objectives

• Use Performance Analyzer, DAX query view, DAX Studio, Tabular Editor and the Best Practice Analyzer to diagnose problems.
• Reduce model size and improve query speed with column, cardinality and data type decisions.
• Rewrite slow DAX with variables, simpler filters and fewer iterations.
• Configure Direct Lake behavior (DirectLakeBehavior), understand fallback triggers and framing.
• Choose between Direct Lake on OneLake and on SQL analytics endpoint.
• Implement incremental refresh for Import tables and plan refresh for large models.

1. How a query is processed

A visual sends a DAX query to the semantic model. Two engines handle it:

Fast models push work to the storage engine: simple aggregations over columns and Boolean filters. Slow models spend most of the time in the formula engine because of iterators over large tables, many context transitions or large intermediate tables. Performance tools tell you which one dominates.

2. Diagnosing performance

Reading a Performance Analyzer capture:

• Long DAX query time: optimize the measure, model or data source.
• Long visual display time: too many data points, a heavy custom visual, or too many visuals on the page.
• Long other time: waiting for other visuals queries to finish; reduce the number of visuals.

Example. A page loads in 14 seconds. Performance Analyzer shows 11 visuals; one matrix takes 9 seconds of DAX query time, the others under 0.5 seconds each. Only that matrix needs work, not the whole page.

3. Reducing model size and speeding up queries

Most speed problems in Import and Direct Lake models are solved by reducing the data the engine scans.

Worked example: shrinking a 9 GB model.

Statement. An Import model of web events is 9 GB. Vertipaq Analyzer shows 70% of the size comes from FactEvents[EventTimestamp] (millisecond precision, 200 million distinct values), FactEvents[SessionGuid] and a text column UserAgent that nobody uses in reports.

Reasoning.

1. UserAgent is unused: remove it from the model (not just hide it).
2. EventTimestamp is used for daily and hourly analysis only: split it into EventDate (relation to the Date table) and EventHour (integer 0–23), then remove the original. Cardinality falls from 200 million to a few thousand.
3. SessionGuid is used only for a distinct count of sessions: keep it if required, or pre-compute a session key as an integer upstream. If sessions are not needed in the report, drop it.
4. Re-run Vertipaq Analyzer to verify the effect.

Solution. Remove unused columns, reduce cardinality of the timestamp and use integer keys.

Why other choices are weaker. Moving to DirectQuery trades memory for latency. Hiding a column keeps its memory. Buying a larger capacity treats the symptom.

Exam tip. "Large size" and "slow refresh" with a timestamp or GUID column usually point to cardinality reduction. "Hide" does not remove data from the model.

4. DAX performance

Rules that often appear in scenarios:

Use DAX query view and DEFINE MEASURE to test a rewrite on the same query and compare timings in DAX Studio, rather than guessing.

-- Slow: materializes the whole fact table
Slow Big Orders =
CALCULATE ( COUNTROWS ( FactSales ), FILTER ( ALL ( FactSales ), FactSales[NetAmount] > 1000 ) )

-- Fast: column predicate evaluated by the storage engine
Fast Big Orders =
CALCULATE ( COUNTROWS ( FactSales ), FactSales[NetAmount] > 1000 )

Note the difference in meaning: ALL ( FactSales ) also removes other filters on the fact table such as the date slicer, so the two measures are not equivalent. The fast form respects the visual's filters, which is almost always the intent.

5. Direct Lake in depth

5.1 How it works

Direct Lake reads the Parquet files of Delta tables in OneLake. Three notions are important.

Framing is by default automatic: the model can update on its own when the Delta tables change (the "Keep your Direct Lake data up to date" setting). You can also trigger it from a pipeline refresh activity, the API or the portal, which is useful to keep consistency after a multi-table load: run the pipeline, then refresh the model once the last table is written.

Delta write patterns matter. Direct Lake analyzes the Delta log to drop and reload only the affected column segments. Append-only loads allow incremental framing and are efficient. Overwriting a table erases the log history the model uses, so the model must reload the data, dictionaries and join indexes. V-Order optimization of the Parquet files, a reasonable number of files and row groups, and regular OPTIMIZE maintenance (and VACUUM with care) keep queries fast. Many tiny files hurt performance.

5.2 Guardrails and fallback

Each capacity SKU defines guardrails: maximum rows per table, number of Parquet files, row groups, and memory. When a Direct Lake model exceeds a guardrail or uses a feature that Direct Lake on the SQL endpoint cannot serve, one of two things happens depending on the flavor and the setting.

For Direct Lake on SQL analytics endpoint, the setting DirectLakeBehavior controls the response:

Typical fallback triggers for the SQL endpoint flavor:

• A table has SQL-level security such as row-level security, column-level security or dynamic data masking defined on the endpoint.
• The model references a non-materialized SQL view (a view, not a Delta table).
• The query exceeds a capacity guardrail (for example too many rows or files).
• The Delta table is not supported in some feature scenario.

Fallback is why a model can be fast in the morning and slow after a data load that exceeds a guardrail. To detect it, test queries with DirectLakeOnly or use the Performance Analyzer (the capture shows a DirectQuery query) and the semantic model's monitoring.

For Direct Lake on OneLake, fallback to DirectQuery does not exist. If a situation cannot be served, the query returns an error instead. This makes behavior predictable and removes the dependency on the SQL endpoint.

5.3 OneLake versus SQL analytics endpoint

Rule of thumb for the exam: if the scenario says "no silent fallback", "predictable performance" or "add an Import table", pick OneLake. If it says "the warehouse enforces row-level security in SQL" or "uses views", expect the SQL endpoint flavor and know it will fall back.

Worked example: why did the model become slow?

Statement. A Direct Lake on SQL analytics endpoint model was fast for a month. After a data engineer added a row-level security policy on the warehouse table FactSales for regional managers, report visuals take 8 seconds instead of 1 second. The capacity metrics show higher SQL endpoint usage.

Reasoning.

1. The SQL-level RLS on the endpoint is a fallback trigger: the model cannot read the Delta files directly for protected tables, so it falls back to DirectQuery.
2. To confirm, set DirectLakeBehavior to DirectLakeOnly in a test: queries will fail instead of falling back, proving the cause.
3. The fix: remove RLS from the SQL endpoint and implement the equivalent role in the semantic model (RLS roles on the model), or migrate to Direct Lake on OneLake with OneLake security or model roles.

Solution. Move the security logic into the semantic model (or OneLake security) so Direct Lake can read the tables directly.

Why the others are weaker. Scaling up the capacity treats the cost, not the cause. Switching to Import hides the Direct Lake benefit and adds refresh. Setting DirectQueryOnly makes it permanently slow.

5.4 Refresh behavior and consistency

• Framing is quick, but a model sitting on half-loaded tables shows inconsistent data. Disable automatic updates when you need a controlled cut-over, load all tables, then trigger the framing from a pipeline.
• A full reload happens after table overwrite; schedule overwrite-heavy jobs accordingly or switch to merge or append.
• Direct Lake does not use incremental refresh partitions; freshness is handled by Delta-table-level partitioning and framing.

6. Incremental refresh

Incremental refresh applies to Import tables. Instead of refreshing the entire table, the service partitions it by date and refreshes only recent partitions, while historical partitions are kept.

Configuration in Power BI Desktop:

1. Create two Power Query parameters of type Date/Time named exactly RangeStart and RangeEnd.
2. Filter the date column of the table with >= RangeStart and < RangeEnd (one boundary inclusive only, to avoid duplicates). The filter must fold to the source.
3. Define the policy on the table: store rows for a period (for example 5 years) and refresh rows for a recent period (for example 7 days).
4. Optionally detect data changes with a column (a last-modified date) so only changed partitions refresh, and get the latest data in real time with DirectQuery for the newest partition (a hybrid table, which needs Premium or Fabric capacity).
5. Publish; the first refresh creates all partitions, and later refreshes touch only the incremental window.

Example. A 400-million-row fact table refreshes in four hours every night. With incremental refresh storing 3 years and refreshing the last 3 days, only three days of partitions are reprocessed, and the refresh drops to 10 minutes.

Exam tip. If the filter does not fold, the service downloads all rows and filters them locally, so incremental refresh loses its benefit. Check "View Native Query" in Power Query. Also remember: Direct Lake does not need incremental refresh.

7. Refresh and capacity planning

• Schedule refreshes outside peak reporting hours; refresh consumes capacity units and can throttle interactive queries.
• Refresh in parallel only as much as the capacity allows; the model can need up to twice its size in memory during a full refresh.
• Use the Fabric Capacity Metrics app to see which model causes the load.
• Prefer partition-level refresh over full model refresh for large Import models.
• Keep one shared, certified semantic model rather than copies per report: one refresh instead of many.

Common pitfalls

• Optimizing the whole model when Performance Analyzer shows one slow visual.
• Hiding unused columns instead of removing them.
• Leaving datetime columns with millisecond precision in the model.
• Using FILTER ( ALL ( Fact ), ... ) where a column predicate is enough.
• Assuming Direct Lake never falls back: the SQL-endpoint flavor can, silently, in Automatic mode.
• Defining row-level security on the SQL endpoint and then wondering why the model uses DirectQuery.
• Overwriting Delta tables on every load and losing incremental framing.
• Using a filter that does not fold with incremental refresh.
• Configuring incremental refresh on a Direct Lake table.

Exam tip. Pick the tool from the symptom: slow visual → Performance Analyzer; slow DAX → DAX query view or DAX Studio; big model → Vertipaq Analyzer; rules and bulk edits → Tabular Editor and Best Practice Analyzer; capacity overload → Capacity Metrics app.

Mini case study

Context. Adventure Works Energy has a Direct Lake on SQL analytics endpoint model on a warehouse with 600 million rows. Symptoms: (1) some visuals alternate between 1 second and 10 seconds without any change; (2) a monthly Import budget model refreshes for 3 hours because of a 5-year transaction table with a timestamp column; (3) a measure counting orders above 1000 € uses FILTER ( ALL ( FactOrders ), ... ) and ignores the date slicer.

Questions.

1. How do you prove and fix the first problem?
2. How do you reduce the refresh time of the budget model?
3. How do you fix the third problem?

Solution.

1. Set DirectLakeBehavior to DirectLakeOnly on a test copy: visuals that error are the ones being served by fallback. Check the causes (SQL security, views, guardrails). Fix by moving RLS into the model, replacing views with materialized Delta tables and compacting files. Consider moving to Direct Lake on OneLake for a predictable, no-fallback behavior.
2. Reduce the timestamp to date plus hour, remove unused columns, then enable incremental refresh with RangeStart and RangeEnd, storing 5 years and refreshing 7 days, with a foldable filter. Enable the large storage format if the model exceeds the size limit.
3. Replace with CALCULATE ( COUNTROWS ( FactOrders ), FactOrders[NetAmount] > 1000 ), which respects the visual's filters and is evaluated by the storage engine.

Comment. Each symptom has a specific diagnostic tool and a specific fix; none requires simply buying more capacity.

Key terms

• Storage engine / formula engine: the two components that evaluate a DAX query.
• Performance Analyzer: tool that times each visual's query and rendering.
• Vertipaq Analyzer: tool that reports the size of tables and columns.
• Framing: Direct Lake refresh that points the model to current Delta versions.
• Transcoding: loading Parquet columns into the in-memory format.
• Guardrail: capacity limit beyond which Direct Lake can fall back or fail.
• DirectLakeBehavior: setting that selects Automatic, DirectLakeOnly or DirectQueryOnly.
• Incremental refresh: partitioned Import refresh using RangeStart and RangeEnd.
• Hybrid table: incremental refresh table whose latest partition uses DirectQuery.

Summary checklist

• I can choose the diagnostic tool from the symptom.
• I can reduce model size by removing columns and lowering cardinality.
• I can rewrite slow DAX with variables and column predicates.
• I can explain framing, transcoding, guardrails and fallback.
• I can name the fallback triggers and the three DirectLakeBehavior values.
• I can choose between Direct Lake on OneLake and on SQL analytics endpoint.
• I can configure incremental refresh and explain why folding matters.
• I know that Direct Lake uses framing, not incremental refresh partitions.

Official sources

• Study guide for Exam DP-600: https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-600 (opens in a new tab)
• Direct Lake overview: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-overview (opens in a new tab)
• How Direct Lake works: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-how-it-works (opens in a new tab)
• Understand Direct Lake query performance: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-understand-storage (opens in a new tab)
• Develop Direct Lake semantic models: https://learn.microsoft.com/en-us/fabric/fundamentals/direct-lake-develop (opens in a new tab)
• Optimization guide for Power BI: https://learn.microsoft.com/en-us/power-bi/guidance/power-bi-optimization (opens in a new tab)