*Please note that you can click on the images to enlarge them*
I was recently working on deployment of SQL Server PowerPivot 2014 integration with SharePoint 2013.
Once the components were successfully installed and configured, I created a sample PowerPivot Report within Excel 2013 and uploaded this to the PowerPivot Gallery within SharePoint. Whilst I was able to carry out a data refresh within the local Excel client against an Analysis Services Multidimensional Cube, when it came to doing the same within Excel services, the data refresh failed with the below error:
To add complication to this, data refreshes were working from some Excel PowerPivots within Excel Services, but failed on other PowerPivot workbooks.
So what was different about these PowerPivot workbooks? We knew that some PowerPivots were being created on local client machines away from the SQL Server 2014 Analysis Services installation and others were being created within an Excel client, which was installed on the same server as Analysis Services.
Upon checking within the connection properties within Excel, I could see that on workbooks which were created on the Analysis Services server, this was using a data provider type called ‘MSOLAP.6’. Whereas on workbooks which were created away from the Analysis Services server, these were using a data provider type called ‘MSOLAP.5’.
What we know is that one of the criteria for data refreshes to successfully run within Excel Services in SharePoint, is that it needs to trust the data provider:
MSOLAP.6 is a new provider driver brought around by SQL Server 2014 and as SharePoint 2013 was released some time before this, Excel Services does not trust MSOLAP.6 by default. Any client away from the SQL Server 2014 installation will always default to the MSOLAP.5 provider, which Excel Services already trusts.
You there need to go into SharePoint Central Administration > Manage Service Applications > Excel Services > Trusted Data Providers > Add Trusted Data Provider. At this point you must then set the identifier for this data provider as ‘MSOLAP.6’ and the data provider type as ‘OLE DB’. Click on ok to apply the new trusted data provider:
Close down the PowerPivot workbook within Excel Services and then re-launch it to test the data refresh and all should be fine!
This Blog was written by James Smith, Technical Consultant at Simpson Associates. To ask James any further questions please contact him at firstname.lastname@example.orgBack to blog