Can one open PowerPivot-based pivot tables in non-PowerPivot SKUs of Excel?

[Greg Truby]

Okay, here's my situation -- my employer's 2013 version is Microsoft Home and Business 2013 which means I cannot use PowerPivot.

I can open up a remote desktop connection to test my 2013-based add-ins and workbooks under Excel 2016. It appears that the SKU for my company's version of '16 is Microsoft Office Standard 2016. I go into the COM add-ins and still no PowerPivot, so I'm guessing that once again I'm screwed. I didn't see the "Standard" SKU listed in Rob Collie's listing of PowerPivot-enabled 2016 SKUs. Can anyone confirm if I'm correct -- and "Standard" 2016 is also sans PowerPivot?

Assuming this is indeed accurate, my next question is: if I somehow manage to get my own personal desktop installed with a version of Excel 2016 that includes PowerPivot, then when I build queries into workbooks that are PowerPivot-based and I then distribute those workbooks to others who still have the "standard" version of Excel. Will the queries fail? I realize the users wouldn't be able to open/edit the query. But would pivot tables based on a PowerPivot-created query fail?

 

[Matt Allington]

Yes on all counts. You need office professional 2016 (E3 or E4) to get power pivot. Ans yes the pivot tables you build on a version with power pivot won't refresh for users that don't have power pivot.

But, I have a suggestion for you. Power BI Desktop is free. Download that and build your model. Power BI service is free. I hear they just released the long awaited "analyse in Excel" feature. I haven't seen it, but I believe you open the data model in Power BI Swrvice and then click "analyse in Excel. This creates a pivot table connected to the cloud power BI service and I believe this will work for all versions of Excel. You could confirm this in your version and post back.

 

[starl]

Matt - the Power Pivot tables won't even pivot? There's no cache in the workbook?

 

[Matt Allington]

Matt - the Power Pivot tables won't even pivot? There's no cache in the workbook?

I think you are you asking me to confirm my previous comments. I can confirm the pivot tables will not work. You will see them pre-loaded with the data that was in the pivot when saved. The pivot table needs an instance of SSAS running on the laptop to work - not a pivot table cache. If you have a version of Excel with power pivot, a new instance of SSAS is spawned and it runs as a service in he background. It is this service that takes the request from the pivot table and does the calculations to populate the pivot table with the answer. If you don't have a version with Power Pivot, there is no such service available and hence it throws an error.

 

[starl]

It was probably the same question, but since I didn't have an understanding of the structure of powerpivot - I had to ask it a different way.
One thing.. your answer clarifies some of the things said at that summit last week...without even a basic understanding of the background of powerpivot, some things just don't make sense. Thanks.

ok - another scenario: I design a workbook with Power Pivot, the data comes from an external file on a network. I sent the workbook to another user with Power Pivot, but who does NOT have access to the network. One would assume, like any linked file, the user would get prompted for the source, and if not found, get an error message. Is this a correct assumption?

 

[Matt Allington]

One would assume, like any linked file, the user would get prompted for the source, and if not found, get an error message. Is this a correct assumption?

No and yes.

Firstly No: Power pivot takes a complete copy of the data, compresses it and loads it in to the workbook. Once loaded, the source data is no longer required, the workbook can be distributed at will to anyone that has power pivot installed. The workbook will fully operate without the source data.

Then Yes: if the user clicks "refresh" in the pivot table or on the data tab, the power pivot workbook will try to refresh against the original data source. If the current user doesn't have the data source then the refresh will fail. But even if the refresh fails, the report will continue to operate.

I never ask "end users" to refresh workbooks. I only ever distribute workbooks that have been refreshed.

 

[barjoman]

I think you are you asking me to confirm my previous comments. I can confirm the pivot tables will not work. You will see them pre-loaded with the data that was in the pivot when saved. The pivot table needs an instance of SSAS running on the laptop to work - not a pivot table cache. If you have a version of Excel with power pivot, a new instance of SSAS is spawned and it runs as a service in he background. It is this service that takes the request from the pivot table and does the calculations to populate the pivot table with the answer. If you don't have a version with Power Pivot, there is no such service available and hence it throws an error.

Matt,

My understanding is that this is only applicable up to Excel 2010. Excel 2013 and 2016 have the required functionality to view and edit (change fields, apply filters, etc) a PowerPivot based Pivot Table, but require the Add-in to edit the PowerPivot model itself.

The only exception to this is Excel for Mac, which does not have the capability to view a PowerPivot based Pivot Table under any circumstances.

Furthermore, it is possible to view and interact with a PowerPivot based Pivot Table through a browser so long as you are using Excel Services (or Sharepoint View mode) instead of Excel Web App.

Please see here:
https://support.office.com/en-us/ar...25d-bccd-4070d2413aa7?ui=en-US&rs=en-US&ad=US

Unfortunately, I cannot find the documentation stating that non-PowerPivot SKUs of Excel can view and interact with workbooks containing Data Models, but I've tested both of these scenarios (Web version and Desktop version) successfully.

 

[Matt Allington]

That is not my understanding.
Excel 2010. It is always an addin and it works with all Windows skus (to my knowledge)
Excel 2013. You need professional plus to author the models, and professional to view/read/interact with the same models. All other skus including home and student edition etc will not work.
Excel 2016. Same as 2013, however you only need professional to both author and view/read/interact.
SharePoint has SSAS behind it in some skus and hence some will work. SharePoint online has a data model file size limit. The size has changed and I am not 100% sure what it is as of today. It was 10MB and may be 40 or 50 MB now - I'm not 100% sure. SharePoint Enterprise has no theoretical limit (like Power Pivot 64bit). SharePoint Foundation will not work.

If someone can demonstrate this is incorrect, then of course I am happy to improve my understanding. However I beleive this information to be correct.

https://technet.microsoft.com/en-au/office/dn788955.aspx?f=255&MSPPError=-2147217396

 

[barjoman]

Matt,

I found this post where the first commenter confirms the case I described.
What Versions of Office 2016 Contain Power Pivot? - PowerPivotPro

I've tested one such workbook on my home machine which is Home and Business 2013 (I think...it is one of the basic versions) and it worked with no issues.

With respect to the file size limit for browser viewing, my understanding is that there is a 10MB limit for the non-Data Model portion of the file. As in, the Data Model itself doesn't count towards the cap, however, I haven't been able to test this because I don't have a file that big

 

[Matt Allington]

I just spoke to Avi (who wrote that comment) and he confirmed had has the same understanding as me - i.e. it doesn't work for the home and student edition but it does work for professional. There may be other editions but I don't know what they are and regardless, I haven't test any of them. If someone has the home and student edition, then maybe they could download a sample workbook from one of the posts and comment back.

Regarding SharePoint, there is definitely a datamodel limit too for SharePoint online.
this post isn't about SharePoint, but it is the same issue. https://powerbi.microsoft.com/en-us/blog/the-conceptual-data-model-and-limits/