What it does

The work of getting an Import model down to the columns, rows and grain the reports actually need, so the VertiPaq engine can compress it properly. Model size drives memory pressure, refresh duration and query speed all at once, which makes it the tuning work with the best return.

Key facts

• Microsoft's rule of thumb is around 10x compression in memory — roughly 10 GB of source data landing at about 1 GB — with a further 20% reduction when persisted to disk.
• Shared capacity hosts models up to 1 GB. Capacity-backed workspaces host larger models depending on the SKU, with the large semantic model storage format switched on.
• VertiPaq stores each column in its own structure. Numeric columns get value encoding; text and other non-numeric data gets hash encoding, which needs a lookup for every store and query.
• Cardinality is the dominant size driver. A column with a few distinct values costs almost nothing; a column with millions costs a great deal regardless of row count.
• Calculated columns defined in DAX compress less efficiently than Power Query computed columns, and they're built after every table is loaded, which extends refresh.
• Auto date/time creates a hidden calculated date table for every date column in the model. All of it counts towards size.
• Vertical filtering means dropping columns. Horizontal filtering means dropping rows — by time window, or by entity.
• Raising the grain is the biggest single lever available. Microsoft's own example puts summarising to month level at a possible 99% reduction, at the cost of ever reporting at day or order-line level again.
• Power Query queries that only exist to feed other queries should have Enable load turned off, or they end up in the model.
• Setting large fact tables to DirectQuery in a composite model removes them from memory entirely, with its own security and performance consequences.

When to use / skip

Do this before you tune DAX, always. Most models that people describe as slow are models that shouldn't be that big, and an afternoon spent removing columns nobody uses will beat a week of measure rewriting. The one time to skip it is when the model is genuinely small and the problem is elsewhere — check the actual size before assuming. Be honest about the trade-off on grain: summarising is enormously effective and permanently removes the ability to answer detail questions, so it's a business decision, not a technical one.

Configuration decisions

• The grain of each fact table, and whether detail is available at all or only through a DirectQuery drillthrough.
• How much history loads. Five years relative to the refresh date is a common landing point, and it's a Power Query parameter, not a hard-coded filter.
• Whether one model covers all regions with RLS, or several smaller models split by entity — smaller and faster, at the cost of duplicated reports and permission management.
• Whether derived columns are computed in the source, in Power Query, or as DAX calculated columns, in roughly that order of preference.
• Whether auto date/time stays on, and whether the model has a proper date dimension instead.
• Whether the large semantic model storage format is enabled before the first refresh if you expect to pass 1 GB.

Gotchas

• One high-cardinality column can cost more than an entire dimension table. Transaction IDs, GUIDs, free-text notes and datetime stamps with seconds are the usual offenders. Splitting datetime into date and time is a cheap, large win.
• Removing columns later is harder than adding them. Dropping a column can break reports and model structure, so it's worth getting the column list right at the start rather than importing everything "just in case".
• Auto date/time is invisible until you look for it. On a wide model with fifteen date columns it's a meaningful chunk of the size and nobody put it there deliberately.
• Refresh needs headroom above the resting model size. A capacity sized on the model at rest will struggle during the overnight window.
• Many small models coexist better on a capacity than one large one — they get evicted from memory less often and contend for less.
• Text order numbers with a consistent prefix can often be stripped to integers, which is a real saving on a high-cardinality column. Set the summarisation to Do Not Summarize afterwards or you'll get nonsense totals.

Consultant notes

• Model size work is unglamorous, cheap and pays back more than anything else. Sell it as a fixed piece of work with a measurable before-and-after, not as vague optimisation.
• Get the grain conversation in front of the business, not the IT team. "You will never be able to see individual orders in this report" is their decision to make and their risk to carry.
• Watch for models built by importing whole tables from a warehouse. That's where the easy 60% usually is, and it costs nothing but a conversation.
• If the client is already on Fabric capacity and the model is straining, Direct Lake is worth raising — it removes the copy rather than shrinking it. Be straight that it has its own constraints and isn't a swap-in for every model.

---

Revisit if the compression guidance or the capacity model size ceilings change, or once Direct Lake is on the table for this client.