• Running Setup to Install AdventureWorks Sample Databases and Samples
• Creating a Database Connection by Using the Business Data Catalog Editor
• Display a summary of business data in a list
Running Setup to Install AdventureWorks Sample Databases and Samples
The AdventureWorks (OLTP), AdventureWorksDW (data warehouse), and Adventure Works DW (analysis services) sample databases, as well as the companion samples, are not installed by default in SQL Server 2005. You can download these from http://sqlserversamples.codeplex.com at Codeplex Center, or you can use the following procedures to install the sample databases and samples during or after setup. Additional instructions for deploying the Adventure Works DW analysis services project are also provided.
Installation during Setup
To install the sample databases and companion samples during setup, use the following instructions.
1. On the Components to Install page, select Workstation components, Books Online and development tools.
2. Click Advanced and then expand Documentation, Samples, and Sample Databases.
3. Select Sample Code and Applications.
4. Expand Sample Databases and then select the sample databases to install.
5. To complete the installation of the samples, after setup, perform one of the following steps:
1. From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.
2. Alternatively, using Windows Explorer, navigate to
Installation after Setup
If you did not install the sample databases or samples during the initial setup of SQL Server 2005, you can install them later.
1. From Add or Remove Programs, select Microsoft SQL Server 2005 and click Change. Follow the steps in the Microsoft SQL Server 2005 Maintenance wizard.
2. From Component Selection, select Workstation Components and then click Next.
3. From Welcome to the SQL Server Installation Wizard, click Next.
4. From System Configuration Check, click Next.
5. From Change or Remove Instance, click Change Installed Components.
6. From Feature Selection, expand the Documentation, Samples, and Sample Databases node.
7. Select Sample Code and Applications.
8. Expand Sample Databases and then select the sample databases to be installed. Click Next.
9. To install and attach the sample databases, from Sample Databases Setup, select Install and attach sample databases, and then click Next.
The database files are created and stored in the folder
10. To install the sample database files without attaching, from Sample Databases Setup, select Install sample databases without attaching, and then click Next.
The AdventureWorks database files are created in the folder
11. Select the instance of SQL Server on which to install the sample databases and samples.
12. Complete the steps in the wizard.
13. To complete the installation of the samples, after setup, perform one of the following steps:
1. From the Start menu, click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.
2. Alternatively, using Windows Explorer, navigate to
Deploying the Adventure Works DW Analysis Services project
1. Make sure that the AdventureWorksDW and Adventure Works DW databases have been installed.
2. Make sure Analysis Services has been installed.
3. From the SQL Server Business Intelligence Development Studio toolbar, click File, point to Open, and then click Project/Solution.
4. Browse to
5. From Solution Explorer, right-click Adventure Works DW and select Deploy or Process.
Creating a Database Connection by Using 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).
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.
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.
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
1. On the Start menu, click Microsoft Business Data Application Definition Editor.
2. In the tool, click Add LOB System.
3. In the Add LOB System window, click Connect to Database.
4. Select SqlServer for Connection Type.
5. 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.
6. Click Add Table.
7. Drag the following tables to the Design Surface:
o Product
o ProductCategory
o ProductSubcategory
o Reseller
8. In the Foreign Keys section of the DimProduct window, select FK_DimProduct_DimProductSubcategory.
9. Click OK.
10. 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
1. In the Metadata Objects pane, expand the AdventureWorksDW node, and then expand the Entities node.
2. Expand the DimProduct node.
3. Expand the Methods node
4. Expand the FindAll_DimProducts node, and then expand the Instances node.
5. Right-click FindAll_DimProduct_Instance, and then click Execute.
6. 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.
7. Make note of one of the ProductKey values for the next step. For this walkthrough, we use the ProductKey value 212.
8. In the Methods node, expand the Find_DimProduct node, and then expand the Instances node.
9. Right-click Find_DimProduct_Instance, and then click Execute.
10. In the Value field, enter the 212, and then click Execute.
11. If the DimProduct table contains any fields that will not display correctly, you might receive a message, Click OK to close the message and continue.
12. If the Find method is working correctly, the record with the ProductKey field matching 212 appears in the Results window.
Testing the Entity Associations
Now you will test the entity associations for the LOB system.
To test the entity associations
1. In the DimProducts node, expand the Methods node, and then expand the FK_DimProduct_DimProductSubcategory node.
2. Expand the Instances node.
3. Right-click FK_DimProduct_DimProductSubcategory_Instance, and then click Execute to open the Execute FK_DimProduct_DimProductSubcategory_Instance window.
4. Click the Search button or click anywhere in the Value field to open the Select Entity Instance window.
5. Select FindAll_DimProductSubcategory_Instance, and then click Next to display the first page of results.
6. 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.
7. 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
1. Select the AdventureWorksDW node in the Metadata Objects window, and then click Export.
2. 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
1. 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.
2. In the left navigation pane, click the name of your SSP.
3. In the Business Data Catalog section, click Import application definition.
4. In the Import Application Definition page that opens, browse to AdventureWorks2005.xml, select the file, and then click Open.
5. Click Import.
6. Click Browse, locate the file, and double-click it.
7. 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.
To export the application definition file from the SSP
1. 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.
2. In the left navigation pane, click the name of your SSP.
3. In the Business Data Catalog section, click View applications.
4. In the Applications list, click the application name.
5. Click Export Application Definition to open the Export Application Definition page.
6. Click Export, and then click Save.
To import an application definition file into the Business Data Catalog Definition Editor and modify the metadata
1. On the Start menu, click Microsoft Business Data Catalog Application Definition Editor.
2. Click Import.
3. 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:
o Connection string
o Authentication mode
o Add or remove entities
Display a summary of business data in a list
You can display business data from an external database in a simple list form by using the Business Data List Web Part. For example, you can display a list of products and their list prices.
After the data appears, you can edit the view properties of a Business Data List Web Part, just as you can edit the view of any SharePoint list. You can also filter by property or limit the number of items shown in the Web Part.
1. Go to the page on the SharePoint site where you want to display a list of business data.
2. On the Site Actions menu, click Edit Page.
3. In the Web Part zone where you want to add the Web Part, click Add a Web Part.
4. In the Add Web Parts dialog box, select the Business Data List check box, and then click Add.
5. From the business data Web Part that you just created, click the Web Part menu, and then click Modify Shared Web Part to open the tool pane.
6. In the tool pane, next to the Type box, click Browse.
7. In the Business Data Type dialog box, select a business data type for the business application that you are working with, and then click OK.
For example, select Product. In this example, the Application is AdventureWorksDWInstance.
8. In the tool pane, expand the Appearance section.
9. In the Title box, replace Business Data List with your own title for the Web Part, and then click OK.
For example, type Product List in the Title box.
10. From the newly added Web Part, click Edit View, and then do the following:
• In the Items to Retrieve section, select Retrieve all items.
• In the Columns section, select the names of the columns that you want to display in the Web Part.
11. Click OK.
The new Business Data List Web Part now displays a list of products from your business application database.
Display details about a single item of business data
You can display the details about a business item from an external database by using the Business Data Item Web Part. For example, you can display the details about a particular product from the business application database.
1. Go to the page on the SharePoint site where you want to display the details about a particular business item.
2. On the Site Actions menu, click Edit Page.
3. In the Web Part zone where you want to add the Web Part, click Add a Web Part.
4. In the Add Web Parts dialog box, select the Business Data Item check box, and then click Add.
5. From the business data Web Part that you just created, click the Web Part menu, and then click Modify Shared Web Part to open the tool pane.
6. In the tool pane, next to the Type box, click Browse.
7. In the Business Data Type dialog box, select a business data type for the business application that you are working with, and then click OK.
For example, select Product for the Business Data Type. In this example, the Application is AdventureWorksDWInstance.
8. Next to the Item box, click Browse to choose the product that you want to display.
9. In the dialog box that appears, the first drop-down list displays the filters that are defined for this entity in the BDC. Click the Name filter. In the adjacent field, type a product category, such as Bike, that you want to find. Click Search to search for product names that contain the category that you entered.
10. Select one item from the list of products that appears, and then click OK. The ID of the item that you selected is now displayed in the Item box in the tool pane.
11. Under Fields, click the Choose button.
12. In the Select Fields dialog box, select the item information that you want to display, and then click OK.
13. In the tool pane, expand the Appearance section.
14. In the Title box, replace Business Data Item with your own title for the Web Part, and then click OK.
For example, type Featured Product in the Title box.
The new Business Data Item Web Part displays the product details from your business application database.
Connect Web Parts to configure business application actions
By using a Business Data Actions Web Part on your SharePoint site, you can add an action to a product profile that enables you to perform a search for that item on MSN without leaving Office SharePoint Server 2007. The Business Data Actions Web Part displays a list of actions, in the form of links or on a toolbar, that are associated with a business item in the BDC.
Step 1: Add and connect a Business Data Actions Web Part
In this procedure, you add an action to a Business Data Actions Web Part and connect the Web Part to the Product List Web Part that you created earlier in Display a summary of business data in a list.
Before you begin, your administrator must download the AdventureWorksDW SQL Server 2005 Sample database and register it with the BDC. Additionally, your administrator must define the Search on MSN business data action and add it to Office SharePoint Server 2007. Find more information about the AdventureWorksDW SQL Server 2005 Sample database in the See Also section.
You must first have the Full Control or Design permission level before you can modify the Web Parts on your SharePoint site. Find links to more information about permission levels and permissions in the See Also section.
1. Go to the page on the SharePoint site where you created the Product List Web Part.
2. On the Site Actions menu, click Edit Page.
3. In the Web Part zone where you want to add the Web Part, click Add a Web Part.
4. In the Add Web Parts dialog box, select the Business Data Actions check box, and then click Add.
5. From the business data Web Part that you just created, click the Web Part menu, and then click Modify Shared Web Part to open the tool pane.
6. In the tool pane, next to the Type box, click Browse.
7. In the Business Data Type dialog box, select a business data type for the business application that you are working with, and then click OK.
For example, select Product for the Business Data Type. In this example, the Application is AdventureWorksDWInstance.
8. Under Actions, click Choose.
9. In the Select Actions dialog box, select the Display check box for the Search on MSN action, and then click OK.
The View Profile check box should be selected by default. The View Profile action is automatically created for each entity (or business item) that is defined in the BDC. This action enables you to view details about the entity. Because actions are associated with entities, the actions appear wherever the entity is displayed on a SharePoint site.
10. In the tool pane, configure the Web Part as needed in the Appearance, Layout, and Advanced sections, and then click OK to close the tool pane.
11. For the Business Data Actions Web Part that you just created, click the Web Part menu , point to Connections, point to Get Item From, and then click Product List.
If a message appears under the Product Actions Web Part that notifies you of a conflict in connecting the Product Actions Web Part to the Product List Web Part, verify that the Web Parts are both using the AdventureWorksDWInstance sample database.
Step 2: Verify that the Web Parts are properly connected and configured
1. If no data appears in the Product List Web Part, click Retrieve Data.
2. Select an item from the list by clicking the option button next to the product key.
3. In the Product Actions section of the site, click Search on MSN. A new window appears displaying search results for the item selected.
Connect Web Parts to display related business items
You can use the Business Data Related List Web Part along with a Business Data List Web Part to display two lists of related business items from a business application. For example, you can connect the two Web Parts to display all the sales orders for a specific customer.
• Business Data List Web Part
• Business Data Related List Web Part
The following procedure uses fictitious customer data from the AdventureWorks SQL Server 2000 Sample database, which must first be downloaded and registered with the BDC. Find more information about the AdventureWorks SQL Server 2000 Sample database in the See Also section.
Step 1: Create a Web Part to display a summary list
The first step in displaying related data from an external database is to create a Business Data List Web Part to display a summary list of customers.
1. Go to the page on the SharePoint site where you want to display orders for a specific customer in the database.
2. On the Site Actions menu, click Edit Page.
3. In the Web Part zone where you want to add the Web Part, click Add a Web Part.
4. In the Add Web Parts dialog box, select the Business Data List check box, and then click Add.
5. From the business data Web Part that you just created, click the Web Part menu, and then click Modify Shared Web Part to open the tool pane.
6. In the tool pane, next to the Type box, click Browse.
7. In the Business Data Type dialog box, select a business data type for the business application that you are working with, and then click OK.
For example, select Customer for the Business Data Type. In this example, the Application is AdventureWorksDWInstance.
8. In the tool pane, expand the Appearance section.
9. In Title box, replace Business Data List with Customers from AdventureWorks, and then click OK.
Step 2: Create and connect a Web Part to display related items
After you create a Business Data List Web Part that can display customers from the AdventureWorks SQL Server 2000 Sample database, you can create a Business Data Related List Web Part that connects to the first list. The end result will allow you to select an item from the customer list and view a list of sales orders for that customer in the Business Data Related List Web Part.
1. On the same page where you created the Business Data List Web Part, click Add a Web Part to create a new Web Part on the page.
2. In the Add Web Parts dialog box, select the Business Data Related List check box, and then click Add.
3. From the business data Web Part that you just created, click the Web Part menu, and then click Modify Shared Web Part to open the tool pane.
4. In the tool pane, next to the Type box, click Browse.
5. In the Business Data Type dialog box, select a business data type for the business application that you are working with, and then click OK.
For example, select SalesOrder for the Business Data Type. In this example, the Application is AdventureWorksDWInstance.
6. From the Relationship drop-down list, select CustomerToSalesOrder.
7. In the tool pane, expand the Appearance section.
8. In Title box, replace Business Data Related List with Sales Orders for Customer, and then click OK.
9. For the Web Part that you just created, click the Web Part menu , point to Connections, point to Get Related Item From, and then click Customers from AdventureWorks.
Step 3: Verify that the Web Parts are properly connected and configured
1. On the same page that contains the List Web Part and the Related List Web Part that you just created, if no data appears in the Customers from AdventureWorks Related List Web Part, click Retrieve Data.
2. Select a customer from the list by clicking the option button next to the customer name. All sales orders related to the selected customer appear in the Sales Orders for Customer Web Part.
No comments:
Post a Comment