Updated 28-Oct-2010: Some folks are reporting that an iisreset is needed to make the updates visible to SharePoint.
So . . . here I am playing around with PowerPivot to get ready for a demo. As this is a group from the SharePoint dev team (actually SharePoint Online), I wanted to include some cool SharePoint functionality into the demo. Trying to be cool and ‘wow’ them, I decide to use a SharePoint list as a data source. I want to show the data mashup capabilities of PowerPivot so I have the bulk of my demo come from the Contoso sample database (the three product catalog tables) and the 4 million row Sales Fact table.
I create my SP list by extracting the 11 distinct manufacturers – and I assign them to a ‘shipper’ that I made up.
When I go to SharePoint an click on:
PowerPivot comes up but it returns an error:
"For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method."
So it turns out that I forgot (it happens to the best of us) to install the ADO.NET Data Services 3.5 SP1, located here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=3e102d74-37bf-4c1e-9da6-5175644fe22d
Which I would have known if I had taken my own advice and read BOL beforehand. Read the “New server” installation notes here: http://msdn.microsoft.com/en-us/library/ee210708.aspx#nextsteps
So the moral of the story is: RTFM
After installing ADO.NET Data Services on my SharePoint site, I was able to bring up the data feed in PowerPivot – setup the relationship between the Manufacturer field in the SharePoint list and the Manufacturer field in the Contoso database and I was off and running . . . I also setup daily data refresh job . . . and I could see the Gallery snapshot changing as I entered new data into the SharePoint list . . WOW!
Enjoy!
Funny. Just ran into the same issue. Thanks for the blog posting! I wasn’t around when the server was built, so assumed everything was installed. Just had to wait for the server to reboot after the install and now I am in business.
Thank you Dave, as ususal you saved us a bunch of time troubleshooting this issue.
Hi Dave – I haven’t found any PowerPivot blogs or content that discuss what happens to the PowerPivot reports when a new Column is added to a SharePoint list…
I used your whitepaper to connect PowerPivot to a SharePoint List – that worked like a charm
i created a nice PivotTable 4 Charts report with some slicers, it looks sharp!
but when I added a new column to my SharePoint List, then refreshed the PowerPivot connection, I didn’t see the new column included in my PowerPivot model… this new column will be the source for a slicer that i’d like to add to my existing report. but it’s nowhere to be found, what am i missing???
any tips on this?
Hi,
Firstly, congratulations for the blog. It is help me so much.
I’m receiving a error that is driving me crazy. =/
Im trying to install the powerpivot for sharepoint and when the installtion is finishing I receive the error: The SQL Server PowerPivot for SharePoint solution cannot be deployed.
Afther this, I try to configurate the sql powerpivot service on sharepoint central administration and I receive the error: an object reference not set to an instance of the object
Could you help me, please?
[...] Continue reading: Using a SharePoint list as a data source [...]
[...] to this. I then went ahead and did a quick search and came across this posting from Dave Wickert Using a SharePoint list as a data source and realized that when the SharePoint 2010 server was setup that the ADO.NET Data Services 3.5 SP1 [...]