Iterating Queries in Direct Query with Large Excel Pivot Tables

[delks]

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 when working with small pivot tables however once the pivot gets to around querying 5.1million cells worth of data (some of which may be blank), the semantic model starts sending multiple queries to snowflake where it is looking at each possible combination of the specified dimensions independantly (i.e. sending a query per combination).

It starts producing queries in the following format (placeholders are written in <>):

select CAST(1 as INTEGER) as "C1", ... <NESTED QUERY FOR DIMENSION 1> WHERE <DIMENSION 1> = ? ) <NESTED QUERY FOR DIMENSION 2> WHERE <DIMENSION 2> = ? ) etc.

Can't seem to figure out what is causing this other than the reason there is too much data for Power BI/Excel to handle? Does anyone know what causes this behaviour and is there a way to avoid it?

[rohit1991]

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.

[Anonymous]

Hi @delks.,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @delks,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[Anonymous]

Hi @delks.,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[Anonymous]

Hi @delks,

Thank you for reaching out to Microsoft Fabric Community Forum.

This behavior is expected in DirectQuery mode, especially when dealing with large pivot tables in Excel. Power BI generates separate queries for each unique combination of filter values to optimize performance. However, when there are too many combinations, it results in excessive queries to Snowflake, causing performance issues.

Ensure indexes and clustering are optimized for the queried dimensions.

Use Materialized Views for pre-aggregated data to reduce query complexity.

Run Performance Analyzer to check which queries are being sent.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[lbendlin]

1. Excel sheets have a limit of 1M rows

2. Excel is NOT a Direct Query data source.

[delks]

Hi,

In response to 1, row wise the pivot table isn't larger than 1 million rows as one of the larger dimension is being presented across the top/columns.

In response to 2, apologies my wording may have sounded weird. Direct Query data source is snowflake and the front end presentation is being done within Excel Pivot Tables.