Introducing Microsoft SharePoint 2010 Business Connectivity Services

Alternative Methods of Connecting SharePoint 2010 with External Systems

SharePoint 2010 provides the following methods of integrating with data that is not stored in SharePoint:

• Access Services This service application is available in the Enterprise edition of SharePoint Server 2010 and the P1, E3, and P4 plans of SharePoint Online. You can also create a Web Database by publishing a Microsoft Access 2010 database, where data held in Access tables is moved to SharePoint lists and forms and reports are created as webpages. You can then access the web database using the browser or the Access client application. You can find more information about using Access Services in the Microsoft paper “Improving the Reach and Manageability of Microsoft Access 2010 Database Applications with Microsoft Access Services” at www.microsoft.com/download/en/details.aspx?id=19061.

• Visio Services This service application is available in the Enterprise edition of SharePoint Server 2010 and the E3 and E4 plans of SharePoint Online. It allows you to share and view Microsoft Visio 2010 web drawings (.vdw files) in the browser without the Visio client application or the Visio viewer installed on your local computer. The Visio web drawings can contain visuals that are linked to data from an external data source. Visio Services can fetch the data from these linked data sources and update the visuals of a Visio web drawing. Only Microsoft Visio Professional 2010 and Microsoft Visio Premium 2010 can publish Visio web diagrams to a SharePoint Server 2010 site. You can find more information about using Visio Services at http://technet.microsoft.com/en-us/library/ee663482.aspx.

• Excel Services With this service application, you can publish Microsoft Excel 2010 workbooks to SharePoint 2010, which allows users to view and interact with the workbooks in their browser. First introduced in the Enterprise edition of SharePoint Server 2007, Excel Services is now implemented as a service application available in the Enterprise edition of SharePoint Server 2010. It consists of Excel Calculation Services, the Microsoft Excel Web Access Web Part, and Excel Web Services for programmatic access. Excel workbooks hosted in Excel Services can connect to external data using embedded connection definitions, or connection definitions can be stored in a data connection library. Excel Services is available in all plans of SharePoint Online, where it can connect only to data stored within SharePoint Online. You can find more information about Excel Services at http://technet.microsoft.com/en-us/library/ee424405.aspx.

• PerformancePoint Services PerformancePoint is available in the Enterprise edition of SharePoint Server 2010 as a service application. At the time of this writing, it is not available in any SharePoint Online plan. PerformancePoint Services allows you to monitor and analyze business information by providing tools to build dashboards, reports, scorecards, and key performance indicators (KPIs). All data used in PerformancePoint is classified as external data, including data stored in SharePoint lists or Excel files published to Excel Services. However, data stored within SharePoint can only be used in PerformancePoint in read-only mode. You can use PerformancePoint to connect to tabular data in SQL Server tables, Excel workbooks, and multidimensional (Analysis Services) data sources, and you can use a PowerPivot model built using the PowerPivot add-in for Excel as a data source. You can find more information about PerformancePoint Services at http://technet.microsoft.com/en-us/library/ee681486.aspx.

• InfoPath forms With InfoPath, you can create both forms and browser-based forms. Users entering data into forms require Microsoft InfoPath Filler 2010. For browser-based forms, users need only a browser and InfoPath Form Services. Form templates for both types of forms can be created using Microsoft InfoPath Designer 2010. Forms created using InfoPath can connect to data sources such as SharePoint lists or web services. Forms or browser-based forms can be saved in a SharePoint Form library. The ASPX pages in external lists that allow you to create, read, update, and modify data from an external system can be replaced with InfoPath browser-based forms.

• InfoPath Form Services (IFS) This service application enables InfoPath browser-based forms to be rendered in SharePoint 2010. This service is available only in the Enterprise edition of SharePoint Server 2010 and the E3 and E4 plans of SharePoint Online. However, in the Standard edition of SharePoint Server, a version of IFS is activated that allows you to use InfoPath workflow association and initiation forms. IFS is not a SharePoint 2010 service application, but it is configured at the farm level using the SharePoint 2010 Central Administration website. For more information about IFS, see http://technet.microsoft.com/en-us/library/cc262498.aspx.

• Microsoft SQL Server 2008 R2 Reporting Services (SSRS) When installed, this free add-in for SharePoint Technologies 2010 allows you to run SSRS Report Server within SharePoint 2010, where the SSRS reports, items, and properties are stored in SharePoint. Users can browse to SharePoint libraries to find the reports, and if they have sufficient permissions to use the Report Builder, they can create reports. You can find an overview of SSRS and SharePoint integration at http://msdn.microsoft.com/en-us/library/bb326356.aspx.

• Data Sources gallery using SharePoint Designer Using Microsoft FrontPage 2003, and then later Microsoft Office SharePoint Designer 2007, you could connect, present, and modify data from several types of external data sources using the Data Source Library and Data Source Details task panes. This method is still available with SharePoint Designer 2010 with the Data Sources gallery, which you can access through the Navigation pane. The Data Sources gallery replaces the Data Source Library task pane in SharePoint Designer 2007. The advantage of using BCS as opposed to the Data Source gallery in SharePoint Designer is that you define the external system and external content type (ECT) only once, and you can then use that ECT on many sites across all web applications that are associated with the BDC service application. The disadvantage is that ECT designers must be given edit permissions to the metadata store, which is a high level of security, whereas with the Data Source gallery you only need to be, for example, a site owner. In addition, you need to set other BCS security settings to allow users to access the external content. You can implement these settings using the SharePoint 2010 Central Administration website or Windows PowerShell. Setting the correct level of BCS security requires a level of collaboration between the ECT designer and the SharePoint farm administrator, which in a large organization are typically two different people.