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.
