Configuring SharePoint Server 2010 Search for External Content Types (Part 1 of 2)

Summary:  Microsoft Business Connectivity Services (BCS) enable users to easily include external data as results for the Microsoft SharePoint Server 2010 search capabilities. Learn to configure external content types for SharePoint Server search indexing and options for configuring SharePoint Server search results.

Applies to: Business Connectivity Services | Office 2010 | Open XML | SharePoint Designer 2010 | SharePoint Foundation 2010 | SharePoint Online | SharePoint Server 2010 | Visual Studio

Provided by:  Bob McClellan

Contents

• Overview of Configuring SharePoint Server 2010 Search for External Content Types

• Example: Searching External Data Using SharePoint Designer 2010

• Customizing Search Results

• Using Associations for Master/Detail Relationships

• Additional Resources

• About the Author

This article is the first in a two-part series of articles on configuring SharePoint Server search for external content types.

• Configuring SharePoint Server 2010 Search for External Content Types (Part 1 of 2) (this article)

• Configuring SharePoint Server 2010 Search for External Content Types (Part 2 of 2)

Overview of Configuring SharePoint Server 2010 Search for External Content Types

Microsoft Business Connectivity Services (BCS) enable you to connect SharePoint solutions to external data sources and to define external content types that are based on that external data. SharePoint 2010 search services can index the external data by using external content types to show meaningful search results. Business Connectivity Services, formerly known as the Business Data Catalog, provide additional improvements that include Microsoft SharePoint Designer 2010 support.

This article describes how to use SharePoint Designer 2010 to define external content types and how to set up a SharePoint Server search crawl that includes that external data. This approach reduces crawl times for large amounts of data by using incremental crawls. We describe this by using SharePoint Designer, SharePoint Central Administration, and XML code examples.

Before beginning, you should have the following:

• Access to SharePoint 2010 Central Administration. This includes the search service and the Business Data Connectivity (BDC) service.

• Access to SharePoint Designer.

• SharePoint 2010 web application that has a site collection. For more information, see Create a site collection (SharePoint Server 2010. Note that you can use Sharepoint - 80.

• Access to Microsoft SQL Server. Note that you can use the same instance of SQL Server that the computer that is running SharePoint Server 2010 uses.

• Sample database for SQL Server (For example, this example uses AdventureWorks. To install this sample database, see AdventureWorks 2008 R2.)

These examples are more meaningful if you understand SQL queries and views. Previous experience with XML is also helpful.

Example: Searching External Data Using SharePoint Designer 2010

In this section, I describe how to set up connections to external data by using SharePoint Designer to define an external content type. Then, I describe how to configure the search service to index that data and show results to the user. The general steps are as follows:

• Verify that the web application has connections to the correct services.

• Create a profile page site and a SharePoint Server search site.

• Configure the business data connectivity service.

• Create the external content type.

• Configure the SharePoint search service and crawl the external data to add it to the index.

• Test the SharePoint Server search.

As you work through the specific steps that follow, you will be using four main areas:

• SharePoint 2010 Central Administration BDC

• SharePoint 2010 Central Administration search

• SharePoint Designer

• SharePoint Server search site that you create

After you start to work in one of those areas, keep it open in the background. For example, after you open the SharePoint 2010 Central Administration: Business Data Connectivity page, open a new window to configure the search service.

The first step is to verify that the web application has a connection to the necessary services. If any necessary services are not connected, you must connect them.

To verify the web application connections

1. Start SharePoint 2010 Central Administration.

2. Under the Application Management heading, click Manage web applications.

3. Select the web applications that you want to use (for example, Sharepoint - 80).

4. On the ribbon, click Service Connections.

   Figure 1. Web Applications ribbon

   

5. Verify that Business Data Connectivity (BDC) service and a search service such as Search Service Application are selected. Note the search service that the web application uses so that you configure the correct one. (For example, you may be using FAST Query SSA.) If one or both are not selected, select them, and then click OK. If you cannot select them, use the list at the top of the page to change from default to custom first as shown in the Figure 2.

   Figure 2. Dialog box for web applications connections

   

Next, you must configure the BDC service. You must set the permissions and a host site for profile pages. Profile pages show detailed information about external data records. The search service links its results to the appropriate profile page. If you do not set up a profile page, the SharePoint Server search results contain a broken link. Each profile page shows the data for the associated external content type. Therefore, you must have a different page for each type. You could host these pages on any site. However, it is usually better to create a separate site, or even a separate site collection for those pages so that they are not stored with regular site content. This article describes how to create the site inside an existing site collection.

To create the profile pages site

1. In Internet Explorer, open the site collection you want to use.

2. In the Site Actions list, click New Site.

3. Select the Blank Site template.

4. In the right pane, click More Options.

5. In the Title box, type Search Results Details.

6. In the URL name box, type ProfilePages.

7. In the Navigation section, for Display this site on the top link bar of the parent site?, click No.

8. Click Create.

The URL that you see is the one that you use for the next step. For example, if the URL for the profile page site is http://server1/ProfilePages/default.aspx, you specify the location of the profile pages as http://server1/ProfilePages as the host URL.

To configure the BDC service

1. Start SharePoint 2010 Central Administration.

2. Under Application Management, click Manage service applications.

3. In the Name column, click Business Data Connectivity Service, as shown in Figure 3.

   Figure 3. Manage Service Applications list

   

4. Click Edit.

5. On the ribbon, in the Profile Pages group, click Configure.

6. In the Host SharePoint site URL box, type the full URL for the site you just created for profile pages, as shown in Figure 4.

   Figure 4. Configuring the profile page host

   

7. Click OK.

8. On the ribbon, in the Permissions group, click Set Metadata Store Permissions.

9. If the account you want to use is not shown in the dialog box, type the account name in the text box next to Add.

10. Click Add.

11. In the bottom list, select the Edit box, the Execute box, the Selectable In Clients box and the Set Permissions box.

12. Select the Propagate permissions to all BDC Models, External Systems and External Content Types in the BDC Metadata Store box.

13. Click OK.

Next, you must create a simple search site for testing.

To create a search site

1. In Internet Explorer, open the site collection you want.

   Ensure that you navigate back to the original site collection and that you are not still viewing the site you created for the profile pages.

2. On the Site Actions list, click New Site.

3. Select the Basic Search Center template.

4. In the right pane, for the Title, type BCS Search. For the URL name, type BCSSearch.

5. Click Create.

Next, you must create an external content type for the product and description view in the AdventureWorks database. You can create the external content type in the root of the site collection or any of the sites in that collection. This includes the one that you previously created. You will connect the external content type to SQL Server as an external data source. Next, you define two basic operations to make the external content type usable by the search service. The Read List operation gets the full list of records from the external content type. The Read Item operation gets a specific record from the external content type. Both operations require you to specify the field that uniquely identifies each record.

To create the external content type

1. On the Site Actions list for the business connectivity services search site that you created, click Edit in SharePoint Designer.

   You should now have three of the four windows open that I mentioned previously. Only the search service has not been opened.

2. In the Navigation pane, click External Content Types.

3. In the New group of the ribbon, click External Content Type, as shown in Figure 5.

   Figure 5. Creating an external content type

   

4. In the External Content Type Information group, next to Name, click New external content type. Type Products and then tab, as shown in Figure 6.

   Figure 6. Summary view of an external content type

   

5. Next to External System, click Click here to discover external data sources and, and then click Add Connection.

6. In the External Data Source Type Selection dialog box, in the list, click SQL Server.

7. Click OK.

8. In the SQL Server Connection dialog box, in the Database Server box, type localhost, as shown in Figure 7.

   Figure 7. SQL Server Connection dialog box

   

9. In the Database Name box, type AdventureWorks.

10. In the Name (optional) box, type AWProducts.

11. Click OK. This connection uses the search service account to connect to the database. In a production configuration, you may want to use one of the impersonated identities so that database access is controlled independently from that account.

12. Expand AWProducts, and then expand Views.

13. Right-click vProductAndDescription to open a pop-up menu, and then click New Read Item Operation, as shown in Figure 8.

    Figure 8. Data source explorer for an external content type

    

14. At the bottom of the Read Item dialog box, click Next.

15. On the Input Parameters page, in the Data Source Elements pane, click ProductID.

    Figure 9. Read Item dialog box

    

16. In the Properties pane, select the Map to Identifier box, and then click Next.

17. On the Return Parameter page, in the Data Source Elements pane click ProductID.

18. In the Properties pane, select the Map to Identifier box.

19. Click Finish.

20. To open a pop-up menu, right-click ProductAndDescription, and then click New Read List Operation.

21. At the bottom of the Read List dialog box, click Next.

22. Click Next.

23. On the Return Parameter page, in the Data Source Elements pane, click ProductID.

24. In the Properties pane, select the Map to Identifier box.

25. Click Finish.

26. In the ribbon, click Summary View.

27. In the Fields group, in the list, click Name.

28. In the ribbon, click Set as Title, as shown in Figure 10.

    Figure 10. Completed external content type

    

29. Right-click the Products tab and on the pop-up menu, click Save.

30. On the ribbon, click Create Profile Page.

Toward the end of creating the external content type, you set the title. That is the value that appears as the link in the search results. If you do not specify a title, the search results show cryptic text as the title, which is likely to be confusing to the end user.

Now that you need to configure the search service to use this external content type while crawling and indexing. The search service uses content sources to determine and control the content it has to search. You must also set the account to use for the crawl operation. This account should be the same as the one that you used to set permissions in the BDC service and it must have read access to the AdventureWorks database.

In a production configuration, you should create and use a unique account to control permissions for the search service.

To configure the SharePoint Search Service

1. Start SharePoint 2010 Central Administration.

   If you open this as a new window, then you now have a separate window for the fourth main area.

2. Under Manage Applications, click Manage service applications.

3. In the Name column, click Search Service Application (or the search service that is connected to your site collection).

4. To change the account to use for crawling, click the link to the right of Default content access account. Set it to the same account that you used to set permissions in the BDC service.

5. In the navigation pane, under Crawling, click Content Sources.

6. Click New Content Source, as shown in Figure 11.

   Figure 11. Content source configuration in the search service

   

7. In the Name box, type AWProductSearch.

8. Click Line of Business Data, and then click Crawl selected external data source.

9. Select the box next to AWProducts, as shown in Figure 12.

   Figure 12. New content source dialog box

   

10. Select the box next to Start full crawl of this content source.

11. Click OK.

The search service is now crawling through the new data to create search indexes for that content. It should finish in several minutes. However, if this view had millions of records, the crawl takes significantly longer. You can click the Refresh link to update the status. When the status is Idle, the search crawl is finished.

To test the search

1. Open the BCS Search site that you created previously.

2. Type Jersey in the search box, and then press Enter.

3. Click any of the results titles to see the profile page for that item.

Now you have a basic search working with external data. Next, you will see how to control the appearance of the profile page and the results of the search.

Customizing Search Results

There are two simple modifications that you can make to SharePoint Server search by using SharePoint Designer 2010. First, you can configure the fields that appear on the profile page and their descriptions by modifying the Read Item operation. For example, the descriptions ProductID and ProductModel are not especially user-friendly. Also, the CultureID field is not especially useful.

If you kept SharePoint Designer open, you can skip the first two steps in the following procedure.

To configure the fields shown on the profile page

1. In SharePoint Designer, in the navigation pane, click External Content Types.

2. To configure that external content type, in the list, click Products.

3. In the External Content Type Operations group, double-click Read Item.

4. Click Next two times.

5. On the Return Parameter page, clear the CultureID box.

6. Click ProductID.

7. In the Properties pane, type ID in the Display Name box.

8. Click ProductModel.

9. In the Properties pane, in the Display Name box, type Model.

10. Click Finish.

11. Click the disk icon to save the external content type.

12. On the ribbon, click Create Profile Page.

13. Click Yes in the warning dialog box.

You can test the results by going to the SharePoint Server search site and looking at one of the profile pages. This change was only to the profile page. Therefore, you do not have to perform another crawl. Obviously, the changes that you can make through the external content type are limited. If you want more advanced customizations, you can configure the page directly and configure it as you want. (For example, you cannot remove the identifier field from the operation. However, you could remove it from the profile page manually.) Be aware that if you ever create that profile page from the external content type later, it overwrites those direct changes, as shown in Figure 14.

Figure 14. Modified profile page

The second change that you can make is to adjust the Read List operation to exclude some fields from the SharePoint Server search indexing. In this step, you remove the description from the indexing. Before you start that step, try a SharePoint Server search for the word microfiber. You should get some matches on that word in the descriptions. Now remove the Description field from the Read List operation, try the SharePoint Server search again, and see that it is no longer indexed.

To configure the field values indexed for search

1. In SharePoint Designer, in the External Content Type Operations group, double-click the Read List operation.

2. Click Next two times.

3. Click Next two times.

4. In the Return Parameter page, clear the Description box.

5. Click Finish, and then click Save.

The external content type has changed, but now you must update the SharePoint Server search index running a full crawl. Whenever you configure an external content type, its internal ID changes. You must re-create the content source. Otherwise, it gets errors trying to find the old ID.

To update the search index for a modified external content type

1. In SharePoint Central Administration, in the search service configuration, in the navigation pane under Crawling, click Content Sources.

2. Click the AWProductSearch content source, click Delete, and then click Yes.

3. Click New Content Source.

4. In the Name box, type AWProductSearch.

5. Click Line of Business Data.

6. Click Crawl selected external data source.

7. Select the AWProducts box.

8. Select the Start full crawl of this content source box.

9. Click OK.

When the crawl is complete, go back to the SharePoint Server search site and try searching for microfiber again. There should be no matches. You can still search for Jersey, and then click one of the Long-Sleeve Logo Jersey items to see that you can view the description with the word microfiber in the profile page. However, it is now excluded from the search results.

Using Associations for Master/Detail Relationships

Business Connectivity Services also enable you to define a master/detail (parent-child) relationship between two external content types. The resulting profile page for the master external content type is automatically generated with a list of the detail records for that particular master record. Our interest here is how the search service handles this kind of association.

To try this out, you create an association from the detail external content type (Product) to the master external content type (ProductModel). Then, you create a content source in SharePoint Server search to see how the results are generated for those. Next, you see how to modify the configuration to give a much simpler and more useful set of results by treating the detail records as attachments to the master record.

To create associations in external content types

1. In SharePoint Designer, in the Navigation pane, click External Content Types.

2. On the New section of the ribbon, click External Content Type.

3. In the External Content Type Information group, next to Name, click New external content type. Type ProductModel in the box. Note that this change replicates to the Display Name text.

4. In the External Content Type Information group, next to Display Name, click ProductModel. Type Model in the box. This label appears on the profile page.

5. Next to External System, click Click here to discover external data sources and.

6. Click Add Connection.

7. In the External Data Source Type Selection dialog box, from the list, choose SQL Server, and then click OK.

8. In the SQL Server Connection dialog box, for the Database Server, type localhost.

9. In the for the Database Name box, type AdventureWorks.

10. In the Name (optional) box, type AWModel, and then click OK.

11. Expand AWModel.

12. Expand Tables.

13. To open a pop-up menu, right-click ProductModel. Note that you probably must scroll to find this table. Click New Read Item Operation, and then click Finish.

14. To open a pop-up menu, right-click ProductModel and then click New Read List Operation, and then click Finish.

15. On the ribbon, click Summary View.

16. In the Fields group, in the list, click Name.

17. On the ribbon, click Set as Title.

18. Save the external content type.

19. In SharePoint Designer, in the Navigation pane, click External Content Types.

20. On the New tab of the ribbon, click External Content Type.

21. In the External Content Type Information group, next to Name, click New external content type. In the box. type ProductDetail. This change replicates to the Display Name field.)

22. Next to External System, click Click here to discover external data sources and.

23. To open a pop-up menu, in Tables within AWModel, right-click Product, and then click New Read Item Operation. Click Finish.

24. To open a pop-up menu, right-click Product, and then click New Read List Operation. Click Finish.

25. To open a pop-up menu, right-click Product, and then click New Association.

26. In the Association Display Name box, type Products. This changes the label for the detail records in the profile page for the master record, as shown in Figure 15.

    Figure 15. Choosing the external content type for an association

    

27. Click Browse.

28. Click ProductModel to select it.

29. Click OK, and then click Next.

30. On the Input Parameters page, in the Data Source Elements pane, click ProductModelID.

31. In the Properties pane, select the Map to Identifier box.

32. Click Next twice.

33. On the Return Parameter page, in the Data Source Elements pane, clear all the fields except for ProductID, Name, ProductNumber, ListPrice, and ProductModelID.

34. If you used the top check box to clear all the fields, you must set the identifier again. In the Data Source Elements pane, click ProductID, and then in the Properties pane, select the Map to Identifier box.

35. Click ProductID and change the text for the Display Name to ID.

36. Click ProductNumber and change the text for the Display Name to Product Number.

37. Click ListPrice and change the text for the Display Name to List Price, as shown in Figure 16.

    Figure 16. Return Parameter page for association

    

38. Click Finish.

39. On the ribbon, click Summary View.

40. In the Fields group, in the list, click Name.

41. On the ribbon, click Set as Title.

42. Save the external content type.

43. On the ribbon, click Create Profile Page.

44. Click the Model tab to see the first external content type.

45. On the ribbon, click Create Profile Page.

In this procedure, you created another named connection for the same AdventureWorks database. The reason is that content sources in the search crawl all external content types that are defined with the same data source. If you want to control when those different external content types are crawled and how they are crawled, you must create different data sources so that you can also create different content sources for SharePoint Server search.

You also did not have to set the identifiers for these external content types. SharePoint Designer automatically detects the primary key field for tables, but not for views.

The associated external content types are ready. Next, you must create a search content source to index the content types. It also helps to delete the old content source so that you can see the results of the new indexing only.

To crawl and index the associated external content types

1. In SharePoint Central Administration, in the search service configuration, in the navigation pane under Crawling, click Content Sources.

2. Click the AWProductSearch content source, click Delete, and then click OK.

3. Click New Content Source.

4. In the Name box, type AWModelSearch.

5. Click Line of Business Data.

6. Click Crawl selected external data source.

7. Select the AWModel box.

8. Select the Start full crawl of this content source box.

9. Click OK.

Click Refresh to update the status. When the crawl finishes, you can search for Jersey and results appear for both the Master (Model) and Detail (Product). The model results do not have sizes (for example, S, M, L) and the link for them includes the words ProductModel instead of ProductDetail. Click one of those to see the master/detail view that is created by the association. Although this search is functional, it is not ideal. For example, the search is not indexing information in the detail. You can see matches for the detail. However, they are not based on the master record. For example, if you search for Dissolver, you only see the detail in the results. To make these results better, first eliminate the detail records from the search, and then configure the external content type to attach the detail record information to the master record. The first change is one that you did previously, but now you remove all the fields that can be indexed, as shown in Figure 17.

Figure 17. Profile page for a master/detail association

If you left the tabs open for the external content types that you defined, skip the first two steps.

To configure the field values indexed for search

1. In SharePoint Designer, click External Content Types in the navigation pane.

2. In the list, click ProductDetail to configure that external content type.

3. In the External Content Type Operations group, double-click the Read List operation.

4. Click Next two times.

5. In the Return Parameter page, clear the boxes for all of the fields in the Data Source Elements pane, except for ProductID and ProductModelID. (If you use the check box to clear all fields, you must select the Map to Identifier box for the ProductID.)

6. Click Finish.

7. Save the external content type.

Follow the usual steps to run a full crawl after you change the model. Remember to delete the existing content source and then create a new one. Now when you search for Dissolver, there should be no results. You now configure the model so that the associated data is attached to the master during search indexing. This change is not supported by SharePoint Designer, so you must configure the model in its XML form. To do so, you must export the model to an XML file, configure it, and then import the changed file.

To export the BDC model to an XML file

1. In SharePoint Designer, click External Content Types in the navigation pane.

2. In the list, click AWModel next to ProductDetail to select that external content type.

3. On the ribbon, click Export Business Data Catalog Model.

4. In the Export Business Data Catalog Model dialog box, type ProductDetail in BDC Model Name, and then click OK.

5. Change the Save as type to All Files.

6. Choose a destination folder for the file.

7. Change the File Name box to ProductDetail.xml, and then click Save.

You can use any XML editor to configure this file. I use SharePoint Designer by finding the file in a file browser and then selecting Edit with Microsoft SharePoint Designer from the right-click pop-up menu. You could also drag the file into SharePoint Designer or even modify it with a text editor such as Notepad.

Modify the XML file to add the highlighted line shown in the following example. Be sure that you are in the MethodInstances element for the association instead of the Read List element or Read Item methods.

<MethodInstances>  
<Association Name="ProductNavigate Association"
  Type="AssociationNavigator" ReturnParameterName="ProductNavigate Association"
  DefaultDisplayName="Products">
  <Properties>
    <Property Name="AttachmentAccessor" Type="System.String"></Property>
    <Property Name="ForeignFieldMappings" ...>
  </Properties>

Save the change and then follow these steps to import the model with the changes.

To import an XML file to update a BDC model

1. In the BDC service configuration in SharePoint Central Administration, click Edit.

2. In the View section of the ribbon, select External Content Types from the drop-down list.

3. Click the drop-down menu for ProductDetail, click Delete, and then click OK.

4. In the Business Data Catalog Models section of the ribbon, click Import.

5. In the Business Data Catalog Model File box, type your XML file name or click the Browse button to browse for it.

6. Click Import. You may see one or two warnings. But there should be no errors.

7. Click OK.

Follow the usual steps to re-create the AWModelSearch content source and run a full crawl. Now when you search for Dissolver, you should get the results that you want. You can also try searching for Jersey and see that it only returns the model records in the results.

This article continues in Part 2 of the series of articles on configuring SharePoint Server search for external content types.

Configuring SharePoint Server 2010 Search for External Content Types (Part 2 of 2)

Additional Resources

For more information, see the following resources:

• Configuring SharePoint Server 2010 Search for External Content Types (Part 2 of 2)

• Enterprise Search administration

• Business Data Connectivity services Administration

• Business Connectivity Services Schema Reference

• Stereotyped Operations Supported by BDC

• The Notion of Associations and the External Item Picker

• Tooling Associations in SharePoint Designer 2010

• Configure the Secure Store Service (SharePoint Server 2010)

• SharePoint Developer Center

• BCS Resource Center | SharePoint 2010

About the Author

Bob McClellan has been developing applications, mostly for database systems, for almost thirty years. He recently worked on the Power Tools for Open XML, which are a series of Windows PowerShell commands for manipulating Open XML documents. Bob has extensive programming experience in a variety of computer languages, database systems and development environments.