XMLA connection is randomly failing

[adetogni]

Hi

I've been using XMLA connector to my models in the past years, but I rarely released to final users.

I generally use it for myself and a selected list of power users. Now I'm going to release to a major audience but it is sending shivers down to my spine due to its instability. I created a simple excel and provided to one of my clients to refresh the data. Yesterday the data was wrong so I opened the Excel and one number was weird. The date selected was 1 to 6 July so I checked the source data and I realized that it was querying 1-5. So I changed the date filter but nothing happens. I press refresh and nothing happens (and it took A LOT to refresh the data). It wasn't throwing an error, but the data for that specific measure was not up to date.

So I created a new pivot in a different sheet...and this new pivot had correct data (and magically even the original pivot had correct data too!).

This is very worrying me because if I cannot trust that XMLA works 100% fine I cannot release to the client. Has it ever happened to you? What is the way to debug this? Everything happens under the hood.

The other weird thing that I don't get is why when the session ends and you try to refresh you get that very weird error as a messagebox in Excel instead of a more "user friendly" message...but whatever.

(Disclaimer: I am a pro developer, working with complex DAX models and queries since 10 years)

[GilbertQ]

Hi @adetogni 

I have never had any issues with the XMLA endpoint when using Excel or querying directly. 

If the semantic model is in Import mode it should work as epxected. It could be possible that Excel was doing some caching?

[v-sshirivolu]

Hi @adetogni 
Thanks for reaching out to Fabrics community forum.

Working with Excel and the XMLA endpoint can present challenges, particularly when expanding to a larger group of users. Here are several important considerations -

Stale Data and Pivot Table Inconsistencies
Excel’s caching and session context may result in outdated or incomplete data, especially in workbooks that remain open for extended periods. Creating a new pivot table or reconnecting can prompt a fresh data evaluation.
Microsoft Docs – Analyze in Excel: Troubleshooting

Data Refresh Settings
Verify that options such as "Refresh data when opening the file" are enabled and background refresh is disabled, as Excel may otherwise delay or skip updates.
Microsoft Docs – Refresh Data in Power BI
XMLA Error Messages
The XMLA endpoint provides raw engine messages, and Excel does not always present these in a user-friendly format. While this is expected, it may not be ideal for end users.
Microsoft Docs – XMLA read/write support in Power BI

Best Practices for Wider Deployment
For production environments, it is advisable to use Analyze in Excel instead of manual XMLA configuration, as it offers improved support for users.
Microsoft Docs – Use Analyze in Excel

Debugging Query Logic
Debugging Query LogicTo better understand what queries are being executed or how measures are being evaluated, reviewing DAX query structure and syntax can be useful.
Microsoft Docs – DAX Queries

For large-scale deployments, it is recommended to use controlled Power BI reports or standardized Excel templates with defined refresh procedures.

Regards,
Sreeteja

[adetogni]

hi @v-sshirivolu thank you

First of all the model is import mode and we're not on Premium capacity.

---

@v-sshirivolu wrote:

Stale Data and Pivot Table Inconsistencies
Excel’s caching and session context may result in outdated or incomplete data, especially in workbooks that remain open for extended periods. Creating a new pivot table or reconnecting can prompt a fresh data evaluation.

 This is an issue. I cannot force the way the user use the Excel asking them to close it from time to time. Creating a new pivot is not a solution as the pivot is getting the data which is then used with GETPIVOTDATA in other parts of the report. So if I re-create the pivot, all my links will need to be rewritted. Is there a way to clean the cache?Microsoft Docs – Analyze in Excel: Troubleshooting

Data Refresh Settings
Verify that options such as "Refresh data when opening the file" are enabled and background refresh is disabled, as Excel may otherwise delay or skip updates.
Microsoft Docs – Refresh Data in Power BI

---

I will try to enable Refresh data when opening the file. Regarding background refresh, it is disabled.

---

XMLA Error Messages
The XMLA endpoint provides raw engine messages, and Excel does not always present these in a user-friendly format. While this is expected, it may not be ideal for end users.
Microsoft Docs – XMLA read/write support in Power BI

THis page does not provide help in finding the raw error messages, it's the XMLA r/w support

Best Practices for Wider Deployment
For production environments, it is advisable to use Analyze in Excel instead of manual XMLA configuration, as it offers improved support for users.
Microsoft Docs – Use Analyze in Excel

---

I am using Analyze in Excel meaning that I ask user, from Excel, to go to Insert Pivot-> From PowerBI (or, if the command is missing sometimes, go to the online report and do Export->Analyze in Excel). Isn't it the same?

For large-scale deployments, it is recommended to use controlled Power BI reports or standardized Excel templates with defined refresh procedures.

Regards,
Sreeteja

---

In my scenario, I will provide selected power user access to the data, if I build the report for them I won't realize a self-service report.

So in a nutshell the issue seems the ability to clear this cache in Excel. How can we do it?

[v-sshirivolu]

Hi @adetogni ,

Excel tends to cache data, and recreating a pivot table when using GETPIVOTDATA can disrupt all dependent links. Unfortunately, Excel does not provide a direct option to clear the cache as browsers do.

Here are some practical solutions you can consider:

• Enable "Refresh data when opening the file" to ensure updated data is loaded each time.
• Disable background refresh to prevent delays or incomplete updates.
• Limit the use of GETPIVOTDATA as it creates strong dependencies on the pivot structure. Alternatives such as cube functions (CUBEVALUE, etc.) or Power Query may offer greater flexibility.
• Use “Export > Analyze in Excel” as it provides a more stable connection compared to manually inserting a pivot from Power BI.

While Excel does not have a dedicated "clear cache" feature, applying these steps can help maintain more consistent and reliable data.

[adetogni]

hi v-sshruvolu

the admission that the relationship Excel-pbi model is fundamentally unreliable is a strong one. If a final use cannot be sure that the data is correct - regardless of the cache - it's an admission that is a broken and very dangerous product. Not becuse it's not working, but because it might work or not but you, as a user, don't know it.

I will test the CUBEVALUE functions, I never used them.

Can you tell me what is the difference between inserting a pivot from excel and using Analyze in Excel from pbi model?

[v-sshirivolu]

Hi @adetogni 
The problem isn’t that Excel with Power BI is malfunctioning, but rather that Excel’s background caching can sometimes display outdated data without notice. To address your question: both “Insert Pivot from Power BI Dataset” in Excel and “Analyze in Excel” from the Power BI service use the same live connection. The main difference is that Analyze in Excel provides a pre-connected file, which is safer and more reliable for sharing. Your idea to use CUBEVALUE is a good one ,it doesn’t rely on the pivot table’s layout, so your formulas are more stable if the pivot changes. It also gives you better control over filters and data selection. Although there’s no official method to clear Excel’s cache, using CUBEVALUE and setting up proper refresh options can help improve your report’s reliability.

[adetogni]

Thank you.

The issue however is that this is not user friendly. I told my (luckily first) users to just use Insert->From PowerBI which now you're saying it's not a reliable way due to cache. 
I followed a tutorial online doing OLAP Tools->Convert to Formulas and
- first of all, throws a message that "formulas are defined in Excel and will not be converted" which is not true
- all of the metrics disappeared (only dimensions are left)

- even if it worked, makes the pivot not dynamic anymore

[v-sshirivolu]

Hi @adetogni 

Thank you for your thorough and insightful follow-up. Your careful analysis of these behaviors is greatly appreciated and highlights key differences between the technical capability and the end-user experience when using Excel’s connection to Power BI.

Although both Insert -- From Power BI and Analyze in Excel utilize the XMLA endpoint, issues such as caching, refresh processes, and limited transparency into Excel’s internal operations can create unpredictability, as you have observed. This unpredictability, particularly when combined with GETPIVOTDATA dependencies and the absence of a comprehensive cache clear/reset option can impact users’ trust in the platform.

Regarding OLAP Tools -- Convert to Formulas, your point is valid: when measures are highly customized or cannot be auto-converted, Excel may omit them, resulting in reduced interactivity and making this feature less practical for many reports.

We fully recognize your concern that, without reliable data accuracy and a transparent cache state, the integrity of self-service BI can be compromised.

We encourage you to submit this suggestion as a product improvement request on the Power BI Ideas forum:

Fabric Ideas - Microsoft Fabric Community

Thank you
Sreeteja
Community Support Team.

[v-sshirivolu]

Hi @adetogni ,

Could you please confirm if you've submitted this as an idea in the Ideas Forum? If so, sharing the link here would be helpful for other community members who may have similar feedback.

Thank you for your time.

[v-sshirivolu]

Hi @adetogni ,
Could you confirm whether you have submitted this as an idea in the Ideas Forum? If you have, please share the link here so that other community members with similar feedback can access it. Thank you for your assistance.

[adetogni · Accepted solution]

HI @v-sshirivolu sorry I was on holiday for a week. You perfectly understood my point. Having such a feature tightly connected with technical limitations which are not visible and manageable by the final users, makes the "product" not working because it cannot be trusted.

https://community.fabric.microsoft.com/t5/Fabric-Ideas/XMLA-Self-Service-BI-half-baked-unreliable-an...

Please however note that this is not an *idea*. I'm not suggesting a product improvement, it's a product breaking issue. Should be treated as a bug, not as a feature.

[v-sshirivolu]

Hi @adetogni ,
Thank you for sharing your idea in the Ideas forum.

We appreciate your contribution. Please note that Microsoft regularly reviews ideas shared by the community, and your suggestion may be considered for future updates.

Thank you once again, and we encourage you to continue engaging with the Microsoft Fabric Community.

Best regards,
Sreeteja