Which In-Memory Analytics Engine is Used in Power Pivot?

Time is money. You know this; that’s why you use Power Pivot to optimize your data analysis. But what makes it so effective? Knowing more about the in-memory analytics engine behind Power Pivot can give you an insight into how to make it work better for you. 

What is xVelocity in-memory Analytical Engine (Vertipaq)?

The in-memory analytics engine used in Power Pivot is called xVelocity, but it is commonly referred to by its original name, Vertipaq. In fact, the internal engine is actually still named Vertipaq, and most users in the industry use this moniker. 

Vertipaq is a powerful engine that analyzes and stores your data. It does this by putting the data into columns and compressing it to save as much space as possible. Speed is the name of the game, and it works by finding the most efficient route to achieve its goals, which in turn saves you time.

Vertipaq is the driving force behind Power Pivot, which can be added to Excel for maximum data analysis. The functions of Power Pivot are also available in Power BI Designer. It is an in-memory analytical engine.

What is in-memory analytics?

With in-memory analytics, queries and data are stored in RAM. This is in contrast to other programs that store data on disks in a much more cumbersome manner. By storing everything in RAM, Vertipaq can process it much faster, which is essential when you are running large amounts of data. 

Vertipaq is Microsoft’s proprietary in-memory analytics engine, so some of the nitty-gritty details about how it works aren’t known, but we can discuss how it works in a broad sense. 

How does Vertipaq work?

Columnar databases save time and space

A columnar database does what it sounds like it would: it stores data in columns rather than rows. This allows for vertical scanning of data, which is more efficient and thus faster. When you think about the way you might physically scan a table to extract information, you would either read across the rows or down the columns. What you do depends largely on what you are searching for, but in general, scanning vertically is faster and more efficient.

Consider the example of finding the sum of Total Sales in a table. You would go directly to the Total Sales column and read only that column. You wouldn’t read each row, because other irrelevant data from the table can be ignored for this query. Vertipaq does just this. It reads and stores your data in columns, which allows for quicker access to the answers you need. 

Vertipaq compresses data to minimize space consumption 

Vertipaq uses several functions to compress your data once it is stored in columns. This compression is beneficial because it saves RAM and is faster to scan. There are a few ways data compression works in Vertipaq. First, it will segment and partition your data into columns. This allows it to read one section at a time. Once it has read a section, it will begin to compress it while simultaneously moving on to read the next section. There are a few ways Vertipaq compresses data. It chooses based on the type and range of data in a column.

• Value encoding reduces the number of bits needed to store data in integer columns by changing the range of data.
• Dictionary encoding converts column data to integers by creating a dictionary of relationships. These integers then take up less RAM.
• Run length encoding further compresses dictionary or value encoded data to eliminate repetitions.

Re-encoding, when Vertipaq goes back and starts the compression process over, can occur if the engine begins compression with either data or value encoding, but later discovers that was not the most efficient choice. It will then start the compression again using the other - better - method. This can take some time to complete. The best way to avoid re-encoding is to ensure that the first rows of your data set provide a good sample of the rest of the data. That way, there are no issues later with surprise outliers that affect the method of compression. 

Make the most of your data by sharing it effectively

When you have your in-memory analytics optimized, you are able to save and process your data efficiently. Shouldn’t sharing your reports be efficient, too? With PBRS from ChristianSteven, your reporting can be automated to fit your needs. We are here to help. Contact us for more information, or start your free trial today.

Get Features PDFStart Free Trial