Upgrade PowerPivot for SharePoint (PowerPivot Configuration Tool)

Upgrading an existing PowerPivot for SharePoint installation from SQL Server 2008 R2 to SQL Server 2012 is a multi-step operation that includes running SQL Server Setup to upgrade the program files on the server, and running upgrade actions in SharePoint. To perform PowerPivot upgrade actions in SharePoint, you must use either the PowerPivot Configuration Tool or PowerShell. This topic explains how to perform these tasks using the tool.

This topic describes steps that are part of a broader process. For more information about steps that precede and follow this one, see Upgrade PowerPivot for SharePoint.

Requirements for using the tool

• You must be a farm administrator, a server administrator on the Analysis Services instance, and db_owner on the farm’s configuration database.

• The tool can only be used with a SQL Server 2012 instance of PowerPivot for SharePoint. Do not use it with SQL Server 2008 R2 installations. You must have run SQL Server 2012 Setup to upgrade the program files before running the tool to upgrade features and solutions in the farm.

• Verify that the SharePoint 2010 Administration service is running.

• Verify SQL Server Analysis Services and SQL Server PowerPivot System Service are started in Central Administration.

Run the Upgrade Task in the PowerPivot Configuration Tool

After you have upgraded the program files using the SQL Server 2012 Setup program, you can run the PowerPivot Configuration Tool to complete the upgrade.

1. On the Start menu, point to All Programs, click Microsoft SQL Server 2012 , click Configuration Tools, and then click PowerPivot Configuration Tool. Note that this tool is listed only when PowerPivot for SharePoint is installed on the local server.

2. At startup, the configuration tool checks the upgrade status of the PowerPivot farm solution and PowerPivot web application solutions. If older versions of these solutions are detected, you will see the message “Newer versions of the PowerPivot solution files have been detected. Please select the upgrade option to upgrade your farm.” Click OK to close the message.

3. Click Upgrade Features, Services, Applications and Solutions, and then click OK to continue.

4. The following warning appears: “Workbooks in the PowerPivot Management Dashboard are about to be upgraded to the latest version. Any customizations you made to the existing workbooks will be lost. Do you want to continue?”

   This warning refers to workbooks in the PowerPivot Management Dashboard that report on data refresh activity. If you customized these workbooks, any changes you made to those workbooks will be lost when existing files are replaced with newer versions.

   Click Yes to overwrite the workbooks with newer versions. Otherwise, click No to return to the home page. Save the workbooks to a different location so that you have a copy, and then return to this step when you are ready to continue.

   For more information about customizing workbooks used in the dashboard, see Customizing the PowerPivot Management Dashboard.

5. Review the actions in the task list and exclude any that you do not want the tool to perform. All actions are included by default. To remove an action, select it in the task list, and then clear the Include this action in the task list checkbox on the Parameters page.

6. Optionally, review detailed information in the Output tab or Script tab.

   The Output tab is a summary of the actions that will be performed by the tool. This information is saved in log files at C:\Program Files\Microsoft SQL Server\110\Tools\PowerPivotTools\ConfigurationTool\Log.

   The Script tab shows the PowerShell cmdlets or references the PowerShell script files that the tool will run.

7. Click Validate to check whether each action is valid. If Validate is not available, it means that all of the actions are valid for your system. If Validate is available, you might have modified an input value (for example, the Excel service application name), or the tool might have determined that a particular action cannot be performed. If an action cannot be performed, you must exclude it or fix the underlying conditions that cause the action to be flagged as invalid.

   Important

   The first action, Upgrade Farm Solution, must always be processed first. It registers the PowerShell cmdlets that are used to configure the server. If you get an error on this action, do not continue. Instead, use the information provided by the error to diagnose and resolve the problem before processing additional actions in the task list.

8. Click Run to perform all of the actions that are valid for this task. Run is available only after the validation check is passed. When you click Run, the following warning appears, reminding you that actions are processed in batch mode: “All of the configuration settings that are flagged as valid in the tool will be applied to the SharePoint farm. Do you want to continue?”

9. Click Yes to continue.

Upgrading solutions and features in the farm can take several minutes to complete. During this time, connection requests for PowerPivot data will fail with errors like “Unable to refresh data” or “An error has occurred trying to perform the requested action. Please try again.” After upgrade is finished, the server will become available and these errors will no longer occur.

Troubleshooting errors

Sometimes errors will occur that prevent the task from completing. You can view error information in the Parameters pane for each action.

For problems related to solution deployment or retraction, verify the SharePoint 2010 Administrator service is started. This service runs the timer jobs that trigger configuration changes in a farm. If the service is not running, solution deployment or retraction will fail. Persistent errors indicate that an existing deployment or retraction job is already in the queue and blocking further action from the configuration tool.

To find and remove a deployment or retraction job that is already in the queue, do the following:

1. Start the SharePoint 2010 Management Shell as an administrator and then run the following command to view jobs in the queue:

   Stsadm –o enumdeployments
   

2. Review existing deployments for the following information: Type is Retraction or Deployment, File is powerpivotwebapp.wsp or powerpivotfarm.wsp.

3. For deployments or retractions related to PowerPivot solutions, copy the GUID value for JobId and then paste it into the following command (use the Mark, Copy, and Paste commands on the Shell’s Edit menu to copy the GUID):

   Stsadm –o canceldeployment –id “<GUID>”
   

4. Retry the task in the configuration tool by clicking Validate followed by Run.

For all other errors, check the ULS logs. For more information, see View Log Files (PowerPivot for SharePoint).