Power BI Architecture Deep Dive: How Data Actually Moves From Source to Dashboard

Introduction

Every mysterious bug in Power BI - the wrong totals, the slow report, the slicer that breaks a measure - has a root cause somewhere in the architecture. Most developers never find it because they never understood the architecture in the first place.

This isn't an abstract concept. Understanding how Power BI ingests, compresses, stores, and queries data is directly useful every time you write a DAX measure, choose between Import and DirectQuery, or wonder why your 50,000-row report takes 8 seconds to load.

---

Core Concept: The Three-Layer Architecture

Power BI is built on three layers that are often invisible to users but fundamental to developers:

1. The Data Ingestion Layer
Power Query sits here. It connects to sources, executes transformations, and loads data. Every query in Power Query produces a table. The quality of this layer determines everything downstream - garbage in, garbage out, no matter how elegant your DAX is.

2. The VertiPaq Engine (In-Memory Columnar Store)
When you press Refresh, Power BI compresses your data into a columnar, in-memory database called VertiPaq. This is why Import mode is fast - the data is already in RAM, heavily compressed, indexed by column rather than by row. VertiPaq uses dictionary encoding and run-length encoding to achieve compression ratios of 10:1 or better. A 1 GB CSV often becomes 100 MB in memory.

3. The Formula Engine (DAX Execution Layer)
When a visual renders, the DAX formula engine generates a query plan, breaks it into storage engine requests, and reassembles the result. This is where CALCULATE, FILTER, and iterators execute. This is also where most performance problems live.

---

Real-World Application

A logistics company had a Power BI report that took 45 seconds to open. The data was only 200,000 rows - not large by any standard. Investigation revealed three problems: bidirectional relationships causing filter ambiguity (forcing the formula engine to scan full tables), a calculated column using RELATED() across a large table (bloating the model), and no query folding in Power Query (loading millions of rows and then filtering in memory).

After fixing the architecture - removing bidirectional relationships, replacing the calculated column with a measure, and pushing the filter back to the SQL source - the report opened in 4 seconds. Same data. Different architecture.

---

Technical Deep Dive: How VertiPaq Compresses Data

VertiPaq stores each column independently. For a column like Region with values [North, South, North, East, North, South], it:

1. Creates a dictionary: {0: North, 1: South, 2: East}
2. Stores the column as integers: [0, 1, 0, 2, 0, 1]
3. Applies run-length encoding where possible: [{0,1}, {1,1}, {0,1}, {2,1}, {0,1}, {1,1}]

The practical implication: low cardinality columns compress better. A column with 10 unique values compresses dramatically better than a column with 10,000 unique values. This is why data modelers hide high-cardinality columns, split datetime into separate Date and Time dimensions, and avoid free-text columns in fact tables.

The DAX query plan works in two phases:

• Storage Engine (SE) requests: Fast, parallelized, run directly against VertiPaq
• Formula Engine (FE) processing: Slower, single-threaded, handles complex logic like CALCULATE

A measure that generates many SE requests is faster than one that pushes logic into the FE. This is why SUMX over millions of rows is slower than SUM on a pre-aggregated column.

---

Common Mistakes

Ignoring query folding. When Power Query transformations aren't folded back to the source (SQL, for example), Power BI downloads the full dataset and filters in memory. Checking the "View Native Query" option in Power Query tells you whether folding is happening. A greyed-out option means it isn't - and your refresh will always be slower than it needs to be.

High-cardinality calculated columns. A calculated column like [Full Name] = [First Name] & " " & [Last Name] on a 5 million row table is stored uncompressed. Replace it with a measure or handle the concatenation at the source.

Bidirectional relationships as a default. Every bidirectional relationship forces the formula engine to consider filter propagation in both directions. On complex models, this creates ambiguous filter paths that make DAX unpredictable and measures slow.

---

Pro Tips / Industry Insight

VertiPaq Analyzer (a free tool by Marco Russo and Alberto Ferrari) gives you a table-by-table breakdown of your model's memory footprint. Every serious Power BI developer should run it on any model before publishing to production. A table that's 300 MB when you expected 20 MB is a signal - and the answer is almost always high cardinality in an unexpected column.

Understanding the architecture also makes you a better communicator with data engineers. When you can explain to a SQL developer why SELECT * FROM orders is worse than a filtered, pre-aggregated view for Power BI consumption, you're adding real value to the team - not just building dashboards.

---

Summary

Power BI architecture is three layers: data ingestion (Power Query), in-memory storage (VertiPaq), and DAX execution (formula engine). VertiPaq achieves high compression on low-cardinality columns. The formula engine is single-threaded and expensive - keep logic in the storage engine where possible. Every performance problem has an architectural root cause. Learn the architecture once, debug forever.

Track your Power BI architecture knowledge with a live DRI score at devwithdata.in.