Optimizing Power BI DirectQuery Against MS-SQL: How Far You Can Push It Before Switching to Import

DirectQuery vs Import: What Actually Changes for SQL Server

You already know the high-level difference: DirectQuery leaves the data in SQL Server; Import loads it into the VertiPaq engine. What matters for performance is how that changes query patterns.

How DirectQuery behaves

When a user:

• Filters a slicer
• Cross-filters a visual
• Drills down
• Opens a page with multiple visuals

Power BI generates SQL and sends it to the source. That means:

• More, smaller queries instead of a few big refresh-time queries
• High concurrency during peak usage (many users, many visuals)
• Query shape driven by your model (relationships, measures, filters)

With Import mode, SQL Server mostly feels:

• Scheduled refresh loads (ETL-style queries)
• Occasional user-triggered refreshes

So with DirectQuery, SQL Server is now a live OLAP backend. Your indexing, schema, and resource allocation must reflect that.

Step 1: Understand the Query Patterns Power BI Generates

Before changing indexes, capture what Power BI is actually doing.

Use Extended Events or Query Store

On SQL Server, capture:

• SELECT queries from the Power BI service / gateway
• Duration, CPU, logical reads
• Query text and parameter values

Example: a typical DirectQuery request might look like:

SELECT
    d.CalendarDate,
    SUM(f.SalesAmount) AS SalesAmount,
    SUM(f.Quantity)    AS Quantity
FROM dbo.FactSales f
JOIN dbo.DimDate d
    ON f.DateKey = d.DateKey
WHERE
    d.CalendarDate BETWEEN @P1 AND @P2
    AND f.ChannelKey = @P3
GROUP BY
    d.CalendarDate;

Patterns to look for:

• Frequent filters on the same columns (DateKey, CustomerKey, ChannelKey)
• Repeated GROUP BY columns
• Large scans on fact tables

These patterns should drive your indexing strategy.

Step 2: Indexing Strategy for DirectQuery-Friendly SQL

You’re not indexing for OLTP anymore; you’re indexing for reporting queries that aggregate and filter.

Core principles

1. Clustered index on the fact table

   • Use a surrogate key or DateKey + Identity depending on your design.
   • Aim for an ever-increasing key to avoid fragmentation.

2. Nonclustered indexes on common filter + join columns

   • Foreign keys from fact to dimensions (CustomerKey, ProductKey, DateKey)
   • High-selectivity filter columns (e.g. ChannelKey if heavily filtered)

3. Covering indexes for high-traffic queries

   • Include columns needed in SELECT and GROUP BY to avoid lookups.

Example: indexing a sales fact table

Assume a typical star schema:

CREATE TABLE dbo.FactSales
(
    SalesId        BIGINT IDENTITY(1,1) NOT NULL,
    DateKey        INT NOT NULL,
    CustomerKey    INT NOT NULL,
    ProductKey     INT NOT NULL,
    ChannelKey     INT NOT NULL,
    SalesAmount    DECIMAL(18,2) NOT NULL,
    Quantity       INT NOT NULL,
    CONSTRAINT PK_FactSales PRIMARY KEY CLUSTERED (SalesId)
);

Add nonclustered indexes tuned for DirectQuery:

-- Filter by date, aggregate by date
CREATE NONCLUSTERED INDEX IX_FactSales_DateKey
ON dbo.FactSales (DateKey)
INCLUDE (SalesAmount, Quantity, ChannelKey);

-- Filter by customer, aggregate by date and customer
CREATE NONCLUSTERED INDEX IX_FactSales_CustomerKey
ON dbo.FactSales (CustomerKey, DateKey)
INCLUDE (SalesAmount, Quantity);

-- Filter by channel
CREATE NONCLUSTERED INDEX IX_FactSales_ChannelKey
ON dbo.FactSales (ChannelKey, DateKey)
INCLUDE (SalesAmount, Quantity);

Columnstore vs rowstore for DirectQuery

For large fact tables, clustered columnstore indexes are often the best choice for analytic queries:

• Excellent compression
• Very fast scans and aggregations

Example:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON dbo.FactSales;

Trade-offs:

• Columnstore is optimal for large, read-heavy tables
• If you need many small, frequent updates, you may need to balance with rowstore or use partitioning

In many DirectQuery scenarios, a clustered columnstore on the fact table plus rowstore nonclustered indexes on key filter columns gives a good balance.

Step 3: Model Design That Doesn’t Punish SQL Server

Even with perfect indexing, a poor Power BI model can generate ugly SQL.

Keep relationships simple

• Prefer a star schema over snowflake
• Avoid many-to-many relationships where possible
• Avoid bi-directional filtering except where absolutely necessary

Each extra hop in the relationship graph becomes another join in SQL.

Push logic into SQL views

Complex DAX often translates into complex SQL. For heavy logic, consider moving it into SQL views:

• Pre-calculate derived columns (e.g. buckets, flags)
• Simplify joins and business rules in a single view

Example: a view that pre-joins and simplifies sales data:

CREATE VIEW dbo.vw_FactSalesReporting
AS
SELECT
    f.SalesId,
    d.CalendarDate,
    c.CustomerKey,
    c.CustomerSegment,
    p.ProductKey,
    p.ProductCategory,
    f.ChannelKey,
    f.SalesAmount,
    f.Quantity
FROM dbo.FactSales f
JOIN dbo.DimDate d     ON f.DateKey = d.DateKey
JOIN dbo.DimCustomer c ON f.CustomerKey = c.CustomerKey
JOIN dbo.DimProduct p  ON f.ProductKey = p.ProductKey;

Then connect Power BI to vw_FactSalesReporting instead of the raw fact table.

Be careful with DAX measures in DirectQuery

Some patterns are more expensive in DirectQuery:

• Heavy use of CALCULATE with complex filter expressions
• Repeated FILTER(ALL(...)) over large tables
• High cardinality columns in DISTINCTCOUNT

Where possible:

• Use pre-aggregated tables
• Reduce cardinality in the source (e.g. bucket values, round measures)

Step 4: Power BI Aggregations – Your Best Friend in DirectQuery

Aggregations let you keep the detail table in DirectQuery while creating Import-mode summary tables for speed.

When aggregations help

Use aggregations when:

• Most queries hit higher-level grain (daily, monthly, product category)
• Only a minority of users need row-level detail

Typical aggregation pattern

1. Create an aggregated table in SQL (or Power Query) at the grain most visuals use.
2. Load that table to Power BI in Import mode.
3. Configure Aggregation settings so Power BI knows when to use it.

Example: SQL aggregation table for daily category sales:

CREATE TABLE dbo.Agg_Sales_DailyCategory
AS
SELECT
    f.DateKey,
    p.ProductCategory,
    SUM(f.SalesAmount) AS SalesAmount,
    SUM(f.Quantity)    AS Quantity
FROM dbo.FactSales f
JOIN dbo.DimProduct p
    ON f.ProductKey = p.ProductKey
GROUP BY
    f.DateKey,
    p.ProductCategory;

In Power BI:

• Load Agg_Sales_DailyCategory in Import mode
• Keep FactSales as DirectQuery
• Map aggregations:
  • DateKey → DateKey
  • ProductCategory → ProductCategory
  • SUM(SalesAmount) → SalesAmount
  • SUM(Quantity) → Quantity

Now, visuals that only need daily category data hit the Import table; only detailed drill-throughs hit DirectQuery.

Design tips for aggregations

• Start with 1–2 aggregation tables at the grains your users actually use (e.g. daily, monthly)
• Avoid overcomplicating with many overlapping aggregations at first
• Monitor which tables are used via Power BI’s Performance Analyzer and SQL monitoring

When DirectQuery Is the Wrong Tool

There’s a point where you’re fighting the engine. Recognize it early.

Signs DirectQuery is hurting you

• User actions regularly trigger multi-second waits even after indexing and aggregations
• SQL Server shows high CPU and many concurrent long-running queries during report usage
• You need complex DAX that DirectQuery translates into poor SQL
• Business users complain that slicers feel sluggish or visuals time out

When Import mode is a better fit

Switch (or partially switch) to Import when:

• Data volume fits into memory with reasonable compression
• Latency requirements are minutes or hours, not seconds
• You can schedule refreshes around business usage

Hybrid patterns work well:

• Keep large historical tables in Import
• Use DirectQuery only for a small, hot slice of recent data

Example architecture:

• FactSales_History (Import, up to last month)
• FactSales_Recent (DirectQuery, current month)
• A unioned table in Power BI for measures, with logic to route queries to the right slice

Practical Checklist: What to Try Before Giving Up on DirectQuery

When a DirectQuery report is slow, work through this list:

1. Capture real queries

   • Use Query Store / Extended Events
   • Identify the top slow queries by duration and reads

2. Index for those queries

   • Add or adjust nonclustered indexes on filter and join columns
   • Consider a clustered columnstore for large fact tables

3. Simplify the model

   • Move to a star schema
   • Remove unnecessary relationships and bi-directional filters

4. Push heavy logic to SQL

   • Use views to pre-join and pre-calculate
   • Ensure views are index-friendly and don’t hide predicates

5. Add aggregations

   • Build 1–2 Import-mode aggregation tables at common grains
   • Configure them properly in Power BI

6. Re-evaluate the mode choice

   • If performance is still poor and latency requirements allow, plan a move to Import or a hybrid design

One Takeaway You Can Apply This Week

Pick your slowest DirectQuery report, capture its top three SQL queries, and create one targeted nonclustered index plus one Import-mode aggregation table that matches those query patterns. In many cases, that single round of tuning is enough to turn a painful report into something users are happy to work with—and it will tell you quickly whether DirectQuery still has headroom, or whether it’s time to plan a move to Import.