Attendees at this year’s Phila HFMA Decision Support and Financial Technology seminar heard several sessions describe the new PowerPivot add-in for Microsoft Excel. Presenters described its ability to handle large million-row data sets with extremely fast response times. But how big can the datasets be, and how fast is the response time?
We recently got a chance to check this out on a bundled payment project for a client in a large metropolitan area that had data for three hospital referral clusters (HRCs). This data contains 100% of the claims for all Medicare beneficiaries who live in that area (which is about a 30-mile radius). The claims data files provided by CMS for this area were about 100 gb, and the combined SQL databases into which the data was loaded were about 230 gb. Processing the data resulted in an episode table that contained all claims (including carrier claims at the claim line level) for all episodes within those HRCs and contained about 44 million records. Microsoft claims that PowerPivot can handle millions, or even billions of records, so we dumped this table into the same PowerPivot Excel file that we had been using successfully for individual HRCs (which are generally 4 to 8 million records) and held our breath.
It took a couple of hours to load it, but in the end we had a 2.4 gb Excel file (that’s not a misprint – it’s really 2.4 gigabytes) containing all of that episode data. (BTW - that same data took 35 gb on the SQL Server, so PowerPivot really compressed it.) OK, it loaded, but how fast is it? We clicked on a slicer button to filter the data for a specific DRG and started counting the seconds. We stopped at 10 seconds when the screen refreshed with the new data. Then we clicked another button to see how long it took once the cache was warmed up (the first query always takes longer). This took about 6 seconds, as did each other selection (DRG, episode length, claim type, diagnosis category, etc.). Wow – it’s truly “stream of consciousness analysis” that provides instant answers to the next question - even for huge data sets.
Oh, and this is all running on a laptop, not a server accessed over the internet. (Ok, it’s a new laptop with 8gb of RAM and running the 64-bit versions of Windows and Excel, but it’s still a LAPTOP!). And the price of PowerPoint is really attractive because it’s FREE. Microsoft is trying to encourage you to buy the version of SharePoint that supports enterprise versions of PowerPivot, but the desktop version is free. You need to be running Excel 2010 (and probably the 64-bit version for models this big), though – it won’t work with earlier versions.
So for big data needs like bundled payment analysis, and probably claims-based analysis for ACOs, this tool can be a game-changer in putting analytical capabilities in the hands of the end user without needing more expensive server-based analysis packages. It’s definitely worth checking out.