In Power BI, every dataset table is connected to its source in one of two primary modes — Import or DirectQuery — and the choice between them determines data freshness characteristics, query performance, feature availability, and source system dependency. Import loads a copy of the source data into Power BI’s VertiPaq in-memory columnar engine; queries execute against this in-memory copy at very high speed, with full DAX capability and no source system dependency at query time. DirectQuery sends a native query (SQL for relational sources) to the source system for every visual interaction; data is always current to the source’s state, but every user interaction produces a source query, and performance is bounded by the source system’s query speed and load capacity.
How It Works
| Characteristic | Import Mode | DirectQuery Mode |
|---|---|---|
| Data currency | As of last scheduled refresh | Live — current to source at query time |
| Query performance | Very fast — in-memory engine | Bounded by source system performance |
| Dataset size limit | Model size limits apply (capacity-dependent) | No dataset size limit; source governs |
| DAX capability | Full DAX available | Significant DAX restrictions apply |
| Source system load | Only during scheduled refresh | Continuous — every user interaction |
| Calculated tables | Supported | Not supported |
| Composite models | Available | Available (mixed mode) |
| Best for | Most finance BI analytical workloads | Very large datasets; real-time monitoring; regulatory compliance where data must be sourced live |
A third pattern — Composite Model — allows Import and DirectQuery tables in the same dataset. The aggregate or dimension tables are imported (fast, fully DAX-capable), while the large fact table remains in DirectQuery for size or freshness reasons. Aggregation tables can serve interactive query performance, with DirectQuery as the fallback for drill-through to transaction-level detail.
Design Decisions and Trade-offs
For finance BI in Oracle EBS, SAP, or similar environments, DirectQuery places every report interaction’s query load on the ERP database — a database already under significant transactional load from operational users. A finance dashboard used by 50 concurrent users each interacting with five visuals simultaneously generates 250 source queries, all executing on the same ERP database that is processing payroll, journal entries, and AP approvals. This is rarely an acceptable production state. Import mode, with a scheduled refresh that updates data on an agreed cadence, separates the analytical query load from the transactional source system entirely.
Common Implementation Errors
The specific DirectQuery implementation error with the most damaging production consequences is deploying a DirectQuery dataset against a production ERP database without a query throttling or resource governor configuration on the source. When report consumers interact with the dashboard simultaneously during peak hours, the uncontrolled analytical query load competes with operational ERP users for database resources — degrading ERP performance for the finance team doing the close, the AP team processing invoices, and every other operational user. Source database resource governance (Oracle Resource Manager plans, SQL Server Resource Governor workload groups) that limits the analytical query pool to a defined CPU and memory percentage is a production prerequisite for DirectQuery against operational source systems.
How Loop Wise Solutions Designs for This
We recommend Import mode as the default for finance BI analytical datasets connecting to ERP sources, with DirectQuery reserved for specific use cases — real-time operational monitoring, regulatory reporting where data currency is mandated, or datasets too large for Import with the available capacity. Where DirectQuery is required, we design the source database resource governance configuration alongside the Power BI model, and we stress-test the source system query load under simulated concurrent user sessions before go-live.
