Prior to 2013, if you wanted to use Microsoft Power Pivot in Excel, you would’ve had to download an add-in to both Excel 2010 and SharePoint 2010 and then deploy these solutions on your client Office installs and SharePoint farm. That’s no longer the case, as Power Pivot is now built in to both products.
To enable PowerPivot in Excel 2013, go to the FILE menu, and choose Options. From there choose the mange COM Add-ins, as shown here:
Ensure the Power Pivot box is checked – that’s it!
On the server side, you only need to have Excel Services enabled in your farm via its corresponding service application, and a SQL Server Analysis Server installed in SharePoint mode outside your SharePoint farm. You simply register the AS server in the Excel Services Service Application.
To install a SharePoint SSAS instance, follow these instructions. Once done, from the main Excel Services service app page in Central Administration, select Data Model Settings:
Then add your server/instance on the following page:
That’s it! You now have basic PowerPivot functionality in Excel Services & SharePoint.
To gain additional PowerPivot functionality, such as the PowerPivot Gallery, Schedule data refresh, Management Dashboard, and data provider, you will need to install the PowerPivot add-in for SharePoint 2013 on the farm and follow the instructions here.
After following these simple steps, you can see that configuration between tools has greatly improved from prior version. In my next post, I’ll review the new features of Excel Services & PowerPivot in SharePoint 2013.