Single Excel pivot refresh creates 2000+ queries to the PBI service

[snoomtreb]

Hi,

we have a P2 premium capacity running, serving about 500 daily users.
We have had some capacity issues, and were monitoring the queries being fired at our most used data model.

We noticed that one user had created about 2000 queries (3-4 every second) in a 10min timespan.
Upon investigation that user had a single excel pivot connected to our data model.
Upon refreshing of that pivot on my own machine, the same 2000 queries were created and the refresh would fail.
This pivot had filters on the columns, each of the 2000 queries mentioned a single member of those column filters not selected.
When moving all column filters to the 'normal' filter area of the pivot, only a single query was fired, and the pivot refreshed like normal. Respons time was around 5 seconds.

Questions:

- Did anyone else notice this issue with column level filtering ?
- Is this an (known) issue with the translation enigne DAX <> MDX not capturing this correctly?

[snoomtreb]

Hi Ibendlin, thank you for your reply!
If I understand correct, you mean create a powerbi report and then have them live-connect excel from there.
However we have moved from legacy MDX cubes to Power BI data models recently. All our users used to have thousands of pivot tables for different processes, which they now re-built on top of the PBI models replacing this.
For the most part all runs smoothly, apart from this weird case? Having them all re-build their processes for this 1 glitch (?) is not reallisitic I'm afraid. For most of them it is also not required.

[lbendlin · Accepted solution]

However we have moved from legacy MDX cubes to Power BI data models recently

If you keep using Analyze in Excel you are actually making it worse.  Now instead of accessing the OLAP cube natively via MDX you are accessing SSAS Tabular via MDX which needs to be translated into DAX (i think).  From our experience accessing SSAS Tabular (including semantic models) via DAX is much faster.

[lbendlin]

Welcome to the club.  Your Excel users have discovered cube functions, and are now running thousands of separate cubes, one in each cell of their Excel sheets.

Best remedy is to show them "Extract with Excel Live connection"  which uses DAX instead and drastically reduced the workload on your side.