I downloaded the Technical Preview for Office 2010 a few days ago, and was intending to blog about it as soon as possible but a few things made me wait a bit. First of all, there was the problem of whether I was in fact allowed to blog about it at all (which a lot of other people also seem confused about – which explains the strange silence on the web about it, perhaps); then there was the problem of actually finding what the new functionality was, since the Office team had neglected to provide any kind of detailed list of what has been added. Hmm. I think they’re planning to blog about everything that’s new soon though.

But anyway, now I’ve been given the go-ahead to blog I thought I’d list all of the new features I’ve found that are relevant to Analysis Services users. This does not include anything to do with Gemini, because Gemini isn’t part of the Technical Preview and I don’t have it yet unfortunately. I’m also not going to comment on bugs or things that don’t work in the way I’d want because, after all, this is not released software and things can and hopefully will change before RTM.

• Slicers. If you’ve seen any of the Excel 2010 or Gemini demos you’ve probably seen that pivot tables can now have large slicer windows which make it a lot easier to select members (and see what has been selected) on the slice axis of a query. In terms of query functionality, as far as I can see they do exactly the same thing as the old single-cell dropdown-box-based slicers, created when you dragged a hierarchy into the Report Filter box, do. You can control their position, size and shape and almost every other aspect of their appearance.
     
• Named sets. Ideally I’d have liked to be able to specify any MDX query I wanted for use in a pivot table, but this is the next best thing: it allows you to create your own named sets either using a simple UI or by (hooray!) entering your own MDX set expression; you can then use these sets wherever you want in your pivot table, for example on the rows or columns axis. This then means you can set up much more advanced selections than you ever could before, and is going to be incredibly useful for BI developers when creating dashboards in Excel. It might also allow for integration between Excel and other SSAS client tools.
  Here’s the simple set UI:
  
  Here’s the MDX set UI:
  
• Show as. A lot more calculation options have been added to the ‘Show as’ feature in pivot tables; Thomas Ivarsson has already blogged about this here. 
• Writeback support. According to the Excel blog we’ll get support for writeback in Excel, at last. Not tested this yet though.  
• Dynamic named sets. Again, according to the Excel blog dynamic named sets will work with 2010 (they didn’t with Excel 2007).
• Search. There’s now a Search feature that allows you to find specific members quickly (although again I’ve not tested this on a really big hierarchy) in the slice dialog. It’s not available in the Slicers mentioned above yet, but I assume it will be.
• Sparklines. Again, if you’ve seen any of the Excel 2010 demos you’ll have seen that at last they’ve implemented sparklines. This isn’t of course an SSAS-specific feature but anyone who’s creating BI dashboards in Excel will want to use them. Here’s a screenshot of what they look like:
  

On a related note, I see Panorama have put out a press release saying how closely they’ll be working with MS, Office 2010 and SQL 2008 R2. It doesn’t say anything meaningful directly, but it’s nice to see that MS and Panorama are friends again and that MS sees value in what Panorama have to offer beyond the pure-MS BI stack; I wonder if MS are coming to regret what they did to Proclarity?

Following on from my blog entry on Kapow the other week (and Jamie’s post on the same subject), I’ve just stumbled on something called the Excel 2007 Web Data Addin, something that Microsoft Research put together. It’s basically an addin that improves on Excel’s built-in functionality for importing data from web pages; it falls a long, long way short of what can be done with Kapow – and it’s a bit buggy - but it’s still interesting. There’s not much to it, or indeed much information out there about it, but here’s the link to download it and a pair of blog entries announcing its release from 2007:
http://research.microsoft.com/en-us/downloads/db5286b6-0bb0-4668-9ebc-c3e9b43a0683/default.aspx
http://blogs.msdn.com/xaw/
http://blogs.msdn.com/excel/archive/2007/10/16/excel-2007-web-data-add-in.aspx

There’s also a short demo video:

I wonder if this kind of functionality will be built into Excel 2010? It would be useful from a Gemini point of view if it was.

Another thought I had when looking at this was that SSRS reports would be the obvious source of data for this kind of functionality (although the addin refuses to work with Report Viewer, I guess it would work if the report was addressed directly via its URL). It should be pretty straightforward to suck data out of an HTML report with a tool like this, and indeed we’ve been told that SSRS 2008 R2 will be able to expose report data as a feed, but thinking about this it struck me that that’s not how I’d really want to work with SSRS data at all.

Rather than Excel linking to a table in an SSRS report, or SSRS rendering a report to Excel, or even what the OfficeWriter functionality we may get in Excel 2010 does, what I’d really want is an Excel addin that works in the same way as Report Viewer: as a sophisticated client to SSRS, pulling data into a worksheet rather than having data pushed to it. I’d want to be able to connect to a report from a worksheet, then be able to enter parameters from within the worksheet (either using dropdown boxes, like filters in a pivot table, or by entering values directly into cells, with me being able to choose which cells held the parameter values), and then when I clicked Refresh have the data from the report brought straight into one or more Excel tables or graphs, with pagination working too. All in all it would work in a similar way to the Excel Cube functions and make it much easier to build applications in Excel based on SSRS data. SSRS would be reduced to the role of running queries, handling some calculations, and doing caching; the actual layout of the report would be controlled from within Excel. Maybe it’s something that could be built by the community, or by MS as a sample app? It wouldn’t be much work to develop, I think, although the problem would be that you’d want to be able to use the RPL rendering format that Report Viewer users (mentioned by Robert Bruckner here) and that’s not publicly documented.