In addition, I’ve been using a great new business intelligence tool called PowerPivot. PowerPivot is an Excel add-in (built using the .NET Framework, for those who care) that provides a way to connect Excel to one or more SQL Server databases (and other large data warehouses) and generate pivot tables and charts. Normally, I wouldn’t be able to discuss new products prior to release, but a beta of PowerPivot has been publicly available for some time.
Prior to the introduction of PowerPivot, most people using Excel pivot tables were confined to reading data stored in spreadsheets or Access databases. This limited their usefulness since corporate data is rarely stored in these formats. You could try to connect to a large SQL Server or Oracle database using an ODC file, but I’ve always had trouble getting it to work.
The other alternative was to work directly in the SQL Server database and learn an entirely new workflow to create pivot tables. I’m a lot less comfortable using SQL Server than I am with Excel. If you’ve been intimidated by the idea of writing your own queries using T-SQL, creating your own OLAP cubes using SQL analytical services, and creating tables and charts using SQL reporting services, then PowerPivot is the tool for you. (On the other hand, if you are intimidated by the idea of using pivot tables in Excel, then unfortunately you are still out of luck.)
When you launch Excel after installing PowerPivot a new ribbon labeled PowerPivot appears on the far right.
PowerPivot ribbon in Excel
Clicking on the first button of the ribbon will open a PowerPivot window which has its own two ribbons labeled Home and Design. Starting from the Design ribbon, you can import data from a database or report and create a pivot table by clicking a few buttons and filling in some fields. Really, it is that simple.
Home ribbon in PowerPivot window
Design ribbon in PowerPivot window
If you want to try out PowerPivot, you can download it from here. You will first need to download Office 2010 Beta from here. and if you don’t have your own data warehouse, you can get sample data from here. If all this downloading is too much work for you, you can run the demo on Microsoft Virtual Lab.
And if you are the ambitious type, there is even a contest at http://www.exceleratorsquiz.com/challenge.html
PowerPivot is a big step up from my first exposure to desktop cross-tabulation software. Over ten years ago, as a market research consultant, I worked on a software project called Polk Marketing Workbench. It was also an Excel add-in. But it was written in VB6 and was slow. It was also buggy and frequently hung or crashed. When that happened on Windows 98, it required rebooting, which made the experience even more agonizing. We were actually selling this tool to our clients, but as you can imagine, customer satisfaction was really low.