Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Importing Azure Marketplace Data into Excel 2013 with Web Queries

with 8 comments

A few weeks ago, when I was playing around with the new WebService() function in Excel 2013, I was disappointed to learn that it didn’t work with services that require authentication – a pretty big limitation in my opinion – so, for example, it meant I couldn’t use it to import data from the Azure Marketplace into Excel. You might be wondering why I’d want to do this, when there’s already built-in functionality for importing Azure Marketplace data into Excel 2013 and an addin to do this for earlier versions of Excel; the reason is that I want to be able to dynamically construct the url used to call the service inside the spreadsheet. For example, in this post I’m going to be using the Bing Search API and I want to be able to enter the search term I pass to it in a cell in a worksheet rather than hard-code it in the connection, which is what happens when using the native functionality. I’m sure I could use some VBA to do the same thing but using VBA always seems like an admission of defeat to me, so in this post I’m going to show how you can use Excel Web Queries to do it instead.

So let’s start with the Bing Search API, one of the many APIs and datasets available via the Windows Azure Marketplace. There are plenty of posts explaining how to use the Azure Marketplace website to import data into PowerPivot (see here for example) so I won’t go into too much detail at this point, but here’s an example Url that returns the top 15 news results for the search term ‘Microsoft’:

https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27microsoft%27&$top=15&$format=Atom

image

Next, you need to fire up Excel 2013 and create a Web Query, a feature that’s been in Excel since ohhhh, Office 2000. There are a lot of good articles on the web describing how this feature works but here’s one of the best I found:
http://www.vertex42.com/News/excel-web-query.html

Specifically, you need to go to the Data tab and click the From Web button:

image

This opens the New Web Query dialog; you then paste the Url from the Azure Marketplace into the Url box and click go. You’ll be prompted for a username and password, and you need to enter your Azure Marketplace account key for both. Click on the small yellow arrow in the top left hand corner of the browser window and you’ll see something like this:

image

Do not press Import at this point though! Instead, press the Save Query button in the toolbar (highlighted in the screenshot above) and then save the query to disk as an .iqy file and click Cancel. Next, find the .iqy file you’ve just saved and open it in Notepad. The contents will be something like this:

WEB
1
https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27microsoft%27&$top=15&$format=Atom

Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

What you need to do now is to parameterise the Url in this file so that, instead of hard-coding it to search for news about microsoft (as in this example) you can enter your own search term. The link above describes how to do this in detail, but here’s the parameterised version of the Url for reference:

https://api.datamarket.azure.com/Data.ashx/Bing/Search/v1/News?Query=%27%5B"Query", "Search For:"]%27&$top=15&$format=Atom

You then need to save the file and go back to Excel.  First, enter a search term in a cell in a worksheet. Then, go to the Data tab, click the Existing Connections button and then the Browse For More button and select the .iqy file. The next step is to choose a cell in a worksheet to dump the data to, and then when prompted for the parameter value click on the cell where you entered your search term and check the Use This Value/Reference For Future Refreshes and Refresh Automatically When Cell Value Changes boxes.

image

Click OK and you’ll be prompted for credentials again – although Excel will remember them – and the query will run, resulting in some XML appearing in the cell you selected:

image

This is ugly, and indeed Excel has the ability to import XML returned from a Web Query as XML – but what I’ve found is that if you don’t use the approach I’m showing here, you won’t be able to refresh you query properly.

So you now have our search results as XML and you want to be able to display them in a more meaningful way. This is where the new FilterXML function in Excel 2013 comes into its own. Create an Excel table with four columns: Rank, Title, Description and Url; then enter numbers from 1 to 15 in the Rank column (there are fifteen results returned from the Bing search). You can then use Excel table formulas like this one to get the Title, Description and Url from the XML and display it in the table:

=FILTERXML($B$4, "//feed/entry[" & [Rank] & "]/content/m:properties/d:Description")

image

(I’m very grateful to Phil Quinn, who I met at SQL Saturday 162, for helping me with the XPath here – I spent ages trying to get it to work with no luck and he managed to provide a working query in 2 minutes)

With this done, you now have the ability to enter a search term in your worksheet and automatically get Bing search results displayed in an Excel table. Of course, this approach would work with any dataset from the Azure Marketplace and indeed any OData source – for example, now that SSRS 2008 R2 and greater can render reports to OData, it should be possible to consume data from an SSRS report in a much more elegant way than the method I described here, because you’d be able to pass parameters from Excel to SSRS (hmmm, maybe this needs its own blog post). Of course, now you have a table in Excel you can add it to the Excel Model and do all kinds of interesting PowerPivot-y things with it.

What this really highlights, though, is that it should be much easier to parameterise the queries used to load data into the Excel Model/PowerPivot, whether they are OData data sources, SQL, MDX or DAX. Not being able to parameterise these queries means you increase the temptation to load all the data that might ever be needed by a user into PowerPivot; if it were easier to parameterise these queries then it would encourage PowerPivot users to build solutions where they only imported the data they actually needed to work with at any given moment.

Written by Chris Webb

September 10, 2012 at 11:14 am

Posted in Excel, OData

8 Responses

Subscribe to comments with RSS.

  1. [...] Importing Azure Marketplace Data into Excel 2013 with Web Queries By SQL MVP Chris [...]

  2. I am following your steps, but it fails on the username and password which I am quite sure that these are correct.

    I guess the issue is IE9, what is your web browser using?

    Thanks

    martin

    November 12, 2012 at 4:03 am

    • I have IE9 installed too

      Chris Webb

      November 12, 2012 at 6:19 am

      • I am lost.

        I tried:
        Username: either blank or the account key
        Password: the account key

        All these failed.

        Can you give me a clue that what is the culprit?

        Thanks

        martin

        November 12, 2012 at 6:33 am

      • Can you import the same data directly into PowerPivot? Are you sure you’re using the correct account key? It’s very easy to use the wrong value here, but you can find your account key by going to the Query Explorer page in Datamarket and clicking Show next to Primary Account Key, as shown in the screenshot here: http://cwebbbi.wordpress.com/2012/04/12/importing-uk-weather-data-from-azure-marketplace-into-powerpivot/

        Chris Webb

        November 12, 2012 at 4:19 pm

      • Yes, also I can import the same data with Excel Datamarket Addin, but in this way, it does not support the cell parameter query. The account key is no probliem.
        I am trying to paste the url to a new IE window, it just keeps to asks the username and password several times when I input the account key into the password, like the excel web query, it ends up to a failure.

        martin

        November 12, 2012 at 11:56 pm

  3. Martin, you can’t use the parameterised url in IE. The parameterised url can only be used in the .iqy file that Excel uses for the web query, as I say in the post.

    Chris Webb

    November 13, 2012 at 9:36 am

    • Yes, I know that.
      The problem is that either IE or Excel Web Query does not retrieve the result but Excel DataMarket Add-in can get the query back.

      martin

      November 13, 2012 at 1:47 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: