Posts Tagged ‘Data Connection Refresh’

This week I was working on generating some reports using Excel 2013, Power Query, PowerPivot and then I wanted to publish them to SharePoint. Unfortunately our server wasn’t ready for it so I went through the process of installing PowerPivot for SharePoint (existing SharePoint) so my data connections in Excel workbooks (Data Models and PowerPivots) could be refreshed correctly.

Through the process of installing and configuring PowerPivot I encountered some problems and finally solved them all. Once again here are my founds as some keynotes and summary:


  1. Your SQL server (2012 in my case) must be at least SP1. Otherwise your DataModels when refreshing data connections could not be loaded and you’ll receive error prompt. More information
  2. A seperate instance of SQL server should be installed for PowerPivot. More information
  3. I’ve also installed Microsoft SQL Server 2012 SP1 PowerPivot for Microsoft SharePoint and followed the configuration as recommended.
  4. The service account for Excel Service Application pool should have execute permission on the Admin Content database. This could be acheived via PowerShell. Otherwise you’ll get error when loading dashboards in PowerPivot service application. Use the following PowerShell: More information

$url = “http://centraladmin“
$webApp = Get-SPWebApplication -Identity $url

  1. Also the Excel Services service account should be administrator of the PowerPivot Analysis service instance. More information

Hope this could be helpful.