Aug 2, 2010

SharePoint 2010 Excel Services and External Data Refresh

Excel Services Configurations

Two most important configurations in Excel Services Application are:
  • Trusted File Locations: the whole sharepoint farm is pre-configured as trusted location.
  • Trusted Data Connection Libraries: it is still required in sharepoint 2010 (folder name can not be skipped).

Workbook Data Connection Properties:

Data Connection properties include usage and definition. The usage is per workbook, its setting is not saved in the configuration file. The connection can either use sql authentication or window authentication. Another import property for each connection definition is "Excel Services Authentication", which will be addressed in another post.

Ways to Refresh Data:

manual refresh (data connection)       refresh on open or periodic:

Some Refresh Problems:

  • Table not refreshable, only pivot table and pivot chart can be refreshed from external data.
  • Browser (IE) can cache old connection string. Close IE before testing new connection. 

  • periodic refresh won;t happen after excel service session timeout:

Excel service create a session (memory/disk) for each workbook and release them after timeout. Periodic refresh can't happen without a  ongoing session.