Reducing model size in Power BI - Deep Dive

Overview - Reducing model size

What is it?

Reducing model size means making your Power BI data model smaller and more efficient. It involves techniques to lower the amount of memory your data uses without losing important information. This helps reports load faster and run smoother, especially with large datasets. Smaller models also make sharing and refreshing data easier.

Why it matters

Without reducing model size, Power BI reports can become slow, crash, or fail to refresh due to memory limits. Large models use more computer resources and take longer to open or update. By reducing size, users get faster insights and better experience. It also saves costs when using cloud services that charge by memory usage.

Where it fits

Before learning this, you should understand basic Power BI data modeling and how tables and relationships work. After mastering model size reduction, you can explore advanced optimization like aggregations, incremental refresh, and composite models to handle even bigger data efficiently.

Mental Model

Core Idea

Reducing model size is about trimming unnecessary data and optimizing storage so your Power BI reports run faster and use less memory.

Think of it like...

Think of your data model like a backpack. If you pack only what you need and organize it well, it’s lighter and easier to carry. But if you stuff it with everything, it becomes heavy and hard to manage.

┌─────────────────────────────┐
│       Power BI Model         │
├─────────────┬───────────────┤
│ Raw Data    │ Large Memory  │
│             │ Usage         │
├─────────────┴───────────────┤
│  ↓ Reduce Size Techniques ↓  │
├─────────────┬───────────────┤
│ Remove      │ Compress Data │
│ Unused Data │ Optimize Types│
├─────────────┴───────────────┤
│ Smaller Model │ Faster Reports│
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Power BI Data Models

Concept: Learn what a Power BI data model is and how it stores data.

A Power BI data model is a collection of tables connected by relationships. It stores data in memory using a technology called VertiPaq, which compresses data for fast access. The model holds all the data your reports use to show visuals and calculations.

Result

You know that the model is the heart of your report and that its size affects performance.

Understanding the data model basics helps you see why its size matters for report speed and resource use.

2

Foundation

What Makes Model Size Grow

Concept: Identify factors that increase the size of your Power BI model.

Model size grows when you load many rows, many columns, or columns with many unique values. Also, storing text data or high-precision numbers uses more space. Unused columns and tables add unnecessary weight. Complex calculated columns can also increase size.

Result

You can spot what parts of your data model cause it to be large.

Knowing what inflates model size lets you target those areas for reduction.

3

Intermediate

Removing Unused Columns and Tables

🤔Before reading on: do you think removing unused columns alone can significantly reduce model size? Commit to your answer.

Concept: Learn to identify and delete columns and tables not used in reports or calculations.

Use Power BI's 'Model' view or 'Manage Relationships' to find tables and columns not referenced in visuals or measures. Removing them reduces memory use because Power BI no longer stores that data. This is often the easiest and fastest way to shrink your model.

Result

Your model becomes smaller and your report faster without losing any visible data.

Understanding that unused data still consumes memory helps you clean your model effectively.

4

Intermediate

Optimizing Data Types for Compression

🤔Before reading on: do you think changing data types affects model size? Commit to your answer.

Concept: Changing columns to more efficient data types improves compression and reduces size.

For example, use whole numbers instead of decimals when possible. Replace text columns with numeric codes if you don’t need the text. Use date/time types properly. Smaller data types compress better in VertiPaq, saving memory.

Result

Your model uses less memory and loads faster because data compresses more efficiently.

Knowing how data types affect compression unlocks a powerful way to reduce model size.

5

Intermediate

Using Aggregations to Reduce Detail

🤔Before reading on: do you think aggregating data always reduces model size? Commit to your answer.

Concept: Aggregations summarize detailed data, so you store less raw data in the model.

Instead of loading every transaction, you can load monthly or yearly totals. This reduces rows and unique values. Power BI can use these aggregations to answer queries faster. You can keep detailed data in a separate source if needed.

Result

Your model is smaller and queries run faster, but you lose some detail in reports.

Understanding the trade-off between detail and size helps you balance performance and analysis needs.

6

Advanced

Implementing Incremental Data Refresh

🤔Before reading on: do you think incremental refresh reduces model size or just refresh time? Commit to your answer.

Concept: Incremental refresh loads only new or changed data, reducing refresh time and model size growth.

Instead of reloading all data every time, Power BI refreshes recent data partitions. Older data stays unchanged and compressed. This keeps the model size stable and refreshes faster, especially for very large datasets.

Result

Your model stays manageable in size over time and refreshes complete faster.

Knowing how incremental refresh controls data loading helps maintain model size in production.

7

Expert

Advanced Compression and Composite Models

🤔Before reading on: do you think composite models always reduce model size? Commit to your answer.

Concept: Composite models combine imported and direct query data to optimize size and performance.

You can import summarized data for fast access and keep detailed data in the source, queried live. VertiPaq compression works on imported data only. Using aggregations, partitions, and direct query together lets you balance size, speed, and freshness.

Result

Your reports handle huge data volumes efficiently without bloating the model size.

Understanding composite models reveals how to scale Power BI beyond memory limits.

Under the Hood

Power BI uses VertiPaq, an in-memory columnar storage engine, to compress and store data. It stores columns separately, compressing repeated values and encoding data types efficiently. Compression depends on data type, cardinality (unique values), and data distribution. Removing unused columns or changing data types improves compression. Incremental refresh partitions data to avoid reloading all data. Composite models split data between in-memory and live queries.

Why designed this way?

VertiPaq was designed to enable fast, interactive analytics by keeping data in memory and compressing it heavily. This design balances speed and memory use. Incremental refresh and composite models were added to handle growing data sizes and real-time needs without overwhelming memory or slowing reports.

┌───────────────┐
│ Raw Data      │
├───────────────┤
│ Columns       │
│ ┌───────────┐ │
│ │ Column A  │ │
│ │ Column B  │ │
│ └───────────┘ │
├───────────────┤
│ VertiPaq     │
│ Compression  │
│ ┌───────────┐ │
│ │ Encoded   │ │
│ │ Data      │ │
│ └───────────┘ │
├───────────────┤
│ Model in     │
│ Memory       │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does removing unused columns always guarantee a big model size reduction? Commit yes or no.

Common Belief: Removing unused columns always drastically reduces model size.

Reality: Sometimes unused columns are small or few, so removing them has little effect. Other times, large columns or high-cardinality columns matter more.

Why it matters: Expecting big gains from removing small unused columns can waste time and distract from bigger optimizations.

Quick: Does changing data types to text always reduce model size? Commit yes or no.

Common Belief: Changing columns to text reduces model size because text is simpler.

Reality: Text columns usually increase size because they have many unique values and compress poorly compared to numbers.

Why it matters: Using text unnecessarily can bloat your model and slow reports.

Quick: Does incremental refresh reduce the total data stored in the model? Commit yes or no.

Common Belief: Incremental refresh always makes the model smaller by loading less data.

Reality: Incremental refresh reduces refresh time but the total data stored can be the same or larger if partitions accumulate.

Why it matters: Misunderstanding this can lead to unexpected memory use and refresh failures.

Quick: Can composite models eliminate all model size problems? Commit yes or no.

Common Belief: Composite models always solve model size and performance issues completely.

Reality: Composite models help but add complexity and sometimes slow live queries. They require careful design.

Why it matters: Overreliance on composite models without planning can cause poor user experience.

Expert Zone

1

VertiPaq compression effectiveness depends heavily on column cardinality and sorting; sorting columns before import can improve compression.

2

Calculated columns increase model size because they are stored in memory; using measures instead can save space.

3

Using star schema design with dimension tables having low cardinality improves compression and query speed.

When NOT to use

Reducing model size techniques are less useful when real-time data is required or when data sources are too large for in-memory storage. In such cases, use DirectQuery or live connections instead of import mode.

Production Patterns

Professionals combine removing unused data, optimizing data types, aggregations, incremental refresh, and composite models. They monitor model size with Power BI tools and automate refreshes. They design star schemas and avoid calculated columns when possible to keep models lean.

Connections

Database Normalization

Builds-on

Understanding how to organize data efficiently in databases helps design Power BI models that reduce redundancy and size.

Data Compression Algorithms

Same pattern

Knowing how compression works in general explains why certain data types and patterns compress better in Power BI.

Minimalism in Design

Analogous principle

The idea of removing unnecessary elements to improve function applies both to visual design and data model size reduction.

Common Pitfalls

#1 Keeping all columns 'just in case' increases model size unnecessarily.

Wrong approach:

Load entire source tables with all columns without checking usage.

Correct approach:

Remove unused columns and only load necessary data for reports.

Root cause:

Fear of losing data leads to loading everything, ignoring performance impact.

#2 Using calculated columns for all logic inflates model size.

Wrong approach:

Create many calculated columns instead of measures for calculations.

Correct approach:

Use measures for calculations whenever possible to save memory.

Root cause:

Misunderstanding that calculated columns are stored in memory like data.

#3 Changing numeric columns to text to simplify data increases size.

Wrong approach:

Convert IDs or codes from numbers to text without need.

Correct approach:

Keep numeric data types for better compression and performance.

Root cause:

Assuming text is simpler or more flexible without considering compression.

Key Takeaways

Reducing model size improves Power BI report speed, refresh time, and user experience.

Removing unused columns and tables is the simplest way to shrink your model.

Optimizing data types and using aggregations help compress data efficiently.

Incremental refresh and composite models manage large data but require careful design.

Understanding VertiPaq compression and data cardinality unlocks advanced optimization.