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.
For real-time OLAP, Data Mining, Excel Services and related demonstrations goto http://RichardLees.com.au/Sites/Demonstrations
9 comments:
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
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).
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
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.
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.
I wish. Surely the next version will.
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
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.
"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.
Post a Comment