Today we will take a walkthrough on how to install and configure PowerPivot in a SharePoint farm. There are a few requirements to consider before getting started. The first is that you can only install a PowerPivot instance on a sever that has SharePoint 2010 installed. This means that you cannot run the PowerPivot analysis services instance on your SharePoint database server. For this walkthrough I have a Server Farm setup (a farm install is required as PowerPivot cannot work with the standalone install of SharePoint and the internal database) I have an app server in the farm dedicated to reporting services and we will now add the PowerPivot instance onto that server. In order to install PowerPivot you must install with an account that has local admin rights on the server. You also need that account to have farm administrator privileges in order to deploy the solution files. For this example I granted my SharePoint farm admin account local admin rights on the server. I will remove this account from the local admin group after the installation is complete.
OK so lets get started with the install. Get your SQL 2008 R2 media and run as administrator and start the installation.
NOTE: You must use Evaluation, Enterprise, Developer, or Data Center edition of SQL 2008 R2. Other editions do not offer the PowerPivot option.
We are going to do a new installation so click installation and then New installation or add features to an existing installation. Click through the wizard choosing new installation again when prompted. Then on the Setup Role screen you will not accept the default and you select SQL Server PowerPivot for Sharepoint. Then if you already have a SharePoint farm up and running that you would like to configure PowerPivot for you choose existing farm from the drop down box. If you have a fresh install of SharePoint then you select New Server. For this walk through we will be doing existing farm since there are more manual configuration steps.
Click next and on the next screen, feature selection accept the defaults.
Keep clicking through the wizard accepting defaults until you get to the instance configuration screen. This page I accepted defaults as well but its good to point out that you can change the Instance ID and root directory. I like to keep these things in the default locations and just change the data directories which we’ll do in a few.
Next we set the service account that this instance will run as. This should be a domain account and only be used for running this instance.
Click next and we are brought to the screen that we grant user accounts (or groups) admin rights to this instance. So add whoever should get admin rights to this analysis services instance. Then click the tab for Data Directories. This is where we can change the path of all the data files. The screenshot shows the defaults but I will change those to another drive and path.
Click through the rest of the wizard and wait for the install to finish. That’s all there is to it. Now we are done with the SQL portion of the install. We can remove the account from the local admin group as well. Next we move on to the SharePoint portion of this process.
SharePoint Configuration Steps
First we need to deploy the PowerPivot solution package to the web applications that we would like to use PowerPivot on. You can deploy this package to just 1 web application or to all its up to you. To do so open up Central Administration, in system settings click on Manage Farm Solutions then click on powerpivotwebapp.wsp and then Deploy Solution. In the Deploy To field you can select where to deploy to. You need to deploy to wherever you want PowerPivot capabilities. If you want to allow the use of PowerPivot everywhere then you will deploy to each web app.
You should also take note that there should be the powerpivotfarm.wsp file and that should already be deployed globally. After you deploy repeat for all web apps that you want PowerPivot on and then start the services. To start the services go to System Settings –> Manage services on server. PowerPivot requires the Claims to Windows Token Service, the Excel Calculation Services, and the Secure Store Service to be running. The Claims to Windows Token Service must be running on every server that runs Excel Services or PowerPivot. So go ahead and start these services if they aren’t already. The screenshot below does show the Secure Store Service as stopped but that is only because it is running on another service in the farm.
Also just confirm that the SQL Server Analysis Services and SQL Server PowerPivot System Service started successfully as well.
Now we need to create the PowerPivot Service Application
Switch over to Application Management and click on Manage Service Applications. Then in the ribbon select New and SQL Server PowerPivot Service Application
Set the Name for the service application and create a new application pool. Add a new managed account to run the pool or use an existing one. Remember what account you used as you will need this information later.
Set the Database Server, Name, and authentication method. If you want this service application to be associated by default with all new web applications then leave the Add application proxy to default group checked, otherwise uncheck it.
OK we now have a PowerPivot service application. Let’s change a few settings to make it work.
Excel Services Service Application Settings
First go into the Excel Services Service Application settings and in the workbook properties area set the Maximum Workbook Size to something bigger like 50. This is because PowerPivot workbooks tend to have large amounts of data and are naturally bigger in size. We will also need to change this setting in another area that we will go over in a bit. Also in the Excel Services Service Application confirm that you have Trusted File Locations set to where you will be putting your PowerPivot Workbooks. Also make sure that Allow External Data to Trusted data connection libraries and embedded is set.
Secure Store Settings
Back in Manage Service Applications screen select the secure store service and click manage in the ribbon. If you haven’t done so already go ahead and generate a new key by clicking the button in the ribbon. After that is done or if you have already done it for another application then lets configure the unattended PowerPivot data refresh account. To do this we first need to create a target application so click on new in the ribbon. For Target Application ID you can call it anything you like I called it PowerPivotDataRefresh. Then for display name I used the same name but with spaces to make it nicer to read. Target Application Type should be Individual. You don’t need to specify the target application page URL as PowerPivot doesn’t use one. For the credentials page accept the defaults and for the Target Application Administrators enter the account that you used for the application pool earlier.
Click ok and when complete you should now see it in the list.
Pull the drop down and select Set Credentials. For Credential Owner enter an account of who should be allowed to change the credentials (I put myself) that get specified below. Then you specify the account that will be used for PowerPivot data refresh account.
PowerPivot Service Application Additional Settings
Now that we have our data refresh account in the secure store we need to configure the PowerPivot Service application to use it. So go back into manage service applications and then click on the PowerPivot service application name which will take you to the dashboard. From here in the top right you should see a configure service application settings link click it. In the Data Refresh section put the name of the Target Application ID that you created in the Secure Store into the PowerPivot Unattended Data Refresh Account field (Mine was PowerPivotDataRefresh) and click OK.
This account will need contribute permissions to any PowerPivot Workbook. Security can be done on a workbook by workbook, site by site, or collection by collection basis. For simplicity I granted this account contribute permission to the entire web application but using a user policy in central admin. So however you need to grant contribute permission to this account to your PowerPivot workbook.
The data refresh account will also need read access to any external data sources that the PowerPivot workbook uses. This completes the setup for the data refresh account. You should now be able to select this option in the data refresh section of each PowerPivot workbook.
Maximum Upload Size
Earlier we increased the Maximum Workbook size to 50 in the Excel Services Service Application settings. In order for this to actually work we need to set the web application to allow uploads of the same size. So in central admin click on manage web applications, choose the web app that will house your PowerPivot data and click on general settings in the ribbon. In the Maximum Upload Size section increase to whatever size you specified in the Excel Services Settings.
Next you may want to enable Usage Data Collection if you want to have SharePoint gather its usage data and display the content in the PowerPivot dashboard in central administration. We’ll go over this in detail in another post.
The last thing left to do would be to Activate the PowerPivot feature for site collections You do this like you would any other site collection feature. Site Actions –> Site Settings –> Site Collection Features –> Activate the PowerPivot Integration Site Collection Feature.
You will need to do the above step for each site collection that you want to use PowerPivot on. In another post I’ll detail out a script to do this as well as a script to configure the service application. Once this is done you can go ahead and create a PowerPivot Gallery and start enjoying all of the fun and value that PowerPivot provides.
If I made any mistakes, or was unclear on anything, please feel free to leave a comment to correct me or ask the question. I try and respond quickly which is usually within a day or so.