| Downloads |
|---|
| 96772.zip |
Microsoft Office SharePoint Server (MOSS) 2007 provides a service called the Business Data Catalog (BDC). The primary purpose of the BDC is to unlock and expose data held in back-end line of business (LOB) applications so that people, processes, and other information can easily and seamlessly link to that data. As long as someone (typically a developer) has a solid understanding of the data, the BDC can make that data pervasive throughout the MOSS platform. In this article I look at the architecture of the BDC and, by way of an example, reveal its power.
The Business Data Catalog
As its name suggests, the BDC is a catalog of business data. As such, it doesn't
physically store business data. Think of a shopping catalog that you've recently
viewed. What does it contain? That's right—descriptions of items
that you can then physically go retrieve. So the BDC is a metadata-driven connector
that describes the data, describes how to connect and retrieve that data, and
physically retrieves the data on request. The data remains in the back-end application,
with the BDC acting as a read-only conduit to it. It will cache some data and
some data is physically copied into SharePoint but you need to find another
vehicle if you want to write to the back-end data.
You can use multiple vehicles to access the data described in the BDC. Out-of-the-box, MOSS lets you access the data through Web Parts, columns in lists and libraries, fields in user profiles, and via search results. And, of course, if you do want to write some code, you can harness the BDC for custom purposes as well. But, as we will see, you can do very powerful things without lifting one coding finger. Figure 1 shows the logical architecture of the BDC.
Describing Data: The Application Definition File
I make it sound so simple by saying you don't need to write any code, don't
I? Although this is true, you do need to do some groundwork to describe the
data, and this will take effort on the part of the business application owner
and someone from IT who's knowledgeable about the physical storage of the data
and possible access methods. The application owner will describe the purpose
of the data and how it's linked to meet a particular business requirement. The
IT person will then create an application definition file (ADF), an XML file
that describes the data, and load the file into the BDC.
The BDC supports two data access methods—direct calls to most popular databases via ADO.NET, OLE DB, and ODBC drivers or calls to application-specific Web services that can gather and return the desired data. Because of space limitations, I'll tackle the Web services method at a later time. In this article, I use a simple database example that will expose the data shown in the two tables that Figure 2 shows, with the end result being a page shown in a standard MOSS team site, as Figure 3 shows. The business purpose of these tables is to list all the movies an actor has appeared in. An analysis of the data shows that the ActorId column links the two tables for such a purpose, with the Actor table being the parent table and the Movie table being the child.
Forget the BDC for a second and imagine the main coding stages you'd need to go through to achieve such a result. First, you'd need to identify and connect to the database with suitable credentials. Next you'd need to open the tables and run some queries that return various rows and columns (e.g., one to gather the parent item and one to link the parent to its child items). So you'd need to define the relationship between the parent and child and the columns that you want returned. Finally, you'd need to format and display the returned data.
The BDC removes the complexity of this process, and the ADF essentially describes how to perform each of these stages except data formatting and display. That step is performed by BDCaware Web Parts, list columns, user profile properties, and search.
The ADF must be well-formed and adhere to the schema defined at C:\Program Files\Microsoft Office Servers\12.0\Bin\bdcmetadata.xsd on a SharePoint Web front-end server. The schema defines connections, entities, methods, filters, actions, and relationships. Let's look at each of these components and the associated XML code that I used to deliver our example. You can download the entire XML metadata file from Windows IT Pro's Web site. (Go to http:// www.windowsitpro.com, enter 96772 in the InstantDoc ID text box, then click Download the Code.)
Connections
The LOBSystemInstance node in Listing 1,
defines authentication and connection information such as which database instance
to connect to and which catalog to open. (Note that some lines wrap because
of space limitations.) From an authentication point of view, the BDC can access
the database as a system account or as the calling user.
The code shows that I've defined an instance called SpiesInstance. It accesses a table called Spies in a SQL Server database called dc2\sqlexpress using the credentials of the calling user. The calling user therefore will need permissions to access the data.
Entities
An entity describes an item in the LOB application. The code in Listing
2, defines two items: an actor and a movie. Each entity contains child nodes
that define identifiers, methods, filters, and actions. Think of the identifier
as the primary key within a database table. It uniquely identifies a particular
instance of an entity, which, in our case, are the primary keys called ActorId
and MovieId. (Note some sound advice from a badly scarred human being: The XML
is case-sensitive so make sure the names match the database columns.)
The properties node defines some characteristics of the entity. The code in Listing 2 shows that the ActorName and MovieName columns will be the default display value that's returned for the entity. It also associates a default action called View Profile that I discuss later.
Methods
The methods component defines the methods by which you can locate instances
of an entity, much like an interface definition. For example, a method can be
a SQL statement, a stored procedure for a database, or a Web method for a Web
service. Certain methods must be defined for certain SharePoint functionality
to function. For example, you must define an IDEnumerator method if the data
is to be indexed by the search engine. This method returns all the valid Entity
IDs so that it can crawl through all instances of the entity. The MOSS 2007
software development kit (SDK), available at http://msdn2.microsoft.com/en-us/library/ms550992.aspx,
provides more details about methods instance types and how to use them for different
types of SharePoint functionality.
Listing 3, shows that for the Actor entity, I've defined two methods: GetActors and GetMoviesForActor. Both of these run appropriate SQL statements to return the desired result based on some input parameters. For each entity you must define a method instance that will return one instance of that entity (type=SpecificFinder) and a method instance to return multiple instances of that entity (type=Finder). Failure to do so will result in errors when you use a Web Part, list, or search to consume the data. In this example, I use the GetActors method to return one or all of the actors.
Parameters
You need to define input parameters that will be used as filters to queries
at the backend. Some default Web Parts let you choose which input parameters
to filter on. You also need to define the return parameters for each method.
You can see in Listing 4, that I defined
a filter called Name that will perform a wildcard comparison. I then associated
that filter with the input parameter that relates to the ActorName column (see
the AssociatedFilter attribute of the TypeDescriptor node that describes the
Actor Name input parameter).
Listing 2 also shows a parameter whose name attribute is Actors. This is where I describe which columns from the table I want returned and what display names to use for them.
Actions
Actions are independent operations that can apply to an entity. You describe
them in the BDC, and they're surfaced in the relevant places. For example, defined
actions will appear in the Actions menu of the Business Data List Web Part.
You can use them to do almost anything—such as, provide a quick link
to the source data through the source application for full access to the data
or link to any location based on the returned data. For example, you might want
to link to a Google or Windows Live search and pass a piece of returned data
into the search.
View Profile is a default action that you get with every entity. You can see in Web Listing 1 (which you can view at http:// www.windowsitpro.com, InstantDoc ID 96772) that I added two other actions. One will feed the actor's name into a search on the Internet Movie Database, and the other will launch the URL that's associated with the WebLink column in the actor's entry in the Actors table. You can also add more actions through SharePoint after the entity is defined within the BDC.
Associations
Associations link entities together. A typical example would be two tables in
a database that have a one-to-many relationship. In our example, I use a relationship
to link an actor to the movies he or she appears in, as Web
Listing 2 shows. The association defines the name of the method to use to
form such a relationship.
As you can see, defining the contents of the ADF requires knowledge about the implementation of the back-end application. Creating the correct XML is never a trivial task, but you can find XML samples that use the Adventure-Works2000 SQL Server sample database in the MOSS SDK. There are also some community tools and third-party applications—such as http://www.bdcmetaman.com/default.aspx— starting to appear that can help generate the right ADF for the job at hand.
Importing an ADF
After you define the ADF, you have to import it into the Shared Services Provider;
the BDC is a shared service and as such can be used by any Web Application or
site that's associated with that shared service. You do this from the Shared
Services Provider Administration Web application, which is accessible through
SharePoint Central Administration. The import analyzes the XML, verifies that
it's conformant, and loads all the metadata definitions into the shared services
database. Once this is complete, the entities within the shared services database
are available for consumption by other parts of SharePoint (e.g., Web Parts,
lists, user profiles, and search). Web
Figure 1 shows the Actor entity as it appears in Shared Services Provider
Administration after the ADF file has been successfully imported. The displayed
fields are those that I defined in the ADF—and in this example, they
correspond to all the fields in the source table—but it's up to the designer
of the ADF to decide how much data to surface from the underlying source. You
can also see the relationship I defined and the three actions. Next, let's see
how this catalog of definitions can be consumed by other components such as
Web Parts, lists, and search.
BDC Web Parts
Out of the box, you get six BDC Web Parts that you can use to display entities
from the BDC. You can connect Web Parts to build very sophisticated Web pages.
So let's see how we can use the Business Data List Web Part and Business Data
Related List Web Part together.
The Business Data List Web Part lets you choose an entity from the catalog and display all instances of that entity in the back-end data source. It lets you filter the query on the backend data based on the FilterDescriptors that are in the ADF file. You can also control the output by using a custom Extensible Style Language (XSL) style sheet. In our example, the Web Part displays the data in the section at the bottom left of Figure 3. I configured the Web Part to return every Actor entity rather than perform any filtering.
I can now use the Business Data Related Links Web Part to exploit the relationship I set up between the Actors and Movies databases in the ADF. When you configure the Related Links Web Part, you'll be able to see all the relationships that are defined in the BDC. Therefore, by choosing the ActorToMovie relationship, then using Web Part Connections to feed the selected actor into it, the movies will be associated with the selected actor. (After you define an entity in the catalog, it's accessible to many places, including a column in a list. So, when I defined the custom list that you see displayed in Figure 3, I specified the Actor column as being of type Business Data.) Next, you'll see a UI that lets you browse the catalog and select an entity that you want to display in that column. Recall that in the example, the default return value was the ActorName field so this is what's displayed. But you can choose to return whichever properties of an entity that you want. The data that's retrieved through the BDC is read-only, but it will be updated automatically when any of the writeable columns in the item are updated. This functionality lets you group custom data with data that exists elsewhere (and is probably currently only accessible through an LOB application). How powerful is that?
When you combine all the new features that SharePoint Search offers with the BDC, you can get similar results to what Web Figure 2 shows. You can see a separate tabbed search results landing page for actors with a pivot link for movies. When you select that link, you'll see another search results page showing all the movies that actor has appeared in.
Getting Down to Business
Do you have any data locked up in back-end business applications that you'd
like to exploit? The BDC might be the answer. The BDC is a fantastic addition
to SharePoint technologies, even though it's still immature with few support
tools. I recommend you get familiar with the topics in the MOSS SDK. Armed with
that information and a detailed knowledge about the back-end data, you'll be
ready to fully exploit all your corporate LOB data.
