Share via

Excel 2013 PowerPivot workbook with PowerPivot for SharePoint 2010 ?

  • Article

Last week, One of my customer had interesting issue with PowerPivot Workbooks. Here is scenario:

My customer has created a PowerPivot Model by using Excel 2013. As you may know, PowerPivot is now shipped with Excel 2013 and it is another addin shipped with Excel.

Please note that imagesbelow are illustrative images.

So Customer created very simple model

 

image

 

And also created Pivot Table in a workbook. Everything is working fine excel side.

 

image

As next step, we deployed our workbook to SharePoint PowerPivot Gallery and try to render workbook through sharepoint. When you first open workbook. You can see that Excel Services manages to render workbook properly. There is no problem so far. Because workbook didn't need to read data from source or doesn't need to refresh data.

So when you click one of drill down options or filter options, excel workbook needs to refresh data and also needs to load workbook into your powerpivot for SharePoint Instance. At this point we get following error message:

“Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: ThisWorkbookDataModel.”

image

 

We couldn't find anything useful in ULS Logs.  After further investigations, we have found out that  This issue occurs because Excel 2013 uses a different method to maintain data models in workbooks than does SQL Server 2008 R2 or SQL Server 2012 PowerPivot for Excel 2010. Excel 2013 uses an internal connection for communication between the workbook and the embedded PowerPivot data. This internal connection does not reference a Microsoft OLE DB provider for Analysis Services (MSOLAP). Whereas PowerPivot for Excel 2010 uses an MSOLAP connection to load the embedded PowerPivot data model from a custom XML part in the workbook.
Excel Services and PowerPivot for SharePoint 2010 load PowerPivot data models from a custom XML part in the workbook. Therefore, this technology is incompatible with internal connections that do not reference an MSOLAP provider (such as the connections that are used by Excel 2013).
Note PowerPivot functionality is not supported by Excel 2013 workbooks that use data models in SharePoint Server 2010 environments.

Seems like only solution here to upgrade your sharePoint farm to 2013 farm or use excel 2010 to create your data models.

To work around this issue, upload the Excel 2013 workbook to a SharePoint Server 2013 farm.
Excel Services and PowerPivot for SharePoint 2013 load Excel 2013 workbooks that use advanced data models and PowerPivot workbooks that are created in PowerPivot for Excel 2010.

 

If you want to read more about it, here is KB Article:

 

https://support.microsoft.com/kb/2755126

Thanks

 

Kagan Arca

Technorati Tags: Power Pivot,SharePoint 2013,SharePoint 2010,Compatibility,ThisWorkBookDataModel,Error

Comments

  • Anonymous
    September 17, 2013
    how to access data based on users using power pivot for excel 2013. For example we have different stores data such as India , USA, London, so one user can able to access India store data only, in this case how can we provide restriction while opening  the power  pivot report(.xlsx)

  • Anonymous
    April 21, 2014
    For PowerPivot, security is dependent on where the PowerPivot workbook is stored. With a PowerPivot workbook that is stored on desk or the file system, security is file level security, so anyone who has access to the file has access to the stored data. When the workbook is stored on the PowerPivot mid-tier server in SharePoint, security for the file is based on SharePoint security. For the type of security you're looking for you would need to move to a Tabular model where you could implement row level security or Multidimensional where you could implement fine grain security on one or more dimensions.