One of the impressive demonstrations at the SQL PASS Summit in Seattle last month was that of PowerPivot for Microsoft Excel, a new integration feature of SQL Server 2008 R2 and Office 2010. The demo would access 100 million rows of data from a cube with interactive sorting within Excel 2010 being performed in a matter of seconds.
It was not too long ago that Excel had a 100,000 row limitation. Now that Microsoft has realized that Excel is the front-end tool of choice for our Business Analysts, the upgrade in technology is impressive. The bar has been raised by using column compression technology which takes advantage of repetitive column data in terms of storage savings and performance gains.
Using Excel makes sense since our analysts know a lot more about that product than we do and feel very comfortable within it. The only difference is that they are accessing data from Analysis Services rather than a local spreadsheet, perhaps a multi-terabyte cube. The Excel Pivot Table and Chart options are the same as always.
If you want to test out this functionality, use the SQL Server 2008 R2 Nov CTP and the following feature pack:
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.