What’s new for Analysis Services users in Excel 2010?

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?