Business Data Catalog in Microsoft Office SharePoint Server 2007: Architecture and Schemas

Microsoft Office SharePoint Services (MOSS) 2007 is Microsoft’s innovative tool that provides the infrastructure to advance collaboration and communication within organizations based on the smooth flow of documents and information.

The Business Data Catalog (BDC), a fresh approach in SharePoint 2007, ensures the integration between SharePoint and back-end systems such as databases as well as Line of Business (LOB) processes like SAP and Siebel applications.

There are two approaches for working with BDC: creating the XML schemas manually or employing SharePoint’s Object Model to programmatically interact with the LOB system.

Because the BDC definition is based in an XML schema, apart from general knowledge of XML, no specialized understanding of programming or software development is required to access data from databases to display in SharePoint. For the first time, in a relatively undemanding manner, database administrators are able to present information buried in tables and views in a web interface.

Likewise, SharePoint’s Model Object provides the tools to work programmatically with schemas, giving developers control over all aspects of the Data Catalog.

Various possibilities exist in the presentation of data in SharePoint; applying SharePoint default WebParts, employing customized ASPX, and utilizing web applications or WebParts. This enables companies to ensure that their business information is more accessible, thus saving money, time, and human resources.

Introduction to the Business Data Catalog and Its Integration Within MOSS

An Intranet is the ideal platform to share all types of information within a business or enterprise. But, information is not restricted to structured materials as in Microsoft Office files (Word documents, Excel spreadsheets) or non-structured data as in lists of any sort, but also information originating from back-end systems and applications. SharePoint is Microsoft’s Intranet solution, and until recently, it was only capable of conserving and organizing its own structured and non-structured materials. To display back-end information, it was necessary to make custom-built, specialized software.

The latest version of SharePoint, Microsoft Office SharePoint Server (MOSS), offers an effective way to unlock information contained in back-end and Line of Business systems using a metadata model known as the Business Data Catalog. Although only the most advanced and expensive version of SharePoint 2007 (MOSS Enterprise) is equipped to utilize the BDC, individuals interested in testing and experimenting with the product can download a 180-day trial version from the Microsoft site.

Data from business applications that is unlocked using the BDC can be employed directly in MOSS using its WebParts infrastructure, Lists, and Content Types utilized in the user’s profile or searched by the SharePoint search engine.

MOSS 2007 is delivered with five out-of-the-box WebParts to service the BDC, but other WebParts can be purpose-built using the SharePoint Object Model. The following are the standard WebParts:

  • Business Data List WebPart: The principal WebPart to display information in the user interface of SharePoint. It provides a way to display a list with the elements found by the BDC and a means to apply filters and activate the BDC actions. Figure 1 illustrates an example of the WebPart displaying data from a BDC entity.
  • Business Data Item WebPart: Exhibits the details of one element. It is possible to connect this WebPart to the List WebPart, allowing the details of the selected element to be displayed in the latter.
  • Business Data Related List WebPart: Presents a sub-list of elements from a main List of the first WebPart. For example, to display all the orders for a selected client in the Business Data List WebPart
  • Business Data Action WebPart: With this tool, actions can be configured to redirect to a related place (a site, for example) or to initialize an application. This WebPart displays the available actions of the BDC
  • Business Data Item Builder WebPart: Applied to create a Business Data Item based in one value in the QueryString of the BDC definition

Figure 1: SharePoint page displaying data from a BDC (“AnimalsEntity List”)

After creating and installing the BDC definition, SharePoint Administrators can install and configure each of the WebParts in a SharePoint site, making the data visible to users. Users with appropriate rights inside a site page also can apply the WebParts to display BDC data individually or for a subset of users. WebParts are an integral ingredient of SharePoint and the application and configuration of the BDC WebParts is no different from the exercise of any other type of WebPart.

Architecture and XML Structure

The Business Data Catalog is a “shared service” of MOSS. Shared Service Providers (SSPs) is a novel element of MOSS 2007 and grants a set of services that can be centrally configured and used by all SharePoint instances in a server farm. The BDC definitions are saved in one of the SSP repositories, and, using DataBase ODBC, ADO.Net, or WebServices connections, query the back-end or LOB information.

The BDC definitions are saved as XML structures based in a metadata-schema in the eponymous repository of the SSP. The final XML definition file describes all the necessary elements to connect with the LOB system (type and localization of the provider, data about the DataBase servers, authorization and authentication), to consult it (queries or Stored Procedures, input parameters), definition of the subsystems (actions, associations), and some metadata to describe the BDC (name, title).

Figure 2: Metadata model of the BDC schema (Source: MOSS SDK)

The BDC does not copy the data from the business application, but captures the data directly each time it needs to be displayed. To avoid delays in rendering the data, SharePoint uses a software caching mechanism that is refreshed each minute: If the data has been changed, the cache is deleted and refilled; the consequence is that rapidly changing data is restricted to one-minute snapshots. As a counterbalance, by utilizing the BDC Object Model, the caching of parts of the BDC can be shut down programmatically, permitting no-caching and accelerating the data display (for fast-changing systems) or releasing server resources (for never-changing systems).

Creation of a BDC

No specialized skills or knowledge of programming are required to design a BDC definition; a basic knowledge of XML is all that is needed. The definition consists of a XML (ASCII) file that can be constructed with almost any standard program, including Notepad-like software. Likewise, no compilation is necessary and the registration in SharePoint can be done by using the system’s user interface. Although no programming knowledge is essential, a firm grasp of the back-end structure and an awareness of business requirements is indispensable: To illustrate relevant information, the BDC designer needs to totally understand the construction of the LOB system.

To illustrate this point, a Business Data Catalog will be defined for a simple DataBase of a hypothetical zoo. The schema of the DataBase is displayed in Figure 2 and consists of two linked tables (one for the animals and another of their perceived danger to humans).

Figure 3: DataBase schema for the sample BDC

The principal parts of the metadata schema include the following:

LobSystem

The LobSystem describes the data source and is an obligatory element of the XML file. One LobSystem object contains at least one instance, but if it is necessary to define different systems, more than one instance is allowed. The properties of the LobSystemInstance define the data provider as well as the connection configuration and security for the LOB system to be used. The LobSystem and LobSystemInstance in the sample are as follows:

<LobSystem xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi_schemaLocation="http://schemas.microsoft.com/office/
              2006/03/BusinessDataCatalog BDCMetadata.XSD"
           
           Type="Database"/p>
           Version="1.0.0.0"
           Name="MyZooLOBSystem">
   <Properties>
      <Property Name="WildcardCharacter" Type="System.String">%
      </Property>
   </Properties>
   <LobSystemInstances>
      <LobSystemInstance Name="MyZooInstance">
         <Properties>
            <Property Name="DatabaseAccessProvider"
                      Type="Microsoft.Office.Server.
                            ApplicationRegistry.SystemSpecific.Db.
                            DbAccessProvider">SqlServer</Property>
            <Property Name="AuthenticationMode"
                       Type="Microsoft.Office.Server.
                             ApplicationRegistry.SystemSpecific.Db.
                             DbAuthenticationMode">PassThrough
            </Property>
            <Property Name="RdbConnection Data Source"
                      Type="System.String">SQLServerName</Property>
            <Property Name="RdbConnection Initial Catalog"
                      Type="System.String">MyZoo</Property>
            <Property Name="RdbConnection Integrated Security"
                      Type="System.String">SSPI</Property>
            <Property Name="RdbConnection Pooling"
                      Type="System.String">false</Property>
         </Properties>
      </LobSystemInstance>
   </LobSystemInstances>

Entity

An Entity is an element of the process. An Entity consists of Identifier, Methods and Actions. In the example, there is only one Entity (“AnimalsEntity”), and the Identifier identifies the DataBase key field (type and name) for the BDC. The following is a possibility:

<Entities>
   <Entity EstimatedInstanceCount="0" Name="AnimalsEntity">
      <Identifiers>
         <Identifier Name="AnimalID" TypeName="System.Int32" />
      </Identifiers>

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read