PowerPivot at 2009 SQL Server PASS Conference

At the Professional Association of SQL Server conference, November 2009, Denny Lee and Dave Wickert, Members of the Microsoft PowerPivot teams and SQLCAT teams, made a presentation about PowerPivot server best practices.

You can follow tweets at www.twitter.com/powerpivot.

PowerPivot is the only Microsoft product that spans across the SQL Server, SharePoint and Office product stacks.

There are two categories of users for PowerPivot. There are "power users" and "standard users." Standard users can use a browser. They don't have to have Excel installed to look at PowerPivot reports.

With the PowerPivot add-in, a 2010 Excel power user can build reports. Behind the scenes, there is VertiPack compression and an Analysis Services engine in-memory. The takeaway, now Excel can work with a lot more data than ever before - and that opens up the door to do more business intelligence data analysis. Power users can gather data from databases and work in Excel rather than within the database where their skillset does not reside.

One trick is to remove rows that are not used to improve performance.

VertiPac is an in-memory system. PowerPivot keeps a cache of detatched databases in the SSAS Backup folder.

So, the recommendation is setting up new application servers rather than trying to repurpose older servers because these days one can get a server loaded up with a lot of memory relatively cheaply.

PowerPivot stores a copy of the database. A fair amount of disk space is used to minimize the load on content databases arising from multiple users hitting the database.