How to resolve if connection string changes throw error in your excel report?

Scenario: For example, an excel report is connected to SSAS cube in Production and it's serving business needs. However; for some reason SSAS server has been changed, hence the connection need to point to the new server. How will you change the connection with new SSAS server? 

Well, you may thinking it should be pretty straight forward, just change the connection string where SSAS server name should be replaced(as like fig 1)  then refresh all and it should be ready to use again. 

Fig 1: Changing data source from Connection string

Unfortunately it will not work for some cases or many cases. I actually did the same but ended up with error like below:

Fig 2: Error after changing connection.

Okay!! got it, then how to solve this? 

Solution: Please find the solution below:

Go to 'Analyse' tab in your excel as like Fig 3. 

Fig 3: Find Analyse tab

And then change the connection by using change data source (Fig 4):

Fig 4: Change data source

After your change the data source, hit the 'Refresh All' button , it should work now without any error.