PowerPivot scheduled data refresh from a SharePoint list using OData data feed connection (PowerPivot 2012 SP1 for SharePoint 2013)


To set up PowerPivot for SharePoint scheduled data refresh for a PowerPivot workbook that has a connection to a SharePoint list in it, you do not need to use a data connection file like a List.atomsvc file (you do not need to use Export as Data Feed).

Another way that you can set up PowerPivot for SharePoint scheduled data refresh for a PowerPivot workbook that has a connection to a SharePoint list in it, is to make an OData data feed connection directly to the SharePoint list data service (and skip the List.atomsvc file altogether).

Here are some steps that I used that worked for me (using SharePoint 2013 and PowerPivot for SharePoint 2012 SP1).

 

Create a new Secure Store Service target application ID

  • make it type Group

  • add the domain group called Domain Users to the Members group for the ID

  • set the ID’s credentials to a domain account and password that for sure has permissions to access the SharePoint list that we will be refreshing from (TIP: to start with, use the domain account and password that you will be logged on with on the machine where you will be launching the Excel client application and creating the Excel workbook in the next steps).

 

 In Central Administration, edit the PowerPivot service application settings and set the PowerPivot Unattended Data Refresh Account to use the Secure Store Service target application ID created above.     

  


Create a new workbook with Excel 2013, click on the PowerPivot add-in tab at the top, then click Manage on the far left.

 

 

Then choose From Data Service > From OData Data Feed

 

 

Then type in the Data Feed Url like this:   http://YourSPservername/_vti_bin/listdata.svc    and then click Next to connect to the service and view the items that appear.

 

 

Then select your SharePoint list and click Finish then Close.

 

 

Then set up your workbook the way that you would like.

For mine, I clicked on the PivotTable button at the top, I chose Existing worksheet, selected a PivotTable field called Value so that my PivotTable had some data, clicked on the Insert tab and added a slicer.

 

 

Then set the workbook’s connection authentication setting to use the Secure Store Target Application ID created above by going to the Data menu at the top > Connections > Properties > Definition tab > Authentication Settings > select Use a stored account > and type in the name of the ID that you created above.

 

Then test to make sure that both slicers and manual data refresh (Data > Refresh All Connections) work in the Excel client application, and then save the file to the PowerPivot gallery in SharePoint and open it in the browser by clicking on it.

Make sure that slicers work in the browser. 

 

 

Make sure that manual data refresh works in the browser by clicking Data > Refresh All Connections.

 

 

 

Then manage the data refresh for the workbook by clicking on the calendar icon for the workbook when viewing it in the PowerPivot gallery.

 

 

Click Enable, click Also refresh as soon as possible, make sure that the setting below called Use the data refresh account configured by the administrator is selected, then click OK.

 

 Then manage the data refresh for the workbook again by clicking on the calendar icon for the workbook again, and then refresh the page a few times using F5 to confirm that the scheduled refresh for the workbook runs and succeeds (refresh can take up to one minute to begin).

 

Assuming that the rest of your PowerPivot for SharePoint 2013 environment is set up well, PowerPivot scheduled data refresh from a SharePoint list should work in this configuration.

 

 

 

For steps on how to set up PowerPivot for SharePoint scheduled data refresh using a data connection file like a List.atomsvc file (Export as Data Feed), see:

Excel Services - Using a SharePoint List as a data source

http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/archive/2013/07/11/excel-services-using-a-sharepoint-list-as-a-data-source.aspx

 

 


Comments (3)

  1. Ravin Singh D says:

    i have try the same method. i can able to refresh the data in excel client. But when i try to refresh the data in browser it will shown error
    We cannot locate the server to load the workbook data model . We are unable to refresh one or more data connection in this workbook . the following connection failed to refresh. "The workbook data model"

  2. Ravin Singh D says:

    ok i have resolved the problem. the problem was SQL service pack. i have installed the SP2. now it’s working

  3. Anonymous says:

    Una de las características que aporta PowerPivot con respecto a solo hacer uso de los Servicios de Excel

Skip to main content