I need to allow external/guest users access to an Excel document with a connection to AAS stored on SharePoint Online (SPO) using Excel for the web.
I have data stored on Azure Analysis Services (AAS). I created a connection to the AAS tabular model in Excel desktop app (M365 Apps for Enterprise [E5], V 2108, Current Channel). The connection is successful and I'm able to manipulate the Pivot Table without error from my desktop.
Next I upload/sync my Excel file with SPO. In Microsoft Edge, I open the Excel file from the SPO documents list in Excel for the web. I attempt to refresh the connection and receive a prompt:
Be careful. The query to get the data might be unsafe so you should only refresh the workbook if you trust its source. Do you want to refresh? Yes or No
I select Yes, and receive the following error message:
We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh:
asazure___centralus.asazure.windows.net_companynameanalyticsdevas adventureworks Model
Does anyone know if this configuration should work? If so, any suggestions on settings that would need to be configured or permissions that might need to be granted?
I've read a bit about Business Connectivity Services (BCS) and Secure Store Target Application or Secure Storage Service (SSS) [possibly using a service account?], but not sure if it's applicable to SPO, AAS, and Excel for web configuration.
