Upgrade Workbooks and Scheduled Data Refresh (SharePoint 2013)
This topic explains the user experience of workbooks created in previous PowerPivot environments and how to upgrade PowerPivot workbooks so that you can take advantage of new features introduced in this release. To learn more about new features, see What's New in PowerPivot.
Warning
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.
This topic contains the following sections:
Upgrade to SQL Server 2012 Service Pack 1 (SP1) workbooks from 2008 R2 Workbooks
Upgrade to Office 2013 workbooks from Versions created by using the 2012 PowerPivot Add-In for Excel
Overview of Upgrading Workbooks
A PowerPivot workbook is an Excel workbook that contains embedded PowerPivot data. Upgrading a workbook has two benefits:
Use new features in PowerPivot for Excel.
Enables scheduled data refresh for workbooks that run with a SQL Server 2012 SP1 Analysis Services server in SharePoint mode.
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.
The following table lists the support and behavior of PowerPivot workbooks based on the environment in which the workbook was created. The behavior described includes the general user experience, the supported upgrade options to upgrade the workbook to the particular environment, and the behavior of scheduled data refresh of a workbook that has not yet been upgraded.
Workbook Behavior and Upgrade Options
| Created In | < | Support and Behavior | > |
|---|---|---|---|
| 2008 R2 PowerPivot for SharePoint 2010 | 2012 PowerPivot for SharePoint 2010 | 2012 SP1 PowerPivot for SharePoint 2013 | |
| 2008 R2 PowerPivot for Excel 2010 | All Features | Experience: Users can interact with the workbook in the browser and use it as a data source for other solutions. Upgrade: Workbooks will auto upgrade in the document library if Auto Upgrade is enabled for the PowerPivot system Service in the SharePoint farm, Schedule data refresh: NOT supported. Workbook needs to be upgraded. |
Experience: Users can interact with the workbook and use it as a data source for other solutions. Upgrade: Auto upgrade is not available. Users must manually upgrade their 2008 R2 workbooks to the 2012 version or to the office 2013 version. Schedule data refresh: NOT supported. Workbook needs to be upgraded. |
| 2012 PowerPivot for Excel | Not supported | All Features | Experience: Users can interact with the workbook in the browser and use it as a data source for other solutions. Schedule data refresh is available. Upgrade: Auto upgrade is not supported. Users can manually upgrade their workbooks to the Office 2013 version. Schedule data refresh: supported. |
| Excel 2013 | Not supported | Not supported | All Features |
Upgrade to SQL Server 2012 Service Pack 1 (SP1) workbooks from 2008 R2 Workbooks
This section describes upgrading to SQL Server 2012 SP1 PowerPivot for Excel 2013 workbooks from SQL Server 2008 R2 PowerPivot for Excel 2010 workbooks.
Behavior Change: SQL Server 2008 R2 PowerPivot workbooks will not be automatically upgraded when they are used in SQL Server 2012 SP1 PowerPivot for SharePoint 2013. Therefore, scheduled data refreshes will not work for SQL Server 2008 R2 PowerPivot workbooks
2008 R2 workbooks will open in PowerPivot for SharePoint 2013, however scheduled data refreshes will not work. If you review the refresh history you will see an error message similar to the following:
"The workbook contains an unsupported PowerPivot model. The PowerPivot model in the workbook is in the SQL Server 2008 R2 PowerPivot for Excel 2010 format. Supported PowerPivot models are the following:
SQL Server 2012 PowerPivot for Excel 2010.
SQL Server 2012 PowerPivot for Excel 2013.
How to upgrade a workbook: The Scheduled data refresh will not work until you upgrade the workbook to a 2012 workbook. To upgrade the workbook and model it contains, complete one of the following:
Download and open the workbook in Microsoft Excel 2010 with the SQL Server 2012 PowerPivot for Excel add-in installed.
Open the PowerPivot window and upgrade the PowerPivot model.
Then save the workbook and republish it to SharePoint.
Download and open the workbook in Microsoft Excel 2013.
Open the PowerPivot window and upgrade the PowerPivot model.
Then save the workbook and republish it to the SharePoint server.
For more information on Changes to Analysis Services features, see Behavior Changes to Analysis Services Features in SQL Server 2014
For more information on refresh history, see View Data Refresh History (PowerPivot for SharePoint).
Upgrade to Office 2013 workbooks from Versions created by using the 2012 PowerPivot Add-In for Excel
This section describes Upgrading to SQL Server 2012 SP1 PowerPivot in Excel 2013 from SQL Server 2012 PowerPivot for Excel 2010 workbooks.
Upgrading a workbook resolves the following error that occurs when attempting scheduled data refresh on the previous workbook version workbook:
"Refresh operation for workbooks created with earlier version of PowerPivot is not available."
How to upgrade a workbook
Upgrade each workbook manually by opening it in Microsoft Excel 2013.
To upgrade the workbook and model it contains, download and open the workbook in Microsoft Excel 2013.
Open the PowerPivot window and upgrade the PowerPivot model.
Then save the workbook and republish it to the SharePoint 2013 server.
Upgrade to SQL Server 2012 workbooks from Versions created by using the 2008 R2 PowerPivot Add-In for Excel 2010
This section describes Upgrading to SQL Server 2012 PowerPivot for Excel 2010 from SQL Server 2008 R2 PowerPivot for Excel 2010 workbooks.
Upgrading a workbook resolves the following error that occurs when attempting scheduled data refresh on the previous workbook version workbook:
"Refresh operation for workbooks created with earlier version of PowerPivot is not available."
How to upgrade a workbook
There are two ways to upgrade:
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, a SharePoint Administrator can enable the auto-upgrade feature for the PowerPivot System Service in a SharePoint farm to automatically upgrade a SQL Server 2008 R2 PowerPivot workbook when schedule 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.
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.
Running Multiple Workbook Versions on a Newer Server
You can run older and newer versions of PowerPivot workbooks side by side on a SQL Server 2012 SP1 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.
Note that Publishing newer version workbooks on previous SQL Server instances 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, and a SQL Server 2012 instance will not load Office 2013 workbooks with advanced data models that you created using the SQL Server 2012 SP1 version of PowerPivot in Excel.
How to Check for MSOLAP Data Provider Information in a PowerPivot Workbook
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.
In Excel, on the Data tab, click Connections. Click Properties.
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.
How to Check for the Current Version of the MSOLAP Data Provider on a Local Computer
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.
In the Registry Editor, go to HKEY_CLASSES_ROOT
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
See Also
Migrate PowerPivot to SharePoint 2013
Upgrade PowerPivot for SharePoint
What's New in Analysis Services and Business Intelligence
View Data Refresh History (PowerPivot for SharePoint)