Posted by: Denny Cherry
Powerpivot, SQL Server, SQL Server 2008 R2, Storage
So if you haven’t heard of PowerPivot yet, it’s going to be the next best thing to sliced bread for your business users. While it’ll take a while for most IT shops to get the full Sharepoint & PowerPivot integration setup, PowerPivot will be available to your users within the next few weeks as Office 2010 is released.
The basic jist of PowerPivot is that you can download a huge amount of data from your data warehouse, then slice and dice the data, analyzing it with the power of the SQL Server Analysis Services engine. The downside of all this you ask? Really, really big Excel workbooks. And when I mean big, I mean big. The user can download 30+ Gigs of data into the Excel workbook, which Excel will compress into an Excel workbook of up to 2 Gigs.
Now by default the users will probably save these documents to their desktop, or their My Documents folder. So when your users start wanting Office 2010 installed be sure that your file servers, and home directory servers have the free space for these huge files.
If you use roaming profiles (like most larger companies do) you’ll want to set some expectations with your users that if they store the files somewhere that needs to replicate data from the workstation to a server it’ll take a long time to replicate these large files on login and logout of the computer.
Now, also don’t forget those quotas. If you’ve got a 500 Gig quote setup on the users profile directory on the file server, and they try and upload a 1 Gig PowerPivot file from their My Documents folder some bad things are going to happen. So in addition to adding more disk space to the file server, you’ll need to increase the quota size for the users that will be using PowerPivot.