VertiPaq

What is VertiPaq?

VertiPaq is the in-memory storage engine that holds your data inside a Power BI import mode model, an Analysis Services Tabular model, or an Excel Power Pivot workbook. When you refresh, it copies the source data into memory column by column, in heavily compressed form. Every DAX query after that reads from this copy in memory, so the original source is never touched again.

The trick is the direction of storage. A transactional database keeps data row by row, which is handy for looking up or editing one order. VertiPaq keeps data column by column, the approach known as columnar storage. For an analytical question like "revenue by month" the engine only reads the two columns it needs instead of all forty, and those columns are already sitting in memory.

That compression is why a model can be far smaller than its source. Microsoft gives a rough rule of thumb of about 10x: roughly 10 GB of source data can land at around 1 GB in the model. It is an order of magnitude, not a promise, and how close you get depends almost entirely on your columns.

How VertiPaq compresses a column

Columns compress well because the values inside one column are alike, far more alike than the values sitting across one row. VertiPaq uses three techniques and combines them per column.

• Value encoding. For whole numbers, VertiPaq stores each value using simple arithmetic, such as subtracting a base value, so every number fits in fewer bits. No lookup table is involved. This is why an integer is almost always cheaper to store than the same information held as text.

• Dictionary encoding. For text and most other non-numeric data, VertiPaq builds a dictionary of the distinct values in the column, gives each one an integer id, and stores only those ids in the column itself. Microsoft's documentation also calls this hash encoding, because a lookup goes through a hash of the dictionary.

• Run-length encoding. When the same value repeats over many consecutive rows, VertiPaq stores it once with a count instead of once per row. A country value of "BE" that runs for four million rows becomes a single entry plus the number four million.

Take a Country column on ten million order lines that only ever contains BE, NL, or FR. Dictionary encoding replaces ten million text strings with a three-row dictionary and ten million small integer ids. If the rows are ordered so identical values sit together, run-length encoding then collapses each run into one entry and a count. The same information, a fraction of the memory.

Why cardinality drives model size

All three techniques depend on repetition, and repetition disappears when a column holds many distinct values. The number of distinct values in a column is its cardinality, and it is the single biggest factor in what that column costs. High cardinality means a large dictionary and little to collapse, so the compression barely helps.

A datetime column recorded down to the second is the classic offender. Over five years, a timestamp to the second is close to unique on every row, so the dictionary grows almost as large as the table itself. Split that one column into a date column and a time column and both shrink at once: five years of dates is under two thousand distinct values, and a time of day carries at most 86,400. Two small dictionaries replace one enormous one, for exactly the same information. SQLBI, whose authors are the recognised authorities on the engine, note that run-length encoding compresses a slow-changing date far better than a fast-changing time.

Segments: how a column is stored

VertiPaq does not compress a whole column in one piece. It slices each column into segments, each holding on the order of a million rows, and compresses every segment on its own. At query time it can scan those segments in parallel, which is part of why import models feel quick.

One consequence matters in practice: because run-length encoding works inside a segment, the order of the rows affects the result. When equal values sit next to each other, runs get longer and the column gets smaller. It is also why a star schema tends to compress better than one wide flat table, since narrow dimension keys repeat cleanly inside the fact table, while the same descriptive values scattered across a wide table keep their high cardinality.

VertiPaq and DirectQuery

VertiPaq only does its work when the data actually lives in the model, which is import mode. In a DirectQuery table there is nothing for VertiPaq to store: the table holds only metadata, and each query is translated into the native language of the source and run there. The model stays small, but the speed then depends on that source system rather than on VertiPaq compression.

So the levers in this article, cardinality, data types and splitting columns, only pay off for import tables in your semantic model. For a DirectQuery table the same tuning belongs in the source database instead. Knowing which storage mode a table uses tells you where VertiPaq can help and where it cannot.