Archive for July 2012
One thing that piqued my interest when looking over the new functions in Excel 2013 were the new functions available that allow you to retrieve data from a web service directly into worksheet: EncodeURL(), Webservice and FilterXML(). Here’s a brief demo of how to use them.
First, find a web service that returns XML and doesn’t require any authentication (I say that because I haven’t worked out how to pass usernames and passwords with these functions yet – I hope it’s possible, and if/when I work out how to do it I’ll blog again). The example I’m going to use is the BBC weather web service, which allows you to subscribe to an RSS feed containing the weather forecast for a particular postcode (similar to a zip code for my US readers). Here’s an example URL which returns the forecast for my home, which has the postcode HP6 6HF:
Let’s now build an Excel 2013 spreadsheet that allows you to enter a postcode and then displays the weather forecast for it using this web service. First of all, I’ll specify cell E2 as the place to enter the postcode:
Next, in cell D4, I need to construct the URL for the web service and retrieve the data like so:
=WEBSERVICE(“http://open.live.bbc.co.uk/weather/feeds/en/” & ENCODEURL(E2) & “/3dayforecast.rss”)
Here, I’ve used the EncodeURL() function to URL encode the text entered in cell E2, and then dynamically generated the URL and passed it to the Webservice() function which simply retrieves the response from the web service. If it’s successful you should see the XML returned displayed in the cell:
If it’s not successful, you get a #Value error:
Finally, you can get values from the response and display them in cells by using the FilterXML() function, which allows you to query the response using XPath. Here are some examples of how you can use FilterXML():
And here are what these three formulae return when placed in cells D6, D8 and D9:
UPDATE: I have had official confirmation from Microsoft that the WebService() function will only work with services that do not require any authentication, which is a shame – I was hoping to use it with things like the Bing Search API and the Microsoft Translator API. Hohum.
In yesterday’s post I showed how you could create surveys in the Excel 2013 Web App, and mentioned that I would have liked to consume the data generated by a survey via the new Excel Services OData API but couldn’t get it working. Well, after a good night’s sleep and a bit more tinkering I’ve been successful so here’s the blog post I promised!
First of all, what did I need to do to get this working? Well, enable Excel Services for a start, duh. This can be done by going to Settings, then Site Collections features, and activating Sharepoint Server Enterprise Site Collection features:
With that done, and making sure that my permissions are all in order, I can go into Excel, start the OData feed import wizard (weirdly, the PowerPivot equivalent didn’t work) and enter the URL for the table in my worksheet (called Table1, helpfully):
Here’s what the URL for the Survey worksheet I created in yesterday’s post looks like:
(there’s much more detail on how OData requests for Excel Services can be constructed here).
And bingo, the data from my survey is loaded into Excel/PowerPivot and I can query it quite happily. Nothing to it.
In a way it’s a good thing I’m writing about this as a separate post because I’m a big fan of OData and I believe that the Excel Services OData API is a big deal. It’s going to be useful for a lot more than consuming data from surveys: I can imagine it could be used for simple budgeting solutions where managers input values on a number of spreadsheets, which are then pulled together into a PowerPivot model for reporting and analysis; I can also imagine it being used for simple MDM scenarios where dimension tables are held in Excel so users can edit them easily. There are some obvious dangers with using Excel as a kind of database in this way, but there are also many advantages too, most of which I outlined in my earlier discussions of data stores that are simultaneously human readable and machine readable (see here and here). I can see it as being the glue for elaborate multi-spreadsheet-based solutions, although it’s still fairly clunky and some of the ideas I saw in Project Dirigible last year are far in advance of what Excel 2013 offers now. It’s good to see Microsoft giving us an API like this though and I’m sure we’ll see some very imaginative uses for it in the future.
Jamie Thomson and I share a number of… obscure enthusiasms. For instance, last week when he spotted the new forms/surveys feature in the Excel 2013 Web App (see here for a mention) he knew I’d be excited. And I was. Excited enough to devote a whole blog post to them.
What is this feature? Basically a rip-off of homage to the Google docs functionality I mentioned here that allows you to create simple questionnaires and save the data back to a spreadsheet. To use it you need to create a new Excel spreadsheet in the Excel Web App (I can’t seem to find it in desktop Excel and it may not even exist there), then click on Form/New Form in the ribbon:
This opens a new dialog where you can create your form/survey:
It’s all pretty self-explanatory from there, you just enter a title and description and then some questions, which can be various types (returning text, numbers, multiple choices etc):
You can then answer the questions yourself or send a link out to other people so they can too:
If you’d like to take the survey you can do so here btw.
The data then lands in a table in the original Excel spreadsheet, ready for you to do something useful with it:
For my next trick, and to go back to another issue that Jamie and I have been moaning about for years, I would have liked to consume the data in this table via an OData feed as detailed here:
Unfortunately I couldn’t get it to work. Whether this is a temporary problem or a limitation with Office 365 (as opposed to on-prem Sharepoint) I don’t know… if someone knows how to make it work, though, I’d be much obliged if you could leave a comment.
UPDATE: First of all, if you can’t see the survey don’t worry – the service seems to be very unreliable. Secondly I’ve got the OData feed working now and will blog about it later.
In part 1 of this series I showed how to build a BI dashboard with UK weather data in Excel 2013. What I’m going to do now is show, very quickly, how this dashboard can be shared with other people via a web browser.
Before I do that, though, an aside: I found a bug in the preview version of Excel 2013 which means that the dashboard I built in the previous post won’t work with what I’m going to describe here. It’s related to how I imported the data – in my last post I imported data from the Azure DataMarket from the Data tab, and what I’ve had to do for this post is import data from inside the PowerPivot window instead. In a way it’s a good thing I did this because the UI for importing data from the Azure DataMarket in PowerPivot is much better than what I showed before (although it too is quite buggy at the moment). No more fumbling around for account keys and OData queries in the way that Rob Collie complains about here, it’s all handled from within the wizard. All you need to do is pick a data set:
And then check the tables you want and optionally apply filters to certain columns that support it:
Anyway, with my workbook rebuilt, the next thing I’m going to do is save it. There’s actually a lot more to saving in Office 2013 than you might expect, and certainly lots of different options for places to save stuff too, so I recommend you read this post to get a feeling of what’s possible. I’ve signed up for the Office 365 Professional Plus Preview which means I have access to all kinds of cloud-based services including Sharepoint in the cloud, so I can save my workbook up to Sharepoint:
I can then go to my Sharepoint site on another computer and view and interact with my dashboard using Office Web Apps in the browser:
Yes, that’s my Power View dashboard in a web browser – and I can change filters and it all works! Here’s my PivotTable:
I can not only drill down, but I can even change what’s on rows and columns by dragging and dropping fields in the Field list in the usual way. It’s a bit slow and (again) a bit buggy at the moment, but by RTM I can see this being a big selling point: anyone with Office 365 will have in place everything they need not only for BI on the desktop, but for sharing their reports over the web (though data refresh may be a problem here). I’m very impressed, and it’s good to see such a strong answer to the question I asked here last year.
Finally, the question I know you’re all dying to ask. Does this work on an iPad? Well, yes and no. The PivotTable works properly and there’s no loss of interactivity, although I’d say that the touch interface doesn’t work all that smoothly yet:
Unfortunately the Power View sheet doesn’t work at all – no Silverlight!
Oh well, maybe that was too much to ask for… this is clearly not the Microsoft mobile BI solution that was promised at PASS last year. That’s not to say it isn’t useful though – there’s still some good stuff you can do with PivotTables and regular Excel sheets. I’m still very happy with what’s been delivered so far!
nsurprisingly there’s been a lot of interest in Office 2013 since it was announced yesterday, and I’m certainly very excited by all of the new BI features in it. Luckily I wasn’t working today so I had the chance to have a proper play with it, and I thought it would be useful to walk through the process of building a simple BI solution in Excel 2013 to show off some of the new features.
Let’s start with a blank workbook:
Some people don’t like the new look for Office, but I quite like it. You can even set custom backgrounds: you may just be able to see some wispy clouds in the top right-hand corner of the screenshot above. But anyway, to business. To import some data, I first need to go to the Data tab as normal:
As you can see there are some new options available here, and I’m going to go to the Windows Azure Marketplace. Actually, I’m going to cheat a little and just say that I’m going to import the UK weather forecast from 12:00am today (July 17th) along with some related tables as described in this blog post. The UI for the import wizard is plain but functional:
Interestingly, the data is always imported at the end of the wizard even if I check the ‘Only Create Connection’ option on the last step of the wizard.
Once the data from all the tables has been imported, I need to specify some relationships. I can either do this by clicking on the Relationships button in the Data tab or (better still) going to the PowerPivot window and using the Diagram View. Now as I said yesterday, xVelocity in-memory database is now integrated into Excel but I still need to use the PowerPivot addin in some cases; The PowerPivot addin comes bundled with Excel 2013 but isn’t enabled by default, so in order to use it I first need to enable it; the steps to do this are detailed here. I can then click on the Manage button in the PowerPivot tab to open the PowerPivot window:
And then build some relationships between my tables in Diagram View, which can be reached by clicking on the small Diagram button in the very bottom right hand corner of the PowerPivot window. Once in the Diagram View, building relationships is simply a matter of dragging one column onto another:
I can also build hierarchies here; Duncan Sutcliffe shows how to do this here, and also how to use hierarchies in Power View which is something new. I’ve created a hierarchy going from Region to Weather Station.
With that done I can of course build a PivotTable. The first thing I’ve noticed is that there isn’t now a separate type of PivotTable for PowerPivot, which is kind of a shame because this means that you don’t seem to be able to create Slicers in the Field List any more, and have to go up to the Ribbon. It’s good for consistency though I suppose. Measure aggregation and number formats are all handled in the Value Field Settings dialog:
There’s a new type of Slicer available too for dates called the TimeLine which Raphael has a lot of good detail on here.
Here’s what the new Quick Explore option, which provides some options on where to drill to and for charts to create, looks like when I hover over a Region on Rows:
Really, though, the thing to do is to create a Power View sheet. This can be accomplished by going to the Insert tab and clicking the Power View button:
This is where things get exciting. Power View in Excel now supports maps and because my weather data contains the latitude and longitude of all of the weather stations in the UK it’s very easy to plot each weather station on a map and visualise the temperature and predicted weather for each station. To do this I just need to drop the Region Name column onto the Filters selection (choosing a region reduces the number of Weather Stations displayed down to a manageable number for the purposes of this demo), then drop the Latitude and Longitude columns onto the main canvas, turn the resulting table into a map, and then set the size of the markers to show temperature and the colour to show the type of weather:
I’ll be speaking at SQL South West this week so what’s the weather like down there at the moment?
From this I can see that today there’s fog in the Scilly Islands (shown by the green marker in the bottom left hand corner) and that it’s very cold and foggy in Liscombe (shown by the small red marker in the middle of the map). This dashboard was ridiculously easy to create, by the way, and I am already completely in love with Excel 2013 simply because of Power View – it’s a killer feature in my opinion.
At this point we’ve got a working self-service BI solution, made very quickly in Excel 2013 and it’s something that every competent Excel user would be able to achieve.
In Part 2, you’ll see how you can share this solution with your co-workers in the Office Web App.
As you may already have read, the first public preview for Office 2013 is now available and there’s lots of new BI functionality in there – see here for an overview. Here’s a quick summary of what the two really important changes are:
- PowerPivot has been integrated into Excel, kind of. This means that the xVelocity (aka Vertipaq) engine is now native to Excel, and you can do all the basic PowerPivot stuff like loading vast amounts of data from multiple data sources and querying it via PivotTables directly in Excel, without installing any extra addins. PowerPivot does still exist as an optional extra however: you need it if you want to use the more advanced functionality that exists in PowerPivot today, such as filtering data before import, using diagram view, defining hierarchies and perspectives and so on.
- Power View has also been integrated into Excel: Power View reports become a new type of sheet inside a workbook, and you can use it against data held in the integrated xVelocity/PowerPivot engine; I’m not clear yet whether it will work on a SSAS 2012 Tabular model (and at some point a SSAS Multidimensional model, once support for DAX on Multidimensional models arrives) but I hope it does. No more need to moan about Power View being tied to Sharepoint!
There are a whole bunch of other BI-related changes in Excel which I’ll try to summarise in another post soon (stuff like the suggestions for charts and PivotTables, flash fill, timeline slicer). However I think that the two changes above represent a master-stroke on the part of Microsoft: they make Excel 2013 a serious contender in the self-service BI tool stakes. Certainly, other vendors will be quick to point out the features they have and that Excel doesn’t, and dedicated BI vendors will always be able to add new features faster and more frequently than Excel, but that’s not the point. It won’t happen overnight but at some point every company will upgrade to Office 2013 and when they do, all users will have a BI tool on their desktops which is vastly more capable than Excel today and will be good enough for the majority of BI scenarios – which means that the need to even look at third party tools will disappear.
UPDATE A few clarifications:
- The PowerPivot addin, while still an addin, comes bundled with Excel – there’s no separate download
- As the comments below show, and I can confirm, Power View does work with SSAS 2012 Tabular models
I’ll be posting more details throughout the day on Twitter as I play with the new preview, and will post something more substantial here later
I’m pleased to announce a new addition to the Technitrain course catalogue: along with Bob Phillips (a UK-based Excel MVP) I’ll be teaching a PowerPivot course in London this October. Full details and registration can be found here:
The course is the one written by Marco and Alberto to tie in with their excellent book “PowerPivot for Excel 2010: Give Your Data Meaning”, and is suitable for BI professionals and Excel professionals. It costs £499 + VAT if you book before August 31st, and £549 + VAT after that.
Other courses coming up this autumn include:
- Andy Leonard’s SSIS 2012 course in September
- Me on SSAS 2012 Tabular in Dublin in September
- Jen Stirrup on Power View and Data Visualisation in Dublin, immediately after the SSAS Tabular course
- Christian Bolton on SQL Server engine performance tuning and troubleshooting, in London in October
- Me on MDX in London in December