Looking for a way to change the connection in an existing spreadsheet from old SSAS server to the new SSAS server without losing the data in the spreadsheet. Hardware refresh has made this change necessary. We use Excel 2010. Nothing I have tried so far has worked. I created a new connection file (.odc) however, I can't seem to get the existing spreadsheet to connect using it or save with the new connection. Any help would be greatly appreciated.
Sql-server – Question regarding Excel connection to SSAS cube
excelsql serverssas
Related Question
- Sql-server – Query SQL Server from Excel with parameters linked to cells
- Sql-server – A connection cannot be made. Ensure that the server is running
- Mysql – Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL
- SSAS Model Refresh – Not enough memory to complete this operation Error
- Sql-server – Windows 10, SQLServer Express (2019), Excel 2016 import to SQLServer error Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
Best Answer
Easiest fix would be to have your networking folks point DNS for your old server name to your new server's IP address. Unless you rely on Kerberos that will likely work fine.
If that's not an option, then if you left click on the PivotTable, to the Data tab in the ribbon, then click Properties under the Connections section, then go to the Definition tab, please see if "Always use connection file" is checked. If it is, then you can just manually edit the .odc file in Notepad and point to the new server.
If "Always use connection file" isn't checked, then you can left click on the PivotTable, then go to the PivotTable Tools... Analyze tab in the ribbon, then click the Change Data Source button, then click Choose Connection, then choose a different .odc file to connect to. Your PivotTables should be preserved and they should now connect to your new server.