Export (0) Print
Expand All

Upgrade Workbooks and Scheduled Data Refresh (SharePoint 2013)

 

This topic explains the user experience of workbooks created in previous Power Pivot environments and how to upgrade Power Pivot 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 Power Pivot.

System_CAPS_warningWarning

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:

A Power Pivot workbook is an Excel workbook that contains embedded Power Pivot data. Upgrading a workbook has two benefits:

  • Use new features in Power Pivot for Excel.

  • Enables scheduled data refresh for workbooks that run with a SQL Server 2012 SP1 Analysis Services server in SharePoint mode.

System_CAPS_importantImportant

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 Power Pivot for Excel, or on a previous version of Power Pivot for SharePoint.

The following table lists the support and behavior of Power Pivot 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 Power Pivot for SharePoint 2010

2012 Power Pivot for SharePoint 2010

2012 SP1 Power Pivot for SharePoint 2013

2008 R2 Power Pivot 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 Power Pivot 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 Power Pivot 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

Arrow icon used with Back to Top link

This section describes upgrading to SQL Server 2012 SP1 Power Pivot for Excel 2013 workbooks from SQL Server 2008 R2 Power Pivot for Excel 2010 workbooks.

Behavior Change: SQL Server 2008 R2 Power Pivot workbooks will not be automatically upgraded when they are used in SQL Server 2012 SP1 Power Pivot for SharePoint 2013. Therefore, scheduled data refreshes will not work for SQL Server 2008 R2 Power Pivot workbooks

2008 R2 workbooks will open in Power Pivot 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 Power Pivot model. The Power Pivot model in the workbook is in the SQL Server 2008 R2 Power Pivot for Excel 2010 format. Supported Power Pivot models are the following:

  • SQL Server 2012 Power Pivot for Excel 2010.

  • SQL Server 2012 Power Pivot 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 Power Pivot for Excel add-in installed.

    Open the Power Pivot window and upgrade the Power Pivot model.

    Then save the workbook and republish it to SharePoint.

  • Download and open the workbook in Microsoft Excel 2013.

    Open the Power Pivot window and upgrade the Power Pivot 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 2016

For more information on refresh history, see View Data Refresh History (Power Pivot for SharePoint).

Arrow icon used with Back to Top link

This section describes Upgrading to SQL Server 2012 SP1 Power Pivot in Excel 2013 from SQL Server 2012 Power Pivot 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 Power Pivot is not available.”

How to upgrade a workbook

  1. Upgrade each workbook manually by opening it in Microsoft Excel 2013.

  2. To upgrade the workbook and model it contains, download and open the workbook in Microsoft Excel 2013.

  3. Open the Power Pivot window and upgrade the Power Pivot model.

  4. Then save the workbook and republish it to the SharePoint 2013 server.

Arrow icon used with Back to Top link

This section describes Upgrading to SQL Server 2012 Power Pivot for Excel 2010 from SQL Server 2008 R2 Power Pivot 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 Power Pivot is not available.”

How to upgrade a workbook

There are two ways to upgrade:

  1. Upgrade each workbook manually by opening it in Excel on a computer that has the SQL Server 2012 version of Power Pivot 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.

  2. Alternatively, a SharePoint Administrator can enable the auto-upgrade feature for the Power Pivot System Service in a SharePoint farm to automatically upgrade a SQL Server 2008 R2 Power Pivot workbook when schedule data refresh runs (only workbooks that are configured for scheduled data refresh are upgraded).

    System_CAPS_noteNote

    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 Power Pivot workbooks to enable data refresh from the server checkbox in the Power Pivot Configuration Tool. Within the tool, the checkbox is on the Upgrade Power Pivot System Service page, and on the Create Power Pivot 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 Power Pivot for Excel add-in.

Arrow icon used with Back to Top link

You can run older and newer versions of Power Pivot workbooks side by side on a SQL Server 2012 SP1 instance of Power Pivot 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 Power Pivot 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 Power Pivot 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 Power Pivot in Excel.

Arrow icon used with Back to Top link

Use the following instructions to check which OLE DB provider is used in a Power Pivot workbook. Checking the data connection information does not require the Power Pivot 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 Power Pivot 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 Power Pivot 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

Arrow icon used with Back to Top link

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft