After my gushing posts about Excel 12 BI a month or so ago, I was co-opted onto the Office 12 beta program with the invitation to blog all I want about it. So… having downloaded the beta bits and installed them, here’s the first in a series of posts on my experiences with Excel 12. I suppose this makes me a willing tool of the evil Microsoft hype machine (maybe if I blogged about games consoles, exotic holidays or stock tips people would try to bribe me with something better than beta testing) but hey, I’m sure you’ve already worked out that I sold my soul to billg several years back!
My first pleasant surprise came with the installation – it was freakishly fast. Having wasted hours installing various versions of SQL2005 on my machine over the last year or so, Office 12 seemed to install in about 5 minutes. Opening Excel it wasn’t hard to get to grips with the new interface and I could appreciate the benefits: it’s a lot more visual, and not having to make your way through several levels of nested menu items does make it faster to use.
Creating a connection to a cube was pretty easy, much better than the wizard of previous versions, and I created a pivot table. Then came my first disappointment. As with the pivot tables we’re all familiar with, Measures are treated as a special case and can only be dragged into a ‘Values’ region on the pivot table rather than put on Rows or Columns directly. After a bit of searching I came across a property which let me move my measures to where I wanted them, but I really don’t understand why this is so difficult; no other AS client tool has this problem. Similarly, when selecting members you’re still only given the option to view a hierarchy as a treeview starting at the All Member and then drilling down to the members below; most, if not all other AS client tools (including SQL Management Studio and BI Dev Studio) also give you the option to see a treeview consisting of the levels of each hierarchy, so you can either select a whole level or expand it and select members from it. Admittedly in AS2005 the fact that you’ll have lots of single level attribute hierarchies in your cube makes this slightly less of an inconvenience, but it’s still pretty irritating, and a bit of a step backwards from the functionality offered in Microsoft’s existing Excel addin.
However, once I’d run my first query there was a whole load of good new functionality to enjoy. For example, under Field Settings/Show Data As, you can switch between showing the actual values of your measure and various calculated values such as percentage of column – meeting a very common requirement and one which MDX can’t handle well, as this recent newsgroup thread shows. Displaying member property values is handled nicely, and the filtering/sorting functionality available on member names, member property values and measure values is very good indeed – possibly better than in any other AS client tool I’ve come across – although I didn’t seem to be able to filter on the values displayed using ‘Show Data As’, only the real values. Then there’s the formatting functionality, already well covered in the Excel 12 blog and again very good indeed. Query performance seemed ok, in fact better than some other client tools on my test cube, and browsing hierarchies with large numbers of members was not a problem.
Like other Excel addins for AS, you can also convert your pivot tables into a set of formulas which return member names and values. I don’t have an installation of the existing Excel addin handy, but from what I remember there are several important improvements here: you can now use formulas to construct the parameters you pass into these functions, eg such as =CUBEMEMBER("localhost MyDB","[Product].[MyUserHierarchy].[Category].&[" & H12 & "]"); there are also some new functions which allow you to declare named sets using whatever MDX you like and then pick members out of them, so that these members can then be passed into the other functions.
Obviously this being beta 1 there were some things that didn’t work. I don’t know whether it was because I had to install Excel on a Windows 2003 box and work via Remote Desktop Connection, but the charts looked like they’d been generated on a ZX Spectrum. There were also rather interesting Group/Ungroup buttons which didn’t work… I wonder if they are for creating custom groups of members? Overall though, the build seemed stable and the BI features worked well.
What next? I need to download and install the documentation (!) and Excel Services so I can check out how easy it is to create BI dashboards for the intranet. This post was only intended to convey my first impressions so as I learn more I’ll make sure I blog about it, and if I’ve said anything so far which is wrong or inaccurate (which is certainly possible) I’ll be happy to issue a correction. If anyone has any BI-related Excel client features they’d like me to check out then please leave a comment and I’ll do my best to oblige. I’m impressed with what I’ve seen so far; maybe the BI community can exert a bit of people power and lobby to get the less good features changed, so that Excel 12 fulfills its promise.
Discover more from Chris Webb's BI Blog
Subscribe to get the latest posts to your email.
Chris, my understanding (from the Sep\’05 SQL PASS Conference) is that Excel 12 still does not support a user-entered MDX query. If true, will this be a significant issue?
I\’ve just asked and found out that no, you still can\’t enter your own queries for Excel 12 pivot tables, although the dev team are aware of it as a much-requested feature.Is it significant? Hmm, well, I can see why it would be useful but I\’ve never been one of those people who see it as an absolute must-have feature. It is useful for creating sets of tuples on visible axes that you can\’t do with simple crossjoins, and that\’s a valid need, but I bet that 50% of the reason why it was requested in AS2K was so that people could use NonEmptyCrossjoin to do filtering and that should no longer be an issue with AS2005. One other possible reason is that people want to be able to specify their own session or query-scoped calculated members, and I believe that will be possible in Excel 12 using whatever MDX you want. If you want to use the AS-specific Excel formulas instead of pivot tables you can get much of the functionality you need – although of course you lose the ability to drill-down and other stuff. As I said in my post, you can create and reference named sets with these functions and that will be useful for creating more dynamic reports.
We are building a budgetting and planning application which are due now using excel xp and as 2000 with writeback. We have maid some additional allocation methods but we have some trouble in "controlling" excel as bi front end.One of our problems is not being able to use MDX – we would like dimensions to be maintained in our application where the user makes some choices and when opening a budgetting form this form should make a pivottable layout according to these choices. There we cannot rely on having made the total layout once before. Perhaps this is a special way to use Excel so tomorrow we are actually discussing with others whether to get rid of excel as the budgeting front end and built one ourselves – or perhaps use owc.That wasn\’t a question – that was just to argument on the need for mdx. A question could be what have happened with write back features in the new version. Nigel is not expecting the performance as TM1 – do you have any info ?We haven\’t gone to production yet and we don\’t have business cases yet were we need to distribute amounts on millions of combinations – but we dread this area.And then there\’s the OWC – this has some good features – will this be put in the background with excel 12 ?
\’Does writeback perform?\’ is the million-dollar question, and I\’m afraid I don\’t know yet. It\’s probably safer to try to design your cube to avoid this scenario.I\’ll ask about what\’s happening with OWC though.
Interesting reading – especially because I have seen a product from a Gold Certified Microsoft Partner, build inside the SQL Server using analysis services. The product is named Executive Suite and a lot of the Top-10000 companies in Europe are using it for forecasting, budgetting, consolidation, scenarios (what if) and predictive analyze. The Executive Suite can be used with Reporting Services, Targit, Executive Viewer etc. We (our company) are running SAP and Oracle and we have decided to use Executive Suite for our future budgetting. You can find the product here: http://www.cip-software.com (they are located in Denmark) and they have made full integration into SAP, Axapta, Navision etc.
We have recently made setup for using Executive Suite (they have developed REAL fast write back). I have learned that ROSS Inc. has resources and programmers for the Executive Suite. We are using Axapta but they do not have a real budgetting module and the Executive Suite is a WEB-Application running on a central SQL Server (2005). They have made build-in consolidation and fantastic budgetting system. We have bought the system from CIP Software in Denmark.www.cip-software.com
Executive Suite can handle MDX, write-back, automaticly building cubes for new scenarios etc. Executive Suite is the fastest and (compared to huge Excel solutions) cheap for organisation who wants to do their budgeting process within a few days. The module (budgetting process management) is fantastic and from a CFO / managers perspective, Executive Suite is not only a standard (Microsoft Analytical Application) but also build on .Net technoloty and Microsoft technology all the way – running in a browser. The application is also an award winning application, awarded for the best integration into Office etc. Please feel free to contact me or visit our webpage: http://www.cip-software.com
Wow, three posts extolling the virtues of CIP Executive Suite from three different people within fifteen minutes? This must be the first recorded sighting of Analysis Services-related comment spam…
Indeed – that looked a lot like spamming….:)
Did you get any info on OWC – to my knowledge OWC is actually use as the new server cube browser tool but that doesnt mean that it will be suupported as much as a client tool.
Hi Michael,
Although I asked the question, I never did get an answer… I\’ll try again. I can\’t think they\’ll drop OWC but I\’m not sure it\’ll get the upgrade that (as you point out) it desperately needs.
Hi again
Rumous from reliable source now states the death of OWC in 2007…?
Any word on whether it will be replaced with something better?
I believe the word is that it will be solved using excel / rep s. So perhaps excel services can do what owc does on share point – I havent\’ tried it so I can\’t tell…..
Does Excel 2010 allow users to set the order that Member Properties are presented? My user\’s like to see additional meta-data about the information they are viewing. For example, when viewing the profitability of a project, they like to see the project code and it\’s start and end date of the project. Unfortunately when the user selects "Show Properties in Report", Excel 2007 shows the Member Properties in alphabetical order which means that the "End Date" appears before the "Start Date" column and the "Project Code" column is last rather than first. Will Excel 2010 provide control over this feature?Excel 2007 allows users to view all the Member Properties of a dimension as columns in