Twitter Updates

    follow me on Twitter

    Wednesday, July 15, 2009

    Excel 2010 - a personal sneak preview

    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.

    image image

    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

    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.

    image image

    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!

    image image

    image image


    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.


    Unknown said...

    You should have a look on MicroCharts and XlCubed. Most of the new features mentioned are already possible with older Excel versions using these addins said...

    Link to this post was added to our webiste in the [SSAS Articles]/[Excel] section.

    F. L. said...

    Thanks man for your personal sneak preview ! Great sharing.


    Anonymous said...

    I haven't been able to get a version of the Excel 2010. We also use Excel 2007 extensively with Analysis Service Cubes for reporting but have found issues. Can you tell me if they have fixed the Drill-through action so that it displays the name of the measure or dimension attribute instead of the uniquename (attribute name and value)? Also, if you are using a report filter and select more than 1 value, does the drill-through action work, it doesn't in 2007? Finally, can you use more than 1 value filter?

    Frederik Vandeputte said...


    - Drill-through action still displays the of the uniquename.
    - Also, if you are using a report filter and select more than 1 value, does the drill-through action work, it doesn't in 2007? = > same behavior on 2010
    Finally, can you use more than 1 value filter: same behavior on 2010.

    So no changes, at least not int he current technical preview.

    Pedro said...

    Very interesting post and blog!
    I'll came back!!

    Pedro said...

    As I promise...
    Also my first look at Excel 2010...