Welcome to the first of an eight part series on the Business Data Catalog (BDC).  While BDC is an enormously powerful tool, it’s also fairly complex and not well understood.  This series is considered technical in nature and intended for SharePoint installers, administrators or developers.  We’ll walk you through the BDC step-by-step, so no prior knowledge of BDC is expected, but you should already have a basic understanding of SharePoint.  For those that have some knowledge of BDC, this series will certainly will fill in the gaps to ensure you know everything you need to know.

We have three goals for this series:  1) Give you a strong understanding of what BDC is and how it works; 2) Ensure you recognize the value it brings organizations; 3) Provide you with the technical know-how to set it up and use its many features.

BDC Overview

As you already know, Microsoft Office SharePoint Server (MOSS) provides tremendous value as an intranet solution. One of the ways it excels is that it provides a single access point for the data assets in your organization. In many cases, these assets are stored in a Word doc, a PDF, an Excel workbook, etc.  In other words, it’s just a file. We call these sources unstructured data due to their flexibility in storing almost endless forms of information. It’s this flexibility that has made MS Office such a popular software suite. Users can work in a rich client application and then simply save the file, presumably into a document library.

This is where the power of MOSS as a document management system comes in. When coupled with an enterprise search engine, it’s even greater. Despite the unstructured nature of these files, users can go into their portal and by either a few navigation clicks or a quick search, they can find the answers they need. Having this centralized location for answers yields not only productive employees, but happy ones. It may seem that users have the best of both worlds: the flexibility of storing data the way they want, and the ability to publish it knowing that other users can find it.

But hang on, it’s not quite this easy. Sometimes, structure is necessary. Structure imposes rules and order. Do we really want to store all our customer orders in a single Excel sheet? Structure helps ensure we have valid, manageable, and most importantly, trusted data. For these structured data needs, IT professionals have relied on the tried and true relational database, and many vendors such as Oracle, IBM and Microsoft have made a bundle selling their flavor of SQL.

With these relational database management systems, information is locked in server-side databases. You usually work with the data through a separate application, perhaps a rich client or a web-based interface. These structured databases are the lifeblood for critical systems such as accounting, CRM (Customer Relationship Management), ERP (Enterprise Resource Planning), and a myriad of other line-of-business needs.

In fact, these solutions have become so popular and easy to build, they have sprung up all over the organization into what I like to call database sprawl. Typically, each of these systems is another data island, disconnected from the others. In many cases, information is duplicated across these systems, causing chaos where order was expected. From the user’s standpoint, they can be perceived as very inflexible and frustrating: Where is the data I need?  How do I log into that system?  Once I’m logged in, how do I access it?  By the time each question is answered, precious time is lost.  Or worse, we give up and make decisions based on no data at all.

This yields an interesting dichotomy in the way we store data. On one end, we have the unstructured nature of MS Office; on the other, we have the structure provided by the relational database. Interestingly, both can result in chaos. What you’ll be pleased to know is that MOSS can also bring some order to the database sprawl as well, and it does so in a very similar way.

With Business Data Catalog, part of MOSS Enterprise, we again get directed us back to the SharePoint interface to get access to our data. With BDC, this structured data can be exposed through Web Parts; it can be indexed and searched; it can be merged with data found in lists. With BDC connections to your back-end systems, we can find and consume both structured and unstructured data assets that exist in our organization. And we do this from one location, one interface.

Architectural Overview

A holistic view of what BDC can do and how it works is best depicted in Figure 1.

Figure 1: BDC Architecture

What you see along the top are the primary ways BDC can expose data from your back-end systems. Here is an overview of each of these from left to right:

1) BDC data can be surfaced using the many out-of-the-box Web Parts. This is the most common way and very easy for the users to search, filter and sort for information. You simply add one of the BDC Web Parts to any page, point it to one of the sources in the catalog and users are ready to go. All data is pulled in real-time using this approach.

2) SharePoint Lists allows you to merge data that lives in SharePoint along with data from a back-end system. For example, perhaps you have customer names stored within a list. From your CRM system, you would also like to see contact information for each customer. By adding a BDC field to your list, you can copy the corresponding data from your back-end system and store it within the list. Typically this matchup is done one-to-one. To ensure the copied data doesn’t get stale, users can refresh it on demand.

3) Search allows you to set up a content source and point it to an application in the BDC. SharePoint then goes through through BDC to crawl your structured data source and store the keywords in its index. Your search queries can now go across both structured and unstructured sources giving you a unified set of results.

4) User Profile Importer allows you to supplement attributes found in the User Profile store from external databases. This is important for many organizations as their Active Directory or LDAP directories are rarely populated with all user details. For example, how many IT administrators will actually fill in the birthday or skills attribute? Data like this is usually found in HR systems anyway.  Using BDC, these values can be pulled and matched up with the user, providing a complete set of accurate values.

5) With custom applications, you can avoid having to write another data layer for your new application. Instead of directly connecting to the system using ADO.NET or a Web Service, you can consume the data using the BDC Object Model. This gives you a more flexible, declarative style of talking to your back end.

With each of these approaches, you may have noticed that the concept is predominantly read-only in nature. This is similar to decision support systems.  That is, we usually use BDC to consume the data from these back end systems, but rarely write back to it. In the final article in this series, you’ll see how you can write to your back end system.

A Quick Look at BDC in Action

Let’s see how this works.  In this first example, we demonstrate the Business Data List Web Part (#1 from above).  This makes the finding and displaying of rows from a table very easy.  As you see here, we are finding all rows from the Customers table (perhaps from a CRM database) that contain the word “market” in the name.

Figure 2: Business Data List Web Part

In the second example, we see how users can search for both documents and BDC sources (#3 from above).  A search for Great Lakes has been issued, and we see both document and BDC Search Results listed together.  We’ll go into far more detail on these features and others as we get into other articles.

Figure 3: BDC Search Results

How Does BDC Work

Now that you have a better idea of what it can do, you’re probably wondering how does it work? As you can see, the BDC is a middle man between your interface and the back end. To provide this universal interface, it uses a database-like XML syntax that is called an Application Definition File or ADF. This ADF defines the connection, the entities (for a database, these are usually tables, views or stored procedures), how to filter, what associations (relationships) exist and what user-friendly names should be used. These names can even be localized in different languages. This ADF is intended to be a generic interface describing the dictionary of information available. Once these ADF files are stored within the catalog, the first four options above are available without having to write any code. Of course, security is an important consideration when we talk about exposing sensitive data, so we’ll see how the BDC addresses this concern in the next article.

For the actual back end, as you can see in Figure 1 above, this can be any ADO.NET accessible database with an OLEDB or ODBC driver.  This covers most every database from MS Access and SQL Server to Oracle and IBM DB2. The back-end can also be a specially-written SOAP-based Web service.  The BDC is configured within a Shared Services Provider (SSP) which allows you to take advantage of BDC technology within any Web Applications within your SharePoint Farm.

Series Outline

With that, that nearly brings us to a close for this first article. But before we let you go, here is an outline on how we are structuring this series.  To get the full value, you should go through these in order, but we’ll try to keep them discrete so that you can jump around and focus on the ones that are most important to you. Note: these may slightly change as we go through each of these.

Part 1
In this first part, as you have now read, the focus is be on the concepts, the value it can bring organizations, the architecture, and an overview of each article in the series.

Part 2
This article will introduce the Application Definition File. By the end, you’ll understand the role it plays, how it works, and have a high-level grasp of its schema.  We’ll also introduce tools that allow you to generate these XML files.  Securing ADFs will also be covered in this article.

Part 3
This article will cover how the Web Services can be used instead of a direct connection to a database.  It will look at the kind of Web methods you’ll need to write if you want to expose your systems to BDC this way.

Part 4
This will be a two-part article.  In first half, we’ll look at the out of the box Web Parts that BDC provides.  These is the most common way that this structured data is presented to users, so we’ll give it a solid overview.  The second half will cover how you can cache BDC data into SharePoint lists.  This will allow you to marry SharePoint data and BDC together into one consolidated view.

Part 5
This article will cover everything you need to know to to configure Enterprise Search to crawl BDC data.  We’ll cover content sources, scopes, and how to configure MOSS for both full and incremental crawls of your structured data.

Part 6
As mentioned above, BDC can supplement properties contained within User Profiles. This allows you to pull critical data elements from external ERP or HR systems and map them to out-of-the-box or custom User Profile properties.  This article will cover everything you need to know to get this working for you.

Part 7
This article will apply mostly to the developers out there and will cover the BDC Object Model.  You’ll see how custom Web Parts, application pages or other custom-built SharePoint applications can consume BDC database without your having to write another data layer.

Part 8
In the final article, we will cover Custom Actions how these can be used to safely write changes to the back-end database or Web Service.  While not used often, this may prove to be a practical way for users to maintain some of the data in these external systems.