6.2 Model Size Reduction and Cardinality Management

Quick Answer: Model size is driven by column count, row count, and cardinality (distinct values per column). Remove unused columns, filter unnecessary rows, use integers instead of text for keys, reduce decimal precision, and disable Auto Date/Time. High-cardinality text columns (GUIDs, free text, timestamps) are the worst offenders.

How VertiPaq Stores Data

Power BI's VertiPaq engine is a columnar store that compresses each column independently using dictionary encoding (mapping distinct values to small integer IDs), run-length encoding (collapsing repeated runs), and value encoding for integers. Compression quality therefore depends far more on how many distinct values a column has than on the row count alone.

Cardinality Levels

Column Optimization

Every column in the model consumes memory, so remove any not used in visuals, measures, relationships, or row-level security. In Power Query, Remove Other Columns (select the ones you need, then remove others) is more future-proof than removing columns individually, because new source columns are excluded automatically.

Reduce precision where you can: use Date instead of Date/Time when time is irrelevant (a huge cardinality saving, since a datetime to the second has up to 86,400 distinct values per day), round decimals to the needed places, and split a high-cardinality datetime into separate low-cardinality Date and Time columns if both are needed.

Replace high-cardinality text keys with integer surrogate keys. A key like "PROD-ABC-12345" compresses far worse than 1, 2, 3. Create one in Power Query with Add Column > Index Column > From 1, or with a merge against a key table.

Row Optimization

Filter history you do not need: Table.SelectRows(Source, each [Date] >= #date(2024, 1, 1)). Aggregate to the reporting grain: if reports never drill below day but data is per-second, Group By Date and Product to sum Amount, which can cut millions of rows to thousands. For very large models, build aggregation tables, Power BI routes summary-level queries to the small aggregation and drillthrough queries to the detail, and use incremental refresh so only recent partitions reload.

Disable Auto Date/Time

File > Options > Data Load > Time Intelligence > uncheck "Auto date/time for new files" (and uncheck it for the current file under the current-file options).

Auto Date/Time silently creates a hidden date table for every date column in the model, each with year/quarter/month/day hierarchies. Ten date columns means ten hidden tables, a meaningful and invisible source of bloat. Replace it with one explicit Date dimension used for all date-based analysis and time intelligence.

Measuring and Diagnosing Size

Use external tools to find the offenders: DAX Studio's VertiPaq Analyzer reports per-column size, cardinality, and dictionary size, letting you target the few columns that dominate the model. Typically a small number of high-cardinality columns account for most of the file size.

On the Exam

The PL-300 tests identifying high-cardinality columns as size problems, choosing strategies to shrink the model, the effect of data types on compression, when to aggregate before loading, and disabling Auto Date/Time in favor of an explicit date table.

Size Is About Cardinality First

The most important mental shift for this objective is that distinct values, not row counts, drive model size. VertiPaq stores each column as a dictionary of unique values plus compact pointers, so a billion-row Boolean column compresses to almost nothing while a million-row GUID column barely compresses at all. This is why the fastest path to a smaller model is hunting down a handful of high-cardinality columns, free-text notes, GUIDs, email addresses, exact timestamps, and either removing them, splitting them, or lowering their precision.

Tools like DAX Studio's VertiPaq Analyzer rank columns by size so you spend effort where it pays off.

Practical Levers, in Priority Order

Work the levers roughly in this order. Remove columns you do not need in any visual, measure, relationship, or security rule, this is the highest-leverage, lowest-risk change. Lower precision: convert Date/Time to Date when time is irrelevant (a massive cardinality drop), round decimals, and replace verbose text keys with integer surrogate keys. Reduce rows by filtering history to the reporting window and aggregating to the needed grain in Power Query. Add aggregation tables and incremental refresh for very large fact tables so only summaries live in memory and only recent partitions reload.

Disable Auto Date/Time so the model is not carrying a hidden date table per date column.

The DirectQuery Trade-Off

When a fact table is simply too large to import, DirectQuery keeps detail at the source and queries it live, trading in-memory speed for currency and unlimited volume. Composite models combine the two, importing small, fast-changing or shared dimensions (set to Dual) while leaving the huge fact in DirectQuery, and layering user-defined aggregations so common summary queries hit a tiny in-memory table. The exam expects you to recognize when import optimization is enough versus when DirectQuery or a composite model is the appropriate architecture for the data volume described.