Upgrade PowerPivot for Excel and PowerPivot Data

Using the newest features requires that you first uninstall the previous version, and then install the current version. PowerPivot for Excel cannot be upgraded using a Setup program. Furthermore, you cannot use an older and newer version of the add-in on the same computer.

1. In Control Panel, in Programs, click Uninstall a program. Select Microsoft SQL Server PowerPivot for Excel, and then click Uninstall.

2. Go to the download site that provides the installation software for the SQL Server 2012 version of the add-in and run the setup program that installs the add-in.

A PowerPivot workbook is one that contains embedded PowerPivot data inside an Excel workbook. Upgrading a workbook has two benefits:

• Use new features in PowerPivot for Excel.

• Enables scheduled data refresh for SQL Server 2008 R2 PowerPivot workbooks that run on a SQL Server 2012 instance.

Upgrading a workbook resolves the following error that occurs when attempting scheduled data refresh on a SQL Server 2008 R2 workbook: “Refresh operation for workbooks created with earlier version of PowerPivot is not available.”

Important
You cannot rollback an upgraded workbook, so be sure to make a copy of the file if you want to use it in the previous version of PowerPivot for Excel, or on a previous version of PowerPivot for SharePoint.

How to upgrade a workbook

There are two ways to upgrade a PowerPivot workbook. One approach is to upgrade each workbook manually by opening it in Excel on a computer that has the SQL Server 2012 version of PowerPivot for Excel, and then republish it to the server. When you open the workbook in the newer version of the add-in, the following internal operations occur: the data provider in the workbook data connection string is updated to MSOLAP.5, metadata is updated, and relationships are recreated to conform to a newer implementation.

Alternatively, you can enable an auto-upgrade feature that automatically upgrades a SQL Server 2008 R2 PowerPivot workbook when data refresh runs (only workbooks that are configured for scheduled data refresh are upgraded).

Note
Automatic upgrade is a server configuration feature; you cannot enable or disable it for specific workbooks, libraries, or site collections.

Caution

You cannot rollback upgrade for workbooks that are upgraded automatically on the server. Once a workbook is upgraded, it remains upgraded. To use a previous version, you can republish the previous workbook to SharePoint, restore a previous version, or recycle the workbook. For more information about restoring or recycling a document in SharePoint, see Plan to protect content by using recycle bins and versioning.

How to configure automatic upgrade during data refresh

To use automatic upgrade, you must select the Automatically upgrade PowerPivot workbooks to enable data refresh from the server checkbox in the PowerPivot Configuration Tool. Within the tool, the checkbox is on the Upgrade PowerPivot System Service page, and on the Create PowerPivot Service Application page if you are configuring a new installation.

You can run the following cmdlet to verify whether automatic upgrade is enabled:

PS C:\Windows\system32> Get-PowerPivotSystemService

The output from Get-PowerPivotSystemService is a list of properties and corresponding values. You should see WorkbookUpgradeOnDataRefresh in the property list. It will be set to true if automatic upgrade is enabled. If it is false, continue to the next step, enabling automatic workbook upgrade.

To enable automatic workbook upgrade, run the following command:

PS C:\Windows\system32> Set-PowerPivotSystemService –WorkbookUpgradeOnDataRefresh:$true –Confirm:$false

After you upgrade the workbook, you can use scheduled data refresh and new features in the PowerPivot for Excel add-in. Remember that after you upgrade workbook, it must be hosted on a SQL Server 2012 version of PowerPivot for SharePoint. You cannot save it to a SQL Server 2008 R2 PowerPivot server. Any further modifications to the workbook must be made in the SQL Server 2012 version of PowerPivot for Excel.

You can run older and newer versions of PowerPivot workbooks side by side on a SQL Server 2012 instance of PowerPivot for SharePoint. Depending on how you installed the server, you might need to install a previous version of the Analysis Services OLE DB provider before you can access older and newer workbooks on the same server.

On an upgraded server, both the previous and current versions of the Analysis Services OLE DB provider are installed, allowing you to run any version of workbook with no additional steps. On a new installation of PowerPivot for SharePoint, you must manually install the SQL Server 2008 R2 version of the OLE DB provider before you can load SQL Server 2008 R2 workbooks on that server. For more information, see Install the Analysis Services OLE DB Provider on SharePoint Servers.

Note that running newer version workbooks on a SQL Server 2008 R2 instance of PowerPivot for SharePoint is not supported. A SQL Server 2008 R2 instance will not load a workbook that you created in the SQL Server 2012 version of PowerPivot for Excel.

Use the following instructions to check which OLE DB provider is used in a PowerPivot workbook. Checking the data connection information does not require the PowerPivot for Excel add-in to be installed.

1. In Excel, on the Data tab, click Connections. Click Properties.

2. On the Definition tab, the provider version appears at the beginning of the connection string.

   Provider=MSOLAP.5 indicates the workbook is SQL Server 2012.

   Provider=MSOLAP.4 indicates SQL Server 2008 R2.

   Data Source=$Embedded$ indicates that the workbook is a PowerPivot workbook, using an embedded database.

Use the following instructions to check which OLE DB provider is the current version on the server or workstation that runs PowerPivot workbooks. Knowing the current version can help you troubleshoot data connection errors after upgrading.

1. In the Registry Editor, go to HKEY_CLASSES_ROOT

2. Scroll down to MSOLAP. Verify that MSOLAP.5 is listed among the OLAP providers installed on the system. Verify that MSOLAP | CurVer is set to MSOLAP.5

Concepts