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

I Don't Use PowerPivot Yet

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.

  1. Microsoft Excel 2010 or Microsoft Excel 2013
  2. Knowing if your Excel 2010 software is 32-bit or 64-bit. To find out, open Excel. Click File, then Help. The version and bitness information will appear on the right side of the window as shown below:

            

If you are using Excel 2010

If you aren’t intending to share your PowerPivot workbooks with other people or use PowerPivot workbooks created by others, you can simply download the newest version of PowerPivot for Excel 2010 and install it on your computer. First, read the download page to understand the software and hardware requirements. You can find more information on the requirements here.

Make sure you download the 32-bit version if you have 32-bit Excel



Or the 64-bit version if you have 64-bit Excel


After you’ve installed PowerPivot, check out the PowerPivot Overview to help you get started using the features. 

If you are using Excel 2013

If you are using Excel 2013, getting started with PowerPivot is even easier, since PowerPivot is already installed. You just need to enable PowerPivot in Excel 2013. Here are a few more considerations:
  1. Power View sheets, Data Models, and PowerPivot add-in not supported in Office RT
  2. Excel 2013 – Implications for PowerPivot and Excel Data Import Users

I don’t user PowerPivot yet, but other people in my company do

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:

  1. An enterprise PowerPivot solution is composed of two products – PowerPivot for Excel and PowerPivot for SharePoint
  2. PowerPivot for Excel is available in three versions – SQL Server 2008 R2 add-in, SQL Server 2012 add-in, and Excel 2013 (built in)
  3. PowerPivot is available in three versions – SQL Server 2008 R2, SQL Server 2012, and SQL Server 2012 SP1 (compatible with Excel 2013 client)
  4. To use PowerPivot for Excel and PowerPivot for SharePoint together, the versions have to match

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.

The first step is to find out which version of PowerPivot for Excel and PowerPivot for SharePoint is being used in your organization. There are a few ways to go about this.

Check the PowerPivot Version

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.

I use PowerPivot now, and want to know more about using the newer versions

The SQL Server 2012 version of PowerPivot has many new features and improvements over the SQL Server 2008 R2 version. And PowerPivot in Excel 2013 has many new features and improvements over the SQL Server 2012 version. While we certainly encourage you to use the newer versions, there are a few questions you need to address before you upgrade from one version to the next.

Workbook compatibility between PowerPivot versions

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:

  1. A company-wide PowerPivot solution is composed of two products – PowerPivot for Excel and PowerPivot for SharePoint
  2. PowerPivot is available in three versions – SQL Server 2008 R2, SQL Server 2012, and SQL Server 2012 SP1 (Excel 2013)
  3. To use PowerPivot for Excel and PowerPivot for SharePoint together, the versions on the Excel client and the SharePoint server have to match

Why do the versions have to match?

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.

What are the risks?

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, 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 is 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:

  1. Upgrading all client installations to the newer version of PowerPivot for Excel and upgrading the SharePoint installation to the newer version of PowerPivot for Sharepoint
  2. Using the previous version of PowerPivot for Excel to recreate the shared workbook from scratch