Setup powerpivot on a server?

I have a client that uses PowerPivot. They save the Power Pivot inside Excel files and have pivottable reports in the same Excel files.

They feel that this puts to much strain on their computers. What is a good way to improve performance?

Is it possible to setup the powerpivot on a server and have the computers make queries to that server? In that case, is there a guide for how to do this? Or would Office 365 Power BI, be the best solution?

Solution 1:

Power Pivot is actually a light-weight version of a subset of features of SQL Server Analysis Services.¹

In SQL Server 2008 R2, Microsoft released PowerPivot for Excel which introduced an in-memory analytics engine called VertiPaq. Vertipaq enables Excel to process hundreds of millions of rows with sub-second response times on desktop hardware.
In SQL Server 2012, Microsoft has taken the same Vertipaq engine and integrated it into Analysis Services. In addition, Microsoft has rebranded the Vertipaq engine that runs inside Power Pivot and Analysis Services to xVelocity in-memory analytics engine.¹

For more information about xVelocity read the xVelocity in-memory technologies announcement on the SQL Server 2012 blog.

To answer your question, I would recommend your client uses Microsoft SQL Server Analysis Services which is installed on a server. Microsoft Excel and the Power Pivot Add-In may then connect to the model hosted on the Analysis Services server.

1. Analysis Services & Power Pivot Blog