Parameterising PowerPivot Connection Strings in Excel 2013

Reposted from Chris Webb's blog with the author's permission.

One of the things I've always wanted to do with PowerPivot is to parameterise the connections used to import data. Despite PowerPivot's ability to handle massive data volumes, most of the time you want your end users to import only the data they actually need - and for them to be able to specify filters on the data themselves somehow. The only way to do this in Excel 2010 was to have them go into the PowerPivot model and change the connection themselves, which is not very user-friendly, but now we have a proper PowerPivot object model in 2013 we can modify connection strings in VBA (so we can take filtering information direct from the worksheet) and this post shows how. I'd like to acknowledge the help I got from Kasper's post here which covers very similar ground, but I came across a few interesting things while building the example here so I thought it was worth a post on its own.

Let's say we want to build our own Bing search engine client in Excel 2013, where a user can enter a search term in a cell, click a button to run the search and then not only be able to see the search results but analyse them in PowerPivot and Power View. The first step is to sign up to the Bing search API in the Azure Marketplace and then import some search results with a hard-coded search term (this tutorial will help if you're not sure how to do this); I used Bing image search to return some results with urls pointing to images on the web. This will create a connection in the Workbook which we can then modify programmatically. However I ran into a problem at this point: I found that only connections created on the Data tab on the ribbon can be modified in VBA, whereas connections created in the PowerPivot addin cannot. This means I had to click here:

..to create my connection, and NOT here:

When you open connections created in the PowerPivot window in the Connections dialog from the Data tab, you see the following message: "Some properties cannot be changed because the connection was modified using the PowerPivot Add-In"

Trying to edit connections created in PowerPivot using VBA just gave me an error.

Not much of a issue though. With the connection in place, here's the VBA code that's needed to alter the connection string and replace the search term with a value from a cell in the worksheet:

Sub RunImageSearch()

Dim mdl As ModelTable

Dim wcon As WorkbookConnection

Dim cs As String

Dim ss As String

Dim azurekey As String

azurekey = "Insert your Azure Marketplace account key here"

Set mdl = ActiveWorkbook.Model.ModelTables("Image")

Set wcon = mdl.SourceWorkbookConnection

cs = "DATAFEED;" & _

 "Data Source=https://api.datamarket.azure.com/Bing/Search/v1/" & _

 "Image?Query=%27ReplacePlaceholder%27;" & _

 "Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=Basic;" & _

 "User ID=AccountKey;Password=" & azurekey & _

 ";Persist Security Info=false;" & _

 "Base Url=https://api.datamarket.azure.com/Bing/Search/v1/Image?Query=%27ReplacePlaceholder%27"

ss = WorksheetFunction.EncodeURL(CStr(ActiveWorkbook.Sheets("Search Term").Cells(2, 3).Value))

wcon.DataFeedConnection.Connection = Replace(cs, "ReplacePlaceholder", ss)

mdl.SourceWorkbookConnection.Refresh

End Sub

Three points to note here:

• If you're copying this code, you need to enter your own Azure Marketplace account key in the place specified
• The search term needs to be url encoded, and luckily there's a new function to do this in 2013: EncodeURL()
• If you're using a different data source then obviously the connection string will be different. Also, because I'm using data from the Azure Marketplace my SourceWorkbookConnection object has a connection of type DataFeedConnection - other data sources will have different connection types, so check the value returned by SourceWorkbookConnection.Type (the list of values in the XLConnectionType enumeration on the web is, at the time of writing, out of date but Object Explorer is up-to-date)

That's all there is to it. The macro can be bound to a button on the worksheet like so:

And we can then do all kinds of clever things to analyse the search results. For example, with the image search results we can show thumbnails in a Power View report (see Jeremy Kashel's post for details):

You can download the whole example workbook here, although again you'll have to edit the VBA to enter your own Azure Marketplace account key if you want it to work.

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .