Upgrade PowerPivot for SharePoint
SQL Server 2012 includes new features for PowerPivot for SharePoint. If you installed the SQL Server 2008 R2 version of PowerPivot for SharePoint, you can upgrade that instance to use the new features that are described in What's New (Analysis Services).
If you are upgrading a multi-server farm that has two or more PowerPivot 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 program files, followed by SharePoint upgrade actions that configure the upgraded services. Server availability will be limited until you run upgrade actions in the PowerPivot Configuration Tool or PowerShell.
Important
The PowerPivot configuration tool is 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, the PowerPivot configuration tool will be upgraded. You may have issues if the PowerPivot configuration tool is upgraded but your PowerPivot instance is not. For more information about SQL Server shared features, see Upgrade to SQL Server 2012 Using the Installation Wizard (Setup).
Upgrading a server does not necessarily upgrade the PowerPivot workbooks that run on it, but older workbooks created in the previous version of PowerPivot 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.
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 will no longer work for those workbooks. To re-enable data refresh, you must upgrade the workbooks. You can upgrade each workbook manually in PowerPivot for Excel, or enable the auto-upgrade for data refresh feature. Auto-upgrade will upgrade a workbook to the current version prior to running data refresh, allowing data refresh operations to remain on schedule. For more information about this feature, see Upgrade PowerPivot for Excel.
This topic contains the following sections:
Prerequisites
Upgrade Steps
Checking the Version of PowerPivot Servers in a Farm
Upgrading Multiple PowerPivot for SharePoint Servers in a SharePoint farm
Applying a QFE to a PowerPivot Instance in the Farm
Post-Upgrade Verification Tasks
Prerequisites
SQL Server 2008 R2 Service Pack 1 is required on the SQL Server 2008 R2 installation of PowerPivot for SharePoint. SQL Server 2012 Setup will only upgrade a previous version that is running SP1 or later.
SharePoint 2010 Service Pack 1 is required for SQL Server 2012 PowerPivot for SharePoint features.
SharePoint 2010 August 2010 Cumulative Update, or later is required when upgrading from a previous version of PowerPivot for SharePoint to SQL Server 2012. The August 2010 Cumulative update or later should be installed after installing SharePoint Service Pack 1. A new installation of SQL Server 2012 PowerPivot for SharePoint does not require the cumulative update. For more information, see August 2010 Cumulative Update for SharePoint has been released.
You must be a farm administrator to upgrade a PowerPivot 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.
Upgrade Steps
To upgrade a PowerPivot for SharePoint server, do the following:
Download SharePoint Server 2010 SP1 and apply it on all servers in the farm. Review the Known Issues document for guidance on how to prepare for and perform the SP1 upgrade.
Verify that SharePoint SP1 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 SP1.
Download the SharePoint 2010 August 2010 cumulative update or a later cumulative update. Apply the cumulative update on all servers in the farm.
If you run the PowerPivot for SharePoint Configuration Tool and receive an error message similar to the following, even after you have installed SharePoint Server 2010 SP1 and run the configuration wizard, it is an indication the Cumulative Update is not installed:
“SharePoint 2010 SP1 is installed but e current server has not been upgraded to SP1. You must run SharePoint configuration wizard to complete the upgrade.”
Run SQL Server 2012 Setup on the SharePoint application server that runs SQL Server Analysis Services (PowerPivot) service to upgrade the POWERPIVOT instance. On the Installation page of the Installation Wizard, choose the upgrade option. For more information, see Upgrade to SQL Server 2012 Using the Installation Wizard (Setup).
Run SharePoint upgrade actions to upgrade the solutions and Web services in SharePoint. Use either the PowerPivot Configuration Tool or PowerShell cmdlets to perform this task. You cannot use Central Administration for this step. For instructions, click one of the following links:
Repeat the previous step for any other instances of SQL Server Analysis Services (PowerPivot) service in the farm.
Verify that upgrade succeeded by performing the post-upgrade steps and by checking the version of PowerPivot servers in the farm. For more information, see Post-Upgrade Verification Tasks in this topic and the following section.
Checking the Version of PowerPivot servers in a farm
All instances of PowerPivot 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:
Microsoft.AnalysisServices.SharePoint.Integration.dll file. This is the file that has the object model for the PowerPivot System Service.
Analysis Services service on each application server that has an installation of PowerPivot for SharePoint.
How to check the version of PowerPivot solutions and PowerPivot System Service
In \Windows\Assembly, find the Microsoft.AnalysisServices.SharePoint.Integration.dll file.
Right-click Microsoft.AnalysisServices.SharePoint.Integration.dll, and select Properties.
Click Details.
File version should be 11.00.<major build>.<minor build>.
There are multiple copies of Microsoft.AnalysisServices.SharePoint.Integration.dll on a PowerPivot for SharePoint server. Copies of the file will be found in the global assembly, \inetpub\wwwroot\wss\VirtualDirectories\80\bin\, and in \Program Files\Microsoft SQL Server\100\SDK\Assemblies.
When checking for file versions in an installation, always use the version that is in the Assembly folder. This is the copy that Setup upgrades. Other copies of the file are added by the powerpivotwebapp.wsp solution package or by Setup if you install connectivity components. The solution package will update the copy it added. Depending on where you are in a multi-server upgrade operation, the copy that is in \inetpub might be newer than the version that is in the global assembly.
How to check the version of Analysis Services
If you upgraded only some of your PowerPivot for SharePoint servers in a 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. The following instructions explain how to verify the version of the SQL Server Analysis Services (PowerPivot) service on each computer.
In \Program Files\Microsoft SQL Server\MSAS11_00.PowerPivot\OLAP\bin, find msmdsrv.exe.
Right-click msmdsrv.exe, and select Properties.
Click Details.
File version should be 11.00.<major build>.<minor build>.
Verify that this number is identical to the Microsoft.AnalysisServices.SharePoint.Integration.dll file.
If msmdsrv.exe is older than Microsoft.AnalysisServices.SharePoint.Integration.dll, run SQL Server Setup to upgrade the Analysis Services instance.
How to Check 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.
In Central Administration, in Application Management, click Manage service applications.
Click ExcelServiceApp1.
Click Trusted Data Providers. You should see MSOLAP.5 if you upgraded your PowerPivot for SharePoint installation. You should also see MSOLAP.4 from the previous version.
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 PowerPivot data access. You must have the SQL Server 2008 R2 version or later to connect to PowerPivot 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.
Upgrading Multiple PowerPivot for SharePoint Servers in a SharePoint Farm
In a multi-server topology that includes more than one PowerPivot 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 PowerPivot solution files in place on the physical computer, but to upgrade the solutions in use by the farm, you must use one of the approaches described earlier (namely, the PowerPivot Configuration Tool or PowerShell).
Applying a QFE to a PowerPivot instance in the farm
Patching a PowerPivot 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 PowerPivot 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 PowerPivot for SharePoint instances that do not have the QFE will be unavailable to handle requests for PowerPivot data.
To ensure the QFE is applied and configured correctly, follow these instructions:
Install the patch using the instructions that are provided with the QFE.
Start the PowerPivot Configuration Tool.
Click Upgrade Features, Services, Applications and Solutions, and then click OK.
Review the actions that are included in the upgrade task, and then click Validate.
Click Run to apply the actions.
Repeat for additional PowerPivot for SharePoint instances in the farm.
Important
In a multi-server deployment, be sure to both patch and configure each instance before continuing to the next machine. The PowerPivot 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.
Post-upgrade verification tasks
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 PowerPivot for SharePoint. |
|
Verify feature activation at the site collection level. |
Activate PowerPivot Feature Integration for Site Collections in Central Administration |
Verify individual PowerPivot 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 \Program Files\Microsoft SQL Server\MSAS11.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). Scheduled data refresh runs on a SQL Server 2012 PowerPivot for SharePoint instance, refreshing data in workbooks that have either been upgraded, or created using SQL Server 2012 version of PowerPivot for Excel. To save steps, you can enable an automatic upgrade feature that performs a version check and then automatically upgrades a workbook so that data refresh can proceed. For more information about this feature, see Upgrade PowerPivot for Excel. |
Over time, monitor the data refresh reports in PowerPivot Management Dashboard to confirm there are no data refresh errors. |
For more information about how to configure PowerPivot settings and features, see PowerPivot 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 (PowerPivot for SharePoint).
See Also
Concepts
PowerPivot for SharePoint Installation (SharePoint 2010)