External Data Sources in SharePoint

Part of SharePoint’s attractiveness is its ability to act as a framework to surface data from a wide variety of sources. Want to replace the outdated SQL Server Reporting Services (SSRS) Report Manager? Microsoft Office SharePoint Server (MOSS) 2007 lets you use SharePoint integration mode to manage reports like other library documents. Want to connect to your PeopleSoft HR application and expose personnel data along with W2 and I9 documents? MOSS Enterprise lets you use the Business Data Catalog (BDC) to manage this connection. Dealing with semi-structured data? InfoPath Forms Services might be your solution. Trying to control the proliferation of spreadsheets? With Excel Services you can render your Excel spreadsheet directly from a web browser without having to install the full Office client.

SharePoint offers a great deal of functionality for end-user applications and report designers, but don’t forget about its SharePoint Designer data connections as well as third-party and custom connections. Let’s look at how to consume and secure end-user and developer-centric data connections.

Data Connection Libraries
The settings that make up a data connection, such as connection strings and OLAP queries, can be saved in an Office data connection (.odc) file or Universal data connection (.udc) file. These data connection files can be stored and secured separately from the SSRS reports, Microsoft Excel workbooks, and InfoPath form templates that use them. MOSS provides Data Connection Libraries (DCLs) that centrally store these Office data connections for reuse. DCLs are a new type of SharePoint library (similar to a document library) that can store and manage connection files. You can use the Data Connection Wizard, available in InfoPath 2007 and Excel 2007, to manage the upload of .odc and .udc files to the DCL. Figure 1 shows a sample DCL with a .odc file used by Excel, a .odc file used by InfoPath Forms, and a Remote Data Connection (RDC) used by SSRS. (For more information about managing data connections, see "Plan data connection management."

SharePoint gives business users the ability to manage the connection to an external system independently of reports and forms that could be using it. This capability is useful both for Excel Services and InfoPath Forms Services because it prevents embedded data connections, which can become stale and difficult to troubleshoot.

InfoPath Forms Services Connections
InfoPath lets you create custom forms for data entry. This data will be stored in a SharePoint list that separates the fields and stores them in the SharePoint SQL Server content database. InfoPath’s form templates can take advantage of .udc files stored in a DCL. These data connections can be used for primary and secondary data connections, as well as for send connections. The following are some of the benefits of storing InfoPath data connections in DCLs:
• You can create the data connection once and use the connection on multiple servers and across multiple domains with both InfoPath client form templates and InfoPath Forms Services form templates.
• You can centrally publish approved data connections, modify data connections without affecting form templates, and determine which connections are safe for cross-domain access.

Figure 2 shows a sample InfoPath form retrieving an employee’s first name and last name from a secondary, read-only data source called vEmployee.

Excel Services Connections

Similar to InfoPath forms, Excel workbooks have the capability to tie to external data sources. These connections can be stored in the workbook itself or in an external .odc file or .udc file, giving end users the ability to reuse connection information and centrally administer connection details. The Microsoft article "Plan external data connections for Excel Services" goes into great detail about connections and Excel workbooks, as well as Excel Services considerations. Figure 3 shows an Excel spreadsheet pulling data from an external data source. The connection information is stored in a DCL in SharePoint.

SSRS Integration
SSRS ships out of the box with a Report Manager web application that lets you manage user access, subscriptions, and centrally controlled data sources. So why would anyone bother to integrate SSRS and SharePoint? The value add is that SharePoint lets you put your reports into a secure collaborative environment—a dashboard—with presentation details from other types of reporting systems (e.g., Excel Services) and BDC information. The following are some additional benefits to using an integrated SSRS and SharePoint environment:

• SSRS data connections are stored in DCLs similarly to InfoPath Forms Services and Excel Services data connections, providing you with a centralized list of connections.
• Because reports are stored in SharePoint document libraries, they can take advantage of SharePoint features such as workflow, information management policies, and versioning.
• Reports fall under the same security model as all other SharePoint content and no longer need to have separate ACLs maintained in the Report Server.
• SSRS reports can be pulled into dashboards, which use SharePoint’s Filter Web Part to provide a dynamic user experience.

Figure 4 shows an SSRS report with a shared data connection. (For more information about performing SSRS tasks in MOSS, see "SSRS and MOSS 2007: Deploying the Power.")