Configure PowerPivot for SharePoint 2010

PowerPivot ( for SharePoint supports Excel 2010 workbooks that contain PowerPivot data and that are published to a SharePoint site. As a result, other users who do not have the PowerPivot add-in installed can view and interact with the workbook. In addition, PowerPivot for SharePoint has unique features to extend the capabilities of Excel workbooks that contain PowerPivot data. For example, you can do the following:

  • Highlight workbooks and the information that they contain from within SharePoint.
  • Refresh external connections to resources to keep the data current.
  • Schedule times to update the data automatically.
  • Reuse PowerPivot data from one workbook in other workbooks.

Requirement for PowerPivot:

Step 1: From your Desktop, click on Server Manager


Step 2: In the left panel, select Roles then click Add Roles in the Roles panel


Step 3: Click Next

Step 4: Check if Application Server and Web Server (IIS) are installed. If all roles exist, click Cancel. If not click Next to install these roles.


Step 5: Open your CD Drive, run SQL Server R2 setup file.

Step 6: Click New installation or add features to an existing installation inInstallation section

Step 7: Click OK to setup support rules

Step 8: Click Install

Step 9: Click Next

Step 10: Click Next

Step 11: Click Next

Step 12: Check “I accept the license terms“, then click Next


Step 13: Check “SQL Server PowerPivot for SharePoint“, then select “Existing Farm

Step 14: Click Next


Step 15: Click Next

Step 16: Keep the default input, then click Next

Step 17: Click Next

Step 18: Click “Use the same account for all SQL Server services” button

Step 19: Input SP Farm account and password, then click OK

Step 20: Click “Add Current User” then Next

Step 21: Click Next

Step 22: Click Next

Step 23: Click Install

Step 24: Setup completed, click Close

Error While Setup (If you see this error)

Step 1: Create new “Text Document” on your Desktops


Step 2: Copy and paste this text to document

<?xml version=”1.0″ encoding=”utf-8″ ?>



    <disableCachingBindingFailures enabled=”1″ />





Step 3: Save as it with name “setup100.exe.config
and Save to Desktop

After create successfully

Step 4: Copy this file and paste into “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\x64

Step 5: From Start Menu, click Control Panel, select “Uninstall a program


Step 6: Left click on “Microsoft SQL Server 2008 (64-bit)“, choose “Uninstall/Change

Step 7: Click “Remove


Step 8: Click OK


Step 9: Select “POWERPIVOT” instance and Next, then click Next

Step 9: Only check feature at POWERPIVOT, then click Next and continue until remove completed

After do all steps above, use can reinstall from step 5 (in main Step) until setup completed


Step 25: After setup is done, go to Central Administration -> Services on Server and check if “SQL Server Analysis Services” and “SQL Server PowerPivot System Services” is exist

Step 26: Select Application Management -> Manage services on server. You will see 2 web parts like picture below

Step 27: Click “powerpivotwebapp.wsp“, then click deploy solution

Step 28: Choose your web application, click OK to deploy this web part

Step 29: Go to Application Management -> Manage Services on Server, start all these services

  • Excel Calculation Service
  • Secure Store Service
  • Claims to Windows token Service
  • SQL Server Analysis Services
  • SQL Server PowerPivot System Service

Step 30: Go to Application Management -> Manage Service Applications,select new SQL Server PowerPivot Service Application

Step 31: Input Name and Application pool name, then click OK to complete

Step 32: Go to Application Management -> Manage Service Applicationsagain, is Excel service is not existed, select New Excel Services Application and input like above step


Configure the PowerPivot Unattended Data Refresh Account (PowerPivot for SharePoint)


The PowerPivot unattended data refresh account is a designated account for running PowerPivot data refresh jobs in a SharePoint farm. By configuring it, you enable the Use the data refresh account configured by the administrator option in a data refresh schedule page (see below). Workbook authors who schedule data refresh can choose this option if they want to use the PowerPivot unattended data refresh account to run a data refresh job. For more information about how to view the Credentials options in a data refresh schedule, see Schedule a Data Refresh (PowerPivot for SharePoint).



Step 33: Go to Application Management -> Manage Service Applications,New Secure Store Service Application and input like above step


Step 34: Click on new Secure Store Service that you have just created

Step 35: Click Generate New Key

Step 35: Input Pass Phrase and Comfirm Pass Phrase, then click OK

Step 35: Select New

Step 36: Input like below, then Next

Step 37: Click

Step 38: Input your domain farm account and click OK to finish

Step 39: Select your New Target Application, click Set Credentials

Step 40: Input credentials information using domain farm admin account

Step 41: Go to Application Management -> Manage Service Applications,Click PowerPivotServiceApplication

Step 42: Click Configure service application settings


Step 43: At Data Refresh section, input your Target Application
in Secure Store Service



Step 44: You can set Maximum Excel Workbook File by the following steps

Click Excel Service in Manage Service Application


Step 45: Setup PowerPivot for Excel (32 bit or 64 bit, depend on your office version) (Link:

Step 46: Go to your current web application -> Site Action -> Site collection features


Step 47: Active PowerPivot Feature Intergration for Site Collections feature

Step 48: From your Desktop, select Windows Power Shell and Run as administrator

Step 49: Run the command below

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa -Name “DisableLoopbackCheck” -value “1″ -PropertyType dword


Step 50: If you cannot create snapshots for the document. Here are some of the troubleshooting steps for this scenario: (

1. Make sure Excel services is rendering workbooks.

2. If you you have Internet Explorer 9 installed on your Farm servers you need to install Sql 2008 R2 SP1 for PowerPivot for SharePoint.

a. if you cannot upgrade to SP1 you can manually make the fix with the below steps.

In %Common Files%\Microsoft Shared\Web Server Extensions\14\Template\Layouts\PowerPivot you will find a file called ASRGLoader.htm.

Make a copy of the file and call it ASRGLoader.orig.htm (this is so that if you mess up anything you can revert :)

Open asrgloader.html in notepad.

Search for a function called onXLFrameLoad

Replace the line

if (ewaFrame.document.readyState == ‘complete’) {

with the line
if ( (ewaFrame.document.readyState == ‘complete’) || (ewaFrame.document.readyState == ‘interactive’) ) {

3. A simple test to see if a getsnapshot is being spawned is to:

a. start task manager on your Web Frong End and click “show processes from all users”

b. go to the document library and go to view “all documents” and just edit properties. This should cause GetSnapshot.exe to fire and you should see it under task manager and can see the account it is running under

4.  You can run GetSnapshot.exe manually and pass in parameters site, url, guid (note guid can be whatever). One caveat though is that the .info and .png file are created in the user’s local temp directory if they are logged in (as I was)…otherwise it will be saved in the c:\windows\temp. You cannot use this method to manually create/replace a thumbnail. This will only tell you if GetSnapshot failed (maybe b/c it took too long or something)


5. Look in C:\Windows\Temp for the thumbnail (.png) and the log file .INFO; .INFO file should report success or failure messages

6.  The workbook might not contain any embedded PowerPivot data. If so, a thumbnail will not be generated.

7.  The workbook might contain more than one data source. If so, a thumbnail will not be generated.

8. If the file is a .rdl file, then the data source must be embedded in the report and it must use the PowerPivot OLEDB provider and point to a workbook (.xlsx) that is contained in the same document library (i.e. the Gallery). If not, a thumbnail will not be generated.

9. Verify Alternate Access Mappings are setup correctly.

10. Add the PowerPivot site to the trusted intranet sites for the account running Getsnapshot.exe

11. Disable loopback check.

12. Remove the check box for refresh on open in the workbook connection properties

13. Open Advanced Settings for the Document library(PowerPivot Gallery). Under Opening documents in the browser change the setting to “open in the Browser”.

14. Disable Internet Explorer Enhanced Security Configuration in Server Manager > Configure IE ESC


Step 51: Create your PowerPivot Gallery and Upload your documents.





Good luck !!!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s