If you’ve previously used a SQL Server Power Pivot add-in to create an embedded Data Model in Excel 2010, the new integrated Data Model in later versions of Excel comes with little to no surprises. In Excel 2013 or later, the same Data Model feature you used in Power Pivot moves intact (i.e., with full support of DAX expressions, KPIs, hierarchies, etc.) out of the purview of SQL Server add-ins for Excel, and into Excel proper.
This is a big paradigm shift in feature placement, and it has a corollary at the server level. As the following diagram indicates, upgrading a Data Model introduces new hosting requirements if you want to view, consume, and manage Workbook Data Models in a collaborative environment. At the same time, upgrading to Excel 2013 opens up new opportunities for using a model in the cloud. But those opportunities come at the cost of interoperability. After upgrading, you will no longer be able to edit or use a Workbook Data Model in Excel 2010 using either previous release of the Power Pivot for Excel add-in, nor can you host it using Excel Services in SharePoint Server 2010. As you’ll see, when it comes to Data Models, upgrade is one way, from client all the way down to the hosting platform.
Organizations that mix desktop or server applications in the same computing environment will run into version compatibility problems if they upgrade a model to a newer format when colleagues are still using Excel 2010 and earlier versions of Power Pivot for Excel. This article describes the upgrade path, explains the dependencies between client and server applications, and lists the error messages that occur when using Data Models in a mixed environment.
Let’s review the above diagram one part at a time to understand the implications.
For Excel 2010, SQL Server released a series of Excel add-ins for building Data Models that simplify and enrich data analysis in PivotTables, PivotCharts, and (in SQL Server 2012 only) Power View reports. The Power Pivot for Excel add-in provided the canvas for authoring a Data Model, as well as internal components for loading and querying the data on the desktop.
If you wanted to share your data analysis in SharePoint, you needed equivalent server components to load and query the Data Model behind the scenes. Power Pivot for SharePoint filled that need.
Notice that when upgrading the servers (by moving on to the next version of Power Pivot for SharePoint or migrating to SharePoint Server 2013), you can continue to use the Data Models as-is, without upgrading the model to a newer format. This is indicated by the dotted lines that extend diagonally from a Data Model to each subsequent version of the server, in a forward only direction.
Note: When you use older Power Pivot workbooks on newer server platforms, you get the same functionality as before, minus scheduled data refresh. Using scheduled data refresh is supported only for Data Models that conform to the version of the server. See Upgrade Workbooks and Scheduled Data Refresh (SQL Server 2012 SP1) for more information.
Fast forward to Excel 2013 workbook Data Models
In the right side of the diagram, notice how Excel 2013 and Excel Services fully integrate Data Models as an internal feature. Just as Excel was enhanced to store Data Models, Excel Services was enhanced to stream a Data Model on a server platform. Because Excel is now the owner of the modeling experience, model storage and data rendering move in tandem across the hosting platforms used for Excel workbooks, including the cloud, but excluding Office Web Apps Server. Office Web Apps Server does not support a Workbook Data Model creation, edit, or consumption experience.
In SharePoint Server 2013, a SQL Server 2012 (SP1) Power Pivot for SharePoint add-in still exists, but only to provide a few extras: scheduled data refresh, Power Pivot Gallery, and Power Pivot Management Dashboard. In a clean break from the past, the SQL Server 2012 SP1 version of Power Pivot for SharePoint no longer performs the heavy lifting when it comes to redirecting queries to Data Models stored in memory.
Notice that SQL Server 2012 SP1 Analysis Services in SharePoint mode is still in the picture. On premises, SQL Server Analysis Services continues to store the Data Model, but it now runs outside of SharePoint, accepting data load and query requests directly from Excel Services instead of the Power Pivot Web Service.
Note to SharePoint Server 2010 c ustomers : Client applications used to create a Data Model need to align with the server applications that host a Data Model. For SharePoint Server 2010, this means you’ll need to continue to use Excel 2010 and a Power Pivot for Excel add-in to create and maintain a Data Model. Excel 2013 cannot be used to create Data Models that run on SharePoint Server 2010.
Error messages in mixed environments
This section describes errors that occur when using a Data Model that is incompatible with your version of Excel.
“Initialization of the data source failed”
This error occurs when opening a newer Data Model in an older version of the Power Pivot add-in. For example, the Data Model was created in Excel 2013 and then opened in Excel 2010.
To work around this error, upgrade to Excel 2013, Office Professional Plus edition. Data Models created in Excel 2013 will run only in workbooks that are also opened in Excel 2013.
More information: Power Pivot error: “Initialization of the data source failed”
“This workbook has a Power Pivot data model created using a previous version of the Power Pivot add-in. You’ll need to upgrade this data model with Power Pivot in Microsoft Excel 2013”
This error message occurs when you open an older Data Model (created in Excel 2010 using the 2008 R2 or 2012 version of the Power Pivot add-in) in Excel 2013 or Excel 2016. The message asks you to upgrade to the newer model format.
“The workbook contains an unsupported Power Pivot model”
This error message happens when you open a newer Data Model (created in Excel 2013 or later) in SharePoint Server 2010. SharePoint Server 2010 will not recognize the new Data Model format.
If you’re not upgrading to SharePoint 2013 right away, find a previous version of the workbook and use Excel 2010 and a Power Pivot add-in to maintain the Data Model. Alternatively, you can use Microsoft 365 to store the workbook. Microsoft 365 supports loading and querying Data Models without Power Pivot for SharePoint. Microsoft Data Centers provide behind the scenes support for hosting Data Models that are embedded in Excel 2013 or Excel 2016 workbooks.
Get more information
Workbook Data Models in Excel 2013 or later enable powerful data analysis, including embedded Power View report visualizations. Follow these links to read about new features, the benefits of upgrading, and other related content.
Upgrade Power Pivot Data Models to Excel 2013 or Excel 2016
Upgrade Workbooks and Scheduled Data Refresh (SQL Server 2012 SP1)
Power Pivot Version Compatibility (on the TechNet WIKI in English only)
Data Model specification and limits
Business intelligence capabilities in Excel Services (SharePoint Server 2013): Look for the sections on “data models.”
