Hands-On Lab

Introduction toWindows Azure Marketplace for Data

Lab version:1.0.0

Last updated:12/9/2018

Contents

Overview

Exercise 1: Exploring the Microsoft Azure Marketplace Portal and Service Explorer

Task 1 – Accessing and Signing Up to the Microsoft Azure Marketplace Portal

Task 2 – Exploring Your Account Keys

Task 3 – Exploring the Datasets in the Marketplace and Subscribing to a Dataset

Task 4 – Using Service Explorer to Preview a Dataset

Exercise 2: Consuming Marketplace Data and Services via Managed Code

Task 1 – Adding a Service Reference

Task 2 – Consuming Data with Managed Code

Exercise 3: Consuming Marketplace Data via PowerPivot

Task 1 – Importing Data From a Marketplace Dataset Into PowerPivot

Task 2 – Importing Data From an External Data Source Into PowerPivot

Task 3 – Creating a Relationship in PowerPivot

Task 4 – Creating a Simple Pivot Table With Your Data

Summary

Overview

In this lab, we willintroduce you to the Microsoft Azure Marketplace for Data. The Marketplace for Data is Microsoft’s Information Service offering which allows developers and information workers to find, acquire and consume published datasets and web services. Users subscribe to datasets and web services of interest and can integrate the information into their own applications via standardized data services. Data can also be analyzed online using the Service Explorer or externally using the PowerPivot Add-In for Excel.

Objectives

In this hands-on lab, you will learn how to:

  • Explore the Microsoft Azure Marketplace portal and Service Explorer
  • Query a Microsoft Azure Marketplace dataset using a URL
  • Access and consume a Microsoft Azure Marketplace dataset and Service via Managed Code
  • Use the PowerPivot Add-In for Excel to consume and analyze data from Microsoft Azure Marketplace datasets

System Requeriments

You must have the following items to complete this lab:

  • Microsoft Visual Studio 2010
  • Microsoft .NET Framework 4.0
  • Microsoft Office Excel 2010
  • PowerPivot

Setup

In order to execute the exercises inthis hands-on lab you need to set up your environment.

  1. Open a Windows Explorer window and browse to the lab’sSourcefolder.
  2. Double-click the Setup.cmd file in this folder to launch the setup process that will configure your environment and install the Visual Studio code snippets for this lab.
  3. If the User Account Control dialog is shown, confirm the action to proceed.

Note: Make sure you have checked all the dependencies for this lab before running the setup.

Using the Code Snippets

Throughout the lab document, you will be instructed to insert code blocks. For your convenience, most of that code is provided as Visual Studio Code Snippets, which you can use from within Visual Studio 2010 to avoid having to add it manually.

If you are not familiar with the Visual Studio Code Snippets, and want to learn how to use them, you can refer to the Setup.docx document in the Assets folder of the training kit, which contains a section describing how to use them.

Exercises

This hands-on lab includes the following exercises:

  1. Exploring the Microsoft Azure Marketplace portal and Service Explorer
  2. Consuming Marketplace Data and Services via Managed Code
  3. Consuming Marketplace Data via PowerPivot

Estimated time to complete this lab: 60 minutes.

Note:When you first start Visual Studio, you must select one of the predefined settings collections. Every predefined collection is designed to match a particular development style and determines window layouts, editor behavior, IntelliSense code snippets, and dialog box options. The procedures in this lab describe the actions necessary to accomplish a given task in Visual Studio when using the General Development Settings collection. If you choose a different settings collection for your development environment, there may be differences in these procedures that you need to take into account.

Exercise 1: Exploring the Microsoft Azure Marketplace Portal and Service Explorer

In this exercise, we will sign up to the Microsoft Azure Marketplace portal and use the Service Explorer to explore your service details and the Marketplace datasets.

Task 1–Accessing and Signing Up to the Microsoft Azure Marketplace Portal

In this task you will access the Microsoft Azure Marketplace portal and sign up:

  1. Navigate to

Figure 1

Microsoft Azure Marketplace portal

  1. Click Sign in button. If you already have a Windows Live ID, use it to sign in. If you do not have a Windows Live ID, create one by clicking Create Windows Live ID button.
  2. If it is the first time you have signed into the portal, enter the requested information in Account Details, review the privacy statement, and select the checkbox to agree with Marketplace privacy policies and thenclick Continue button.

Figure 2

Registering for Marketplace

  1. Review the Agreement, and accept it by selecting I accept the Terms of Use checkbox. Click Register button to complete the registration.

Figure 3

Accepting User Agreement

  1. Once registered, go to My Data section by clicking My Account tab on the ribbon and then selecting My Data option on the left pane.

Figure 4

My Data Page

Task 2 – Exploring Your Account Keys

In this task, you will explore your account keys and create a new account key.

  1. From the PortalHomePage, click the My Accounttab.

Figure 5

Clicking My Account

  1. Click Account Keys on left pane. You will create a new account key. To do that,click the Addaccount keybutton.

Figure 6

Creating a new account key

  1. The screen for adding an account key will display. Enter a description for the account key and click Create.

Figure 7

Adding a new Account Key

  1. You will be redirected to the Account Keys view and you will now see your new account key in the list of available keys.

Figure 8

Your new account key

Task 3 –Exploring the Datasets in the Marketplace and Subscribing to a Dataset

In this task, you will explore the Marketplace for Data. The Marketplace allows users to browse and subscribe to available datasets.

  1. Click Datatab on the ribbon.

Figure 9

Clicking Applications

Note: For each dataset, the Marketplaceshows the data provider, a description of the dataset’s content, the type of the content, and provides a link that will allow you to subscribe to the dataset.

  1. Using Search the Marketplace, use the keyword Gender to locate the UN DATA dataset called “Gender Info 2007 – United Nations Statistics Division”.

Figure 10

Searching Dataset

  1. Click on the dataset and review the information. Click on Sign Up button to subscribe to the dataset.

Figure 11

Reviewing dataset information

  1. Review and accept Dataset Terms. Click Sign Up button.

Figure 12

Signing Up Dataset Subscription

  1. Review the Receipt.

Figure 13

Your Marketplace Subscription Receipt

Task 4 – Using Service Explorer to Preview a Dataset

In this task, you will use the Service Explorer to preview a dataset to which you have subscribed.

  1. From the MarketplacePortal,clickMy Accounttab and select My Data on the left pane. You will see a list of your subscribed datasets.
  2. Locate the Free subscription for “Gender Info 2007” Dataset and click the title to open the subscription.
  3. Click Explore This Dataset link.

Figure 14

Exploring the dataset

  1. The dataset will be opened in the Service Explorer.

Figure 15

Service Explorer

Note: You can use the Service Explorer to query the dataset, invoke the dataset as an Atom feed, or analyze the dataset usingthe PowerPivot add-in for Excel 2010.

  1. In the Build Query pane, change the Query dropdown to Values.Modify or set some of the Optional parametersand click the Run Query button.This will display a preview of the dataset based on the criteria you set.

Figure 16

Previewing your Dataset

  1. By default, the preview will display in Table format, however you can click Atom Pub link to preview the dataset in Atom Pub format.

Figure 17

Preview Formats

Exercise 2: Consuming Marketplace Data and Services via Managed Code

In this exercise, you will add the service reference to consume Marketplace data. You will create a class to expose the consumed data.

Task 1 – Adding a Service Reference

In this task,you will add the service reference to consume Marketplace data.

  1. Log on to the Windows Azure Marketplace portal.
  2. From the MarketplacePortal, click My Account tab and select My Data on the left pane. You will see a list of your subscribed datasets.
  3. Locate the subscription for “Gender Info 2007” Dataset and click the title to open subscription.
  4. Click on Explore This Dataset link to open dataset details.

Figure 18

Exploring the Dataset

  1. From the dataset explorer page,click Run Query button without setting any extra parameter. You will see the list with the query results on the left pane.

Figure 19

Copying Query root URL.

  1. Press the Develop tab in the right pane and copy the URLfrom the Current expressed query. Note that you have to copy only the URL without the filters. To do that, dismiss the text after the last slash. Your URL should look like . You will use this URL later in this exercise.

Figure 20

Copying Query root URL.

  1. Open MicrosoftVisual Studio 2010.
  2. Open solution called Begin.sln located in the Sourcefolder Ex02-ConsumingMarketplaceDataAndServicesViaManagedCode\begin\CS.
  3. Right click on the ConsumingMarketplaceData project and select Add service reference.
  4. Paste the URL you copied into the Address textbox. Click on the Go button. Once the service is discovered, add UNData as Namespace and click OK.

Figure 21

Adding Service Reference

Task 2 – Consuming Data with Managed Code

In this task, you will create a simple class that wraps the dataset queries.

  1. InConsumingMarketplaceData project, add a new class called UNGenderData.

Figure 22

Adding UNGenderData

  1. Add System.Net and ConsumingMarketplaceData.UNData to the using list.

(Code Snippet – Introduction to Marketplace – Ex2Adding Using – CS)

C#

using System.Net;

using ConsumingMarketplaceData.UNData;

  1. Add the public access modifier to the class UNGenderData.
  2. Add the properties serviceUri and context to your class.

(Code Snippet – Introduction to Marketplace – Ex2Adding Properties – CS)

C#

privateUri serviceUri;

privateUnitedNationsGenderInfo2007Container context;

  1. Create the class constructor with the following code inside.

(Code Snippet – Introduction to Marketplace – Ex2Create Constructor – CS)

C#

public UNGenderData()

{

this.serviceUri = newUri("{replace-with-dataset-url}");

this.context = newUnitedNationsGenderInfo2007Container(this.serviceUri);

this.context.Credentials = newNetworkCredential(

"{replace-with-your-user-id}",

"{replace-with-your-key}");

}

  1. Now, you have to replace the placeholdersin the code with the actual data. To do this, navigate back to the Marketplace portal.
  2. Click on the My Account tab.
  3. Click Account Keys link in the left pane.
  4. Copy the account description you created on Exercise 1 and replace {replace-with-your-user-id} placeholder with it.
  5. Copy the account key of that description and replace {replace-with-your-key}with it.

Figure 23

Getting Account Key details

  1. Click My Datalink located in the left side pane.
  2. Click Gender Info 2007 – United Nations Statistics Division title to view details of the dataset.
  3. Click on the Explore this Dataset link.
  4. Copy Service root URL link.

Figure 24

Getting Account Key details

  1. Replace {replace-with-dataset-url} placeholder with the Service Root url you copied.
  2. Add the following code at the end of the class.

(Code Snippet – Introduction to Marketplace – Ex2GetValues Method – CS)

C#

publicIListValues> GetValues()

{

IEnumerableValues> query;

query = from value inthis.context.Values

where value.CountryName == "Argentina"

select value;

try

{

return query.ToList();

}

catch (Exception ex)

{

Console.WriteLine("Error: {0}", ex.Message);

returnnull;

}

}

Note: The method GetValues() queries the context provided by the Marketplace service. You can add other methods that query using other parameters. To do this you can review the different optional parameters on the dataset service explorer page.

  1. Copy the following code on the Program class Main method.

(Code Snippet – Introduction to Marketplace – Ex2Main Method – CS)

C#

privatestaticvoid Main(string[] args)

{

UNGenderData data = newUNGenderData();

foreach (var value in data.GetValues())

{

Console.WriteLine(string.Format("Country {0} year {1} {2} value {3}", value.CountryName, value.Year, value.SubGroupName, value.Value));

}

Console.ReadKey();

}

  1. Run the application by pressing the F5 key. A Console window will open and a tabular view of the data returned by our query will be displayed. Press the Enter key to close the Console window.

Figure 25

Console running

Exercise 3: Consuming Marketplace Data via PowerPivot

In this exercise, you will use the Excel Add-In PowerPivot to consume and analyze data from Marketplacedatasets.

Note: PowerPivotis a free data analysis tool that lets you manipulate large data sets (often millions of rows) with about the same performance as processing a few hundred rows, integrate data from a multitude of sources, including corporate databases, spreadsheets, reports, text files, and Internet data feeds

Download the latest version of PowerPivot from the following link (You will need Microsoft Excel 2010):

Task 1 – Importing Data From a Marketplace Dataset Into PowerPivot

In this task, you will import data from a Marketplace dataset into PowerPivot.

  1. Navigate to
  2. Click the Data tab in the ribbon. Using Search the Marketplace, locate the UN data dataset “UNESCO UIS Data”.
  3. Click on title to view detailed information.
  4. Click Sign Up to subscribe.

Figure 26

UNESCO UIS Data Dataset

  1. Agree and confirm subscription. To do this, check the checkbox and click Sign Up button.

Figure 27

UNESCO UIS Data Dataset

  1. Click on the Explore This Dataset link.
  2. Select Values on Query dropdown box. Complete with optional parameters. To narrow the query you can write 2000 on the TimePeriod textbox. Select Run Query.

Figure 28

UNESCO UIS Data Dataset service explorer

  1. Switch to the Export tab. Make sure that Export to program is set to Excel PowerPivot. Click Download.

Figure 29

Exporting the atom feed

  1. Click on the Open button.

Figure 30

Opening the atom feed

  1. If prompted, select <Create a New Workbook> and click Ok button, when prompted by PowerPivot.

Figure 31

Selecting the workbook

  1. Microsoft Excel will launch and the PowerPivot Table Import Wizard dialog will display. On the Table Import Wizard, complete with a Friendly connection nameand copy your account key into the Account Key textbox. To do this, see exercise 2 task 2. Optionally, check Save my account key. Click Next button.

Figure 32

Excel Table Import Wizard

  1. Click Finish button to start importing information.

Figure 33

Importing dataset data

  1. The wizard will import data from the dataset, when finished click on the Close button. The imported data will be displayed in PowerPivot. Keep the PowerPivot window open to do the next tasks.

Note: The import operation may take several minutes to complete.

Figure 34

Dataset imported into PowerPivot

Task 2 – Importing Data From an External Data Source Into PowerPivot

In this task, you will import data from an existing Excel spreadsheet into PowerPivot.

  1. From the PowerPivot window, click the FromOther Sources toolbar item.

Figure 35

From Other Sources option on PowerPivot toolbar

  1. On the Table Import Wizard, scroll down and select Excel File and click Next. Click the Browsebutton. Browse to Ex03-ConsumingMarketplaceDataViaPowerPivot\ in the Source folder of this lab, select the RegionOrArea.xlsxfile, and select the Use first row as column headers option. Click Next to select the tables, which will be imported. Make sure that the RegionOrArea$ source table is checked. Click Finish to import the file into PowerPivot. Finally click Close.

Figure 36

The Excel spreadsheet imported into PowerPivot

Task 3 – Creating a Relationship in PowerPivot

In this task, you will create a relationship between the two sets of data we have imported into PowerPivot.

  1. From the PowerPivot toolbar, click the Design tab.

Figure 37

Design tab on the PowerPivot toolbar

  1. From the PowerPivotDesign toolbar, click the CreateRelationship button.

Figure 38

Create Relationship button on the PowerPivot Design toolbar

  1. Set the following criteria on the CreateRelationship dialog box:

◦Table: RegionOrArea

◦Column: RegionOrAreaCode

◦Related Lookup Table: UNESCO UIS Data….

◦Related Lookup Column: ReferenceAreaCode

Figure 39

Create Relationship dialog window

  1. Click the Create button. This will create a relationship between the two PowerPivot tables.

Task 4 – Creating a Simple Pivot Table With Your Data

In this task, you will use PowerPivot to create a simple PivotTable using the data imported in the previous tasks.

  1. Click the Home tab on the PowerPivot toolbar.

Figure 40

Home tab on PowerPivot toolbar

  1. Click the PivotTable button on the PowerPivot toolbar, choose the PivotTable option.

Figure 41

PivotTable button on the PowerPivot toolbar