Analyze in Excel fails

[ToddChitt]

I am an Admin in a simple Fabric Workspace with a Lakehouse. The Lakehouse has one table. I click the elipsis next to the Semantic Model for it and select "Analyze in Excel". It creates an ODC file locally. I open that file in Excel and get error messages like this: 

I am also a Fabric Admin and can verify that in the Admin Portal, the option for :Users can work with semantic models in Excel using a live connection" is ENABLED for the entire organization. 

Also, in Excel setting I have added the file location as a Trused Location, and bypassed just about all trust settings.

Any ideas?

Thanks in advance.

[lbendlin · Accepted solution]

Please remember that "Analyze in Excel"  uses MDX queries. That is not very efficient.  Instead, consider running DAX queries against the semantic model.

[v-sgandrathi · Accepted solution]

Hi @ToddChitt,
Thank you for being a part of the Microsoft Fabric Community.

As  @lbendlin mentioned Analyze in Excel does indeed use MDX queries, which can be less efficient compared to DAX queries. Here are a few additional points to consider:
Make sure that your Excel application is up to date and ensure that the Power BI add-in for Excel is installed and enabled. Once try clearing the cache in Excel.

Here’s a link to official Microsoft resources once refer this:
Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn

I f you have any more questions, please clarify in a follow-up reply. If this post clears your doubt, please give us Kudos and consider marking Accepting it as a solution to guide other members in finding it more easily.

Regards,
Sahasra.

[ToddChitt]

@v-sgandrathi

While I cannot prove it, I suspect the issue was due to the fact that the Semantic Model did not have tables, or had not been processed after loading a simple table from CSV file in the Lakehouse. Just a bit frustrated that Microsoft throws an error of "Forbidden" and nothing else in this situation.

 @lbendlin  Thanks for the reminder, but the language or protocol for querying the semantic model in Excel doesn't come into play UNTIL you can actually connect. This client is heavily into Excel, and this was a desired approach.

Regards, and keep up the good work.

[lbendlin]

fye: Why DAX is better than MDX for bulk extracts of data from Power BI

[v-sgandrathi · Accepted solution]

Hi @ToddChitt,

You're likely correct that the issue stemmed from the semantic model not having any tables or not being processed after loading the table from the CSV file.
your insight about the unprocessed model will likely help others encountering the same issue. If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more easily.

Thanks for using Microsoft Fabric Community Forum.

[v-sgandrathi · Accepted solution]

Hi @ToddChitt,
Thank you for being a part of the Microsoft Fabric Community.

As  @lbendlin mentioned Analyze in Excel does indeed use MDX queries, which can be less efficient compared to DAX queries. Here are a few additional points to consider:
Make sure that your Excel application is up to date and ensure that the Power BI add-in for Excel is installed and enabled. Once try clearing the cache in Excel.

Here’s a link to official Microsoft resources once refer this:
Create Excel workbooks with refreshable Power BI data - Power BI | Microsoft Learn

I f you have any more questions, please clarify in a follow-up reply. If this post clears your doubt, please give us Kudos and consider marking Accepting it as a solution to guide other members in finding it more easily.

Regards,
Sahasra.

[v-sgandrathi]

Hi @ToddChitt,

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.

Thankyou for being a Microsoft Community Member!

[v-sgandrathi]

Hi @ToddChitt,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and select "Yes" if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a part of the Microsoft Fabric Community!

[v-sgandrathi]

Hi @ToddChitt,

I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question

Thank you for being a part of the Microsoft Fabric Community.

[lbendlin · Accepted solution]

Please remember that "Analyze in Excel"  uses MDX queries. That is not very efficient.  Instead, consider running DAX queries against the semantic model.