THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

PowerPivot Compatibility across versions

There are several versions of PowerPivot available and starting with Excel 2013 there are also several versions of Excel. It is useful to look at the compatibility between the different versions of Excel and PowerPivot available now.

As a general rule when you have a PowerPivot workbook saved with a specific version of PowerPivot:

  • You can upgrade a workbook to a newer version of PowerPivot
  • You can upgrade a workbook to a newer version of Excel
  • You cannot open a workbook using a previous version of PowerPivot
  • You cannot open a workbook using a previous version of Excel, if it contains PowerPivot data

First caveat: you can open an Excel 2010 workbook containing a PowerPivot data model in Excel 2013, but once you save it in Excel 2013, you can no longer open it in Excel 2010. This is because a different file format for PowerPivot data used by Excel 2013 that cannot be understood by Excel 2010.

Second caveat: A file saved in Excel 2010 with PowerPivot 2012 (RTM or SP1) cannot be opened by an Excel 2010 running PowerPivot 2008 R2.

Each workbook has a compatibility level for PowerPivot data that corresponds to the PowerPivot version used to save the data. Here is the list of currently available compatibility levels:

  • 1050 (2008 R2)
  • 1100 (2012 RTM/SP1)
  • 1103 (Excel 2013)

The following table tells you what happens when you try to open an Excel 2010 file in a certain compatibility level (columns) with a certain version of PowerPivot (rows):

 

1050

1100

2008 R2

Read/Modify

Not Supported

2012 RTM

Read/Upgrade++

Read/Modify

2012 SP1

Read/Upgrade++

Read/Modify/Upgrade

++ It is important to call out that it is *not* supported editing 1050 PowerPivot models in the 2012 release – you *have to* upgrade the model to 110x before you can edit/refresh the model.

A detailed list of errors you can have opening different a workbook with a different compatibility level than the current version of PowerPivot you have is available in the article Version compatibility between PowerPivot Data Models in Excel 2010 and Excel 2013. As the article mention, PowerPivot for SharePoint can open PowerPivot workbooks of previous compatibility levels, but in order to upgrade the data on the server (scheduling a data refresh) you need to upgrade the workbook to the newer format.

You have to be careful using PowerPivot in mixed environment, especially when you want to share an Excel file containing PowerPivot data. Once you refresh PowerPivot data, you need to upgrade the compatibility level if you opened the workbook with a newer version of PowerPivot, and once you do that, your colleague with a previous version of PowerPivot can no longer open the same workbook. Saving the file on SharePoint makes it possible to navigate into data, but you need a correspondent or newer version of PowerPivot on SharePoint than that used to save the file.

For these reasons, when we provide the examples for our PowerPivot Workshop and our book for Excel 2010, the sample files are saved with the older version of PowerPivot (2008 R2). Now that we are working on the Excel 2013 version of the book and of the workshop (soon to be available!), we are finally going to release samples using a newer version of Excel and PowerPivot.

Published Monday, January 14, 2013 2:44 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Chien-sheng Tsai said:

Bought several different PowerPivot books and trying to learn, but have not been too successful.  In spite of the hype and all the books written by so many authors, PowerPivot just does not seem ready for prime time use.  The idea is good, but so many issues, and so much time required to track errors down--almost not worth the trouble--and now find it difficult to trust PowerPivot.  

I am in a learning mode, so not mission critical.  If I were to rely upon PowerPivot to build a real application, it would be problematic.  

So much promise heralded in the books, and such potential, all wasted in the poor execution.  Good thing it is a free add-in.  Very disappointing.  No wonder the other vendors are still doing well.  MS had potential of being # 1 in self-service BI with PPVT, if it worked well.  

I am running Excel 2010 32-bit on Vista 64-bit with 16 GB RAM and Quad-Core CPU, so it is not that my machine is inadequate.    

amount of interest

April 18, 2013 1:22 AM
 

Marco Russo (SQLBI) said:

Thank you for the feedback.

In order to be useful (for articles and book), can you be more specific about the reasons why you are not successful using PowerPivot?

Is this because of DAX Language, some bug in the product, some limitation in calculation and/or data modeling, ... ?

What do you see in other product that is better in this regards?

Thanks again,

Marco

April 18, 2013 3:21 AM
 

Kuba said:

Hi Marco,

my name is Kuba and I hope you could help me with issues I encounter with Powerpivot. I created a report based on a simple data model. It has a significant amount of data, however I thought it is still acceptable from Powerpivot perspective. The file is around 100MB.

I use a machine with 8 cores and 16 GB of RAM. However, if I start playing with different attributes from data and move them around ROWS, COLUMNS and FILTERS, I end up with Excel eating almost 15GB of RAM. Then, I start receiving strange errors in Powerpivot about expired sessions etc. It seems as if the tool was quite unstable. What I also noticed is that Excel does not release the memory. It does not matter if the report has complex layout and filters or it is as simple as possible - Excel will occupy those 15BB of RAM as long as I reopen the file. Any ideas why it is like that? Is it a memory management bug?

Greetings,

Kuba

May 17, 2013 5:09 PM
 

Marco Russo (SQLBI) said:

Kuba, the issue you describe might be related to some measure that materializes data in memory during query. If this is the case, you have some measure in the data model that could be optimized, but it's hard to make such a diagnosis without analyzing the data model in detail.

If the memory raise up also on simple measures such as SUM(table[column]), then the cause could be something else.

In Excel 2013 I observed some instability (and I hope that a Service Pack will be released soon for that) but it's the first time I heard about memory consumption so huge - unless it's caused by some measure.

Marco

May 18, 2013 9:18 AM
 

MerchandisePlanner said:

I am facing problems regarding the stability of the Power pivot that it keep crshing on me & I am loyhe data. many times I have faced the message (Document not saved) & always when I close the power pivot window the whole excel sheets is crashing & the windows asking me to explor the internet to find a solution & every time no solution appears.

I appreciat your support

May 20, 2013 2:25 PM
 

Marco Russo (SQLBI) said:

Which version crashes? You should open an incident with Microsoft Support for this type of issues in order to get some assistance. I have experienced some instability but more with Excel 2013 than with Excel 2010.

Marco

May 20, 2013 2:54 PM
 

Massimo said:

Hi Marco,

can i open a Excel 2013 file (with a PowerPivot and a PowerView, without Tabular Model) in a Sharepoint 2010 PowerPivot Gallery (with SQL2012)?

Thanks

Greetings,

massimo

May 22, 2013 1:10 PM
 

Marco Russo (SQLBI) said:

Massimo, you need SharePoint 2013 in order to open an Excel 2013 file with PowerPivot and/or PowerView.

Marco

May 22, 2013 3:24 PM
 

Badrul said:

I agree with one of the above commentators - PowerPivot in Excel 2010 is more like a beta version than the finished product.

My current is that after saving I cannot re-open the same Powerpivot on the same machine. Something to do with having installed SQL Server Express 2012 after Excel 2010 + Powerpivot 32 bit I reckon.

August 20, 2013 2:10 AM
 

Marco Russo said:

Badrul, this is strange. PowerPivot in Excel 2010 is more stable than in Excel 2013, at least if you use the latest build of the PowerPivot add-in (currently SP1 CU5). If you have a case that you can repro, I suggest you openin an incident with Microsoft support, they usually are able to identify and solve this type of setup issues.

August 20, 2013 2:47 AM
 

michael vardinghus said:

Hi

I have upgrade that goes totally wrong. How can that be ?

I have a powerpivot and before upgrade its connected to "thisworkbookdatamodel"

After accepting upgrade it explodes some of the connections inside powerpivot and make several connections inside excel all connected to same powerpivot pivottable but with weird connection strings.

I have no clue where to look and it seems weird there isnt any info or tool related this. It doesnt seem solid that a file can be this fragile between versions.

September 8, 2013 5:31 PM
 

Marco Russo (SQLBI) said:

Michael, do you know in which version you saved the file?

In any case, it's better opening an incident with Microsoft support for this type of issues.

Marco

September 8, 2013 5:35 PM
 

Marijke said:

Hi Marco

We have upgraded to Office 2013, specifically for the new features and capabilities in PowerPivot 2013. I am rather disappointed though because upon refreshing data the report doesn't want to save, or give various other error reasons. I find myself having to repair my office 2013 constantly.

Just my 2c worth.

October 16, 2013 3:27 AM
 

Shane said:

Hi Marco, I've been tasked with creating a powerpivot self serve solution for our organization and I wanted to know if there are any compatibility issues between 32-bit and 64-bit created files as we have multiple people on a combination of both 32-bit and 64-bit platforms

ie. if I create a powerpivot with 32-bit, is someone with 64-bit able to open, use, manipulate the data?

Everyone in the office either runs Windows XP with 32-bit powerpivot,

Windows 7 64-bit with 64-bit powerpivot or Windows 7 32-bit with powerpivot 32-bit

October 18, 2013 9:47 AM
 

Marco Russo (SQLBI) said:

Shane,

moving from 32 to 64 bit and from 64 to 32 bit is not an issue (you might have not enough memory available in 32 bit if the workbook saved in 64 bit is very large - where large means an Excel file larger than 250MB). The problem is PowerPivot compatibility between different version of Excel, as described in the blog post.

October 18, 2013 9:57 AM
 

Shane said:

Thank you very much Marco, appreciate the quick reply.

October 18, 2013 10:22 AM
 

Des said:

Hi Marco.

I have just come across your Blog in search of solution to an issue with Excel 2013 x64 and Powerpivot in fact I have had the same problem across Excel 2010 and the 2012 addin.

Do you now why the data connections, specifically one is to MSAS Cube on an SQLServer and the other is to a Teradata DB, WILL NOT save the connection passwords. The appearance is that the passwords are saved but when a Refresh is done the password is requested each time .Only  while still in the Data Model will it connect and refresh.

Once loaded up to Sharepoint 2013 it is useless because it can't be refreshed.

Any help would be greatly appreciated.

February 20, 2014 5:31 PM
 

Alvaro said:

Hi Marco.

First of all, I just wanted to let you know that I've been learning a lot about Power Pivot through out your You-tube tutorials and presentations, thanks for that.

I have been working with power pivot reports successfully with no problems at all, but I got an error message today when I tried to update my Power Pivot file, it said: SQLException64

              Microsoft PowerPivot Engine

              14.0.7109.5000

Do you have any idea why this happened and how to fix it?

Thanks Marco.    

April 1, 2014 10:22 AM
 

SimonaP said:

Hi Marco,

I have a very simple question.

Is it possible to schedulate in Office 365 a data refresh (through gateway configuration) for an Excel 2013 power pivot with a SQL Server 2008R2 data source? Or do I necessary have a Sql server 2012? That is: is it possibile to offer the customer an Office 365 Power BI solution without upgrading to SQL 2012?

Thank you very much and many congratulation for your excellant work.

Simona

May 12, 2014 6:25 AM
 

Marco Russo (SQLBI) said:

Hi Simona,

you can schedule a data refresh using SQL Server 2008 R2 as a data source. I'm sure it works with SQL Server 2008 and (if I remember well) also with SQL Server 2005.

Thanks for the nice words!

Marco

May 12, 2014 8:10 AM
 

Emma said:

Hi Marco,

I accessed excel file from another system in my office, saving takes about 10 minutes the file size is 261MB.

August 23, 2014 6:14 AM
 

Marco Russo (SQLBI) said:

It could be an issued caused by the network speed - have you tried to copy the file locally?

August 23, 2014 10:37 AM
 

DexterMorgan said:

PowerPivot=Garbage, until the instability and crashes are addressed. Can't change Measure names after-the-fact (if you change a few at once, all measures break with a yellow error icon), too many add-in crashes where the file can't be saved, and you basically have to nuke your installation of Excel-- and this is just after creating some pretty basic things and not really pushing any limits. Maybe in 2 or 3 years after MS stabilizes things, it could be more reliable, and therefore more useful. Right now, though (and this is after 2 YEARS), it still feels very beta. It's one thing for a Google web service to feel beta, but for hardcore business backend data analysis app to feel beta like this, it's pretty unacceptable, IMO..

September 18, 2014 3:21 PM
 

Marco Russo (SQLBI) said:

Morgan, I wouldn't agree with the definition of garbage :-) (many companies actually use Power Pivot in production), but I agree with the stability issue that affects Excel 2013 version (Excel 2010 seems better).

September 19, 2014 12:49 AM
 

Nikhil said:

Hi Marco,

My current organization use Microsoft office 2007. Since from my previous organization I m hands on working on power pivot so requested them and got a single desktop licence version of Microsoft Office standard 2010. I have been facing issue in installing the power pivot even after installing the .Net Framework4 and Visual for 2010 but excel is not opening up now. It opened only once and I cud see powerpivot icon in the excel (making me happy just for that moment). I closed the file and opened again but since then excel is just in starting... mode. I have done several uninstall/install process but nothing is helping me.

Can you please help?

September 22, 2014 1:29 AM
 

Marco Russo (SQLBI) said:

Nikhil, for these setup issues I would suggest you contacting Microsoft Support. They have much more tools to diagnose the issue you have.

Probably there is some conflict on your machine, but it's hard to say what without a detailed analysis.

September 22, 2014 1:55 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement