How to refresh SharePoint 2013 site collection with Powerview reports from one farm to a different farm

When you refresh SharePoint 2013 site collection with Powerview reports from one farm to a different farm, you will get this error "An error occurred while loading the model for the item or data source 'EntityDataSource'" on Powerview reports. I have described the details how to reproduce this issue from previous blog.

Here is the original site with one Powerview file and one report named "Report1". 

The error when click the Report1 after refreshed to another SharePoint 2013 farm is listed as below.

The ULS log display the following error message.

0x310C  SQL Server Reporting Services   Report Server Processing          00000    Unexpected       Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'EntityDataSource'. ---> System.Net.WebException: The remote server returned an error: (401) Unauthorized.     at System.Net.HttpWebRequest.GetResponse()     at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetHttpResponse(String url, String soapAction, String user, String password)     at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenHttpConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)     at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)     at Microsoft.AnalysisServices.AdomdClie...   bac7a59c-ef58-a07c-043a-91f83e92f4c7

07/18/2014 09:29:45.29*               w3wp.exe (0x1D68)                       0x310C  SQL Server Reporting Services   Report Server Processing            00000    Unexpected       ...nt.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)     at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)     at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)     at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()     at Microsoft.ReportingServices.DataExtensions.AdoMdConnectionBase.InternalOpen()     at Microsoft.ReportingServices.Diagnostics.DataExtensionConnectionBase.OpenConnection(IProcessingDataSource dataSourceObj, DataSourceInfo dataSourceInfo, IDbConnection conn)     --- End of inner exception stack trace ---;               

The root cause we found is the Powerview reports will have a connection to original Powerview file and the connections will still point to original SharePoint 2013 farm URL. That is the reason we have "Unauthisrized" error from ULS log and we have connection error from UI.

The workaround to resolve all the Powerview reports issue is to reconfigure the data source connection to the new SharePoint source file URL as described below.

1. Change PowerPivot Gallery library view by click LIBRARY->Change "Current View" from "Gallery" to "All Documents"
2. Select the Powerview Reports migrated and click "Manage Data Sources". Then click "EntityDataSource"

3. Modify the "Connection string" from original URL to the current migrated SharePoint farm URL.

Original site was on spsbx15 and connection string is:
Data Source="http://spsbx15/sites/Harry/PowerView/HelloWorldPicnicPowerViewRTM-2010.xlsx";

Site migrated to spsbx08 and connection string changed to:
Data Source="http://spsbx08/sites/Harry/PowerView/HelloWorldPicnicPowerViewRTM-2010.xlsx";
 

Now if you click the "Report1" the report will be generated without any issue!

The question become if we refresh the whole SharePoint farm to a different one, how we identify ALL the Powerview reports and fix the connection string using automatic way. We are working with Microsoft now to finalize the automation.