Data in the cloud! DataMarket Add-In for Excel and Excel Services
There are some exciting things happening around Microsoft cloud services. One of the services is Windows Azure marketplace DataMarket, a cloud service that helps end users who need data for business analysis and decision making. You can conveniently consume the data you subscribe to directly in such Microsoft Office applications as Microsoft Excel 2010 and Microsoft business intelligence tools as PowerPivot and SQL Server Reporting Services. Then you can share the reports and visualization you create in SharePoint. To learn more, see the DataMarket_Whitepaper.
There are some end-to-end scenarios where Office draws data from the cloud through DataMaket, then shares it in SharePoint Server 2010 through Excel Services and perhaps from there to a PerformancePoint Services dashboard. The following diagram is a specific scenario where data is drawn from the DataMarket, then saved to your desktop, then shared on SharePoint Server 2010 through Excel Services.
Note: Office365 is included in the image only to give the big picture, in that there are three major offerings and DataMarket is a result of what is offered in Windows Azure and SQL Azure.
This post highlights the DataMarket Add-In for Excel, which is a free application that allows discovering and importing data from Windows Azure Marketplace DataMarket into Microsoft Office Excel. I also give a brief description of each cloud offering.
DataMarket Add-In for Excel (how it works)
The DataMarket Add-in for Excel (CTP1) gives you a simple experience allowing you to discover datasets published on the Windows Azure Marketplace DataMarket. You can browse and search for a rich set of datasets from content publishers within a tool you’re already familiar with, Excel. Here is how:
1. Download the add-in here, open the folder (or save to your machine), and double-click the Windows Installer Package.
Note: You may be asked to download Microsoft .NET Framework 4.0 Client Profile. The 4.0 framework installs the WCF Data Services, which is a component of the .NET Framework that enables you to create REST-based services and applications that use the Open Data Protocol (OData) to expose and consume data over the Web.
2. Open Excel and click the Data tab to see that the add-in was installed as extension to Excel. You should see a button named Import data from DataMarket in the Excel ribbon, as follows:
3. Click the Import data from DataMarket button. The sign-in dialog box opens:
This dialog box introduces the DataMarket and lets you:
- Browse the marketplace: Clicking this link opens a new browser window where you can browse the datasets exposed by the DataMarket.
- Sign up for DataMarket: Opens the browser with the sign-up page for the DataMarket. Signing up is free!
- Privacy statement: Opens the browser showing the privacy statement for this add-in.
The main purpose of this dialog box is to help you sign in to your list of subscribed datasets. To sign in, you need to provide an account key. An account key is your password to access all the datasets in the DataMarket and it can be found at https://datamarket.azure.com/account/keys. Because the account key is your password (tied to your DataMarket account), you need to sign up with the DataMarket to get access to it.
Copy the account key from the account key page (https://datamarket.azure.com/account/keys) and paste it into the Account key field. Additionally, you can specify whether the account key is being saved for further use by checking the Remember my account key check box.
If you have signed up, copy your account key and click Sign-In to load your subscribed datasets.
The following screeshot shows the three datasets I signed up for. Some datasets, such as STATS, charge a fee. Other datasets are free but may have limitations on the data you can view. For example, Zillow gives you 3,000 transaction per month for free. The other data providers offer a various number of transactions.
For next steps see: