Forum Discussion

delks's avatar
delks
Frequent Visitor
1 year ago

Iterating Queries in Direct Query with Large Excel Pivot Tables

I am creating a semantic model to be connected to Excel and to be used like a cube.   It is currently set up as a direct query connection to a snowflake instance.   Everything works as expected w...
Need Help
rohit1991's avatar
rohit1991
Super User
1 year ago

Hi delks

 

This is a common issue when using DirectQuery especially with large pivot tables in Excel. What’s happening here is that Excel fires off multiple queries behind the scenes, one for each combination of filters, rows, and columns it detects. And when that’s paired with DirectQuery, every one of those interactions gets sent straight to the source database. That’s where the slowness (or overload) usually starts.

 

Power BI’s DirectQuery mode just isn’t optimized for handling that kind of heavy interaction  it prioritizes real-time data over speed. So if your pivot table involves lots of dimensions or slicers, it’s going to generate a huge number of queries.

 

Here are few steps:

  • If possible, simplify your pivot setup  fewer slicers or flatter hierarchy levels.

  • Consider switching to Import mode instead of DirectQuery if you don’t need live data it’ll be way smoother for Excel.

  • Or if you must stick with DirectQuery, maybe create a summarized or pre-aggregated table with fewer combinations it’ll reduce the query load dramatically.