Level 3 · Column types, cardinality and compression

A Power BI Import model lives in memory, compressed by VertiPaq. How well it compresses decides how big the file is, how long refresh takes and how fast every visual scans. The single biggest factor is something you control: cardinality, the number of distinct values in a column.

What you are working in

This is a quiz. To look at a real model’s size in Power BI Desktop, open Model view and check each column’s Data type in the Properties pane, or use the free DAX Studio: Advanced → View Metrics (VertiPaq Analyzer) lists every table and column with its cardinality and size.

How VertiPaq stores a column

Each column is stored on its own. VertiPaq builds a dictionary of the column’s distinct values and replaces every row’s value with a small number pointing into it. Runs of the same number are stored once with a count (run-length encoding). Whole numbers can skip the dictionary and be stored as compact numbers directly (value encoding).

So a status column with four values and 12,000 rows costs almost nothing: four dictionary entries and a list of tiny numbers. A column where every row is different costs a full dictionary of 12,000 values plus the references. Row count matters far less than distinct values.

Types, from cheap to expensive

• Whole numbers (keys like product_id, quantities): compress best. The ideal key type.
• Dates: good. Three years of days is about 1,100 distinct values.
• Decimals: fine at sensible precision; Fixed decimal number (currency) is usually smaller than full decimal.
• Datetimes to the second or millisecond: a different value on nearly every row.
• Long text (comments, user agents, URLs): large dictionaries, poor compression.
• GUIDs: 36 random characters, unique on every row. The worst column you can import.

Lowering cardinality

• Split date and time. One date column and one time column (or an hour column if the report only needs hours) replace millions of timestamps with a few thousand values. In Power Query: select the column, Add Column → Date → Date Only and Time → Hour, then remove the original.
• Integer keys. Relate tables on whole-number keys, not text or GUIDs. If the source only has a GUID, ask for a surrogate key in the warehouse.
• Round to the grain you report. Prices to cents, times to the hour.
• Drop free text the report doesn’t show. Replace it with a category if one is needed.
• Remove columns nobody uses. The cheapest column is the one you don’t load.

Common mistakes

• Text keys because the source happens to have them.
• Calculated columns for everything. They are stored and compressed like any column, and they add size; a measure costs nothing until a visual asks for it.
• Auto date/time left on. Power BI builds a hidden date table for every date column; switch it off under File → Options and settings → Options → Data Load and use one Date table.