Data Connection Libraries

Microsoft Office SharePoint Server (MOSS) 2007 provides a business intelligence (BI) feature called the Data Connection Library (DCL), which lets you centrally store and manage Microsoft Office external data connections. Office external data connections are the external data source connection files that Microsoft Excel and InfoPath use for sourcing data from external data sources. Office external data connections are useful because you can reuse them—the end user doesn't need to remember the data source specifics (e.g., address, authentication).

Why would you want to centrally store your Office external data connections? First, by centrally storing an enterprise’s data connections, you're providing a one-stop shop for users to find and retrieve data. Second, you ensure that corporate security measures are adhered to because you're centrally storing the method by which users connect to various data repositories. Third, it makes connection management much easier for your IT department because the connections aren't directly embedded within hundreds of Office documents.

A DCL is created by default in the Report Center; however, you can also create your own unique instances of a DCL in any site. Creating a DCL is the same process as creating any other library—you can do so via a site’s Site Actions menu. Because a DCL is just another SharePoint library, it must store data based on the content type. DCLs can support two content types: Office Data Connections (ODCs) and Universal Data Connections (UDCs). ODCs are used primarily by Microsoft Excel to connect to external data sources, but they have other uses as well, which I'll explain momentarily. UDCs are used primarily by Microsoft Office InfoPath for external data connections.

Excel and Excel Services Usage
Excel uses ODCs for sourcing data outside of the workbook. You can create and manage local ODCs in Excel by clicking the Get External Data and Connections tabs in the Data ribbon. Local ODCs are nice for local reuse; you can store them in a DCL as well. Once ODCs are stored in a DCL, you can retrieve them by selecting the connection files on network option in Excel’s existing connections dialog box. Additionally, you can publish workbooks to MOSS 2007 that reference ODCs stored in a DCL.

Excel Web Access (EWA) and Excel Calculation Services (ECS) can use ODCs stored in DCL for workbook data sourcing. To enable EWA or ECS DCL usage, you must configure an existing DCL as a trusted DCL at the SharePoint Central Administration website. Additionally, you can force ECS to require that ODCs be stored in DCL rather than in embedded workbook connections.

InfoPath Usage
InfoPath forms use data connections to submit and retrieve data, and InfoPath uses UDCs for its data access layer. You can search an existing MOSS site for UDCs to use as your forms data connections inside of the InfoPath client.

Reporting Services Usage
While Reporting Services typically does not use Office external data connections; you can use ODCs to serve as shared data sources for reports consumed in a SharePoint integrated mode SQL Server Reporting Services deployment. Not all ODC files can be used for report data sourcing purposes, though. For example, you can't use an ODC file that sources data from Microsoft Access. Additionally, report models don't support sourcing data via ODC files.

DCL for BI
DCLs are very useful for enabling enterprise connection management in an Office-based organization. BI environments in particular can use DCLs to provide reporting and analytics consumers with a one-stop shop for all their data sourcing needs. If you intend for Excel 2007 to be used as a front end for your BI projects, find out if MOSS is already in the environment or can be obtained. If so, you should consider using DCL for connections management.