Power BI analyze in excel not working

[Matlefo]

We have a large semantic model in a PPU workspace with a size of 40 GB. When trying to make a live connection to the dataset from excel, it sometimes works and sometimes not. The general problem is that an attempted connection or refresh from excel gets stuck with "Excel not responding". I can't seem to figure out what's causing this. At first we tried using the Power Platform - From Power BI connection which almost never worked. Then we tried connecting via Analysis services and XMLA endpoint which seems to work better but not satisfying. We've checked all the prerequisities! Have anyone had the sme issues or can explain what's causing this? is it the size of the model or something else?

[v-jialongy-msft]

Hi @Matlefo

It seems that the size of your model (40 GB) is a key factor contributing to the performance issues when connecting from Excel. Large semantic models in Power BI, especially when accessed via live connections, can put significant pressure on memory and CPU resources.

Excel may struggle with large datasets because it's not optimized for handling massive amounts of data through live connections. The "Excel not responding" error often occurs when the dataset overwhelms Excel's processing capacity.

Potential Solutions:

Consider simplifying or partitioning your dataset, if possible, or reducing the number of columns/rows returned in your Excel queries.

Use Excel's query parameters to limit the amount of data requested initially.

 

 

 

 

 

 

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Matlefo]

Hi @v-jialongy-msft ,

Thank you for your reply. Can you elaborate on Excel query parameters? Would that work on live connection against a Power BI semantic model. What I can see there is no support for global parameters than can limit the amount of data being processed.
Do you have any idea if a premium capacity licens would make any difference?

[sergej_og]

Hey @Matlefo ,

from my point of view (for experience reasons) performance depends on the model size and the connection establishment to the model took also time - but it should not affect the connection (technically) by itself.

I guess tenant settings are set for using Excel via live connection.

Do you have used the "Analyze in Excel" button or how did you connected to the model?

Regards

[Matlefo]

Hi @sergej_og,

We (several people) have tried:
- "Analyze in excel" from Service
- "From Power Platform-->From Power BI" in excel
- "From Database --> From Analysis Services" in excel
Nothing works as smoothly as one should expect. We have no problems connecting live to the dataset from Power BI desktop so this is some issue with excel. 
One thing we've noticed is that the worse part of the days trying to connect or work with the excel-file is during dataset refresh, however it's working as expected anytime of the day. I'm having trouble finding any explanation in microsoft documentation.

[lbendlin]

There is another aspect to this.  "Analyze in Excel"  uses legacy MDX and treats the Semantic Model as if it were a cube.  That adds processing overhead.  Nowadays business users tend to migrate away from that, towards running DAX queries. Not as feature rich as cube functions, but much faster.

[Matlefo]

I have read about this MDX translation but that doesn't excuse the poor documentation on this subject. Being pragmatic - does anyone know of documentation on this subject? It would be nice of them saying if there is a dataset size limit or that you should avoid refreshing excel while dataset is refreshing in service. For me this is just a black hole where nothing seems to work and I can't figure out the root cause.

[lbendlin]

 or that you should avoid refreshing excel while dataset is refreshing in service.

These things are unrelated. An ongoing dataset refresh has no impact on the Excel activities,  as the refresh is done on a copy of the dataset. Only once it is completed successfully there will be a moment where the new version is swapped in, but that is near instantaneous.