Power BI Unplugged from Data to Dashboards: VertiPaq The Brain & Muscles Behind the Magic

Have you ever wondered what makes Power BI so fast and powerful when it comes to performance?

Power BI is one of those tools that feels almost magical. You connect to a dataset, drag a few fields into a chart, and suddenly you’re looking at insights that would’ve taken hours to calculate manually. But behind that magic is some serious engineering.

At the heart of it all lies VertiPaq — the in-memory columnar storage engine that makes Power BI dashboards lightning-fast. In this post, we will dig deep to discover what is “under the hood” of Power BI, how your data is being stored, compressed, and queried, and peel back the curtain to see how VertiPaq works, why it matters, and how you can design your models to get the best out of it.

How do we bring data to Power BI?

• Open Power BI Desktop

• In the home tab we find Data Group, there we have get data where we can fetch the data from various sources.
• Some of popular data sources are pinned in data group

• If we extend get data we find other available data sources

Data Sources: Where the Story Begins

Your data doesn’t live in Power BI — it lives out in the wild: Excel sheets, databases, cloud services. Power BI just fetches a copy (or connects live) so you can start shaping it.

Think of data sources as the “ingredients” in your kitchen. Power BI is the chef that knows how to mix them into something delicious.

This is where our data actually lives, This is the original source of our data and everything lives completely outside of powerbi ex: CSV, Excel, Sql server

PowerBi Desktop

• Data Layer: This is the raw data that powerbi brings from the sources. This is just a local copy of your data.
• Model Layer: This is where we describe and organize your data, here we have tables, columns and relations between tables to each other. SO it’s just like structure a description of our data but not the data itself.
• Visual Layer: This where we build our reports using visuals like charts, bars, slicers

All these three layers always work together

Example: If you want to create a new visual or you are interacting with it powerbi send a request to the model in order to get the data and the model going to prepare the final result and send it back the visual

---

VertiPaq – “Brain & Muscles” behind Power BI

vertipaq core system that performs the actual processing behind the scenes(backend).

• The VertiPaq engine is Power BI’s in-memory, columnar storage engine that compresses data and enables extremely fast query performance for analytical workloads.
• When we use import mode Powerbi copies the data from the data source and then a compression mechanism takes place which compresses the data and optimizes the data and then data gets stores to disk by vertipac engine
• Let’s understand this compression mechanism When source data is loaded into memory with the help of this compression mechanism, it’s possible to achieve 10x compression, So it’s completely reasonable to expect that a 10 GB data which is there in the data source can compress to about 1GB in size and stores in the disk.

Now we will dig deep to discover what is “under the hood” of Power BI, how your data is being stored, compressed, queried, and finally, brought back to your report.

First, I want you to meet the VertiPaq engine, “brain & muscles” of the system behind not only Power BI, but also SSAS Tabular and Excel Power Pivot. Truth to be said, VertiPaq represents only one part of the storage engine within the Tabular model, besides DirectQuery

When you send the query to get data for your Power BI report, here is what happens:

• Formula Engine (FE) accepts the request, process it, generates the query plan and finally executes it
• Storage Engine (SE) pulls the data out of Tabular model to satisfy the request issued within the query generated by the Formula Engine

Storage Engine works in two different ways in order to retrieve requested data: VertiPaq keeps the snapshot of the data in-memory. This snapshot can be refreshed from time to time, from the original data source.

On the opposite, DirectQuery doesn’t store any data. It just forwards the query straight to the data source for every single request.

Internal architecture of the Analysis Services Tabular model

Analysis Services Tabular mode is a data modeling engine built on top of Microsoft SQL Server Analysis Services (SSAS).
It’s designed to store, process, and query analytical data efficiently using in-memory columnar storage (VertiPaq).

Data in Tabular model is usually stored either as an in-memory snapshot (VertiPaq) or in DirectQuery mode. However, there is also possibility of implementing a hybrid Composite model, which relies on both architectures in parallel.

Formula Engine – “Brain” of Power BI

Formula Engine accepts the query, and since it’s able to “understand” DAX (and MDX also, but it is out of the scope ), it “translates” DAX into a specific query plan, consisting of physical operations that need to be executed in order to get results back.

Those physical operations can be joins between multiple tables, filtering, or aggregations. It’s important to know that Formula Engine works in a single-threaded way, which means that requests to Storage Engine are always being sent sequentially.

Storage Engine – “Muscles” of Power BI

Once the query been generated and executed by the Formula Engine, the Storage Engine comes into the scene. It physically goes through the data stored within the Tabular model (VertiPaq) or goes directly to a different data source (SQL Server for example, if DirectQuery storage mode is in place).

When it comes to specifying the storage engine for the table, there are three possible options to choose between:

• Import mode – based on VertiPaq. Table data is being stored in-memory as a snapshot. Data can be refreshed periodically
• DirectQuery mode – data is being retrieved from the data source at the query time. Data resides in its original source before, during and after the query execution
• Dual mode – combination of the first two options. Data from the table is being loaded into memory, but at the query time it can be also retrieved directly from the source

As opposed to Formula Engine that doesn’t support parallelism, the Storage Engine can work asynchronously.

VertiPaq Storage Engine

let me explain in more details what VertiPaq does in the background to boost performance of our Power BI reports.

When we choose Import mode for our Power BI tables, VertiPaq performs the following actions:

• Reads the data source, transforms data into columnar structure, encodes and compresses data within each of the columns
• Establishes dictionary and index for each of the columns
• Prepares and establishes relationships
• Computes all calculated columns and calculated tables and compresses them

Two main characteristics of VertiPaq are:

1. VertiPaq is a columnar database
2. VertiPaq is an in-memory database

As you can see in the illustration above, columnar databases store and compress data in a different way to traditional row-store databases. Columnar databases are optimized for vertical data scanning, which means that every column is structured in its own way and physically separated from other columns!

With columnar databases, single-column access is fast and effective. Once the computation starts to involve multiple columns, things become more complex, as intermediary steps’ results need to be temporarily stored in some way.

Simply put, columnar databases are more CPU-intensive, while row-store databases increase I/O due to many scans of unnecessary data.

Conclusion

This is just the beginning of our Power BI Unplugged series. We’ve met VertiPaq — the muscle behind the magic. Next, we’ll meet some great topic that translates your DAX into lightning-fast queries.