Colin Phillips (Alumni)
A while back a customer wanted to combine external data sources and SharePoint together, and they thought the best way to do it was with Excel PowerPivot. Their intentions were in the right vein, but the possible path of execution didn’t actually exist. The idea being that they wanted to use a tool (Excel PowerPivot in their minds) to bring together a bunch of data sources and then store that data in a SharePoint list. As I described for them (below), this isn’t actually possible (using Excel), but it is possible using BDC. Here’s a snippet of my conversation with them in this regard.
I believe what you’re trying to accomplish is drawn out below.
It sounds like what you want is to take data out of a number of different data sources, and using Excel PowerPivot, put this data into SharePoint (the raw data) as list data in a SharePoint list.
Unfortunately what I just described is a marvellous, mythical beast that doesn’t exist. You can do a portion of what you described, but the link between exporting the data from Excel to SharePoint is a technical limitation which does not exist.
What does exist today is the following.
Within SharePoint, the only thing you can do with the data from Excel is surface that data – such as on a dashboard / web part page – in a web page in SharePoint. Here’s the key point ** SharePoint itself isn’t aware of the data, just that it’s reserved screen space for Excel services to put some kind of “Excel thing” in that space. What that is, SharePoint has no further knowledge of ** (The same is true of Office 365 and PowerBI – there’s no way that I know of to get that data out of Excel and into a SharePoint list.)
What you’re describing is the concept of BDC, which in the top image replaces Excel Services/PowerPivot.
Unfortunately, as highlighted in this blog post, BDC leaves many things to be desired, as data sources other than MS SQL can be a great challenge. At this point, this is the only supported solution (that I am aware of) to accomplish what you’re describing.