Configure PowerPivot for SharePoint 2010

PowerPivot (http://msdn.microsoft.com/en-us/library/gg701942(v=office.14).aspx) 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: http://technet.microsoft.com/en-us/library/ee210640.aspx

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″ ?>

<configuration>

  <runtime>

    <disableCachingBindingFailures enabled=”1″ />

  </runtime>

</configuration>

 


 

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
Next


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: http://www.microsoft.com/en-us/download/details.aspx?id=7609)


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: (http://support.microsoft.com/kb/2361559)

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)

a. http://blogs.msdn.com/b/mtn/archive/2010/10/15/how-to-manually-refresh-powerpivot-gallery-thumbnails.aspx

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.  http://support.microsoft.com/kb/926642

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 !!!

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s