Share and manage connections to external data

You can use Microsoft Office SharePoint Server 2007 to connect people in your organization to databases that are external to your SharePoint sites. Administrators can create a central pool of connections that can easily be accessed and reused by anyone who has the necessary permissions. This allows people to view and work directly with that data from within a SharePoint site and also helps the administrators to manage the connections.

Connect to external business data by using data connection libraries

Data connection libraries (DCLs) are centralized SharePoint document libraries that store Office Data Connection (ODC) files. Each of these .odc files contains information about how to locate, log on, query, and access the external data source. Centralizing ODCs in a data connection library makes it possible to share, manage, and search data connection files from within a SharePoint site.

Microsoft Office SharePoint Server 2007 supports data connection to the following external sources:

• Microsoft Office Excel 2007 workbook

• Microsoft Office Access database

• Other OLEDB or ODBC data source

• Microsoft SQL Server database

Share data as "one version of the truth"

You connect to an external data source first by using the data connection library to connect to a database. Next, you can analyze that single source of data by using Excel Services (an Office SharePoint Server 2007 shared service) and Microsoft SQL Server 2005 Analysis Services. These combined technologies enable you and other users in your organization to always view a consistent set of values and formula results from a master workbook that uses secure and up-to-date Office Data Connection information.

1. A master workbook contains cumulative financial data that is regularly updated.

2. A workbook is published at the end of each fiscal quarter.

3. "One version of the truth" simplifies decision-making and comparisons between fiscal quarters.

Manage external data sources from one location

Managing external data sources is simplified with Excel Services. Excel Services and the data connection library together provide an efficient way to manage Excel spreadsheet connections to external data sources. A spreadsheet gets the latest Office Data Connection (ODC) definition from a data connection library and uses that definition to connect to the data source. Not only is current data more immediately accessible to users, the management of what data is available and in what format is all performed locally and centrally in the data connection library.

Note: This article refers to an example SharePoint site created by Adventure Works, a fictitious company that manufactures bicycles, bicycle components, and bicycling accessories.

The IT department at Adventure Works, for example, has configured a set of common databases (ODCs) to which employees have access. This enables employees to use the data connection library to easily surface data from different data sources into their own spreadsheets for analysis.

Data connection libraries simplify the maintenance and management of data connections by enabling spreadsheets to connect to external data sources and then maintain those connections even if the sources are renamed or moved to different servers. If a connection changes, it need only be updated in the library instead of in the individual spreadsheets that are linked to the original data source. By configuring Excel 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. At Adventure Works, for example, if an external data source needs to be moved to another server, the IT department simply updates one file in the data connection library. The connection from the spreadsheet is automatically updated with minimal interruption.