There is actually a quite easy way of getting list data from SharePoint foundation or server into a Excel PowerPivot model eventhough you don’t have the Export as Data feed button in the SharePoint list as stated on MSDN.
Here is an example on how it can be done…
Here is a list with store information that I want to use in order to link to sales information in PowerPivot – notice that one of the columns actually is data from business data catalog so I actually have a store key available that I can use to create a relation in my PowerPivot model.
In order to get at Data feed url – I then found this article about SharePoint Foundation REST Interface – Link – so the URL “http://myserver/_vti_bin/ListData.svc” gives me a overview of which information is available on the site.
So the list I want to include in PowerPivot can be called via the following URL
Then open Excel and the PowerPivot window – and click the Data Feed button.
If you click “Test connection” you will get an error depending on the credentials needed to connect to the server.
In order to specify credentials, click OK in the Error dialog and click on Advanced to setup the connection security.
Enter the necessary userid and password
And click “Test connection” and hopefully you get this one.
Click OK and ok in the Advanced Connection setup and then Next
If you need to filter the data you can do it here and change the Friendly name as well.
Click finish and import should start and show you the success.
When you close the dialog the PowerPivot model will contain the data from the SharePoint list and you can begin to integrate with other data sources…
And the coolest part is that via business data catalog connection to the store column I actually have the store key to link to my data from Analysis services or SQL server datamart.
So nice and Self Service BI that is so Cool !!!!!