Sampath's Blog

A cool add-on to the SharePoint family is the power pivot or code name Gemini. A easy to use in-memory BI tool allowing easy access and design of BI reports with millions or rows of data. Power pivot can be deployed directly in to personal computer or you can deploy into Microsoft SharePoint 2010 server. Lets look at how we can deploy Power Pivot to Microsoft SharePoint 2010.

Requirements –

1. 1. Microsoft SharePoint 2010 Server Enterprise (Of course)
2. 2. Microsoft SQL Server 2008 R2

Setup -

• First we need to install power pivot component in the SharePoint Server. For that run the SQL Server 2008 R2 Setup from setup.exe – Select Installation – New installation or add features to existing installation – Click OK – Install Support components – Next Screen Select SQL Server Power Pivot for SharePoint.

• Click Next and if the server detects analysis services installation it will be automatically selected. Remember you need analysis services when you install SharePoint this time. Then you will get the verification report. Make sure all is green and click next -  Click next again to confirm the instance ID (The thing here is that you cannot install PP in to an existing SQL Server instance to it has to be a new instance. So keep the default and click next) – Review setup summary and click next – enter the user for the service account – next screen click add current user – review the summary and start the installation.

• Next step is for us to deploy the Power Pivot solutions package in to SharePoint Server. Therefore go to Central admin – System Settings – Manage farm Solutions – Click on powerpivorwebapp.wsp and select deploy and select the correct web application you want to deploy it – Then you will bet the message saying it was successfully deployed. If gives any errors for an IISREST and retry.

• Now go to system services and make sure SQL Server and PowerPivot services are started. Also in SharePoint make sure that Secure Store, Excel Calc, Claims to Windows are started.
• Next we need to create the SSA for PowerPivot – So go to SharePoint Central Administration again – under application management – manage service applications – Click new from the ribbon – new SQL PowerPivot Service Application
• Enter the name for the service application
• Enter the name for the new application pool (I recommend using a new app pool for this)
• Select the Service user account
• Enter the database server name you want the DB to be created
• Enter the database name for the SSA
• Leave the Windows Authentication by default
• Keep the tick on add the SSA to the default proxy group and click OK

• Finally activate the PowerPivot to a site collection. To do that open the SharePoint site – Go to Site settings – Site collection features – Find the PowerPivot site collection feature – Click Activate

• Now You can upload your PowerPivot documents in to SharePoint with Data Access. Try uploading a Excel PowerPivot and using a slicer to refresh data.
• Have Fun!