When creating a pivot table against the OLAP cubes in Excel the following error is encountered:
Complete text of error message is:
The pivot table could not be inserted for the following reason:
Initialization of the data source failed.
Check the database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database.
This error typically occurs when there is a mismatch of the MSOLAP driver on the machine. One cause could be certain upgraded SQL components (such as SQL Server Management Studio) that have overwritten the default MSOLAP provider in the system's registry while the driver itself is not present on the machine.
To resolve the issue, the appropriate "SQL OLE DB Analysis Services" provider should be downloaded and installed from the Microsoft website.