Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Upgrade Power Pivot for SharePoint

This topic summarizes the steps required to upgrade a deployment of Power Pivot for SharePoint to Microsoft SQL Server 2016 Power Pivot for SharePoint. The specific steps depend on the version of SharePoint your environment is currently running and include the Power Pivot for SharePoint Add-in (spPowerPivot.msi).

Applies to: SharePoint 2010 | SharePoint 2013

For release notes, see SQL Server 2014 Release Notes.

In this topic:

Prerequisites

Upgrade Steps

Upgrade an Existing SharePoint 2013 Farm

Upgrade an Existing SharePoint 2010 Farm

Workbooks

Data Refresh

Verify the Versions of Power Pivot Components and Services

Upgrading Multiple Power Pivot for SharePoint Servers in a SharePoint farm

Applying a QFE to a Power Pivot Instance in the Farm

Post-Upgrade Verification Tasks

  • If you are upgrading a multi-server SharePoint 2010 farm that has two or more Power Pivot for SharePoint instances, you must fully upgrade each server before continuing to the next server. A full upgrade includes running SQL Server Setup to upgrade Power Pivot for SharePoint program files, followed by SharePoint upgrade actions that configure the upgraded services. Server availability will be limited until you run upgrade actions in the appropriate Power Pivot Configuration Tool or Windows PowerShell.

  • All instances of Power Pivot System Service and Analysis Services in a SharePoint 2010 farm must be the same version. For information on how to verify the version, see the section Verify the Version of Power Pivot Servers in a Farm in this topic.

  • The Power Pivot configuration tools are one of the SQL Server shared features and all shared features upgrade at the same time. If during an upgrade process you select other SQL Server instances or features that require a shared feature upgrade, then the Power Pivot configuration tool will also be upgraded. You may have issues if the Power Pivot configuration tool is upgraded but your Power Pivot instance is not. For more information about SQL Server shared features, see Upgrade to SQL Server 2016 Using the Installation Wizard (Setup).

  • The Power Pivot for SharePoint Add-in (spPowerPivot.msi) installs side by side with previous versions. For example the SQL Server 2016 add-in installs to the folder c:\Program Files\Microsoft SQL Server\120\Tools\PowerPivotTools.

Arrow icon used with Back to Top link Top

Permissions

  • You must be a farm administrator to upgrade a Power Pivot for SharePoint installation. You must be a local administrator to run SQL Server Setup.

  • You must have db_owner permissions on the farm configuration database.

SQL Server:

  • If the existing Power Pivot installation is SQL Server 2008 R2, the SQL Server 2008 R2 Service Pack 2 (SP2) is required for an upgrade to SQL Server 2014 Power Pivot for SharePoint.

  • If the existing Power Pivot installation is SQL Server 2012, the SQL Server 2012 Service Pack 1 (SP1) is required for an upgrade to SQL Server 2014 Power Pivot for SharePoint.

SharePoint 2010:

  • If the existing installation is running SharePoint 2010, install the SharePoint 2010 Service Pack 2 before upgrading to SQL Server 2016 Power Pivot for SharePoint. For more information, see Service Pack 2 for Microsoft SharePoint 2010. Use the PowerShell command (Get-SPfarm).BuildVersion.ToString() to verify the version. To reference the build version to release date, see SharePoint 2010 Build Numbers.

Arrow icon used with Back to Top link Top

To upgrade Power Pivot for SharePoint deployed in SharePoint 2013, do the following:

powerpivot for sharepoint 2013 upgrade
  1. Run SQL Server 2016 Setup on backend server(s) that run Analysis Services in SharePoint mode. If the server hosts multiple instances of Analysis Services, upgrade at least the POWERPIVOT instance. The following list is a summary of setup wizard steps related to a Power Pivot upgrade:

    1. In the SQL Server Setup Wizard, click Installation.

    2. Click Upgrade from SQL Server…...

    3. On the Select Instance page, select the POWERPIVOT instance name and then click Next.

    4. For more information, see Upgrade to SQL Server 2016 Using the Installation Wizard (Setup)

  2. Restart the server.

  3. Run the Power Pivot for SharePoint add-in (spPowerPivot.msi) on each server in the SharePoint 2013 farm to install the data providers. The exception is servers where you ran the SQL Server setup wizard, which also upgrades data providers. For more information, see Download Microsoft SQL Server 2014 Power Pivot for Microsoft SharePoint 2013, and Install or Uninstall the Power Pivot for SharePoint Add-in (SharePoint 2013).

  4. Run the Power Pivot for SharePoint 2013 Configuration tool on one of the SharePoint application servers to configure the SharePoint farm with the updated solution files that the add-in installed. You cannot use Central SharePoint Administration for this step. For more information, see the following:

    1. From the Windows Start page, type Power Pivot and in the search results, click Power Pivot for SharePoint 2013 Configuration. Note the search may return both versions of the configuration tool.

      two powerpivot configuratoin tools

      Or

      On the Start menu, point to All Programs, click Microsoft SQL Server 2016 , click Configuration Tools, and then click Power Pivot for SharePoint 2013 Configuration Too. Note that this tool is listed only when Power Pivot for SharePoint is installed on the local server.

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

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

    4. Review the actions in the left pane 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 left task list, and then on the Parameters page, clear the Include this action in the task list checkbox.

    5. Optionally, review detailed information in the Script or Output 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\130\Tools\PowerPivotTools\SPAddinConfiguration\Log.

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

    6. 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 noteImportant

      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.

    7. 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?”.

    8. Click Yes to continue.

    9. Upgrading solutions and features in the farm can take several minutes to complete. During this time, connection requests for Power Pivot data will fail with errors similar to “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.

    For more information, see the following:

  5. Verify that upgrade succeeded by performing the post-upgrade steps and by checking the version of Power Pivot servers in the farm. For more information, see Post-Upgrade Verification Tasks in this topic and the following section.

Arrow icon used with Back to Top link Top

To upgrade Power Pivot for SharePoint deployed in SharePoint 2010, do the following:

powerpivot for sharepoint 2010 upgrade
  1. Download Service Pack 2 for Microsoft SharePoint 2010 and apply it on all servers in the farm. Verify that SharePoint SP2 installation succeeded. In Central Administration, on the Upgrade and Migration page, open the Check product and patch installation status page to view status messages related to SP2.

  2. Verify that the SharePoint 2010 Administration Windows service is running.

    Get-Service | where {$_.displayname -like "*SharePoint*"}
    
  3. Verify the SharePoint services SQL Server Analysis Services and SQL Server Power Pivot System Service are started in SharePoint Central Administration or use the following PowerShell command:.

    get-SPserviceinstance | where {$_.typename -like "*sql*"}
    
  4. Verify the Windows service SQL Server Analysis Services (Power Pivot) is running.

    Get-Service | where {$_.displayname -like "*powerpivot*"}
    
  5. Run SQL Server 2016 Setup on the first SharePoint application server that runs the SQL Server Analysis Services (Power Pivot) Windows service to upgrade the POWERPIVOT instance. On the Installation page of the SQL Server Setup Wizard, choose the upgrade option. For more information, see Upgrade to SQL Server 2016 Using the Installation Wizard (Setup).

  6. Restart the server before running the configuration tool. This step ensures that any updates or prerequisites installed by SQL Server Setup are fully configured on the system.

  7. Run the Power Pivot Configuration Tool on the first SharePoint application server that runs SQL Server Analysis Services (Power Pivot) service to upgrade the solutions and Web services in SharePoint. You cannot use Central Administration for this step.

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

    2. At startup, the configuration tool checks the upgrade status of the Power Pivot farm solution and Power Pivot web application solutions. If older versions of these solutions are detected, you will see the message “Newer versions of the Power Pivot 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 Power Pivot 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 Power Pivot 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 Power Pivot 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\130\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 noteImportant

      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.

    10. Upgrading solutions and features in the farm can take several minutes to complete. During this time, connection requests for Power Pivot 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.

  8. Repeat the process for each SQL Server Analysis Services (Power Pivot) service in the farm: 1) Run SQL Server setup 2) Run the Power Pivot Configuration tool.

  9. Verify that upgrade succeeded by performing the post-upgrade steps and by checking the version of Power Pivot servers in the farm. For more information, see Post-Upgrade Verification Tasks in this topic and the following section.

  10. Troubleshooting errors

    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.

    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 Power Pivot 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 Configure and View SharePoint Log Files and Diagnostic Logging (Power Pivot for SharePoint).

Arrow icon used with Back to Top link Top

Upgrading a server does not necessarily upgrade the Power Pivot workbooks that run on it, but older workbooks created in the previous version of Power Pivot for Excel will continue to work as before, using the features available in that release. Workbooks remain functional because an upgraded server has the version of the Analysis Services OLE DB provider that was part of the previous installation.

Arrow icon used with Back to Top link Top

Upgrade will impact data refresh operations. Scheduled data refresh on the server is available only for workbooks that match the server version. If you are hosting workbooks from the previous version, data refresh might no longer work for those workbooks. To re-enable data refresh, you must upgrade the workbooks. You can upgrade each workbook manually in Power Pivot for Excel, or enable the auto-upgrade for data refresh feature in SharePoint 2010. Auto-upgrade will upgrade a workbook to the current version prior to running data refresh, allowing data refresh operations to remain on schedule.

Arrow icon used with Back to Top link Top

All instances of Power Pivot System Service and Analysis Services must be the same version. To verify that all server components are at the same version, check version information for the following:

Verify the version of Power Pivot Solutions and the Power Pivot System Service

Run the following PowerShell command:

Get-PowerPivotSystemService

Verify the CurrentSolutionVersion. SQL Server 2016 is version 12.0.<major build>.<minor build>

Verify the version of the Analysis Services Windows Service

If you upgraded only some of your Power Pivot for SharePoint servers in a SharePoint 2010 farm, the instance of Analysis Services on un-upgraded servers will be older than the version expected in the farm. You will need to upgrade all of your servers to the same version in order for them to be usable. Use one of the following methods to verify the version of the SQL Server Analysis Services (Power Pivot) Windows service on each computer.

Windows File Explorer:

  1. Navigate to the Bin folder for the Power Pivot instance. For example C:\Program Files\Microsoft SQL Server\MSAS12.POWERPIVOT\OLAP\bin.

  2. Right-click msmdsrv.exe, and select Properties.

  3. Click Details.

  4. SQL Server 2016 file version should be 12.00.<major build>.<minor build>.

  5. Verify that this number is identical to the Power Pivot solution and system service version.

Service start information:

When the Power Pivot service starts, it writes version information into the Windows event log.

  1. Run Windows eventvwr

  2. Create a filter for source MSOLAP$POWERPIVOT.

  3. Look for an information level event similar to the following

    Service started. Microsoft SQL Server Analysis Services 64 Bit Evaluation (x64) RTM 12.0.2000.8.

Use PowerShell to verify file version.

You can use PowerShell to verify the product version. PowerShell is a good option if you want to script or automate version verification.

(get-childitem "C:\Program Files\Microsoft SQL Server\MSAS12.POWERPIVOT2000\OLAP\bin\msmdsrv.exe").VersionInfo

The above PowerShell command returns information similar to the following:

ProductVersion FileVersion FileName

12.0.2000.8 2014.0120.200 C:\Program Files\Microsoft SQL Server\MSAS12.POWERPIVOT2000\OLAP\bin\msmdsrv.exe

Verify the MSOLAP Data Provider Version on SharePoint

Use the following instructions to check which versions of the Analysis Services OLE DB providers are trusted by Excel Services. You must be a farm or service application administrator to check the trusted data provider settings of Excel Services.

  1. In Central Administration, in Application Management, click Manage service applications.

  2. Click the name of the Excel Services service application, for example ExcelServiceApp1.

  3. Click Trusted Data Providers. You should see MSOLAP.5 (Microsoft OLE DB Provider for OLAP Services 11.0). If you upgraded your Power Pivot for SharePoint installation, you will also see MSOLAP.4 from the previous version.

  4. For more information, see Add MSOLAP.5 as a Trusted Data Provider in Excel Services.

MSOLAP.4 is described as the Microsoft OLE DB Provider for OLAP Services 10.0. This version might be the default version from SQL Server 2008 that is installed with Excel Services, or it might be the SQL Server 2008 R2 version. The default version that SharePoint installs does not support Power Pivot data access. You must have the SQL Server 2008 R2 version or later to connect to Power Pivot workbooks on SharePoint. To verify you have the SQL Server 2008 R2 version, use the instructions in the previous section that explain how to verify the version by viewing file properties.

Verify the ADOMD.NET Data Provider Version

Use the following instructions to check which version of ADOMD.NET is installed. You must be a farm or service application administrator to check the trusted data provider settings of Excel Services.

  1. On your SharePoint application server, browse to c:\Windows\Assembly.

  2. Sort by assembly name and find the Microsoft.Analysis Services.Adomd.Client.

  3. Verify you have version 12.0.<build number>.

Arrow icon used with Back to Top link Top

In a multi-server topology that includes more than one Power Pivot for SharePoint server, all server instances and components must be the same version. The server that runs the highest version of the software sets the level for all servers in the farm. If you upgrade just some of the servers, the ones that are running older versions of the software will become unavailable until they are also upgraded.

After you upgrade the first server, additional servers that are not yet upgraded will become unavailable. Availability is restored after all servers run at the same level.

SQL Server Setup upgrades the Power Pivot solution files in place on the physical computer, but to upgrade the solutions in use by the farm, you must use the Power Pivot Configuration Tool described in a previous section of this topic..

Arrow icon used with Back to Top link Top

Patching a Power Pivot for SharePoint server updates existing program files with a newer version that includes a fix for a specific problem. When applying a QFE to a multi-server topology, there is no primary server that you must begin with. You can start with any server as long as you apply the same QFE to the other Power Pivot servers in the farm.

When you apply the QFE, you must also perform a configuration step that updates the server version information in the farm configuration database. The version of the patched server becomes the new expected version for the farm. Until the QFE is applied and configured across all machines, the Power Pivot for SharePoint instances that do not have the QFE will be unavailable to handle requests for Power Pivot data.

To ensure the QFE is applied and configured correctly, follow these instructions:

  1. Install the patch using the instructions that are provided with the QFE.

  2. Start the Power Pivot Configuration Tool.

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

  4. Review the actions that are included in the upgrade task, and then click Validate.

  5. Click Run to apply the actions.

  6. Repeat for additional Power Pivot for SharePoint instances in the farm.

    Important noteImportant

    In a multi-server deployment, be sure to both patch and configure each instance before continuing to the next machine. The Power Pivot Configuration Tool must complete the upgrade task for the current instance before you move on to the next instance.

To check version information for services in the farm, use the Check product and patch installation status page in the Upgrade and Patch Management section in Central Administration.

After upgrade is complete, use the following steps to verify the server is operational.

Task

Link

Verify the service is running on all computers that run Power Pivot for SharePoint.

Start or Stop a Power Pivot for SharePoint Server

Verify feature activation at the site collection level.

Activate Power Pivot Feature Integration for Site Collections in Central Administration

Verify individual Power Pivot workbooks are loading properly by opening a workbook and clicking on filters and slicers to initiate a query.

Check for the presence of cached files on the hard drive. A cached file confirms that the data file was loaded on that physical server. Look for cached files in the c:\Program Files\Microsoft SQL Server\MSAS12.POWERPIVOT\OLAP\Backup folder.

Test data refresh on selected workbooks that are configured for data refresh.

The easiest way to test data refresh is to modify a data refresh schedule, choosing the Also refresh as soon as possible checkbox so that data refresh runs immediately. This step will determine whether data refresh is successful for the current workbook. Repeat these steps for other frequently used workbooks to ensure that data refresh is functional. For more information about scheduling data refresh, see Schedule a Data Refresh (PowerPivot for SharePoint).

Over time, monitor the data refresh reports in Power Pivot Management Dashboard to confirm there are no data refresh errors.

Power Pivot Management Dashboard and Usage Data

For more information about how to configure Power Pivot settings and features, see Power Pivot Server Administration and Configuration in Central Administration.

For step-by-step instructions that guide you through all of the post-installation configuration tasks, see Initial Configuration (Power Pivot for SharePoint).

Arrow icon used with Back to Top link Top

Community Additions

ADD
Show:
© 2015 Microsoft