Home » sharepoint » SharePoint 2013 » Changes in Excel Services for SharePoint with SharePoint 2013

Changes in Excel Services for SharePoint with SharePoint 2013

This is the first of a couple of posts on changes in Excel services for SharePoint 2013.

Microsoft® Excel is growing in popularity as a Business Intelligence (BI) tool. But the PowerPivot add-in for Office 2013 is no longer a free feature. To obtain this add-in an organization needs to purchase the professional addition of this office suite. Microsoft sums up the differences: “In Excel 2013, much of [the PowerPivot add-in for Excel 2010] functionality, including the infrastructure that supports it, is built directly into the Data Model in Excel. Without installing a separate add-in, you can now:

  • Import millions of rows from multiple data sources.
  • Create relationships between data from different sources, and between multiple tables in a PivotTable.
  • Create implicit calculated fields (previously called ‘measures’) – calculations created automatically when you add a numeric field to the Values drop zone of the Field List.
  • Manage data connections.
  • ” (quoted from What’s new in PowerPivot in Excel 2013)

On the topic of what features are included in the PowerPivot add-in for Excel 2013, Microsoft states: “The PowerPivot add-in is available, too, and provides more advanced modeling capabilities for more experienced data modelers.” (ibid)

Line of Business (LOB) Excel 2010 PowerPivot users looking to provide a collaboration platform for BI found a solution in SharePoint 2010. Excel Services for SharePoint 2010 worked fine with external connections. However, with SharePoint 2013, “[Excel 2010] Workbooks with external data connections that use Windows authentication cannot be refreshed in the browser. Instead, you are prompted to open the workbook in the Excel client program. Workbooks that have database or Windows credentials stored either in the Secure Store Service or in the connection string can still be edited in the browser. This change applies only when Excel Web App in Office Web Apps Server is used to view workbooks, not when Excel Services in SharePoint Server 2013 is used.” (quoted from Changes from SharePoint 2010 to SharePoint 2013).

Therefore, LOB users should carefully consider whether or not it makes sense to quickly migrate to SharePoint 2013. Dashboards built for the older version of Excel Services might not work. A lot of retrofitting may be required to correct the problem by adding a lot of code to connection strings.

In the next post we will look deeper at these changes.

Ira Michael Blonder

© Rehmani Consulting, Inc. & Ira Michael Blonder, 2013 All Rights Reserved