๐Ÿง  Second Brain

Search

Search IconIcon to open search

VertiPaq (xVelocity)

Last updated Jan 29, 2025

The VertiPaq engine is an in-memory Columnar database powering MS Excel Power Pivot, SQL Server Analysis Services (SSAS) Tabular, and Power BI. When data is loaded into a data model, the VertiPaq engine compresses and stores it in RAM for efficient processing.

# How does it work?

VertiPaq memory optimized ColumnStore index, is the same technology implemented in the SQL Server engine, in the form of non-clustered columnar indexes Source

Source: Inside the VertiPaq Engine by Marco Russo

This technology is comparable to the Photon Engine used by Databricks for their compute operations, as well as similar solutions employed by Snowflake and other platforms.

Another thing to note is that VertiPaq handles joins through pre-computed hashmaps (called relationships) that are rebuilt anytime a table in the join changes. The hashmap sits in the CPU cache and this avoids regular join perf concerns. Another read-focused design choice. This doesnโ€™t apply for CCI in SQL Server or Synapse, just the read-heavy analytics-focused implementations of VertiPaq. Alex Barbeau on Bsky

# Different Versions

Got multiple flavors of xVelocity:

The 8-byte rule applies to all the following technologies: xVelocity memory-optimized columnstore index can see a 400% gain in performance when using datatype 8ย bytes or less in size. Some history knowledge in:

# Used in

More shared by Rif Kiamil:

# History

Based on the discussion in RW The rise and history of Power BI with Amir Netz by Kasper On BI between Amir Netz and Kasper On:

The VertiPaq development journey began around 2008 when Microsoft’s team sought to create a column store engine capable of delivering unprecedented performance. The breakthrough occurred when Amir Netz, inspired by an article stating that “data is not distributed uniformly in systems,” developed a revolutionary algorithm during one sleepless night. Christian Petrulis, one of Microsoft’s top developers, implemented the initial version, which comprised approximately 2 million lines of code.

VertiPaq’s revolutionary nature stemmed from its fundamental departure from traditional OLAP approaches. Rather than depending on disk-stored pre-computed aggregations, it utilized in-memory processing with innovative compression algorithms that could operate on compressed data directly. The engine was optimized for small to medium-sized datasets that fit in memory. Its initial performance tests were so remarkable that they raised skepticism, with Amir noting it performed “an order of magnitude faster than any other column store” โ€“ a distinction that remains true over a decade later.

The technology transformed Self-Service BI by eliminating the lengthy pre-computation times typical of traditional OLAP systems. This advancement allowed business users to make changes and view results instantly, similar to the Excel experience. Amir considers VertiPaq a “once-in-a-lifetime” technological breakthrough, describing it as “perfect” โ€“ so well-optimized that its core algorithm has remained largely unchanged for over a decade. The development team carefully introduced it to avoid disrupting users’ existing technology adoption. This shift from disk-based, pre-computed OLAP systems to interactive, in-memory analytics proved crucial for Power BI’s success and modern business intelligence.

# Renaming

From 2022-03-19: VertiPaq has been rebranded to xVelocity. The official name is now “xVelocity in-memory analytics engine (VertiPaq),” though both terms โ€“ xVelocity and VertiPaq โ€“ are used interchangeably when discussing Analysis Services. Source

# Other In-Memory Formats/Engines


Shared on Twitter

See more on In-Memory Formats.

# Further Readings


Origin: Tweet
References: In-Memory Formats
Created: 2023-03-03