PowerPivot: 44 Million Records in a 5MB File
At the front of a crowded room, Carmen Taglienti stood over his laptop, hovered the cursor over the "Sort" button and clicked. Instantly, some 44 million records in his Excel app were sorted, newest to oldest, top to bottom. That's right:
44 million records. Welcome to the world of "self-service BI."
Taglienti, a Microsoft technology architect, was at the monthly meeting of the New England SQL Server User Group to show off the PowerPivot for Excel 2010 add-in (formerly code-named "Gemini"). He said Microsoft developed PowerPivot to rein in the growing enterprise problem of "spreadmarts," basically tech-savvy users developing their own sophisticated spreadsheets to cull business intelligence out of various data sets and shipping the resulting -- sometimes gigantic -- files all over the company via e-mail, with no organization, security or management.
With PowerPivot, he said, users can create much more useful BI and share it throughout the company via SharePoint 2010, for example (PowerPivot is also a SharePoint add-in).
His presentation elicited some "oohs" and "aahs"from the 40-plus developers at the meeting, with the 44-million-record example generating special interest. Previously, Excel users were limited to using 1 million records.
"It's amazing," said Taglienti, as several people inquired about the demonstration. It did seem hard to believe that 44 million records could be instantly sorted on a laptop.
Granted, he was using a 64-bit machine with 4GB of RAM, but he said the remarkable speed of his demonstration was primarily attributable to compression technology that shrinks huge data sets so that they can be manipulated entirely in-memory. In fact, Taglienti said, the 44 million records were squeezed into an XLSX file of less than 5MB. That also got everybody's attention: 44 million records in a 5MB file. PowerPivot's data engine technology (called VertiPaq) has achieved up to about 20-to-1 compression, he said.
Surprisingly, it turns out that 44 million records is nothing special. There has actually been a 100-million-record PowerPivot experiment. Taglienti also demonstrated how PowerPivot can access data from the cloud. He showed how easy it was to connect, in just a few clicks, to an Open Government Data Initiative data feed and download it into PowerPivot. The OGDI data is hosted -- of course -- in Windows Azure, Microsoft's fledgling cloud platform.
Other usable data sources include several different kinds of databases, files and Microsoft Reporting Services feeds. The strategy behind PowerPivot is "to empower the end user" to do their own data analysis and intuitively build their own custom BI solutions, Taglienti said. This lets IT concentrate more on infrastructure and data management instead of numerous custom BI projects.
He stressed that it's all beta technology at the moment, and Microsoft isn't entirely certain how it will be leveraged in real-world, day-to-day use. He posited the question of whether-if a company has some 10,000 PowerPivot cubes floating around in SharePoint, for example-PowerPivot should have its own server. While questions such as that get sorted out, Microsoft is out in the front lines trying to convince users that, as Taglienti says, "it's a game-changer."
The mood at the meeting seemed to be wait-and-see.
Teresa DeLuca, a Web developer at International Data Group, which is headquartered in Boston, said she didn't anticipate using PowerPivot. But she said, "I do work with pivot tables a lot," so there might be some benefit to checking the product out.
Dean Serrentino, a software developer/consultant at Paradigm Information Systems in Wilmington, Mass., said before the meeting, "I'm totally unfamiliar with it" and that he was at the meeting to learn more about it. "I usually don't go bleeding edge," he said. After the meeting, he said in an e-mail, "I don't anticipate using it for two reasons. First, I think it is too complicated for use by the audience that would most benefit from it. Second, it is an SQL Server Enterprise version feature. Most of my clients are small to medium sized businesses and don't need the features in SS Enterprise, or have the resources to afford it."
Gary Chin, an independent developer in Newton, Mass., said, "I don't see using it immediately, but it's going to come in handy for BI and SQL Server applications ... the whole idea of storing the live data from SQL Server and downloading it down to financial analysts or other users that need to manipulate the data to see what happens within the data."
Some people are more skeptical. Readthis entertaining, semi-contentious exchangebetween one of PowerPivot's founding engineers and a user arguing about what capabilities PowerPivot provides that plain old Excel doesn't.
What do you think? Marketing hype or must-have technology? Have you tried it? Are you going to? Comment here or send me an e-mail.
Posted by David Ramel on 01/22/2010