Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

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

with 7 comments

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.
    2010slicer   
  • 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:
    SimpleSet
    Here’s the MDX set UI:
    MDXSets
  • 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:
    2010sparklines

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?

Written by Chris Webb

July 17, 2009 at 12:14 pm

Posted in Excel

7 Responses

Subscribe to comments with RSS.

  1. Chris,Nice review. dynamic sets and writeback do work in this CTP, see my personal (p)review on http://www.vandeputte.org/2009/07/excel-2010-personal-sneak-preview.htmlFrederik

    Frederik

    July 17, 2009 at 1:42 pm

  2. Thanks Frederik, I didn\’t know you had a blog!

    Chris

    July 17, 2009 at 3:12 pm

  3. Hi Crhis,Is it possible to save calculated members and named sets outside the document (ie, sharepoint library) ?. I remember the very useful option of sharing sets and calculations in Proclarity. I think that MS team want to centralize business logic within SSAS model but sometimes , users must be able to create their own calculations before asking for the requirement.

    Leandro

    July 17, 2009 at 9:50 pm

  4. No idea about that, I\’m afraid Leandro. If I find out I\’ll post here…

    Chris

    July 19, 2009 at 8:59 pm

  5. Chirs does this allow multiple sets. I know in Excel 2007 has a limitation of selecting multiple sets. We had to write our own excel add-in to over come this issue.

    Nagendra

    July 22, 2009 at 3:28 am

  6. Hi Cris! Very Interesting post…Also add some content related to this new versionhttp://pedrocgd.blogspot.com/2009/12/bi-tools-first-look-to-excel-2010.htmlRegards,Pedro

    Pedro

    December 19, 2009 at 12:59 am

  7. [...] time we could use Tableau (see Jen’s session in SQLBits 8), Strategy Companion, CubePlayer or Excel 2010. In 11g OLAP usrs can use Answers to browse the cube, which provides basic [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,146 other followers

%d bloggers like this: