How to Create a Database Connection by using the Business Data Catalog Definition Editor

MSDN Resource:

http://msdn2.microsoft.com/en-us/library/bb736296.aspx

How to Create a Database Connection by using the Business Data Catalog Definition Editor

Summary:

Learn how to create a database connection for the Business Data Catalog in Microsoft Office SharePoint Server 2007 with the Microsoft Business Data Catalog Definition Editor tool for Microsoft Office SharePoint Server 2007. (13 printed pages)
Jo-Anne West, Microsoft Corporation
August 2007
Applies to: Microsoft Office SharePoint Server 2007

Contents:
Introduction to the Business Data Catalog Definition Editor
Adding an LOB System
Testing the AdventureWorksDW LOB System Connection
Testing the Entity Associations
Exporting the LOB System Instance Metadata
Importing the Application Definition File into the Shared Services Provider
Modifying an Existing Application Definition by using the Business Data Catalog Definition Editor
Next Steps
Conclusion
Additional Resources

Introduction to the Business Data Catalog Definition Editor
The Business Data Catalog in Microsoft Office SharePoint Server 2007 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 site, and make it available to the Enterprise Search crawler, you must build an application definition file, which is an XML file that identifies where the data is stored (either in a database, or as a Web service) and what format the data is stored in (for example, what the data types and primary keys are).

For details about how you can use the Business Data Catalog Definition editor to create a connection to a Web service, see How to Create a Web Service Connection by using the Business Data Catalog Definition Editor. For more information about the Business Data Catalog, see Business Data Catalog. For more information about Enterprise Search and the Business Data Catalog, see Enabling Business Data Search, and Searching Business Data.

The Microsoft Business Data Catalog Definition Editor helps you to author application definition files for the Business Data Catalog. This tool automatically generates the XML for the definition file, so you do not need to manually create the file in an XML editor.

This how-to topic shows you how to create an application definition file for the Business Data Catalog and how to create a database connection by using the new Business Data Catalog Definition Editor. This example uses the AdventureWorksDW sample database for Microsoft SQL Server 2005. The metadata in the generated file is similar to the metadata for the AdventureWorksDW in the Welcome to the Microsoft Office SharePoint Server 2007 (Beta) SDK.

System Requirements
Before you begin, you must install the following:
Microsoft Office SharePoint Server 2007, Enterprise Edition.
Microsoft SQL Server 2005 with the AdventureWorksDW database installed.
Microsoft Business Data Catalog Definition Editor. This tool is part of the Microsoft Office SharePoint Server 2007 SDK 1.2 download (released August 2007).

Adding an LOB System
Begin by adding a LOB system for the AdventureWorksDW sample database for Microsoft SQL Server 2005.

To add an LOB system
On the Start menu, click Microsoft Business Data Application Definition Editor.
In the tool, click Add LOB System.
In the Add LOB System window, click Connect to Database.
Select SqlServer for Connection Type.

Figure 1. Add LOB System database connection.

For Connection String, enter the following text.
Copy Code

Server=\;Database=AdventureWorksDW;Integrated Security=SSPI;
Replace \ with the name of your database server.

Click Add Table.

Figure 2. Connect to database.

Drag the following tables to the Design Surface:
Product
ProductCategory
ProductSubcategory
Reseller

In the Foreign Keys section of the DimProduct window, select FK_DimProduct_DimProductSubcategory.
Figure 3. Add tables.

Click OK.

In the LOB System Name dialog box that opens, click OK to create the AdventureWorksDW connection.

Testing the AdventureWorksDW LOB System Connection

Next, test the connection to the AdventureWorksDW LOB system.
To test the connection to the LOB system
In the Metadata Objects pane, expand the AdventureWorksDW node, and then expand the Entities node.

Expand the DimProduct node.
Expand the Methods node
Expand the FindAll_DimProducts node, and then expand the Instances node.
Right-click FindAll_DimProduct_Instance, and then click Execute.
Figure 4. Execute FindAll_DimProduct_Instance method.

In the Execute FindAll_DimProduct_Instance window, click Next, and verify that the values from the ProductKey field of the DimProducts table are displayed in the Results window. Click Next to page through the results.

Figure 5. Execute FindAll_DimProduct_Instance window.

Make note of one of the ProductKey values for the next step. For this walkthrough, we use the ProductKey value 212.

In the Methods node, expand the Find_DimProduct node, and then expand the Instances node.
Right-click Find_DimProduct_Instance, and then click Execute.
Figure 6. Execute Find_DimProduct_Instance method.

In the Value field, enter the 212, and then click Execute.
If the DimProduct table contains any fields that will not display correctly, you might receive a message similar to the following:
Figure 7. Execute Find_DimProduct_Instance message.

Click OK to close the message and continue.

If the Find method is working correctly, the record with the ProductKey field matching 212 appears in the Results window.

Figure 8. Find_DimProduct_Instance method results

Testing the Entity Associations
Now you will test the entity associations for the LOB system.
To test the entity associations
In the DimProducts node, expand the Methods node, and then expand the FK_DimProduct_DimProductSubcategory node.
Expand the Instances node.
Right-click FK_DimProduct_DimProductSubcategory_Instance, and then click Execute to open the Execute FK_DimProduct_DimProductSubcategory_Instance window.
Figure 9. Execute DimProductSubcategory Instance method.

Click the Search button or click anywhere in the Value field to open the Select Entity Instance window.
Figure 10. Execute DimProductSubcategory Instance window.

Select FindAll_DimProductSubcategory_Instance, and then click Next to display the first page of results.
Figure 11. Select FindAll_DimProductSubcategory_Instance.

Select the row for the record from the DimProductSubcategory table you want to retrieve the associations for, and then click OK. If the record is not displayed in the first set of results, click Next to page through the results. For this example, select the record with the ProductSubcategoryKey 31.
Figure 12. Execute DimProductSubcategory_Instance window.

In the Execute FK_DimProduct_DimProductSubcategory_Instance window, click Execute to display all the records from the DimProduct table where the ProductSubcategoryKey field matches 31.
If results are returned, the associations are configured correctly.
Exporting the LOB System Instance Metadata
After you confirm that the connection for the LOB system and with the entities, associations, and methods, are configured correctly within the Business Data Catalog Definition Editor, you are ready to export the LOB system instance metadata to an application definition file.

To export the metadata
Select the AdventureWorksDW node in the Metadata Objects window, and then click Export.
Figure 13. Export AdventureWorks LOB System.

Save the file as AdventureWorks2005.xml.

Importing the Application Definition File into the Shared Services Provider
Next, you import the application definition file into the Shared Services Provider (SSP).
To import the application definition file into the SSP
To start the SharePoint 3.0 Central Administration Web page, click Start, and then point to All Programs. Point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.
In the left navigation pane, click the name of your SSP.
In the Business Data Catalog section, click Import application definition.
In the Import Application Definition page that opens, browse to AdventureWorks2005.xml, select the file, and then click Open.
Click Import.
Click Browse, locate the file, and double-click it.
Leave all other application definition settings with their default values, and then click Import.
Modifying an Existing Application Definition by using the Business Data Catalog Definition Editor
You can also modify the metadata in an existing application definition file by importing it into the Business Data Catalog Definition Editor. If you are modifying the application definition for an existing application in the Business Data Catalog, you must export the metadata file for the application definition from the SSP first.

Note:
If you are modifying the application definition for an existing application in the Business Data Catalog, you must first either delete the original application in the Business Data Catalog or update the application version number before you import the application definition file.

To export the application definition file from the SSP
To start the SharePoint 3.0 Central Administration Web page, click Start, and then point to All Programs. Point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration.

In the left navigation pane, click the name of your SSP.
In the Business Data Catalog section, click View applications.
In the Applications list, click the application name.
Click Export Application Definition to open the Export Application Definition page.
Click Export, and then click Save.

To import an application definition file into the Business Data Catalog Definition Editor and modify the metadata
On the Start menu, click Microsoft Business Data Catalog Application Definition Editor.
Click Import.
Browse to the location of the application definition file, select it, and then click Open.
After the LOB system is loaded into the Business Data Catalog Definition Editor, you can make several changes to the LOB system instance, such as:
Connection string
Authentication mode
Add or remove entities

Next Steps
After completing these tasks, you can configure Enterprise Search to crawl this content so that your users can search business data. For information about how to configure Enterprise Search, see Walkthrough: Configuring Search for the AdventureWorks Business Data Application Sample. For details about how you can customize the Search user interface this scenario, see Walkthrough: Add a Tab and Custom Search Page with Enterprise Search Web Parts to the Search Center.

Conclusion
The Business Data Catalog in Microsoft Office SharePoint Server 2007 enables you to integrate LOB data that is hosted outside of the portal site into elements such as lists and Enterprise Search within Office SharePoint Server 2007. The Business Data Catalog requires a definition file with metadata that defines the connection to the external data source and identifies the data type that is being retrieved from the data source.

The Business Data Catalog Definition Editor automatically generates the XML metadata for the LOB system, making it easy to create an application definition file for the Business Data Catalog.
Additional Resources

For more information about the Business Data Catalog and Office SharePoint Server 2007, see the following resources:
Walkthrough: Add a Tab and Custom Search Page with Enterprise Search Web Parts to the Search Center
Welcome to the Microsoft Office SharePoint Server 2007 (Beta) SDK
Business Data Catalog Information Center
SQL Server 2005 Samples and Sample Databases