4.5 Optimization & Refresh

Optimization Is a Tested Skill, Not an Afterthought

The semantic-model domain explicitly covers DAX optimization, model optimization, and incremental refresh. Expect scenarios that describe a slow report or a model exceeding memory and ask for the most effective remedy — and where the wrong answers are heavy-handed changes (switching the whole model to DirectQuery, adding bidirectional filtering everywhere) that trade one problem for a worse one.

The guiding principle the exam rewards is measure first, change surgically. Before recommending a fix, you should know which visual is slow and whether the cost is in the storage engine or the formula engine. A blanket 'rebuild the model' answer is almost always wrong when a profiling-then-targeted-fix answer is offered. The rest of this section walks the highest-yield levers in roughly the order you should consider them: model shape and cardinality, aggregations, incremental refresh, and finally diagnosis.

Model Size and Speed Fundamentals

VertiPaq compresses each column independently, so column cardinality (the number of distinct values) is the primary driver of model size and query cost. High-cardinality columns — long unique strings, free text, high-precision datetimes, GUIDs — compress poorly and dominate memory.

Highest-impact levers, roughly in order:

• Use a star schema; avoid wide, snowflaked, or fact-embedded designs.
• Remove unused columns and tables entirely — unused columns still consume memory.
• Reduce cardinality: split a datetime into separate date and time columns, round numeric values that do not need full precision, and drop high-precision keys you never display.
• Replace calculated columns with measures wherever a row-level value is not strictly required.
• Disable auto date/time and use a single, marked date table instead of dozens of hidden per-column date tables.

For Direct Lake specifically, the Delta tables should be V-Order optimized with well-sized row groups; poorly maintained Delta files force excessive paging and slow the fast path even when the model itself is lean.

Aggregations

An aggregation table stores a pre-summarized version of a large fact — for example daily totals by product and region rather than every transaction. When a visual only needs the summary grain, the engine answers from the small aggregation table; when it needs detail, it transparently falls through to the detailed fact. The user never chooses; the engine matches the query to the finest table that can answer it.

Aggregations pay off most over very large facts in DirectQuery or Direct Lake, where the typical dashboard query asks for a monthly or category roll-up and should never scan the billion-row detail. A well-placed aggregation at the month-and-category grain can turn a full-table scan into a millisecond lookup.

• Match the aggregation grain to the common query, not the rare drill-down.
• Keep the aggregation table in Import (or as a small Direct Lake table) so it is memory-resident even when the detail is DirectQuery.
• Note: user-defined aggregations are not supported as a Direct Lake feature directly, so the pattern is often 'Import aggregation table over a DirectQuery/Direct Lake detail fact' in a composite model.

Incremental Refresh

Incremental refresh partitions an Import table by a date range using the reserved RangeStart and RangeEnd Power Query parameters. After the initial full load, scheduled refreshes only reprocess recent partitions (for example the last few days) while historical partitions stay cached. The benefits are large: far shorter refresh windows, lower memory and capacity use, and optional near-real-time freshness on the latest partition via hybrid tables (a DirectQuery 'hot' partition over cached cold partitions).

It applies to Import-mode tables only. A classic DP-600 trap is applying incremental refresh to a Direct Lake table — Direct Lake stays current through framing, not partition refresh, and does not support model-level partitions (partitioning happens at the Delta-table level instead).

Diagnosing Slow Reports

Use Performance Analyzer in Power BI Desktop to record how long each visual takes and to split DAX query time from visual rendering time. If one visual's DAX query dominates, you have isolated the culprit and can copy its query for deeper analysis.

For that deeper analysis, DAX Studio captures server timings and the breakdown between the storage engine (SE) and the formula engine (FE). A query dominated by SE time points at scanning too much data — fix with aggregations, cardinality reduction, or a better filter; a query dominated by FE time points at expensive row-by-row logic — fix with variables, simpler iterators, or pre-computed columns.

The exam consistently favors answers that measure first (profile the slow visual, read SE-vs-FE timings) over blanket changes such as 'switch the whole model to DirectQuery.' For Direct Lake, keep models narrow (fewer, lower-cardinality columns), keep Delta tables V-Order optimized and reframed appropriately, and treat excessive DirectQuery fallback (on the SQL-endpoint flavor) as a performance red flag rather than acceptable behavior.