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.

Installation

As usual finding the product key is the hardest part;-) Apart from that installation is a snap: next, next, ... finish.

Feedback

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

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

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.

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.

Writeback

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!

Conclusion

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.

PTPower - review

I recently ran into PTPower .  PTPower is a free Excel add-in which fixes 3 topics on my personal Excel/Analysis Services features whislist:

• An easy way find the underlying MDX SELECT statement of an Excel Pivot table
• Smoother development and debugging of calculated members
• Add calculated measures to an pivot table on the fly from Excel

PTPower is exposed as an Excel 2007 Add-In - within the "PivotTable Tools" Ribbon.

 

 

Just click the Show MDX button and PTPower will show you a popup window with the underlying MDX statement of the pivot table.  The MDX Statement is color highlighted and ready to be copy/pasted in MDX Studio, Report Builder, ...  Very simple, but just works :-)

The second feature is even cooler.  Developing and debugging calculated measures in BIDS is a bit cumbersome.  Most of the time I use the script view in BIDS for that. For every change in the MDX statement you have to deploy the cube (or at least the MDX scipt using BIDS Helper) and switch back and forth to the cube browser or Excel to see the result.

PTPower allows you to build and test your calculations from Excel using a nice GUI.  When you click the Calculation button a GUI similar to the calculation tab in BIDS pops up. Write your MDX calculation, click Save, click Close.  Excel will refresh your pivot table and you will see the results immediately.

 

 

Calculations created via PTPower are implemented as session members.  This means they are not saved to cube, but only available in the current session.  When you save the Excel file and reopen it, PTPower will recreate the session members.

And  this is very cool, because it allows end-user to add their own calculations from Excel.  Altough they will need to learn MDX.  Which might be a bit challenging for most end-users ;-)

Anyway, PTPower is nice addition to my Analysis Services toolkit. And is tooltje van de week.