Importing Azure DataMarket feeds in PowerPivot

I just had a quick play with the new PowerPivot add-in for Excel and I tested the import with a few data feeds from the new Azure Marketplace. Straight away, I have a few lessons learned points which may be good to share:

1. The instructions of how to import the feeds through PowerPivot are slightly incorrect. Instead of “Getting Started with this Dataset”, you should click on “Explore this Dataset”. Then, on the bottom-left there is a drop-down, which allows you to select PowerPivot. From here onwards it’s staright-forward.

2. Wait for the a feed to finish before importing the next one. It may take a while with a large feed and I was impatient (on a 64bit version for reference), which crashed my Excel.

3. Importing the dataset through the Service Root URI (trying to paste this in PowerPivot) will result in a Bad Request 400 even though the connection tests are successful. Marco Russo blogged about adding a slash in the end of the URI here.

And a quick suggestion – it would be good to see the size of the feed we will be importing in the Explore Dataset page. Since it displays the top 100 items only, there is no way of telling if there are 101 or 1000000000 feed items – which in term doesn’t allow us to prepare for a long wait.

Apart from these, I had not other issues and I am happily browsing the datasets in PowerPivot to the amazement of my .NET colleagues.