SSAS: Excel for Microsoft 365: Initialization of the data source failed

Question

Hello, I have seen various posts on this matter over the months/years, but do not see a solution that seems applicable.

We have numerous excel workbooks created over the years and are currently running Excel 365. Users have begun to report the "Initialization" of data source failed error in the last few months on-and-off. I have also received the error periodically. The work around we've been using is to close the actual workbook throwing the error. Open a new excel workbook and connect to the SSAS server. Close the "work-around" workbook and then re-open the original workbook and the SSAS connections work again.

The connections themselves are "embedded" within the workbook and are not using any file based odc connection to my knowledge. as the connection file shown just happens to be whomever made the initial workbook that is being distributed across many potential users. Needed component drivers should be installed as again this error is inconsistent and the work around seems to indicate something that excel is not recognizing as existing upon initial launch of the workbook using olap connections. The connections themselves would be created using the basic Wizard of Data->Get Data->From Database->From Analysis Services and using Windows Authentication.

Any suggestions on this error and permanently resolving. While the workaround seems to "work", our users continue to lose their faith in the stability of our SSAS environment because of this situations.

Example of connection properties below.

Answer 1

Hi @Bunkers, Darin ,

Welcome to Microsoft Q&A!

I suspect that this error is not connection issue because sometime you still can connect to Excel. You need to check antivirus or firewalls on client side have blocked the connection or not. Default instance listens to TCP port 2383. If the client does not use a port number or an instance name, and it is TCP connection, the client will try reach TCP port 2383. Make sure this port is open in the firewall. The best way to test if this port is open is to use telnet. In addition, have you check msmdsrv.log or using SQL Server Profiler to monitor servers?

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Haven't yet had time to look for and at logs. We've been looking at steps to reproduce the error consistently and seems more related to excel sheets being stored on a synced QUAD OneDrive MS environment on cloud. QUAD had recently started automatically syncing most local pc files for users to their OneDrive on cloud. Timing of this environment change seems to coincide when this error periodically started to occur. When XLS is then downloaded locally to be run it appears as of the something that might be required for the OLAP connections doesn't come "with"? When we open a new XLS sheet locally and make a new connection (same OLAP server obviously) then maybe connection/drivers are loaded correctly or a security policy is disabled? Unsure yet exactly obviously. We will make time to look at logs (although quick google of their location on server hosting SSAS didn't seem lead to locating)

Does this potential OneDrive integration seem plausible in anybody's experience.
Thanks
Darin

Answer 3

Hi @Bunkers, Darin , thanks for your replying. Based on my experience, there is no issues with SSAS because you still can analysis in Excel. I suggest that you could post this question with the other related tags, such as office-onedrive-client-itpro, in Microsoft Q&A Forum that can help you easier to find the root cause. You could find all products that supported on Q&A forum from this link. Waiting for your good news.