Creating a Database Connection by using the Business Data Catalog Definition Editor

Article
10/16/2013

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 Creating 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 SQL Server 2005 Sample in the Microsoft Office SharePoint Server 2007 Software Development Kit (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.
```
Server=<DATABASE_SERVER_NAME>\<INSTANCE_NAME>;Database=AdventureWorksDW;Integrated Security=SSPI;
```
Replace <DATABASE_SERVER_NAME>\<INSTANCE_NAME> 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:

Share via