Excel for Microsoft 365: Initialization of the data source failed

Question

Hi,
we use Excel for Microsoft 365.
The data in the Excel workbook are stored in an external data source (OLAP cube).
The Excel workbook was created with Excel 2010.
We want to perform a refresh operation to retrieve the updated data (with our current Excel version 365).
The connection information is stored in the workbook. We don’t use the connection file.
Some user can refresh the data. But others get the following error message:
„Initialization of the data source failed. Check the database server or contact your database administrator. Make sure the external database is still available, and then try the operation again….

All users have used the same Excel workbook for testing the refresh.
We have checked the connection by using the Connection Properties dialog box.
The data connection information that is stored in the Excel workbook contains the following:
„Provider=MSOLAP.4;…“.
In case of a correct refresh of the data the data connection information were also modified automatically (without user activity).
The provider was automatically modified to: Provider=MSOLAP.8.
If the refresh doesn’t work, there is no update concerning the data connection information (Provider=MSOLAP.4).
The MSOLAP provider version MSOLAP.8 is the correct version, because our Analysis Services version = SQL Server 2017.

Users with the above-mentioned problem can refresh data in case that the Excel workbook was created with Excel version greater than 2010.

What‘s the cause of the error?

I’m looking forward to your response.
Thanks.

Kathrin

Answer 1

Hi @Leipold Kathrin ,
How did you create the connection in Excel? Did you use Power Pivot or Power Query to create the connection?

I suggest you check if you use 32-bit or 64-bit version of Excel 365, try to uninstall the existed one and re-install another.

Try it and check the result.

---

If the response 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

MSOLAP provider is basically a database driver needed on the client side. Apparently, the computers where users are unable to refresh the connection do not have it installed, although it is supposed to be installed as a part of Excel installation. See https://learn.microsoft.com/en-us/analysis-services/client-libraries for details and download links.

Answer 3

Hi,
I want to complete what I said in my first post. It`s a result of a further test.
By changing the provider version manually within the data connection information the refresh of data also works properly.
My question is:
What’s the reason why some users have to change the connection string manually and other users need no manual activity because there is an automatic update of the connection string? I think of special settings, authorisation, registry keys, ...

Thanks.
Kathrin

Answer 4

Thank you for your answers.

I want to add how we created the connection to cube in Excel.
We used the Data Connection Wizard.

In Excel:

• Data tab
• Then „From Other Sources“ (group: Get external data)
• Then „From Analysis Services“ to open the Data Connection Wizard
• In the Data Connection Wizard: „Server name“ and login option „Use Windows Authentication“
• The next window was the selection of database/cube which contains the data --> selection of the requested database/cube