Plan data connection management
Updated: February 26, 2009
Applies To: Office SharePoint Server 2007
Topic Last Modified: 2009-02-20
In this article:
Because connections to data sources are volatile, IT managers need an efficient way to maintain and update connections from Microsoft Office Excel 2007 spreadsheets to data sources. The physical stores that contain data sources can be moved and the names of physical stores can be changed. When a spreadsheet contains direct links to data sources, link integrity is dependent on the validity of the logical address of the linked source. If the logical address of a data source changes, or if the address of the data store that contains the data source changes, the link to the data source is broken.
Excel spreadsheets can contain data from multiple external data sources. Tracking and maintaining connections to multiple data sources within a single spreadsheet can be difficult and time consuming. IT managers often have to track and maintain multiple data sources in hundreds or thousands of spreadsheets. With Microsoft Office SharePoint Server 2007, you can prevent broken links to data sources by defining data connections and storing them in data connection libraries that can be centrally managed, updated, maintained, and listed in a SharePoint site.
To plan a strategy for data connection management, you need to determine what kind of external data Excel spreadsheets will be accessing in your deployment scenario. Excel spreadsheets can access Microsoft SQL Server data and online analytical processing (OLAP) data. You can determine how data connections in workbooks that are opened in Excel Calculation Services are handled by the server. You can configure Excel Calculation Services to:
Block all data connections.
Allow data connections from trusted data connection libraries only.
Allow embedded data connections and data connections from trusted data connection libraries.
Data connections can be saved as Office data connection (.odc) files, which are stored separately from spreadsheet files. Workbook authors who have write permissions to Office SharePoint Server 2007 can create data connection files from Office Excel 2007 by using the Data Connection Wizard. The wizard enables workbook authors to create .odc files and save them to a data connection library. Workbook authors who have access permissions to the data connection library can then use the .odc files to create data connection links in their workbooks.
Excel Services in Microsoft Office SharePoint Server 2007 provides an efficient way to manage the connections from Excel spreadsheets to external data sources by preventing the use of embedded data connections in spreadsheets. Instead of using an embedded link to connect directly to an external data source, the spreadsheet gets the latest connection definition from a data connection library and uses that definition to connect to the data source.
Data connection libraries contain .odc files and metadata about the connections. Data connection libraries enable spreadsheets to locate external data sources and maintain connections to data sources that have been renamed or moved to different servers. If an external data source is moved to a new server, the .odc file in the data connection library can be updated by using the location of the new server, and the connection from the spreadsheet is automatically updated.
You can restrict save access to data connection libraries and allow only trusted authors to save connection files. Restricted data connection libraries enable you to ensure that all connection files in a data connection library are authorized. By configuring Excel Calculation Services to use only data connection library connections, instead of direct connections to external data sources, you can ensure that spreadsheets connect only to authorized databases. By default, data connection libraries are created as part of the Report Center template. However, data connection libraries can be created anywhere in Office SharePoint Server 2007 and accessed from a centrally managed Office SharePoint Server 2007 portal site.
This topic is included in the following downloadable book for easier reading and printing:
See the full list of available books at Downloadable content for Office SharePoint Server 2007.