Power BI Model Frameworks
Understanding Import, DirectQuery, and Composite Models
Lecture Agenda
Core Concepts Advanced & Applied
Introduction to Connectivity Composite Models & Aggregations
Import Mode Architecture Performance Benchmarking
DirectQuery Mechanics Real-world Discussion Scenarios
Understanding Data
Connectivity
The foundation of every Power BI report
The Three Pillars of Connectivity
Import DirectQuery Composite
Data is loaded into Power BI's Data remains at the source. A hybrid approach mixing Import
internal memory. Fastest Queries are sent in real-time. and DirectQuery tables.
performance.
1. Import Mode
The default and most common storage mode.
Import Mode Architecture
How it Works
When you use Import, Power BI takes a snapshot of your
data and stores it in its internal VertiPaq engine.
This creates a highly compressed, column-oriented copy
of the data optimized for analytical queries.
Note: Data is disconnected from the source until the next
refresh.
The VertiPaq Engine
Columnar Storage Compression
Unlike row-based SQL databases, VertiPaq stores VertiPaq uses state-of-the-art compression
data by column. This is incredibly efficient for (Value Encoding, Hash Encoding). A 10GB CSV
aggregations (e.g., "Sum of Sales") because the file might compress down to 1GB in Power BI
engine only scans the relevant column. memory, enabling fast in-memory processing.
Advantages of Import Mode
Blazing Fast Performance: Queries run in-memory,
providing sub-second response times.
Full DAX Functionality: Supports all DAX functions,
including complex time intelligence.
Data Transformation: Full support for Power Query
transformations (ETL).
Independence: No load on the source system during
report viewing.
Limitations of Import Mode
Data Latency: Data is only as fresh as the last
scheduled refresh (max 8x/day for Pro, 48x/day for
Premium).
Size Limits: 1GB model limit for Pro users; 10GB-400GB
for Premium.
Refresh Time: Loading massive datasets can take hours
and consume significant resources.
Use Case: Historical Sales Analysis
Scenario
A retail company needs to analyze 5 years of historical
sales data (50M rows). The data doesn't change during
the day.
Why Import?
The dataset is static and fits in memory. Users require
complex Year-over-Year calculations and instant filtering
performance.
2. DirectQuery Mode
Connecting directly to the source without moving data.
DirectQuery Architecture
No Data Stored
Power BI stores only metadata (table names,
relationships). No actual data is loaded.
Query Translation
When a user interacts with a visual, DAX queries are
translated into SQL (or other native languages) and sent to
the underlying database.
When to Choose DirectQuery?
Real-Time Needs Big Data Governance
When users must see data When the dataset is too massive When data sovereignty policies
exactly as it is right now (e.g., to fit into memory (e.g., prevent data from leaving the
monitoring production lines). Petabytes of log data). original source.
Implications of DirectQuery
Performance Costs Functional Limits
Visual rendering depends entirely on the source Not all DAX functions are available (especially
database's speed. Network latency and complex Time Intelligence). Power Query
concurrent user load can significantly slow down transformations are limited to what can be folded
reports. into SQL.
Use Case: Stock
Monitoring
Scenario
A financial trading floor needs a dashboard monitoring
live stock prices and trade volumes.
Why DirectQuery?
Import mode's 30-minute refresh latency is
unacceptable. Traders need to see price changes the
second they happen in the database.
3. Composite Models
The best of both worlds.
What is a Composite Model?
"Allows a report to connect to data from more than one
DirectQuery source or combine DirectQuery with Import data."
Prior to this, a model had to be 100% Import or 100% DirectQuery.
The Magic of Aggregations
User Experience
Architecture
When users view high-level charts, Power BI hits the fast
You create a small Import table that holds summarized
Import table.
data (e.g., Sales by Month).
Only when they drill down to transaction level does it
You keep the massive transaction table (e.g., Individual
query the slow DirectQuery source.
Transactions) in DirectQuery.
Result: Big Data with Import-like speed.
Use Case: EDW + Local Data
Scenario
A marketing team uses a corporate Data Warehouse
(DirectQuery) but wants to combine it with targets stored in
a local Excel file.
Why Composite?
DirectQuery allows connection to the secure EDW without
moving data. Import allows loading the small Excel targets.
The Composite model seamlessly joins them.
Framework Comparison
Feature Import DirectQuery Composite
Performance Fastest (In-Memory) Slower (Depends on Source) Hybrid (Fast High-Level)
Data Freshness Scheduled Refresh Real-Time Mixed
Dataset Size Limited by RAM Virtually Unlimited Unlimited (for Detail)
Complexity Low Medium High
Query Response Time Comparison
*Conceptual representation of query speeds for aggregated queries.
Memory Footprint Comparison
DirectQuery shifts the storage burden to the source system, keeping the Power BI file tiny.
Discussion Scenario 1: Retail Chain
Recommended Solution
Framework: Import Mode
Reasoning:
1. Daily refresh implies real-time is not needed.
The Challenge
2. 20GB compresses to ~2-3GB, easily fitting
A global retailer has 10,000 stores. They want a in Premium.
dashboard for Store Managers to see daily sales 3. High concurrency kills DirectQuery
performance compared to targets. The data performance; Import handles thousands of
volume is moderate (20GB total), but user users easily.
concurrency is high (10,000 users).
Discussion Scenario 2: IoT Factory
Recommended Solution
Framework: DirectQuery
Reasoning:
1. Every second updates require real-time
connectivity.
The Challenge
2. Petabytes of data cannot physically fit in
A factory needs to monitor sensor temperature Import memory.
every second to prevent overheating. The data is 3. DirectQuery allows passing the "Average
stored in Azure Data Explorer. The volume is Temperature" query directly to the source
massive (Petabytes). database.
Questions?
Thank you for attending.
Slides and additional reading materials will be shared via the LMS.
Image Sources
[Link]
Source: [Link]
[Link]
Source: [Link]
[Link]
Source: [Link]
[Link]
Source: [Link]
[Link]
Source: [Link]
[Link]
Source: [Link]
Image Sources
[Link]
Source: [Link]
[Link]
Source: [Link]