Share via

Creating Business Data Catalog Entities in SharePoint Server 2007

  • Article

Summary:  Learn how to create Business Data Catalog entities in Microsoft Office SharePoint Server 2007.

Office Visual How To

Applies to:  2007 Microsoft Office System, Microsoft Office SharePoint Server 2007

John Peltonen, 3Sharp

April 2007

Overview

The Business Data Catalog is a Microsoft Office SharePoint Server 2007 feature that exposes and incorporates line-of-business (LOB) data into other baseline portal functionality, such as lists and enterprise search. To incorporate this data into your portal, you must build an XML file that identifies where the data is stored (either Microsoft SQL Server or a Web service) and what the data looks like (for example, what the data types and primary keys are).

Code It

This section walks through building a Business Data Catalog metadata file that defines a simple LOB data entity. The prerequisites are as follows:

  1. Install Microsoft SQL Server 2005 with the AdventureWorks database.

  2. Copy the Business Data Catalog schema files, Bdcmetadata.xsd and Bdcmetadataresource.xsd, into the Microsoft Visual Studio 2005 Schema Library. Copy these files from program files\Microsoft Office Servers\12.0\Bin\ to program files\Microsoft Visual Studio 8\Xml\Schemas\.

To build a Business Data Catalog metadata file that defines a simple LOB data entity, follow these instructions:

1. Create an Empty XML File

Begin by creating an empty XML file.

To create an empty XML file

  1. Start Visual Studio 2005.

  2. Create an XML file.

  3. Right-click the document surface and click Properties.

  4. Click within the Schemas property box, click the ellipsis, and attach the Bdcmetadata.xsd schema file to the document.

2. Define the LOB System

Add the following XML to the file (below the XML declaration) to define the LobSystem top-level parameters:

3. Define Top Level Security Parameters

Note

This is optional. You can also configure these parameters in the Business Data Catalog admin pages.

Add the following XML to the end of the LobSystem element (before the </LobSystem> end tag). Note that you must update the Principal attribute values with the correct domain and user or group names.

4. Define the LOB System Instance

With a database, the system instance defines the database connection parameters that the Business Data Catalog uses. Add the following XML as a child of the LobSystem element (after the AccessControlList element). Note that you must replace the text your_server_name with the actual server name.

Note

A drawback to using pass-through authentication is that the operating system exposes only the user name and password. Therefore, if a company uses two-factor authentication (that is, users are required to have some specific—private—information in addition to a user name and password), you cannot use pass-through authentication. Despite these drawbacks, its simplicity makes pass-through authentication a good candidate for use in a testing environment. If your company is using two-factor authentication and you want to connect the Business Data catalog to a SQL Server database, you can use Single Sign On (SSO). To use SSO, follow either these instructions Step 6 (Optional): Use Single Sign-On to Connect to the AdventureWorks2000 Database (WindowsCredentials), or these instructions How to: Adapt the Samples to Connect to Oracle and Other Databases (RdbCredentials).

5. Define the Customer Entity

An entity consists of a title (defined in the Properties element), a unique identifier (which equates to a primary key), and methods that both define the fields of an entity and indicate to the Business Data Catalog how to pull the entity data out of the LOB system. The following steps define an entity named Customer.

To define the customer entity

  1. Add the XML shell for the entity after the LobSystemInstances element. This XML contains the placeholder elements you use to define the entity’s title, the unique identifier, and the method to return all customers.

    Note

    A method contains a Properties element that defines the database query (or Web service method), a Parameters element that defines the data returned from the query, and a MethodInstances element that identifies specific functionalities in SharePoint Server 2007 that the method supports.

  2. Define the properties for the method named GetCustomers. With a database, the properties for your entity shows the Business Data Catalog how and what to query within the database. Add the following XML within the Properties element for the GetCustomers method. Notice that our T-SQL statement has a WHERE clause and includes references to the minCustomerID parameter and maxCustomerID parameter that you must define. Also note that the greater than and less than symbols are escaped.

    Note

    You do not start in order here. In the beginning, focus on the method properties, not on the higher-level entity properties. This is where you define the query that drives the other areas of the entity.

  3. Go back to the entity level and define the properties and identifiers. Replace the Properties and Identifiers placeholder elements with the following XML:

  4. Define the method parameters. Add the following XML within the Parameters element of the GetCustomers method. Note that three parameters are defined. The first two are input parameters that filter the list based on CustomerID. The third parameter defines the data returned by the SQL query.

    Note that all three parameters include references to the identifier that you defined in the previous step. The identifier is an important part of the entity. It is used by SharePoint Server 2007 to query for a specific element (hence the identifier being defined on the input parameters). It also applies various additional pieces of functionality to the outputted entities (such as applying Business Data Actions). Without the identifier, the entity is severely crippled.

    Also note that the return parameter is somewhat complex in nature. It is defined as a collection of data records. This indicates to Office SharePoint Server 2007 that there is more than one entity to return.

    <Parameter Direction="In" Name="@minCustomerID">
   <TypeDescriptor TypeName="System.Int32" Name="customerID" IdentifierName="CustomerID">
      <DefaultValues>
         <DefaultValue MethodInstanceName="CustomerFinderInstance" Type="System.Int32">
         0</DefaultValue>
         <DefaultValue MethodInstanceName=
            "CustomerSpecificFinderInstance" Type="System.Int32">
         0</DefaultValue>
      </DefaultValues>
   </TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name="@maxCustomerID">
   <TypeDescriptor TypeName="System.Int32" Name="customerID" IdentifierName="CustomerID">
      <DefaultValues>
         <DefaultValue MethodInstanceName="CustomerFinderInstance" Type=
         "System.Int32">9999999</DefaultValue>
         <DefaultValue MethodInstanceName=
            "CustomerSpecificFinderInstance" Type="System.Int32">
            9999999</DefaultValue>
         </DefaultValues>
   </TypeDescriptor>
</Parameter>
<Parameter Direction="Return" Name="Customers">
   <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" 
      Name="CustomerDataReader">
      <TypeDescriptors>
         <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, 
            Version=2.0.3600.0, Culture=neutral, PublicKeyToken=
            b77a5c561934e089" Name="CustomerDataRecord">
            <TypeDescriptors>
               <TypeDescriptor TypeName="System.Int32" IdentifierName=
                  "CustomerID" Name="customerID" />
               <TypeDescriptor TypeName="System.String" Name="Name" />
               <TypeDescriptor TypeName="System.String" Name="Phone" />
               <TypeDescriptor TypeName="System.String" Name="EmailAddress" >
                  <LocalizedDisplayNames>
                     <LocalizedDisplayName LCID="1033">Email Address
                     </LocalizedDisplayName>
                  </LocalizedDisplayNames>
               </TypeDescriptor>
            </TypeDescriptors>
         </TypeDescriptor>
      </TypeDescriptors>
   </TypeDescriptor>
</Parameter>

  5. Define the method instances. Add the following XML within the MethodInstances element. SharePoint Server 2007 uses the Finder method instance to get all known instances of the entities. Specifically, SharePoint Server 2007 uses the finder method instance to populate the Business Data List Web Part. SharePoint Server 2007 uses the SpecificFinder method instance type to query for a specific instance of a method. Search, Business Data Actions, Filters and almost every other piece of Business Data Catalog functionality use this method.

    Note that a method usually only supports zero or one method instance. In this example, we have a single method that is capable of returning all customers or a single customer. When you design your entities, you may find it easier to have two methods, a GetCustomers method and a GetSingleCustomer method. We used this abbreviated method to condense the XML.

  6. Save the file.

6. Upload the Completed Metadata File into the Shared Service Provider

The next step is to upload the completed metadata file into the shared service provider.

To upload the completed metadata file to the shared service provider

  1. To start the SharePoint 3.0 Central Administration Web page, click the Start button, and then point to All Programs. Point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.

  2. Click your shared service provider (usually named SharedServices1) in the left navigation bar.

  3. In the Business Data Catalog section, click Import application definition.

  4. Click Browse, locate the saved metadata file, and double-click it.

  5. Leave all other application definition settings with their default values, and then click Import.

7. Use the Business Data List Web Part to Show the Customer Data

The next step is to use the Business Data List Web Part to show the customer data.

To use the Business Data List Web Part to show customer data

  1. Within any site in your installation, add a Business Data List Web Part to a page.

  2. Within the Web Part, open the tool pane.

  3. In the Business Data List task pane, type Customer in the Type box and press Enter. Office SharePoint Server 2007 resolves this entry to the Customer (CRMDB) entity.

  4. Click OK to see the customer entries exposed in the Web Part.

Read It

The Business Data Catalog is a Microsoft Office SharePoint Server 2007 feature that allows you to include LOB data that is hosted outside of the portal in such items as lists and enterprise search. The Business Data Catalog requires an XML metadata file that defines the connection parameters to the external data source, the various queries (or Web service method calls), and both the input and output parameters of each query or method. To build the metadata file, it is important to understand its structure. However, understanding entities is the most important part of understanding the Business Data Catalog. Entities consist of:

  • Properties, which enable the Business Data Catalog developer to define certain metadata at the entity level, such as the default display element (Title).

  • Identifiers, which are the collection of LOB fields that uniquely identify the entity.

  • Methods, which contain the T-SQL or Web service methods that retrieve the entity data. Entities can contain multiple methods and can optionally have parameters and filters defined that Business Data Catalog users can use to filter results.

 See It Splash screen MOSS2007CreatingBusinessDataCatalog

Watch the Video

Length: 16:33 | Size: 11.0 MB | Type: WMV file

Explore It