Microsoft’s PowerPivot: Five Things You Need to Know

It boosts your business intelligence. Even the most robust business intelligence shops create only a small percentage of the reports an ­organization needs, says Rob Collie, CTO for the consulting firm ­Pivotstream. Microsoft’s ­PowerPivot lets managers execute many of the same types of queries and generate reports directly from Microsoft Office. Like a pivot table on steroids, it can pull large amounts of data into Excel from multiple sources.

It may change how you do BI. Microsoft Senior Vice President Ted Kummert sorted 100 million rows of data almost instantly before a live audience at the company’s TechEd conference. Since it runs Microsoft’s BI software on the back end, PowerPivot can do much of what a full-fledged BI application does. The best part is that it’s a free feature of Microsoft Office 2010. This means Excel power users in your organization will get to play with it, says Gartner analyst Rita Sallam.

More data is not always better. PowerPivot lets users easily create ad hoc reports to flesh out a hunch, says Andrew Brust, the CTO for Microsoft integrator Tallan. Some IT departments are wary of it as a result, says Sallam. It can allow multiple reports on the same topic to circulate at once, a problem that has already caused headaches for organizations suffering from rampant overuse use of spreadsheets. And some observers predict it will only get worse.

Good data management is a must. But PowerPivot may ease the proliferation of spreadsheets, suggests Herain Oberoi, director of product management for Microsoft’s SQL Server Business Group. Reports can be published to SharePoint, viewed by others and automatically updated as data changes. Sallam advises maintaining a repository of sanctioned data sources and metrics. If the source data has been vetted and cleaned, you can allow data mash-ups.

It won’t replace the data warehouse. Once CIOs see PowerPivot’s capabilities, they may question the need for a data warehouse. But the software doesn’t have as wide a range of features as other stand-alone, self-service BI offerings such as Tibco’s Spotfire, Sallam says. And while PowerPivot can handle lots of information, it’s doubtful if it can handle the petabytes of data some warehouses manage, notes James Dixon, CTO for BI software provider Pentaho.