How Power BI Auto Aggregations (on Databricks) Cut Query Time from 8s to 0.3s and What That Means for Your Business

8 seconds to 0.317 seconds

That’s the query performance gain we saw after enabling Power BI’s automatic aggregations on a Databricks-connected semantic model.

But this wasn’t just a speed test. It was about answering a bigger question.

Can enterprise BI teams unlock speed at scale without adding more complexity or compute cost?

In this post, we will show you how automatic aggregations worked in our Databricks environment, what the results looked like, and why this feature might deserve a permanent place in your BI playbook.

What Are Automatic Aggregations?

Automatic Aggregations in Power BI use machine learning to detect frequently accessed queries and precompute their answers in advance. Instead of reprocessing full datasets, Power BI fetches data from a highly optimized summary table. This drastically cuts down query time and resource usage.

The result? Faster insights, reduced compute load, and a smoother analytics experience for end users.

How Does Automatic Aggregation Work?

Power BI’s Automatic Aggregations combine machine learning with in-memory caching to make slow queries disappear without adding complexity. Here’s how

1. Learns Which Queries Matter Most

Power BI uses AI to monitor how users interact with reports. It learns which questions come up often, like “Sales by region” or “How did Q2 actually go?” and proactively decides to summarize those queries in advance. It is like your favourite barista who starts prepping your order the moment you walk in.

2. Builds Optimized Summary Tables (Automatically)

Once it learns what’s popular, Power BI builds mini memory banks (aggregated tables) to store fast-access answers. These are maintained automatically, and no manual setup or maintenance is required. Think of it as a diligent librarian keeping your most-requested titles always ready.

3. Prioritizes Speed with a Smart Cache

When a report runs, Power BI first checks the in-memory aggregation cache—your express lane. If it finds the answer there, it loads instantly. If not, it quietly routes the query through the main data pipeline (DirectQuery). Either way, it feels seamless to the user.

4. Continuously Optimizes in the Background

The engine doesn’t stop learning. Power BI continuously analyzes new query patterns, building and retiring aggregations as needed. Your data model essentially keeps tidying itself for top performance.

5. Turbocharges DirectQuery with Hybrid Power

Automatic Aggregations were built to enhance DirectQuery datasets, including composite models. This means you get near real-time access for detailed data and in-memory speed for frequently used summaries. It’s the best of both – freshness + speed.

6. Ridiculously Easy to Turn On

Enabling Automatic Aggregations is as simple as flipping a switch in Power BI Premium. No deep configuration or advanced setup. Just smarter reports, faster dashboards, and happier users.

How Databricks Accelerates Power BI’s Automatic Aggregations?

Pairing Power BI with Databricks brings together two high-performance engines, and the results are compelling for any organization dealing with large, complex datasets.

When Power BI connects to Databricks through DirectQuery, each dashboard visual can trigger a live query to the Lakehouse. While that ensures up-to-date data, it can also slow things down if your datasets are huge or your reports are heavily used. That’s exactly where Automatic Aggregations come in, and Databricks makes them even better.

Here’s how Databricks accelerates the process

1. Smarter Learning from Real Usage

Databricks logs every user query with rich detail, making it easier for Power BI to learn what users are asking for. That means the right aggregations get built faster, focused on the most valuable patterns, not just technical assumptions.

What is in it for you? – Your reports get faster because Power BI knows which questions matter most to your users and answers them before they are even asked.

2. Faster Aggregation Generation with Instant Compute

When Power BI decides to build pre-summarized tables, it needs to scan a lot of data. With Databricks’ Serverless SQL, that happens instantly without the need to wait for compute resources to spin up.

What is in it for you? – Your data team doesn’t need to manage infrastructure or wait for jobs to run. Aggregations get built quickly, behind the scenes.

3. Responsive Dashboards, Even at Scale

Power BI dashboards often involve multiple queries firing at once. Databricks handles this effortlessly, scaling up resources in the background to keep performance consistent even when usage spikes.

What is in it for you? – Teams across your organization can use reports simultaneously, without slowdown. Everyone sees fast, responsive dashboards, no matter how big the audience.

4. Lower Costs, Higher Impact

Because aggregations reduce the number of expensive live queries, they help cut down on backend compute usage. With Databricks’ pay-as-you-go Serverless model, you only pay for what you use.

What is in it for you? – You get enterprise-grade performance without needing to overprovision resources. Better performance, smarter spend.

Real Results – From 8s to 0.3s

We wanted to see how much of a difference Automatic Aggregations could make in Power BI with Databricks and how that performance compared to a Tableau report using a live connection to the same Databricks database.

So, we ran an experiment that mirrors the kind of analysis business teams do every day, measuring real-time performance before and after turning the feature on.

Step 1: Building a Realistic Test

Using the TPCH sample schema from Databricks’ catalog, we created a semantic model in Power BI

• Dimension tables (Customer, Nation) were set to Dual mode.
• Fact tables (Orders, LineItem) used DirectQuery to simulate live, large-scale data.

We built a simple dashboard tracking

• Order Count
• Shipment Date
• Total Discount
• Total Quantity

We also added a filter for Nation, so users could explore data interactively.

Power BI Report Snapshot

Tableau Report Snapshot

Step 2: Measuring Without Automatic Aggregations

Before enabling Automatic Aggregations, we tested the dashboard’s filter performance.

• Test: Selecting “Argentina” in the Nation slicer
  Result: ~8 seconds to load
• Measurement tools: Databricks SQL Warehouse logs and Chrome Developer Tools.

At this speed, the dashboard felt sluggish

Databricks SQL Warehouse Log

Chrome Network Trace

Step 3: Enabling Automatic Aggregations

With a single configuration change, we enabled Automatic Aggregations in Power BI

• Coverage Achieved: 98% of common queries
• Refresh schedule: Twice daily (10:30 AM and 5:00 PM)

We re-ran the same slicer interaction.

Test: Selecting “Argentina”
Result: 317 milliseconds (nearly instant).

More than 50% of queries now run in under a second. Only two training and refresh operations were needed to reach this level of optimization.

Databricks SQL Warehouse log

Chrome Network Trace

Step 4: Comparing Power BI vs. Tableau

To evaluate Power BI’s performance in context, we ran the same test using another popular BI tool, Tableau.

Nation filtered: “Egypt”

• Power BI response time: 1.80 sec

Tableau response time: 3.133 sec

Power BI not only closed the performance gap it also outran it.

Conclusion

This POC demonstrated that enabling Automatic Aggregations in Power BI can significantly enhance performance for reports utilizing DirectQuery mode on large datasets.

1. Key Findings:

• Significantly reduced response time from approximately 8 seconds to under a second (~317 ms).
• Enhanced user experience and operational efficiency when managing large-scale databases.

2. Power BI (with Automatic Aggregations) vs Tableau (with Live connection):

• Power BI achieved a response time of  1.80 seconds using Automatic Aggregations.
• Tableau recorded a response time of  3.133 seconds with a  Live Connection to the same Databricks database.
• Power BI outperformed Tableau in this scenario, demonstrating faster response times under comparable conditions.
• However, the optimal choice between Power BI and Tableau ultimately depends on the specific use case and business requirements.

Turning Performance into a Strategic Advantage

The results we saw an 8-second query dropping to under 0.3 seconds aren’t just about speed. They are about removing friction between decision-makers and decisions.

That’s the real opportunity with Power BI’s Automatic Aggregations and Databricks.

In most enterprise setups, analytics workflows are bottlenecked not by data availability, but by system latency, compute inefficiency, and dashboard complexity. By offloading repetitive query loads and intelligently pre-aggregating high-usage data patterns, Automatic Aggregations reframe what real-time reporting means without demanding new infra or model rewrites.

Databricks, with its unified approach to data engineering, warehousing, and AI, acts as the perfect foundation for this. Together, this architecture gives BI teams what they’ve been promised for years – Fast, flexible, and cost-aware insight delivery at scale.

But the bigger takeaway? This architecture simplifies what used to require tradeoffs.

Where teams once had to choose between:

• Performance vs. real-time data
• Flexibility vs. standardization
• User needs vs. engineering feasibility

They can now operate in a middle zone where ML handles the optimization automatically, and the system gets smarter as usage grows.

This moves BI out of the “report factory” mindset and into a continuous intelligence model.

What Happens Next?

These performance insights underscore the need to rethink BI strategy, considering evolving data architectures and user expectations. Traditional BI tools and approaches may no longer suffice when speed, scalability, and real-time insights are critical.

Databricks One introduces a unified platform that seamlessly integrates data engineering, analytics, and AI, enabling faster, more intelligent decision-making. Its ability to support both low-latency queries and massive-scale data processing makes it a compelling foundation for modern BI workloads.

The initial benchmarks, such as Power BI’s superior performance with Automatic Aggregations, highlight the potential of this new architecture. We will be executing a broader set of benchmarks across tools, workloads, and configurations to provide deeper insights. Results will be published in an upcoming series, offering guidance for teams looking to modernize their BI stack.

Schedule a strategy call with our data experts to see how Power BI + Databricks can supercharge your analytics, reduce query loads, and deliver real-time insights that drive business outcomes.