Chapter 10
Integrating Business Data

IN THIS CHAPTER

• Introduction to the Business Data Catalog
• Configuring a New Business Data Application
• Using the Business Data Web Parts
• Searching for Business Data Entities
• Using Entity Actions
• Using Business Data Columns in Custom Lists

The Business Data Catalog (BDC) is arguably one of the most powerful new features available in Microsoft Office SharePoint Server (MOSS) 2007. This chapter provides details on how to consume data provided by a BDC application, including entities, associations, custom column lookups, searching, browsing, and much more. After you have a firm grasp on what the BDC is and how to consume BDC data, you can move on to Chapter 11, “Creating Business Data Applications,” which provides details on how to create your own BDC applications and expose your data to SharePoint via the BDC.

Introduction to the Business Data Catalog

SharePoint is often perceived as a point of integration or aggregation. This is the main reason why SharePoint Portal Server 2003 was used so much—it facilitated the publication of content and made it easy to gather multiple sites to create a cohesive portal.

As more and more consultants and developers began working with SharePoint, it became obvious that integrating content and data from non-SharePoint sources required tedious coding, testing, and deployment efforts often resulting in custom Web Part development or even in circumventing entire sections of SharePoint entirely.

The BDC provides a means by which developers and administrators can connect SharePoint to external data sources. Those external data sources can be integrated so tightly that entities from external Line of Business (LOB) applications can appear in search results, on user profiles, in Web Parts, and even as custom columns in lists.

The BDC is driven by several core types of information:

• Entities—An entity is a unit of data. This is typically a single row in a database table or a single element of information, such as a customer, a bug in a defect tracking system, an order, and so forth.
• ID enumerators—For SharePoint to use the BDC to properly index remote entities such as customers or orders, it needs to be able to enumerate the unique IDs of each entity. BDC application definitions provide definitions for ID enumerators.
• Methods—A method on a BDC entity is responsible for retrieving entity lists or retrieving related entities. These methods can be used to provide the user with the ability to filter the display and choose which columns are returned for each operation.
• Associations—Associations provide links between entities. These links can be one-to-many or many-to-many and go in either direction, giving the developer a lot of flexibility. These associations are used to go from one record (such as a Customer) to one or more related records (such as purchase history or a list of relatives).

The BDC is a shared service, which means it can be provided by any of the servers within a farm and you have to use the Shared Services administration console to configure the BDC. The Shared Services administration console can be reached by going to the SharePoint Central Administration website and clicking on the link for your Shared Services instance (often called SharedServices1 by default).

Authentication

Most external LOB applications require authentication to gain access to the data. Quite frankly, if they don’t require authenticated access to their data, those applications might have other problems that SharePoint will never be able to solve.

The BDC supports multiple forms of authentication:

• Passthrough—The authentication mode quite literally passes credentials through from the front end (SharePoint) to the back end (LOB application identified via the BDC). In other words, authentication is done against whatever process under which the BDC is running.
• RevertToSelf—This authentication mode “reverts” whatever impersonation might be done by Internet Information Services (IIS) and authenticates against the BDC application using the identity of the IIS application pool itself.
• Single  Sign-On (SSO)—This authentication mode uses the SSO subsystem for authentication to the BDC application.
• RdbCredentials—This authentication mode uses database credentials pulled from the SSO service to supply clear-text database credentials. In this case, make sure you’re using Secure Sockets Layer (SSL) or some other means of obscuring the otherwise clear-text credentials.
• WindowsCredentials—This authentication mode uses manually supplied Windows credentials to authenticate against the BDC application.

BDC Pros and Cons

As a developer, you are often asked to make recommendations on systems as well as to develop against them. As such, you should know some of the things that the BDC does extremely well and some of the situations in which the BDC might not be a viable option for your solution.

The following are some of the benefits of the BDC:

• Integrate existing LOB applications with relative ease.
• Extend SharePoint’s reach to aggregate and integrate multiple disparate systems throughout your enterprise into a single cohesive location.
• Allow external data to be searched and indexed in a meaningful way.
• Wherever external entities appear, you can define custom actions that might take you to the entity’s host application (for example, launch your Defect Tracking system when looking at a bug within SharePoint).
• Data is read-only; SharePoint adopts the “one and only one data owner” approach and leaves the work of editing remote entities up to their native host application.

The following are some of the drawbacks of the BDC:

• Authentication—Although SharePoint provides multiple means of authentication, the most common LOB application authentication method is not supported. If the user credentials themselves are stored within the remote database or web service, SharePoint has no way of validating those credentials. This makes it extremely difficult to access large application data sets like those contained within third-party applications like SAP from within SharePoint.
• Configuration—As you will see in the next section of the chapter, configuration of the BDC is done entirely through very large, difficult-to-manipulate Extensible Markup Language (XML) files and there are currently no tools available to make this process any easier.
• Data is read-only—Although some people consider this a positive aspect, other developers might consider it a negative.

Configuring a New Business Data Application

Configuring a new BDC application involves creating an XML metadata file for the application and then uploading it to the SharePoint BDC application manager.

The schema for this BDC metadata file is somewhat complicated and hand-creating these XML files on your own is time consuming and error prone. Given the fact that there are currently no tools available for manipulating the BDC application definition XML files, your next best bet is to take the XML files from sample applications and modify them to suit your needs. The following XML snippet shows the first few elements and the last element of a BDC application definition file:

The business data application represented by the preceding configuration XML is a relational database application residing on the local SQL Server instance officeservers (the default instance name for the standalone installation of SharePoint) and uses the database named CustomerData and authenticates via Passthrough authentication. The details of the entity definitions, method instances, and associations were left out to keep the file readable within the context of this chapter. You can also tell from the XML file that the name of the business data application is BugSystem. Presumably it will have definitions for entities that deal with defect tracking.

Before continuing through the rest of the chapter, you should download and install the “AdventureWorks Cycles Database 2000” SQL 2000 database. This database is used as the basis for many of the figures in this chapter as well as much of the code written in the following chapter. You can download the AdventureWorks SQL database sample at the following uniform resource locator (URL):

http://go.microsoft.com/fwlink/?linkid=55253

A version of the AdventureWorks BDC XML application definition file is available with the code downloads for this book that defines a connection to the .mdf disk file under the officeservers local SQL instance (the default if you installed on a standalone server). If you are downloading the XML file on your own, make sure you edit the XML file with notepad and change the server name to the one appropriate for your environment.

To install the configuration for a new business data application, go to the SharePoint 3.0 Central Administration website and then go to the Shared Services administration console. Under the BDC heading, click Import Application Definition. You will be prompted to browse for an XML file; select the XML file that corresponds to the AdventureWorks 2000 sample application.

It could take a while to finish the application import process. When complete, you will be able to examine the list of entities and entity relationships. Figure 10.1 shows the overview of a newly configured application definition (the BugSystem application).

Figure 10.1. Overview of a newly configured business data application.

Figure 10.2 shows the entity profile home page for a business data application entity. In this case, it is the Bug entity. You can see that the Bug entity has the following properties: Created, CreatedBy, Description, ID, and Status. You can also see the relationship that links individual bugs with one-to-many bug notes. There is even an action definition (covered later in the chapter) called Search on Live.

Figure 10.2. Viewing an entity profile.

After you have uploaded an application definition file that was provided to you by a third-party vendor or that you manually created yourself, you can start consuming business data contained within the remote application.

Using the Business Data Web Parts

Business Data Web Parts allow you to render the data contained within LOB applications defined and accessed by the BDC. The following is a list of the Web Parts that SharePoint provides for rendering and querying line of business data:

• Business Data Item—Renders a single business data entity
• Business Data Item List—Displays a list of business data entities
• Business Data Actions—Displays a list of actions that pertain to a business data item indicated by a row supplied by a connected Web Part
• Business Data Item Builder—Creates an item from parameters in the query string and provides it to other Web Parts on the page via Web Part connections
• Business Data Related List—Displays a list of entities related to an entity supplied by a connected Web Part

Using the BDC Web Parts is actually quite easy. Simply open up a page for editing and click the Add Web Part link at the top of a Web Part zone. From there, select one of the BDC Web Parts and add it to the page.

Figure 10.3 shows the Business Data List Web Part. It was associated with entities of type Bug. You can browse for an entity type easily using a browser window, which displays all of the entity types for all installed business data applications.

Figure 10.3. The Business Data List Web Part.

When the Business Data List Web Part appears on a Web Part page, it doesn’t immediately render data. When you define the configuration XML, you can optionally define a set of filters. These filters provide queries and parameters that filter the entity data when the user clicks the Retrieve Data link.

The Business Data List Web Part is also a connectable Web Part. It exposes the currently selected row as an entity that can be supplied to other connected Web Parts on the same page. This means that when the user selects an entity from a Business Data List, child rows or related rows can be displayed in a Business Related Data List Web Part.

Figure 10.4 shows the context menus that allow you to connect a Business Data List and a Related Business Data List Web Part together using the ASP.NET 2.0 connected Web Part mechanism.

Figure 10.4. Connecting a Business Data List and a Related Business Data List.

After the Web Parts are connected and you click one of the records in the Business Data List, the Related Business Data List will automatically update itself to retrieve records related to the one provided by the source list. This can be done to traverse one-to-many relationships as well as many-to-many relationships.

Figure 10.5 shows an example of these connected Web Parts using the data from the AdventureWorks 2000 sample SQL database.

Figure 10.5. Connected Business Data Web Parts (AdventureWorks 2000 sample).

Searching for Business Data Entities

Business data application definitions provide for method types referred to as Wildcard methods. These methods are invoked by SharePoint when searches are performed against the system. When these searches are performed, SharePoint returns applicable business data entities mixed with native SharePoint data. This provides a unified search experience that allows entities from external databases to appear as though they were native SharePoint entities.

The SharePoint Software Development Kit (SDK) provides full documentation on the metadata schema required to define wildcard operations. Wildcard operations can be performed against both relational database applications and web service applications.

Using Entity Actions

Entity actions allow developers to define URLs that can be launched from a drop-down menu of actions related to a particular entity. Information from the entity itself (such as an ID) can be passed on that URL. One example of when this might be useful is if you have an existing defect tracking system and you are looking at a bug entity. You might want an action on that entity to take you to the host defect tracking web application to allow the user to make changes to the data.

Figure 10.3 shown earlier in this chapter contains an action with the text “Search on Live.” This action redirects the current browser window to Windows Live Search, using the title of the entity as the search query string.

As long as the action you want to allow users to take can be expressed as a URL composed of data from the entity itself, you can create any action that does virtually anything—providing an incredible amount of flexibility and room for enhancement.

Using Business Data Columns in Custom Lists

In addition to just being able to render lists of business data entities and related entities, SharePoint allows you to do special business data lookups as columns within custom lists.

Assume you have your own internal customer management system and the standard SharePoint contact lists are insufficient. You might have data where you need to know the customer that is related to a single item in a list. In this situation, you could create a custom column that stores a reference to an individual customer. Anywhere a row in this list appears, you can also have a live link to the individual customer entity.

Figure 10.6 shows an example of how to create a column in a custom list that stores a reference to a Customer entity from the AdventureWorks sample database.

Figure 10.6. Creating a Business Data column.

Figure 10.7 shows what it looks like when a row in the custom list is displayed and the Business Data column is included in the view. Note how the customer is clickable and there is even a drop-down menu next to the customer that contains entity actions defined by the BDC application definition.

Figure 10.7. A Business Data column in action.

Summary

This chapter provided a walk-through of the basics of consuming and integrating LOB applications using the BDC. This is accomplished using a Business Data configuration file. You can find the one used for the AdventureWorks 2000 database in the file advworks_bdc.xml with the code downloads for this chapter. Using BDC Web Parts, custom BDC columns, and wildcard searching and indexing, you can create powerful extension and integration points within SharePoint to extend SharePoint’s reach and even extend the reach of the data contained within your external application. The next chapter focuses on the information you need to create applications that can be consumed by the BDC and how to use the BDC application programming interfaces (API).