Introducing Microsoft SharePoint 2010 Business Connectivity Services

  • 6/15/2012
This chapter from Microsoft SharePoint 2010 Business Connectivity Services introduces BCS, looks at its architecture and how it connects to the external system, and considers its security implications. This chapter also introduces the integration of BCS with Microsoft Office client applications and examines how you can use BCS features to build composite solutions and dashboard pages, making SharePoint and Office a simpler and more cost-effective platform to integrate with your business data.

Chapter 1, “Making SharePoint the Central Hub for Business,” discussed the frequent problems enterprises have when integrating disparate business data. Many organizations see Microsoft SharePoint as yet another silo of information that can only exploit data stored in lists and libraries. However, by using Microsoft Business Connectivity Services (BCS), an organization does not need to move all its data into SharePoint to build integrated business solutions, bringing together the business processes and the business data in a familiar user experience.

BCS—the evolution of the Business Data Catalog, which was shipped with Microsoft Office SharePoint Server 2007—is a key feature of SharePoint 2010. First introduced in the Enterprise edition of Microsoft Office SharePoint Server 2007, Business Data Catalog enabled you to integrate data stored in SharePoint with data from line-of-business (LoB) applications and other enterprise and Web 2.0 data sources. It allowed you to connect to business data from back-end systems and then, by using prebuilt Web Parts, you could display information from these external data sources on dashboards without the need for coding.

With Microsoft SharePoint Server 2010, these features have been enhanced. BCS is now available in the base product, Microsoft SharePoint Foundation 2010, and SharePoint Online, which is part of Microsoft Office 365.

This chapter introduces some key elements of BCS and forms the foundation of the rest of the book. You’ll learn about BCS, look at its architecture and how it connects to the external system, and consider its security implications. This chapter also introduces the integration of BCS with Microsoft Office client applications and examines how you can use BCS features to build composite solutions and dashboard pages, making SharePoint and Office a simpler and more cost-effective platform to integrate with your business data. The rest of the book explores each of these areas in detail, showing how BCS can help you mitigate the frequent problems enterprises have when integrating disparate business data, as discussed in Chapter 1.

What Is Business Connectivity Services?

Business Connectivity Services (BCS) in SharePoint 2010 enables integration with line-of-business (LoB) applications and other external data sources. As mentioned previously, BCS is built on the SharePoint Server 2007 Business Data Catalog technology. It bridges the gap between the various applications that an enterprise uses for surfacing key business data, from platforms such as Siebel, CRM, and SAP or data stored in a mainframe such as an AS/400 system, to SharePoint sites, lists, search functions, user profiles, and Microsoft Office applications.

As noted in Chapter 1, many companies have invested a lot of time and money into the LoB systems that manage their business, with each LoB system having its own specialist and set of management tools. The data from the external system is typically business critical, yet it cannot be used by the end users who need it. Those end users who can access the LoB system data have to contend with multiple different user interfaces (UIs) with an array of terminology. Typically these end users have undergone training in each UI and have developed their own cheat sheets to translate terms in the UIs into their everyday business speech.

The aim of BCS (and any solutions you build with BCS) is to simply streamline the access to external data for end users who need to use it. That is, BCS connects with the external systems and exposes the external data either in SharePoint or in Office applications, such as Outlook 2010, Access 2010, and SharePoint Workspace 2010.

When using BCS, organizations no longer have to train their developers and end users in multiple systems. Professional developers need to learn only one method of developing against the external systems: the BCS application programming interfaces (APIs). End users can exploit their knowledge of SharePoint and Office applications to use LoB data in their business decisions.

Types of BCS Solutions

Solutions that bring together data from a number of systems to assist in the automation of a business process are known as composites in SharePoint. BCS is a key component in building composites. BCS solutions can be divided into three types, as shown in Figure 2-1.

Figure 2-1

Figure 2-1 The three types of BCS solutions are simple, intermediate, and advanced.

  • Simple Built using the out-of-the-box capabilities within SharePoint. Connecting Office applications to external lists exposes the external data, such as when you use a Quick Part in Word 2010. Many of these simple solutions require that the definition of how to connect to the external system is already in existence. The solution is built almost entirely using the ribbon in the browser or Office applications.

  • Intermediate Built by power end users, site owners, or business analysts. Such users, termed “citizen developers” by Gartner, Inc., operate outside the scope of IT, work in the business domain, and can use the WYSIWYG tools to create new business applications for consumption by others.

    Citizen developers use a combination of technologies, such as InfoPath forms, webpages, workflows, and integration into Office applications, such as Outlook task panes or Word documents. Citizen developers know what they want to achieve, they understand their business needs, and with a bit of SharePoint knowledge, they can wire together the business processes or sets of tasks.

    Intermediate solutions are more complex than simple solutions, and they may involve the use of Office application macros or the manipulation of XSLT using the code view of Microsoft SharePoint Designer 2010. Therefore, citizen developers may initially need some training or help from the organization’s central SharePoint team, particularly if they have never used SharePoint Designer or InfoPath Designer before.

  • Advanced Built by the IT department and professional developers, involving the development of reusable components to augment simple and intermediate solutions or solutions that require a deep knowledge of architectural concerns and a formal code, test, deploy, and support management process. Such reusable components could include .NET assembly connectors to connect, aggregate, and transform data from external systems, custom Web Parts, custom workflow actions that can be used from within SharePoint Designer, code for InfoPath forms, or extensions to the browser UI. Many of these components can only be developed with the use of Visual Studio.

IT departments will need to differentiate between the types of solutions citizen developers can create and those that the IT department should develop. When this identification process is completed successfully, it should free up IT resources for more complex problems.

Although many business users will have developed complex solutions with, say, Excel, that involved thousands of rows of data, the simple and intermediate types of BCS solutions will be based around forms or business processes. While many users in an organization may not have the specific data skills to build solutions in Excel or Access, they may have the SharePoint skills to create SharePoint solutions in which BCS defines data from multiple external systems. Because users will see little difference in creating solutions where data lives within SharePoint databases and solutions where data is stored in external systems, they are likely to promote the use of BCS related solutions throughout the organization.

The increased use of citizen developers to create business solutions may be new to an organization and will instigate a user adoption strategy as well as an education program. This education program will be focused more on introducing and managing the changes in the way the business will work going forward than enhancing skill sets. Other organizations may assimilate the use of SharePoint and its tools into their formal/informal reengineering process. Whichever way an organization chooses to introduce SharePoint and BCS, it should not be seen by end users as another task for them to complete in their already busy day; rather, end users should be encouraged to view the use of these technologies as a new way of working, so they can accomplish more in the same amount of time.

Many of the most successful SharePoint solutions are built by the users who use them: the citizen developers. The solutions are successful because the citizen developers know what they want to achieve, they are using the solutions as they develop them, and they can resolve any problems, including issues that can only be uncovered by using the solution. Citizen developers find that there is no need to provide feedback to others or raise incidents with their organization’s help desk. These citizen developers are probably very passionate about their own SharePoint solutions. Therefore, when an organization encourages citizen developers to instigate the business reengineering process, it is more likely that other users in the organization will adopt the solution, as one of their own developed it, and that person knew the business requirements and experienced firsthand the issues of the solution.

Key to the success of this paradigm shift is that organizations need to take the citizen development strategy into consideration with any development process. That is, any SharePoint-related development project needs to add to the list of citizen developer tools, continuing the SharePoint philosophy of self-service for end users, content owners, business owners, and site owners.

By using the different types of BCS solutions, an organization can accomplish the following objectives:

  • Reduce or eliminate the code required to access LoB systems.
  • Achieve deeper integration of data into places where an end user works.
  • Centralize deployment of data source definitions for use by both BCS and Office applications.
  • Reduce latency to access and manipulate data. After a data source is defined in the BCS repository, it will be immediately available to the web applications associated with that particular BCS service application.
  • Centralize data security auditing and connections.
  • Perform structured data searches.

The Structure of a BCS Solution

Both SharePoint Designer and Microsoft Visual Studio 2010 enable users to model how to connect with external business systems. Once a BCS model is created, the data from the external system can be surfaced within the browser or Office application. Then, depending on the operations defined within the model, the end user can manipulate the external system data with full create, read, update, and delete (CRUD) operation support. The definitions within the BCS model enable full integration into SharePoint Server 2010, such as surfacing data from an external system within search or a user’s profile.

A BCS solution can be divided into four layers:

  • External System Layer that contains the external content.

  • Connectivity Layer that connects the presentation layer with the external system. It uses different types of connectors depending on the interfaces supported by the external systems, together with information defined in an XML file, known as the Business Data Connectivity (BDC) model, to read and write data to and from the external system.

  • Presentation Layer that extends the user experience (UX) to display and manipulate content from external systems in Office 2010 client applications and websites built on top of SharePoint 2010.

  • Tools Layer used to develop solutions and create the BDC model.

Figure 2-2 shows the high-level interaction among the four layers, SharePoint 2010, and Office applications.

Figure 2-2

Figure 2-2 The high-level architecture of BCS.

External System

This layer contains the external data. Before using BCS, you should work with the owners of the external system to evaluate the best method to connect to it. There may be more than one method—for example, you may obtain the data from the external system by directly interrogating the database or by using a web services interface. If your external system does not have a compatible interface, you can develop your own BCS connector or expose the content as a web service.


This layer of BCS connects to the external systems and is referred to as Business Data Connectivity (BDC). (Note that this acronym was used in the previous version of SharePoint, where it represented the Business Data Catalog.) To connect to the external system, you need to define the location of the external system, the protocol to use, and the credentials. Once connected, you need to define the operations on the data that are allowed and the format of the data that will be returned. The operations that can be used on the external data and its format are defined in an external content type (ECT). The definition of the location of the external system together with the ECT is known as the BDC model.

The BDC model consists of XML declarations that describe the external system you want to access as well as the operations you want to perform on the external data. For example, you might want to read a data record, update one data record, or delete one data record. You can create the BDC model on a development or test SharePoint installation, and from there download and import it into the SharePoint production farm, a SharePoint installation that is installed on one or more servers that share the same SharePoint configuration database.

All BDC models to be used within SharePoint are stored in a Microsoft SQL Server database created especially for the use of BCS, known as the metadata store or ECT repository.

Microsoft Office 2010 client applications can also use the BDC model. Office 2010 client applications only contain components to upload a BDC model, so Office 2010 client applications do not provide any management or configuration interface. With SharePoint 2010, however, you can use the SharePoint 2010 Central Administration website or Windows PowerShell to manage and configure the BDC model.


BCS enables you to bring external data into SharePoint and Office and allows end users to gain insight into the underlying data in a reusable way. SharePoint uses the information in the BDC model to present the external data using external lists, the Business Data Web Part, external data columns, and external data search, as well as by using SharePoint Designer to create Data Form Web Parts (DFWPs) to display the data. Other Web Parts, such as the Chart Web Part, can also present external data.

Your professional developers could create new Web Parts to present the data, but typically your citizen developers will use tools such as the browser, InfoPath Designer, or SharePoint Designer. For example, in SharePoint 2010, you can display data from external systems on publishing pages, Web Part pages, and wiki pages. No matter which page you are working with, external data is exposed by using Web Parts. For example, external lists use Web Part pages to create views of the external data. These pages contain the XSLT List View (XLV) Web Part, or you can choose to use an InfoPath form or the DFWP. The XLV Web Part and the DFWP use XSLT, and you can use SharePoint Designer’s WYSIWYG editing pane as well as its code view to amend the code for these Web Parts.

External data can also be presented in Office 2010 client applications by connecting through SharePoint to the external systems or by using the BDC model to connect directly with the external systems.


Both SharePoint and Office client applications provide programming interfaces that use BCS and allow professional developers to create solutions. SharePoint 2010 also provides tools for the power user, site owners, and business analysts—the citizen developer tools, such as SharePoint Designer and Microsoft InfoPath Designer.

You can use Visual Studio 2010 to create the BDC model, interact with the BCS program interfaces, and manipulate BDC objects. Third-party tools are also available for you to work with, and you can even use an XML editor, such as XML Notepad 2007 or Notepad, to create a BDC model.

Interaction Between the Layers

There is a close symmetry between the BCS architecture of a SharePoint installation and Office 2010 applications. The Office 2010 client applications do not have a BDC metadata store, but they have a BDC client-side cache that is used to store the BDC model when data from an external list is taken offline. If amendments are made while the client is offline, they are stored in the client data cache and committed to the external system when the client is next online.

The offline data from the external list is also stored in the client-side cache, within a SQL Compact Edition client database. When the user’s computer is turned off, the BDC model and the offline external data is persisted.

Also, note in Figure 2-2 that Office 2010 client applications have their own connectors. When you switch from offline mode to online mode, the Office application connects directly to the external system without connecting through SharePoint. Access 2010 can import a client-side version of the BDC model, therefore it does not need to connect to SharePoint at all—it connects directly to the external system.