Sunday, October 11, 2009

What's preventing Excel from being the ubiquitous cube browser

I have said earlier that Microsoft’s Excel 2007 is not the ubiquitous cube browser that it should be. What’s wrong with it? Well nothing is really wrong with it, but there are several features, that I would expect from a product that is 10 years old. With OLAP services first release 1998, there was an update for Excel PivotTable. As I understand it that first version was largely the result of one great developer (TC). However, since that release, the upgrades have been unimpressive. Excel 2007 is a great improvement on Excel 2003, but it still has a long way to catch up to the sophistication that is available in Analysis Services. Here are a few of the things I would like to see in Excel

  • Full support for calculated members. They are partially supported, calculated measures are OK, but other calculated members are treated as second class citizens.

  • Ability to create calculated members that are stored within Excel, and available like any other calculated member for that user.

  • Support for 3 dimensional queries. Currently, a user can only ask for a two dimensional report. Excel, is inherently a 3 dimensional space when you include worksheets. So I would like to be able to create a PivotTable with a dimension placed on the worksheet axis.

  • Support for naive users who open up a dimension member list with a million members. For example, ask them if they really want the one million rows (columns) and perhaps defaulting to a top 100 list.

  • More optimised MDX. Sometimes I despair with the performance of pivottable queries, and when I look at the MDX I see that it could run quickly with more efficient MDX.

  • Ability to see properties of ancestors

  • Better integration into Excel spreadsheets. For example, ability to format measures and PivotTable to remember these, even after drill down, nesting etc.

  • Ability to define sets.

  • Eposure to some set functionality in a gui fashion. For example, it might allow the user to select a crossjoin on rows with an exception function to exclude some members based on a filter criteria.

  • Ability to right click on members to “select only” or “deselect”

  • More control over charts. For example mixing the dimensionality of measures so that one measure is cumulative on series and the other is unrelated to the series.
Don’t get me wrong, even with this wish-list, I am a great fan of Excel PivotTable. As a rich client cube browser, it is still my favourite. It’s just that it isn’t keeping up to Analysis Services, which is truly a market leader in the OLAP field. In a later blog, I will write about my favourite features of Excel pivottable, many of which are not immediately obvious.

For real-time OLAP, Data Mining, Excel Services and related demonstrations goto http://RichardLees.com.au/Sites/Demonstrations
at

9 comments:

  1. Nathan GriffithsOctober 12, 2009 at 7:18 AM

    Hi Richard, regarding your comment that KPIs are not supported in Excel, could you elaborate on that? My understanding was that they are supported and displayed in a special "KPIs" folder.

    Regards, Nathan

    ReplyDelete
  2. Richard LeesOctober 12, 2009 at 4:35 PM

    Thanks Nathan,
    You are correct, Excel does support KPIs. It just doesn't support the hierarchy.

    I think I forgot about Excel's KPI support since AS KPIs are not supported by SQLS' Reporting Services, which is a big shame (but totally unrelated).

    ReplyDelete
  3. Josh RobinsonOctober 16, 2009 at 4:11 AM

    Great ideas Richard! I share your frustation with regard to named sets, and it appears that they are addressing it in Excel 2010:

    http://blogs.msdn.com/excel/archive/2009/10/05/pivottable-named-sets-in-excel-2010.aspx

    ReplyDelete
  4. UnknownOctober 16, 2009 at 9:59 AM

    Hi Richard, I couldn't agree more with you. I also wish Excel could support multiple filters on one field. e.g. value between X and Y and not equals to C etc. Another thing is to pass values to SSAS, e.g. today's date, last 7 days etc.

    Hope Excel 2010 could address these limitations.

    ReplyDelete
  5. UnknownOctober 21, 2009 at 1:09 PM

    Hi Richard. Is it possible to write your own mdx to execute in Excel? I would like this or at least to have the ability to see the mdx that Excel has created without using the Profiler.

    ReplyDelete
  6. Richard LeesOctober 21, 2009 at 6:46 PM

    I wish. Surely the next version will.

    ReplyDelete
  7. PedroJanuary 14, 2010 at 9:47 AM

    Hi Richard!
    You have a fantastic blog here!
    Well this post itr's interesting... but I dont think that creating calculated members could be good... I think it'll good to create more information islands...
    take a look at this dashboard i created in Excel 2010:

    http://pedrocgd.blogspot.com/2009/12/bi-tools-first-look-to-excel-2010.html

    Regards,
    Pedro

    ReplyDelete
  8. Richard LeesFebruary 19, 2010 at 6:31 AM

    Rob, thank you for your comment. Point one is a good one and I should add it to the Excel tips.
    Regarding point 2, yes you could install an add-in, but my point is that Excel should support this feature out of the box.

    ReplyDelete
  9. MartinIstiFebruary 14, 2012 at 3:09 PM

    "What about this method: right click on a member, choose filter in the context menu, and there are the "hide selected items" and "keep only selected items" filter options."

    It's promising, I was quite happy when I found it in Excel but when it tries to extract data from a cube it's slow. Very slow, no user can wait that much. Excel is using the CPU almost a 100% and the app itself seems freezing without showing any signs of life. I might know (or hope) that it's doing something but user won't care. He just wants data in a few seconds and when he selects only a few members this requirement is quite justified I think.

    ReplyDelete