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. 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.

  Note

  Gartner, Inc. reports that citizen developers will build at least 25 percent of new business applications by 2014 (see www.gartner.com/it/page.jsp?id=1744514) and warns that IT departments that fail to capitalize on the opportunities that citizen development presents will find themselves unable to respond to rapidly changing market forces and customer preferences.

• 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.

Note

One feature often cited as new for BCS in SharePoint 2010 is the ability to write back to external systems. You could create SharePoint Server 2007 Business Data Catalog data source definitions that allowed you to update or insert data into the external data sources; however, none of the out-of-the-box Web Parts exposed this feature. You needed a developer to create a custom Web Part to match the data source definition.

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. 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.

Connectivity

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.

Note

See Also You can find detailed information on the BDC model XML schema in Appendix A.

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.

Note

See Also For more information about creating a BDC model, see Chapter 4.

Presentation

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.

Note

External data can also be exposed when you use SharePoint search or in a user’s profile. Some of the search Web Parts also use XSLT to present a user-friendly view of the data.

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.

Note

See Also You can find more information about how to present external data in SharePoint and in Office applications in Part II of this book.

Tools

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.

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

A SharePoint 2010 installation can consist of one or more servers, depending on the number of concurrent users to the SharePoint sites and the functionality they use. Such a collection of servers is called a SharePoint farm, and it uses a client/server architecture, as shown in Figure 2-3.

Figure 2-3. SharePoint 2010 is installed as a client/server architecture.

The server architecture consists of four tiers:

The server architecture can reside on one or more servers. The number of servers will vary from organization to organization and depend on a number of factors, such as the number of concurrent users, scalability, redundancy, resiliency, and the number of service applications used.

For example, when deploying SharePoint 2010 for evaluation or development purposes for a small number of users (for example, fewer than 100 users), the implementation could consist of one server. The single server, known as a single-server farm, would host the web, application, and database tiers. For between 100 and 1,000 users, where redundancy and resiliency are considerations, a four-server farm could be implemented, where two servers would host the web and application tiers and the other two servers would host a clustered or mirrored database server configuration—the database tier.

The Business Connectivity Services (BCS) data connectivity layer, known as the Business Data Connectivity (BDC), uses connectors to access the external systems. The built-in connectors allow you to connect to databases, cloud-based services, Windows Communication Foundation (WCF) endpoints, and web services, as well as provide you with the ability to create new connectors, such as .NET assembly and custom connectors. These two connectors are created using Visual Studio. The .NET assembly connector is typically developed internally in an organization and allows a developer complete control over the operations with the external system with the code the developer writes. Custom connectors are typically developed by third-party companies so that the purchasers of the third-party solution can integrate the solution with a SharePoint installation.

Once SharePoint knows how to connect to an external system, then depending on the operations SharePoint is allowed, it can retrieve, modify, and create external data. An improvement in SharePoint 2010 is the introduction of batch and bulk operations for retrieving external data. When multiple documents are retrieved, it is also possible to retrieve the documents in chunks, which reduces the number of round-trips to retrieve the data.

The connection and data operation details are stored in the BDC metadata store, also known as the BCS database. In this database, SharePoint stores and secures external content type (ECT) and related objects defined in the BDC model, so this database is also known as the ECT repository. This database does not contain external data; it contains only information about the external system. The metadata store is accessed by the Administration and Runtime interfaces, as shown in Figure 2-4, which are discussed in more detail in the following sections.

Figure 2-4. External data can be accessed either from an Office client application or using SharePoint.

BCS Security

To create, modify, or delete the BDC model, or to access the data from the external systems, you will need to plan and consider security requirements. The security requirements of the external systems will usually be outside your control. Many of the external systems will have been installed in your organization before the installation of SharePoint, probably many years before SharePoint was even considered by your organization. You will need to work closely with the external system owners, and with your organization’s security experts if you are in a highly regulated industry, as you develop SharePoint composite solutions.

Security requirements can be divided into two types: authentication and authorization. Authentication involves some type of system that provides a mechanism such that users can prove their identity. An authentication mechanism commonly used by large organizations is Windows authentication, where users are provided with a user name and a password. This approach could be augmented with advanced techniques, such as smart cards or fingerprint recognition. Windows authentication involves the installation of Active Directory (AD) within your organization and servers (domain controllers) whose role is to take requests from other servers in your organization to check that a user’s credentials are valid. Once the requesting servers have received positive notification from a domain controller concerning the user’s credentials, then the requesting server can authorize the user to perform actions based on the permissions granted to the user (authorization).

Authentication

A BCS solution involves a number of systems and each system has its own authentication method. A solution typically involves an end-user device, such as a laptop, desktop computer, mobile phone, or tablet, and on that device an application will run, such as a mobile phone application, an Office application, or a browser. A BCS SharePoint solution involves a SharePoint farm as well as the external system. Each one of these systems—end-user device, SharePoint, and the external system—will be able to use a number of authentication methods, such as Windows authentication, SAML token, or Windows Live ID.

In an ideal world, once a user has signed on to their device, there should be no need to have the user authenticate again. Such an environment, in which a user need only authenticate once no matter how many systems the user’s application has to request information from, is known as single sign-on. Although over the years the authentication process has become increasingly transparent to the end user, it still doesn’t happen by magic. In most BCS solutions, you will have work to do. If not you, then someone in your organization will need to understand the authentication methods.

For example, in an organization that hosts its own AD and SharePoint farm, the credentials that a user logs on to their computer with are used to authenticate with the web server—this is called Windows authentication. SharePoint, using the authorization settings for the user (for example, the site permission settings), allows the user to complete certain operations. In this scenario, the two systems involved—the user’s computer and the SharePoint web server—share the same authentication method, and there is just one hop from the user’s computer to the SharePoint server.

With a BCS solution, in the same scenario three systems are involved, as shown in Figure 2-5: the user’s computer, the SharePoint web server, and the external system.

Figure 2-5. Displaying data from an external system requires two-hop authentication.

Even if the external system shares the same authentication method as the end-user device and SharePoint, when you try to pass a user’s Windows authentication identification on to the external system, doing so will always incur a double hop: one hop from the client’s computer to the SharePoint server, and a second hop from the SharePoint server to the external system. Unless SharePoint 2010 and the external system are installed on the same server, the user’s credentials will not be successfully passed on to the external system. This is known as the double-hop issue.

Note

SharePoint 2010 and the external system are usually installed on the same server only in small organizations, or in larger organizations in a development, prototyping, or demonstration environment.

The workaround to the double-hop issue, where you want to use Windows authentication both for the SharePoint site and the external system, is to use Kerberos or an SSS service application.

The SSS service application is available with SharePoint Server 2010 and can be used with BCS to authenticate users with external systems. It supports access to external data by allowing you to map the credentials of SharePoint users or groups to external system credentials. The SSS application has its own database where the user credentials are encrypted.

Note

Microsoft recommends using Secure Sockets Layer (SSL) when connecting end-user devices and SharePoint web servers, as well as SSL or Internet Protocol Security (IPsec) between servers running SharePoint and external systems, to ensure that sensitive data is not compromised.

Authorization

There are at least three levels of authorization with a SharePoint-based BCS solution:

• SharePoint permissions Sites are organized into site collections, and a site collection is a security boundary—that is, the top-level site of a site collection starts with its own unique security settings. When you create sites, by default they inherit the permissions of their parent site, which then percolates down to all lists, libraries, list items, and files within that site. When you create a page to display external data or when you create an external list, SharePoint permissions are used to control who can see that page or list. You can break the inheritance on your site so that pages and lists can have their own unique permission settings.

  Note

  When you create an external list from an ECT, you cannot have unique permissions on items within the external list. You can have only per-item level permissions when the data resides within SharePoint. An external list presenting data held in the external system and only the external system can apply permissions on a per-item level.

• BCS service application permissions Since any BDC model could be extensively used through your SharePoint installation and could be a critical component to the day-to-day working of your organization, there is a mechanism to guard against accidental or malicious modifications to the model. The BDC service application also allows you to specify who can use the definitions to access the external data that the BDC model describes.

• External system authorization Most external systems have their own authorization settings determining who can see the data as well as who can view, create, manipulate, or delete the data.

When you create a SharePoint page to display or modify external data, the user experience will be a combination of these three levels of authorization. The user must have SharePoint permissions to the library where the page is stored. The user must have BCS model permissions for SharePoint to read the external system connection details and ECT information. Then the external system must allow the data to be retrieved for that user.

Also, once a BDC model is created, then the visibility of the external data is not limited to a single site. You can set permissions on the BDC model so that only a subset of users can see it, and those users may be site owners or list owners on other sites. However, without a full understanding of how you set up the authorization permissions, the users may decide to create solutions to use the data on their sites or within their lists with unexpected results. Other users on these sites may not have permissions to see the external data because of authorization settings at the BCS service application level or within the external system. These users on other sites may be presented with an error message, and then they might phone the help desk for support. Therefore, it is important to understand the security implications of your solution, not just on the site where you plan to create external lists and business dashboards, but also throughout all the web applications associated with the BCS service application where your model is stored.

Note

See Also For more information about configuring BCS security, see Chapter 3.

As previously explained in this chapter, Business Connectivity Services (BCS) allows you to connect to external data through SharePoint Server 2010 and surface the data in Office 2010 client applications, but that is just the beginning. By further extending the Office 2010 client that exposes the external data, you can create very rich, user-friendly, intuitive solutions. Once you’ve established the BCS connection, you can do a great deal more in the client applications to enhance the user experience.

For example, Microsoft Access 2010 makes connecting to external data easier than ever before. Access has always been a great landing pad for data. With Access 2010, Microsoft has built a new cached mode to dramatically improve the performance of connections to SharePoint lists. Additionally, Access can now slice, dice, and report on BCS data, just like any other linked table.

Although you can develop your own BCS connectors, most BCS development within an organization will concentrate on the presentation and manipulation of the data. With a web-based solution, you have the choice of developing logic that will run on the server, server-side code, or code that runs within the browser, client-side code:

Whenever a solution is created that should be easily used by many people, whether it is a SharePoint-related solution or any other type of solution, the ease of use will come only if the architect of the solution has a deep understanding of the types of business scenarios it will be used for, information about the systems that store the business data, and knowledge of the skill set of the users. This is true for a Business Connectivity Services (BCS) solution as well. The person who architects the solution could be a business analyst, a citizen developer, a power end user, or a representative from the IT department, such as a developer.

A BCS solution consists of many components, and with good planning you can create an effective solution. The key to successfully using BCS is to create a Business Data Connectivity (BDC) model file that contains meaningfully named definitions that users can then use to expose the external data on their sites.

During the planning stages, you will need to liaise with a number of people, depending on your knowledge, the scope of the solution, and the number of external systems the solution will use. The solution can be very complex or simple. You need to give consideration to security, ensuring that users who should be allowed to use the data can use the data and that those users are not presented with permission-related error messages.

A BCS solution is not like a solution that can be created using the Data Sources gallery in SharePoint Designer. Such solutions tend to be created on a per-site basis, for a small set of users, by a person who knows the external system and knows the users who will use the solution. The creator of such a solution tends to also be a user of the solution and can tweak and readily extend the solution, creating other Data Source definitions as required using naming conventions that only the creator needs to understand.

If you plan to make heavy use of BCS within your organization, you will need a thorough understanding of BCS, which is the aim of this book. The project/program manager will have to create a communication plan so that end users adopt the solution. For example, the project/program manager will have to obtain the backing of the business owner; train the relevant citizen developers and help desk staff in the use of external lists, SharePoint Designer, and InfoPath Designer; and create electronic help or additional text on pages, such as any new pages you plan for the search center to inform users of the new search capabilities.

You may have to build a prototype that enables you to create the necessary business plan so you can identify whether the BCS solution is a SharePoint-related solution and/or an Office client application solution, whether users want to connect to the data using Outlook, and whether they want to take the data offline. You will need to identify if you can create the solution with only the out-of-thebox functionality or if you will need professional developer assistance.

In reading this chapter, you most likely have already identified some of the technical decisions and tasks you will need to complete when creating a BCS solution, including the following infrastructure and security-related tasks:

Business Connectivity Services (BCS) allows you to hook up external data with SharePoint and Office applications, enabling either professional developers or citizen developers in your organization to quickly develop composite solutions. BCS is implemented as a SharePoint service application, which allows you to create an external system definition once, known as a Business Data Connectivity (BDC) model, and share these definitions with many sites. In addition, a SharePoint farm can host more than one BCS, and each one can be configured independently by different sets of administrators.

The BDC model consists of XML declarations that describe the external system you want to access as well as the external content type (ECT) that defines the operations you might like to use on this external data and its format. Once an ECT is defined, then using the browser or SharePoint Designer, you can manipulate the data from the external system as you do other SharePoint objects, such as lists and Web Parts, by using the external list, a new list type in SharePoint 2010.

You can extend the out-of-the-box BCS functionality using either client-side or server-side code. Client-side code can be developed using tools such as the browser, InfoPath Designer, or SharePoint Designer, whereas server-side code is developed using Visual Studio and deployed as precompiled dynamic-link libraries (DLLs).

SharePoint 2010 has tighter integration with Office client applications than in Microsoft SharePoint Server 2007 had with the Office client applications. You can use the BDC model to reveal external data in Microsoft Office 2010 applications, including Microsoft Outlook 2010, Microsoft Access 2010, Microsoft Workspace 2010, Microsoft Word 2010 and Microsoft InfoPath 2010.

Whether you create a SharePoint-related or Office client application BCS solution, you should consider the infrastructure and security implications, and plan the solution accordingly.