Excel MDX query against Power BI semantic model leads to timeout because of measure format strings

[Thias]

Recently our team came accross a probably wide know issue: Users connecting against Power BI sementic model with Excel shooting mdx queries on it experience slow performance.

In our case it was even worse: We get an error "10 GB Limit" is reached and no result came back. 

In this post i will show you our investigation process, which finally resolved the issue as we found the route cause at an area we never would have expect it. Worth to mention that the error came up suddently and did not exists for months. So there might be some issues with Power BI or Excel version, which made the error happen.

First some information: 

• Finance Model  < 2GB running on P1 capacity
• Main fact tables: Actuals (about 12 Mio records before issue come up 16 mio as it started to be a problem) & Plan data < 2 mio records
• main dimension like account, profit center, cost center etc. in the range of 2000 to 32.000 records all with excessive hierarchies

Of course we wondered ourselves as the issue came up more or less from one day to another. More and more users got the error.

• We checked for excel version changes, but that didn't resolve the issue - possibly not aiming for the right version. Also we tried at least up to date power bi versions but did not help.
• We checked the cardinality of our dimensions, which growed over time and especially excel reports leveraging one or more of the hierarchies faced the error. We were able to get cardinalty down in some of the dimension and: This helped! It doubled the speed of some mdx queries we fetched from the user reports. But it did not resolve the issue completly.
• We found that getting rid of columns and row totals in the pivot table will ommit the "Drill by member" in the mdx statement and some of the reports will at least work again. Nonetheless this also does not solve the issue for all the reports depending on their complexity.
• We recommend to our users to do large data batches with the "Insert as table" feature to leverage DAX queries in Excel. This is awesome for power users. But does not resolve the problem for standard users slicing the data in their daily reporting business.
• We partitioned the dataset and turned on "large dataset mode". This did have an impact on the compression (30%) but no impact on the MDX query performance
• We build different datasets, where we get rid of hierarchies in dimensions, get rid of a lot of columns just leave some needed for the reports, but this did not help at all. Same is true for getting rid of 1000 measures which the dataset contains. 
• We added the leaf element to the hierarchies: Did not help. 
• We just load less data at all: Did not help

Finally one of my developer collegues discovered that reports work most of the time just fine - still to be tested - if you turn off the retrival of formats from the server for the pivot table: 

I remembered that i had once the same issue with dynamic format strings. They would also corrupt you excel pivot analysis. 

Therefore i created a dataset which turns the format of all measures to "General", so no format string can be found in the TOM. 

And this really helps! The reports work. And executing the MDX queries wit SSMS omitting the "Cell Properties - Format String" in the query runs in few seconds.

As the Format strings seems to be retrieved for each cell in excel it is causing an issue approx. up from December 2024. Setting all measures to have no format string available seems to be a workaround. 
We did not test all the excel based reports yet. Excessive usage of hierarchies, the MDX "drill down member" as well as cardinality of dimension play a role. But the format string might be an issue we would have never thought of directly. Therefore i want to share this here.

Are there more people facing similar issues? 
What did helped in your case? 
I would be glad if there are people with some other ideas. I will come back after we tested setting format to general for measures with more reports.  

[lbendlin]

Keep in mind that Power BI Semantic Models are SSAS Tabular.  They do not speak MDX natively. You will get much better performance with DAX.

[Thias]

Fair point. Therefore we motivated Excel Users to use insert table or Power BI Export to Excel --> summarize. Of course we build up Power BI reports step by step. Nevertheless a lot of users will still use Excel Pivot tables in their daily business and won't use Power BI from next day on. My silent hope would be that Excel Pivot tables learn to speak DAX. 🤔

[v-bmanikante]

Hi @Thias ,

We would like to follow up to see if the solution provided by the super user @lbendlin  resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

B Manikanteswara Reddy

[Thias]

Hi, 
no the issue is not resolved. For some reasons format strings started to slow down the MDX queries Excel Pivot tables send to SSAS Tabular. The workaround to set all format strings to "General" in SSAS Tabular is only a workaround. It worked fine until end of last year, so would be happy to know why this occured suddently. 

[v-bmanikante · Accepted solution]

Hi @Thias ,

Thank you for the follow-up, and sorry to hear that you're still experiencing the issue. Here are a few workarounds that you can try (some of which you already did):

• Turn off formatting options in the connection properties in Excel (number format, fill color, font style, etc.). This has helped many users improve performance.
• Change your measure formats to "General" in the Power BI model to prevent Excel from trying to retrieve custom format strings.
• Use "Insert Table" instead of Pivot Table in Excel when possible. This uses DAX instead of MDX and performs better.
• Reduce the use of hierarchies and totals in your Pivot Tables to avoid triggering complex MDX queries.
• Consider creating a simplified version of your dataset just for Excel use—fewer dimensions, fewer measures, no hierarchies.

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,

B Manikanteswara Reddy

[v-bmanikante]

Hi @Thias ,

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,

B Manikanteswara Reddy

[Thias]

Hi, 
thank you for reaching out. As you listed the solutions i provided, feel free to accept them as solution. 

[v-bmanikante]

Hi @Thias ,

Thank you for your support and confirmation!

Glad the solutions were helpful. I've gone ahead and marked your response as the accepted solution. Appreciate you taking the time to follow up ,it really helps the community!

Best regards,

B Manikanteswara Reddy