Marshaling firm resources with SharePoint

Special to Law.com

By Mark Gerow

Given the number of new or improved features vying for your attention in Microsoft Office SharePoint Server (MOSS) 2007, it's easy to overlook the Business Data Catalog (BDC), SharePoint's foray into the "middleware" market. And, because the most apparent capabilities of the BDC, which ships with MOSS Enterprise Edition, can be duplicated by other products or features, you may have filed it away for consideration at some unspecified future date.

In this article, I'll explain what the BDC is and how it can play a central role in making your key business data accessible to all of your firm's employees. We'll then walk through the process of integrating one common legal application, Elite, using the BDC. I'll also cite a few third-party products that may be worth your evaluation. Of course, you should evaluate these products carefully before deciding whether their benefits to your situation warrant the associated cost and complexity.

What Is the Business Data Catalog?

The BDC aims to integrate SharePoint with extant resources such as a client relationship or time-and-billing applications. The BDC provides a central repository for metadata describing the databases and applications your firm uses so that SharePoint users can query those resources and receive the results from within SharePoint. This metadata is authored in the form of XML documents that describe how to connect, query, and format data from your data sources, which can be either SQL databases or Web services.

Prior to the BDC, you had two choices for data integration in SharePoint: Write your own or purchase a general-purpose integration framework.

•

While home-grown solutions (which range from configuring DataView Web parts in FrontPage/SharePoint Designer to developing custom Web parts or Web services) require significant understanding of the underlying data semantics and structures, they are "lighter weight" than frameworks because they need only solve the specific integration problem at hand.

•

General-purpose integration frameworks, such as those offered by Handshake, SeeUnity, or XMLaw, can solve most data integration problems but add complexity to the SharePoint environment.

When evaluating these frameworks, you should take into account the up-front cost, learning curve, vendor support, and alignment with Microsoft's strategy for SharePoint.

Note: Handshake, SeeUnity and XMLaw all provide ready-to-use schema (metadata libraries) for connecting to common legal data sources, such as Elite or InterAction databases, which is not the case with the BDC.

Benefits of the BDC
If you're currently having success with an integration framework, you may still want to evaluate aspects of the BDC that are not available with such frameworks. If you don't currently own an integration framework, I would recommend evaluating the BDC first to determine whether the features it offers are sufficient to meet your data integration needs. Here's a quick look at what the BDC can offer your firm.

Benefits of the Microsoft Office SharePoint Server BDC
Of the features listed above, the ability to full-text index line-of-business (LOB) application data is particularly compelling in a legal setting because it allows lawyers, paralegals, and other knowledge workers to construct ad hoc queries to return data in the same way that they already search for documents. Later in this article I'll show how this works.

Creating a BDC Schema

While we can hope that legal software vendors such as Elite and Aderant release BDC schema for their products, they have not yet done so. However, with a little study and effort, we can create the necessary schema ourselves. To make authoring BDC schema a bit easier, Microsoft has included a BDC schema editor in the MOSS software development kit (SDK). There are also a few third-party BDC schema editors, such as BDC Meta Man (http://www.bdcmetaman.com/), which can ease the authoring process.

As is often true when working with new technology, patience and persistence are virtues. Although I'll try to point out some strategies that will make your work easier, you or your team should be prepared to study and experiment.

Before you begin, you will need to assemble the following:

•

The latest version of the MOSS SDK, which is available as a free download. This includes documentation on both the BDC XML schema as well as a BDC schema editor.

•

SQL connection information for any data sources (such as Elite or Aderant) you want to add to the catalog. You will need the database server and database names, as well as an SQL login and password.

•

An SQL SELECT statement that returns a list of rows matching user-specified criteria (e.g., a WHERE clause). In BDC terminology this is referred to as the "Finder" query. Such a query might look like this:

•

SELECT client.clname1, matter.*

•

FROM matter, client

•

WHERE (mclient=clnum) AND (mmatter LIKE @mmatter) AND (mopendt >= @mopendt_start) AND (mopendt <= @

•

mopendt_end) AND (client.clsort LIKE @clsort) AND (mopendt >= '1/1/2007')

•

The query parameters, indicated by the "@" character, will be filled in either by the end user or the BDC at run-time. As the preceding query demonstrates, your BDC entries are not limited to single-table queries.

•

An SQL SELECT statement that returns a single row matching a given primary key (e.g., the "mmatter" column that uniquely identifies a matter in Elite). This is referred to as a "Specific Finder" query. Here's an example:

•

SELECT client.clname1, matter.*

•

FROM matter, client

•

WHERE (mclient=clnum) AND (mmatter LIKE @mmatter)

•

An SQL SELECT statement that returns a list of all primary keys for a query (e.g., a list of all "mmatter" values in the "matter" table in Elite). This is referred to as the "ID Enumerator" query. This query has particular importance in that, without it, a BDC data source cannot be indexed by SharePoint Search. An ID Enumerator for the Elite matter table might look like this:

•

SELECT mmatter

•

FROM matter

•

WHERE (mopendt >= '1/1/2007')

The following image shows a portion of the Elite BDC schema displayed in Microsoft's BDC schema editor:

As you can imagine from the display above, there's quite a bit to a BDC schema, and we don't have space to cover it here, but you'll find all the information you need in the MOSS SDK noted previously. When your schema is complete you will import the definition into SharePoint using the SharePoint Central Administration Web site (SCAW). This process must be run on one of the MOSS front-end Web servers by a SharePoint administrator.

Suffice it to say that a few days' worth of studying and experimentation are probably needed to master the basics. The good news is that once you've successfully created and installed your first schema, creating subsequent schema will be much easier because the base structure will be similar across all schemas.

Querying and displaying BDC data

Let's fast-forward beyond all the installation details, and say that, for example, you've installed a BDC schema that understands how to query the Elite billing system's client table. How do you make that available to your users?

There are essentially three things your users can do with the data you've defined in the BDC:

•

Search for and display the data using the BDC Web parts

•

Look up data and associate it with list items

•

Perform full-text searches against the data

Using BDC Web parts

BDC Web parts come with MOSS 2007 and may be used with any installed schema. These Web parts allow end users to perform on-the-fly queries against the data sources and display the matching data. Such queries are most appropriate for users who understand the data and perform specific tasks with it, such as looking up client addresses. The following image shows a BDC Web part for client information lookup in action:

The search options available are determined by the filters defined in your BDC schema. In the above example, I have defined filters on both the client name and number columns.

Look up data and associate it with lists
The preceding example shows how easy it is to create lookup forms without coding. But things get even more interesting when you combine your LOB data with SharePoint-generated data.

For example, let's say you want users to tag documents in a library with a valid client number from your billing system, and that you want the client name to be displayed as well. You can do that by defining a "Business Data"-type column for a list and associating it with a method in your schema that retrieves client data. Such a list is shown in the image below (note the "Client #" and "Client #: Client Name" columns):

SharePoint automatically links the displayed value for the client number to a BDC-generated page so that if the user clicks on the client number ("B9176," in this example), a page displaying the entire client record will be shown.

Perform full-text searches
The most interesting benefit of defining and installing BDC schema for your LOB applications is that you can then search your LOB databases the same way you would your document libraries or file shares using SharePoint Search. This method of finding structured data is often more intuitive for end users who are not familiar with the data. Knowledge workers such as attorneys and paralegals will find data more readily if they can search for it in the same way they search your intranet or the Internet for documents. The following image shows a completed search using our Elite BDC data source:

While Microsoft has made tremendous improvements in the capabilities and performance of SharePoint Search, the out-of-the-box user interface and presentation are still a bit weak. For example, if instead of "B9176" I had entered "B9176 or Fenwick" in the search box, no rows would have been returned. Although SharePoint Search is capable of performing such a query, the default search pages do not expose that capability.

One option to address this shortcoming is to write a custom search page, but this requires familiarity with .NET development as well as the SharePoint Search object model. Another option is to use a third-party product that can augment the SharePoint Search UI without programming. Products from vendors such as Ontolica can be used to provide a more natural and robust user experience.

The following image shows a search page developed using Ontolica's Web parts. In addition to providing more intelligent parsing of the search expression, Ontolica offers improved formatting and the ability to drill down by keywords found in the results (client name, in this case).

Conclusion

The MOSS 2007 BDC provides a means to make data from your core LOB applications readily available to SharePoint users throughout your firm, without requiring any custom development. Once defined to the BDC, this data can be presented to your users through a variety of interfaces, including BDC Web parts, custom list integration, and SharePoint Search.

Given that knowledge workers spend between 15 and 30 percent of their time searching for information, integrating LOB applications into SharePoint using the BDC could help your firm access more of the potential value represented by the data in those applications as well as realize a significant improvement in productivity.

Mark Gerow has more than 20 years of experience in IT, professional services, and software product development, and has provided consulting services to hundreds of companies throughout the San Francisco Bay area and Northern California. He currently works for Fenwick & West, where he leads the application development team and is responsible for defining and implementing the firm's intranet and extranet strategies using SharePoint technologies.


Was this information useful?