Yesterday Microsoft released the Windows Azure Marketplace DataMarket (which everyone will simply call DataMarket, I hope). It was previously called Codename “Dallas”. This is an important step toward the empowerment of self-service BI tools. Microsoft also released two add-ins:
- The Windows Azure Marketplace DataMarket Add-In for Excel (CTP1)
- It imports data into Excel workbooks that you can manage by leveraging all the tools in Excel
- PowerPivot for Excel 2010 (new build!)
- It is the PowerPivot add-in we already know, but they have added a specific wizard to import data from the DataMarket, so that you can get your data directly into a PowerPivot workbook
Now, I already tried the tools and I discovered some little issues.
The user interface of the Add-In for Excel is more advanced and complete. You can browse DataMarket data sources without leaving Excel, whereas in PowerPivot you need to open a browser window and you can get data preview directly from the browser. The best way to import data is to select the filters in the browser and the click on the button to load data in PowerPivot (you can also select Tableau software as destination for that data). This approach works very well.
As an alternative, when you open the Table Import Wizard for DataMarket you can directly paste a dataset URL into the Azure Datamarket dataset URL textbox. However, if you use the URL published as the Service root URL in the DataMarket pages, then you have to check that the URL has a slash at the end of the string!
In fact, if you use (for example) this URL (that is the one published on the website):
It is not going to work. You have to use this one instead:
This is the only way you can import more than one tables at once by using PowerPivot. I hope they will fix this issue soon, because even if it is not so hard to workaround, it is important to offer a better user experience to PowerPivot users.
Originally appeared on: http://sqlblog.com/blogs/marco_russo/archive/2010/10/29/datamarket-and-powerpivot.aspx