For the last couple of years I have been working on quite a few projects where we used Excel 2007 as the number one front-end on top of Analysis Services 2005/2008 cubes. I am a big fan of the killer combination of Excel/Analysis Services. And I am pretty good in convincing (potential) customers of this. Recently, a customer spoke the infamous words :" It is a Revelation", when he saw me slice and dice his data in Excel. I couldn't agree less ;-)
Anyway, although I am a big fan, I must admit that I sometimes reached the boundaries of the pivot table functionality in Excel 2007. Earlier this week Microsoft released the Technology Preview of Office 2010 and Sharepoint Server. I was very eager to find out which topics on my personal feature wish list made it into the product.
In the following weeks I will play around with the bits and focus on the following topics:
- Excel as a front-end on Analysis Services 2005/2008
- Excel interaction with the upcoming fancy stuff in SQL Server 2008 R2 (Gemini, Kilimanjaro)
- Sharepoint Performance Point Services, the successor of PPS Monitoring and Analytics and the web based version of Excel
- Below you can find my *very personal* review after playing around a few hours with the Technology Preview the Excel 2010 fat client.
Below you can find my *very personal* review after playing around a few hours with the Technology Preview Excel 2010 fat client.
As usual finding the product key is the hardest part;-) Apart from that installation is a snap: next, next, ... finish.
The CTP contains a very nice feedback mechanism. When you click the happy or unhappy icon in the taskbar, a input screen pops up where you can write your feedback.
One click of the mouse and your feedback is sent along with a screenshot to Microsoft. This is a lot more convenient that the traditional Connect website.
Show 1 measure multiple times + Show value as ....
You can now drag a measure more than once to the values area, and show the value in different forms.
Even cooler: Year To Date calculations without writing MDX
Filter Dimension members in report filter
Really cool: a big time saver when filtering large dimensions.
Slicers are a great new way to filter and compare data for specific dimension members.
I was not able use them on a Analysis Services pivot table. In the screenshot I created a pivot table on an Excel range, but you get the idea.
UPDATE: Slicers didn't work on a SSAS cube because my VPC was running with Belgian (Dutch) regional settings. Switch your regional settings to US English and the slicers do work.
Sparklines are another great visualization. Sparklines are tiny little graphs, the size of a single cell. A great new way to visualize trends!
Sparklines and Slicers are shown in this little webcast
Dynamic sets (eg. Top 25 Selling Products) were a great addition in Analysis Services 2008. Unfortunately Excel 2007 didn't handle them very well. Now they are fully supported :-)
Create custom set from Excel
Named set are really great when doing data analysis. You can now create them from Excel via very nice GUI.
There is even a little GUI which allows you to write MDX. A little code highlighting would have been nice ;-)
Really cool, we can now create sets from Excel. But how about calculated measures or members? Well it looks like this will be added in later CTPs. In the current CTP the menu items (Calculated Field, Calculated Item) are grayed out. Something to look forward to.
Analysis Services has had a writeback functionality for quite some time now. This is a great feature for planning and budgeting applications because it allow end-users to validate different what-if scenario's. With Office 2010 this functionality is finally exposed in Excel. A killer feature!
After a few hours fun with Excel 2010, loud and clear: " It is a Revelation". Upgrading from Excel 2003 to Excel 2007 was like going from black and white television to color television. With Excel 2010 we get full HD digital televison on a gigantic plasma TV. I am looking forward to the upcoming CTPs and I hope that all the new stuff will make it in the RTM version.