If you use (or want to start using) PowerPivot to analyze data and present it to others, this topic will help you understand the compatibility between different releases of these products and the differences in capabilities. I’ll walk you through the most common upgrade/compatibility scenarios and provide screenshots to help you understand. I will also link to more detailed information on how to accomplish some of the more complex tasks or for more information.
If you are someone who supports information workers (for example, a SharePoint Administrator or SQL Server DBA) you should still find this interesting, and I’ve provided links to content that will help you.
Let’s get started… Pick the statement below that best describes you and scroll down to the appropriate section.
I don’t use PowerPivot yet
I don’t use PowerPivot yet, but my coworkers do
I use PowerPivot now, and want to know more about compatibility between versions
PowerPivot is the product that started Microsoft Self-Service BI. It is an add-in for Excel 2010, and integrated in Excel 2013. You’ll need a few things before you get started using it.
After you’ve installed PowerPivot, check out the PowerPivot Overview to help you get started using the features.
As I mentioned above, PowerPivot is an add-in for Excel, but it also has an associated add-in for SharePoint. This add-in enables a company-wide self-service BI solution.
If the rest of your organization is using PowerPivot you will want to share PowerPivot workbooks with other people. However, there are some important things to understand:
You can use PowerPivot for Excel on your computer without using PowerPivot for SharePoint. But when you have access to a SharePoint site that has PowerPivot for SharePoint installed, you can easily share workbooks with others, view them in a web browser, schedule automatic refreshes of the data used in your PowerPivot workbook, and other cool things.
If one of your coworkers is using PowerPivot for Excel, ask them which version they are using. If they don’t know, the easiest way to find out is to look at the list of installed programs.
SQL Server 2008 R2 version looks like this: SQL Server 2012 version looks like this:
Alternatively, if you already have a version of PowerPivot for Excel installed, you can navigate to the PowerPivot for SharePoint library, open a shared workbook and start working with the data. If the version of PowerPivot you are using doesn’t match the shared version, you’ll get one of two following error messages:
This means you’re using an older version than the rest of your company. You should upgrade your computer with a newer version.
Stop! Don’t click OK yet! These messages mean you’re using a newer version of PowerPivot than the rest of your company. If you click OK and upgrade the data model, you might render the workbook unusable for others in your company. If you are using Excel 2010, you should install the SQL Server 2008 R2 version of PowerPivot instead. If you want to learn more about using the newer versions of PowerPivot for Excel, please read the next section to understand the compatibility between versions before you upgrade the workbook.
The backend data model that powers PowerPivot was modified in the SQL Server 2012 release. It was modified again for the Excel 2013 release. These modifications mean that the data model in a workbook created by using an earlier version must be “upgraded” to a newer version of the data model before it can be used with a newer version of PowerPivot. If you choose to upgrade the workbook, be aware that the data model upgrade process is not reversible. This means that the workbook will no longer be usable for people using the previous version of PowerPivot for Excel, and you will get the following error message:
If you are using PowerPivot for Excel on your own computer and don’t share workbooks with colleagues, this is typically not an issue.
If you share workbooks with colleagues but do not use PowerPivot for SharePoint (for example, you use a shared folder or a shared drive), this means that in order for everyone to keep sharing workbooks, everyone MUST be using the same version of PowerPivot for Excel.
However, if you are using PowerPivot for Excel AND PowerPivot for SharePoint, there is some potential risk and more coordination between end users and IT needs to take place. As a quick refresher:
When you upload a PowerPivot workbook to SharePoint, it is stored in the SharePoint document library. The document library itself is just a container, and will happily accept any version of Excel workbooks. The requirement for matching versions has to do with the data model discussed earlier. As an analogy, let’s think of the SharePoint platform as a car. In a SharePoint-enabled self-service BI solution, the PowerPivot for Excel workbook with the embedded data model is the fuel, and PowerPivot for SharePoint is the engine.
Uploading files to SharePoint is easy. The SharePoint document library will store any version of a workbook, just like the fuel tank of a car can store any type of liquid fuel. However, putting a SQL Server 2012 PowerPivot workbook in a SQL Server 2008 R2 PowerPivot for SharePoint environment will have the same effect as putting diesel fuel in a car with a gasoline engine… it won’t work well. You won’t be able to interact with the workbook when viewing it in the browser, and data refresh will not work.
The biggest issue is that “downgrading” workbooks to a previous version of the data model is not possible. Therefore, if other people are using the PowerPivot workbook, they will have to upgrade their version of PowerPivot:
However, if your solution uses PowerPivot for SharePoint, the PowerPivot for SharePoint environment will also need to be upgraded to the compatible version. This is a much larger undertaking, will require the time and expertise of the IT department, and has licensing cost considerations. Therefore, this isn’t undertaken lightly.
Adding to the risk is that the version control feature of SharePoint is often not enabled for document libraries that contain PowerPivot workbooks. One of the features of PowerPivot is the ability to import large amounts of data. This can often result in workbooks that are quite large. Sorting multiple versions of large workbooks could quickly consume the server storage capacity. Thus, it is common for PowerPivot libraries to not have versioning enabled.
The result of these factors is the distinct possibility of one user in an organization installing a newer version of PowerPivot for Excel and upgrading the data model of a workbook to one incompatible with other users in the company and with the version of PowerPivot for SharePoint. Since the upgrade process is irreversible, unless a backup copy of the workbook is available, the only options are: