OakLeaf Systems: Using the Windows Azure Marketplace DataMarket (and Codename “Data Hub”) Add-In for Excel (CTP3)

Monday, May 21, 2012

Using the Windows Azure Marketplace DataMarket (and Codename “Data Hub”) Add-In for Excel (CTP3)

• Updated 5/22/2012: Clarified prerequisites below.

The Windows Azure Marketplace DataMarket and SQL Labs’ Codename “Data Hub” Preview promote Microsoft PowerPivot for Excel 2010 on each dataset’s summary page and offer a direct Export to Excel PowerPivot option on the Build Query page. PowerPivot’s business intelligence (BI) features often are overkill for simple analytical activities, such as browsing datasets, or might intimidate potential DataSet users.

The Windows Azure Marketplace DataMarket Add-In for Excel (CTP3) provides a simple alternative to exporting data as Excel *.csv files from DataMarket or Data Hub and opening them in Excel. According to the download page’s “Overview” section:

Microsoft Windows Azure Marketplace DataMarket Add-in for Excel (CTP 3) provides a simple experience allowing you to discover datasets published on the Windows Azure Marketplace DataMarket or in your instance of Microsoft Codename "Data Hub" right within Excel. Users can browse and search for a rich set of datasets within a tool they already use.

With only a couple of clicks, the user can query their datasets and import the data as a table into Excel. Once in the workbook, the data can be visualized, joined with other data sources (including owned/on premise data) or exported to be used in other applications.

Prerequisites: Installation of the Windows Azure Marketplace DataMarket Add-In for Excel (CTP3) is required. (See step 1 below.)

This tutorial assumes (but doesn’t require) that you have accounts for the Windows Azure Marketplace Datamarket and the OakLeaf Public Data Hub and have subscribed to (Datamarket) or added the Air Carrier Flight Delays dataset to your collection (Data Hub). See Accessing the US Air Carrier Flight Delay DataSet on Windows Azure Marketplace DataMarket and “DataHub” for details.

The following steps apply to both DataMarket and Data Hub datasets unless prefixed with [DataMarket] or [Data Hub]:

1. Download the Windows Azure Marketplace DataMarket Add-In for Excel (CTP3) from here, extract the files from the DataMarketExcelAddInSetupCTP3.zip, and run Setup.exe to install the add-in and its prerequisites.

Note: The following steps are more detailed than the Getting started with Windows Azure™ Marketplace DataMarket Add-In for Excel (CTP3) page, which displays when installation completes.

2. Launch Excel 2010, click the Data tab, which now sports an Import Data from DataMarket button:

3. Click the button to open a Subscribed Datasets taskpane:

4. If you’re not familiar with the public DataMarket, click its Learn More link to open the DataMarket’s About page. Alternatively, click the private DataHub’s Learn More link to open the SQL Azure Lab’s Welcome page for Codename “Data Hub,” and sign in with a Live ID.

5A. [DataMarket] Click the Sign In or Create Account button to open the sign-in page:

5B. [Data Hub] Type the HubName.clouddatahub.net URL for your organization’s Data Hub in the text box:

5C.[Data Hub] Click the link to find your account key (sign in, if requested):

5D. [Data Hub] Select and copy the Account Key to the Clipboard, close the Data Hub page to return to Excel, paste the Account Key to the text box and, optionally, mark the Remember Me checkbox. (Refer to step 5B’s screen capture.)

6A. [DataMarket] Click Sign In to open an Allow Access form:

6B. [DataMarket] Click Allow Access to display a list of the Datamarket datasets to which you’ve subscribed:

6C. [Data Hub] Click Sign In to display a list of Data Hub data sets in your collection:

7. Click the US Air Carrier Flight Delays Import Data link to open the Query Builder dialog:

Note: It isn’t clear how to enable the Group and Ungroup buttons.

8. Click the Add Filter button to add the first filter builder, select Month and type 2 as the value; click +, select Year and type 2012 as the value; and click +, select Dest and type OAK as the value to restrict the number of rows to a reasonable number:

Note: It isn’t clear how to return to where you were before clicking the Preview Data button, so clicking it isn’t recommended.

10. Click the Sort Results tab, click the Add Sort Order button to add the first sort builder, select FlightDate, click + and select Carrier to order by date and carrier:

11. Click the specify Returned Fields tab and clear the DayOfMonth, Month, RowId and Year check boxes:

12. Click Import Data to fill the worksheet, which has 3,462 rows and includes empty columns for the fields you omitted in step 11:

Tip: You can get help for the DataMarket add-in by searching online Office help for DataMarket.

13. Click the Insert tab, open the PivotTable gallery and choose PivotChart to open the Create PivotTable with PivotChart dialog, accept the default Table/Range value and click OK to open a PivotTable and PivotChart with a PivotTable Filed List taskpane.

14. Mark the Carrier and DepDelayMinutes field check boxes, open the Sum of DepDelayMinutes list in the Values area, select Value Field Settings, choose Summarize Values by Average, delete the legend and edit the chart title: