Tim Mallalieu's Blog.

Just a PM's random musings on data, models, services...

Using the Montego Client & Excel

Using the Montego Client & Excel

  • Comments 3

 

When we did the demo at PASS we were showing the cloud experience. In my last post I had a video of one of our earlier iterations on the Montego client - the mashup component of Data Explorer. This next post shows a little bit about how you can reference mashups created in the client in Excel. When we originally started the project we were exploring the client being like MS Query only more powerful in terms of supportedt data sources and formats and the ability to do federated queries across multiple sources. As we evolved to deliver a cloud service the client evolved to be more of a client complement to the cloud service. Today you can use the client to build mashups and we enable creating connections from Excel to these Mashups using ODBC.

Here is a simple example...

For this post I decided to go get a bunch of Football stadia from Wikipedia and import them to excel. First we have to create a new mashup document:

Once we have created a new mashup we see it available to us and we can choose to edit it:

Choosing Edit opens the editor view of the Mashup as follows:

Note that the default experience allows you to add data from the supported data sources and shows recently used data sources as well. Note that much of this UX is getting a significant face lift in the Data Explorer bits before we publish the labs release later this year.

 At this point we can add data from a web page, we choose the Web Content icon and get the "builder" - the configuration screen for pointing to the data source (in this case our wikipedia page for football stadiums):

Looking at the above picture we can talk about some of the basic abstractions in the editor:

The blue tab that says WebContent1 is what we call a resource. Resources are the things that are exposed from a mashup document. You can have many resources in a mashup document. The highlighted icon (WebContent) is a task. We allow you to compose many tasks together in order to shape the resource how you want it. In this case we have added the task to retrieve data from an http end point. Once we choose "Done" from the Web Content builder the runtime makes a call to the web page and retrieves the content:

 

At this point we can treat the content as text or HTML. I chose HTML as this allows us to reference the tables and lists in the HTML document as if they were data sources:

From here I extract all the tables in the HTML documnet... I see that there are only two:

I then drill into the first table and use it as my data source:

 

Now that I have the data in I can do some shaping. Before I do that, just to illustrate the mutliple resource concept I can create a new resource and I will reference the WikipediaResource from it:

Above, you can see that I can write expressions manually into the formula bar. In this case I referenced the Wikipedia resource which allows me to start this Resource with the output of the Wikipedia resource.

Now I will start to shape the data a bit (Note... I renamed the new resource to Stadia but am not showing that for this post). The first thing I will do is split the capacity column so that we can get just the capacity of the stadiums without the footnotes:

 

 I chose split by position, one can also specify delimiters instead. For this it was easier to choose position:

 

Once I have split the column out I filter by Country (looking for stadiums in England):

After this I did three more steps:

-  I changed the data type of the Capacity column to Number:

- I hid the Caveats and Year of Construction Columns

- I sorted by Capacity.

The final data set was ready to go into Excel. Opening Excel I can go to the Data tab and, since I installed the Montego Client, I get two Montego buttons. The first button launches the Montego client but we want to use the second button here. The second button allows you to import data into Excel from an existing Montego mashup:

 

 

Choosing Import Data you get a dialog where you can see all of your mashup documents. In this case I chose the FootballStadiums mashup and see the two resources I built:

 Hitting OK queries the mashup document (ODBC to our runtime with a reference to the document):

Under the covers we create a data connection. If one were to look at the connections in Excel you would see the connection:

Hitting the "refresh all" button on the refresh page refreshes the data. To illustrate that I decided to change the definition of the resource we referenced. One of the nifty things that I can do with the tasks that we use to define a resource is that I can edit them after the fact. In this  case I can go back and change the Filter condition to filter for stadiums in Spain instead of England and everything just continues to work:

I could have expanded the "builder" to reconfigure the task but instead I just highlighted the task and edited the formula in the formula bar. The formula bar always shows the formula for the selected task and you can edit it in place rather than use the builder if you so desire.

Refreshing the data in Excel yields the expected result, we see the stadiums in Spain:

 

So there we have it... we pulled in some data from Wikipedia, shaped it and then imported it into Excel. Excel can refresh the data as if the mashup were just any other queryable data source.

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
  • Tim,

    With every blog post I get more and more impressed. I can't wait to get my hands dirty with this stuff.

    @jamiet

  • @Jamie

    Awesome... we are really eager to get the bits in the hands of folks so that we can get more feedback and start delivering something that people will find useful.

  • Tim,

    Excellent, I can't wait. I've posted some more thoughts here: sqlblog.com/.../thoughts-on-data-explorer.aspx in case you're interested.

Page 1 of 1 (3 items)