afedeli on Wed, 23 Sep 2015 14:42:37
I am beginner about PowerPivot for SharePoint, so could you please help me about this problem: I have published an Excel file that retrieves data throught a PowerPivot cube connected to SQL Server database (the same used to configure SharePoint), but every time that I try to refresh data (DATA-->Refresh all connections), I get the folowing error:
External Data refresh Failed.
The workbook was created in an older versione od Excel and PowerPivot and cannot be refreshed until the file is upgraded.
We cannot locate a server to load the workbook Data Model.
We were unableto refresh one or more data connections in this workbook. The following connection connections failed to refresh: PowerPivot Data1.
"PowerPivot Data1" is the connection used to connect SQL Server database. It may be useful for you to know that data refreshing works fine both if I connect SQL Server directly from Excel or if I use Analisis Sercices cube.
Thanks all in advanced for your precious help
Charlie Liao on Fri, 25 Sep 2015 06:35:45
According to your description, you are experiencing "The Workbook was created in an older version of Excel and Power Pivot cannot be refreshed until the file is upgraded" issue when scheduling data refresh, right?
In your scenario, which version of PowerPivot are you using? Interactive data refresh is only available for workbooks that created in Excel 2013. If you try to refresh an Excel 2010 workbook, Excel Services displays an error message similar to “Power Pivot Operation Failed: The Workbook was created in an older version of Excel and Power Pivot cannot be refreshed until the file is upgraded”.
Please refer to the link below which explains the user experience of workbooks created in previous Power Pivot environments and how to upgrade Power Pivot workbooks so that you can take advantage of new features introduced in this release.
afedeli on Fri, 25 Sep 2015 13:30:08
thanks for your suggestions.
I am using SharePoint 2013, SQL Server 2012 and Excel 2010. According to your replay, is it normal that throught SSAS and with a direct connection to SQL Server this version problem does not appear?
afedeli on Fri, 25 Sep 2015 15:58:37
Just to better clarify: I have SharePoint 2013, SQL Server 2012 and now Excel 2013.
What I need is being able to browse data throught Excel file published to SharePoint. If I have well understood, this action can be perfomed in 2 ways: build a cube in SSAS, connecting Excel to it or build a PowerPivot cube connecting directly to SQL Server database: is it correct?
Actually, I work fine in the first case, while I am facing problems with the second case...that is the one required by business users.
thank you in advance for your collaboration
Pat Wheeler on Tue, 24 Nov 2015 00:29:43
You are correct that these are two mechanisms through which you can achieve the desired results. The one thing to be careful with here is that scheduled data relies on saving credentials to be used later. Your desire though is for interactive refresh. Interactive refresh requires Claims to Windows Token Service to pass the User's Token to the AS server (which is now on a separate machine in 2013) and then Kerberos Constrained Delegation needs to be enabled to allow this token to be passed on to the data source.
Edit: This answer assumes your latest comment that you are using Excel 2013 and SharePoint 2013 only.