Installation and Configuration of PowerPivot in SharePoint 2010

PowerPivot is a Business Intelligence reporting feature of SharePoint. PowerPivot helps analyze the data in more efficient way. It is highly interactive and user self-service reporting tool. Using PowerPivot we can analyze data from SQL Server Analysis services or any other OLAP data mart. Now let us see how we can configure the PowerPivot for SharePoint. To install and configure PowerPivot, we will need the following things hands:

 

1.       SharePoint Service account for PowerPivot and Excel Services. If we use the same service account, the whole process will be easy.

2.       SQL Server 2008 R2 Enterprise Edition Installer

Installation of PowerPivot:

 

To start with the installation, the first thing we have to do is to create a configuration file like this: 

1.       Create a text file in notepad and paste the following paragraph into there:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

                <runtime>

                                <disableCachingBindingFailures enabled="1" />

                </runtime>

</configuration> 

2.       Save it as Setup100.exe.config in %ProgramFIles%\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64. We have to do this to work around a known bug in "Analysis Service Installation for SharePoint" (Ref: http://support.microsoft.com/kb/2261507)

3.       Start the SQL Server Enterprise Edition installer as "Administrator"

4.       Select "New Installation or add features to an existing installation"

 

5.       Install setup support files

6.       Select "New Installation or add shared features" in "Installation Type"

7.       Select "SQL Server PowerPivot for SharePoint" and select "Existing Farm" in the "Add PivotTable for SharePoint to:"

8.       In the "Instance Configuration" leave the default name of the Database Instance which is "POWERPIVOT" and click "Next"

9.       Add the Service Account/Password in "Server Configuration" area for the PivotTable database instance

10.   Add SharePoint Administrator account to "Analysis  Service  Configuration"

11.   Then finish the installation by clicking "Next" and finally "Finish" and you will see the successful installation message from the installer

Configuration of PowerPivot:

1.       Configure and Start the following services:

a.       Excel Calculation Service

b.      Secure Store Service

c.       Claims to Windows token Service

d.      SQL Server Analysis Services

e.      SQL Server PowerPivot System Service

To do that, you may have to go to SharePoint central Administration->System Settings->Manage Services on Server

2.       Create PowerPivot Service Application:

To create the PowerPivot Service Application, you have to go to Central Administration->Application Management->Manage Service Applications (In Service Applications group). Then type the new application pool name with the Configurable Identity that will be used to connect to external data sources to create the report. Leave the other selections (Name, Database Server, and database name) as-is or change it based on your preferences. Finally select the checkbox for "Add the proxy for this PowerPivot service application to the default proxy group".

3.       Now we have to create the Target Application ID for PowerPivot in "Secure Store Service". To do that, please go to SharePoint Central Administration –> Application Management –> Manage Service Applications->Secure Store Service. If you haven't created the "Secure Store Service" yet, please follow another of my blog entry on PerformancePoint (http://fazlulchowdhury.blogspot.ca/2012/12/performancepoint-ultimate-bi-reporting.html ).

Then click on "New" from the Ribbon and fill in the Target Application ID, Display name and Administrator Email and click "Next". Leave everything as-is and click "Next". Add "target Application Administrators" and click ok to save the configuration. It will take you back to "Secure Store Service Application" section.

At this point, you have to set the credentials for the Secure Store Service Application for PowerPivot that you have just created. Check the checkbox beside the target Application ID and click on the "Set Credentials" button from the ribbon. The set the following:

a)      Credential Owner: This is the owner of the credential. It will be used by PowerPivot unattended account but this is not the account used as the unattended account. Generally it is SharePoint Administrator Service account.

b)      Windows user name and password: This is the actual account used as the unattended account. Generally it is SharePoint Service account for Pivot Table or Excel Services.

Click OK to save the configuration. 

Then associate the application with PowerPivot Service Application. To do that, go to SharePoint Central Administration –> Application Management –> Manage Service Applications -> PowerPivotServiceApplication1 and click on Manage from the ribbon or just click it. It will open PowerPivot dashboard. Click on Configure Service Application Settings in Actions frame. It opens PowerPivot Settings. Find PowerPivot Unattended Data Refresh Account input box under Data Refresh. Type the ID of the application created in Secure Store Service (i.e. PowerPivotDataRefresh).

4.       Set the Excel Services properly. To do that Go to SharePoint Central Administration –> Application Management –> Manage Service Applications and select Excel Services Application->Trusted File Locations. Then click on "http://" and change the values as required but uncheck the "Warn on Refresh" and Select Trusted data connection libraries and embedded under Allow External Data.

5.       Deploy the PowerPivot Solutions: If you go to SharePoint Central Administration->System Settings->Manage Farm Solutions, you will notice that there are two new .wsp files:

powerpivotfarm.wsp and powerpivotwebapp.wsp. powerpivotfarm.wsp has been globally deployed. But, you have to deploy powerpivotwebapp.wsp to the web application where you are going to use PowerPivot. Click the powerpivotwebapp.wsp file and select "Deploy" followed by selecting right web application to deploy.

Now the configuration is complete for "SharePoint PowerPivot Services". In case of getting errors (i.e. "Cannot Open Excel Workbook" ) with PowerPivot, please make sure that the "Unattended Service Account" for Excel Services and PowerPivot Services has access to the SharePoint Database and connecting data source as well. To allow the service account access to SharePoint, please run the following script for each account.

 

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA 0

$webApp = Get-SPWebApplication "---SharePoint Site URL---"

$webApp.GrantAccessToProcessIdentity("---Unattended Service Account----")

 

Testing PowerPivot in a Business Intelligence enabled site:

1.       Go to Site Collection Administration-> Go to top level site settings. Then go to Site Collection Administration-> Site collection features and activate following services:

a)      PerformancePoint Services Site Collection Features

b)      PowerPivot Feature Integration for Site Collections

c)       SharePoint Server Publishing Infrastructure

Also make sure that "PerformancePoint Services Site Features" is activated under "Manage Site Features"

 

 2.       Create a Business Intelligence Template Site: Go to Site Actions->View All Site Content->Create. Then go to Data under "All Categories" and select "Business intelligence Center" and create the site.

 

 

 3.       Create a Pivot table Gallery: Go to Site Actions->View All Site Content->Create.

 

 4.       Start creating Pivot table reports