Manage your PowerPivot datafeeds in a SharePoint datafeed library

By | October 6, 2010

Now that PowerPivot is getting more and more known the IT departments are getting used to the idea they will need to publish data feeds to end users. But how can we do this ? How can we make one central location for this ?

In this blog post we are going to create a data feed on top of my SSAS cube and publish this feed to a central datafeed library.

I want to create a feed of stores we have, so I open up Report builder 3.0 and create a report where I include all data I think my users need to have on stores. Don’t forget to name your tablix right, in this case I named it Stores. This name will be used inside PowerPivot to name your table.

Tip: Don’t forget to select “include empty cells” when your data doesn’t include a measure, otherwise it won’t return rows in your report:

Now save this to your Reporting services 2008 R2 reportserver and open it:

Now click the export to data feed button and save the ODATA feed file to your hard drive, opening the file would load the data straight into PowerPivot:

(yes i use IE9).

Now go to SharePoint and create a data feed library, one of the default library’s you can choose from (screenshot is in dutch):

Now you can upload the ODATA feed file we just created to the library:

Now users can click on this feed to import the data into PowerPivot.

You can also add other data feeds to this library, for example from azure of dallas, in this case I want to add a Dallas datafeed to my library. I go to Dallas and select the datafeed I want to create:

And again save the OData feed to your desktop and upload it to the library again:

This way you can create a data feed library for users in your organisation, as extra you can use the SharePoint security to add security to feeds as well.