Business Connectivity Services, External Content Types and Content By Search in SharePoint 2013 – Part 2

In this blog post I’ll show how to surface data from external systems in SharePoint 2013 using Managed Navigation and Content By Search web parts. For instructions on how to crawl external systems using BCS, External Content Types and SharePoint Search, refer to my previous blog post: Business Connectivity Services, External Content Types and Content By Search in SharePoint 2013 – Part 1.

Managed Properties

In order for us to be able to use different Product and ProductModel external content type fields, we need to create a number of managed properties. For this example, the following managed properties need to be created:

  1. Navigate to Central Administration > Manage service applications > Search Service Application
  2. Click the Search Schema link in the Queries and Results side navigation section
  3. Click New Managed Property to create a new managed property for each of the items below
  4. ProductModelSummary
            • Name: ProductModelSummary
            • Type: Text
            • Searchable: True
            • Retrievable: True
            • Safe: True
            • Crawled property mapping: vProductModelCatalogDescriptionRead
              ListElement.Summary
  5. ProductModel
    • Name: ProductModel
    • Type: Text
    • Searchable: True
    • Queryable: True
    • Retrievable: True
    • Safe: True
    • Crawled property mapping: vProductAndDescriptionRead
      ListElement.ProductModel
  6. ProductDescription
    • Name: ProductDescription
    • Type: Text
    • Searchable: True
    • Retrievable: True
    • Safe: True
    • Crawled property mapping: vProductAndDescriptionRead
      ListElement.Description
  7. CultureID
    • Name: CultureID
    • Type: Text
    • Queryable: True
    • Safe: True
    • Crawled property mapping: vProductAndDescriptionRead
      ListElement.CultureID
  8. ProductID
    • Name: ProductID
    • Type: Integer
    • Queryable: True
    • Safe: True
    • Crawled property mapping: vProductAndDescriptionRead
      ListElement.ProductID
  9. Click the Content Sources link in the Crawling side navigation section
  10. Start Full Crawl for the AdventureWorks2012 content source

Result Sources

The next step is to create two new result sources on the publishing site that we can use later to configure content search web parts.

  1. On the publishing site, navigate to Site Settings > Search Result Sources
  2. Click New Result Source to create each of the result sources below
  3. Product
    • Name: Product
    • Query Transform: {searchTerms} contentsource:AdventureWorks2012 entityname:Product cultureid:en
  4. ProductModel
    • Name: ProductModel
    • Query Transform: {searchTerms} contentsource:AdventureWorks2012 entityname:ProductModel

Site Navigation

Now let’s confirm that managed navigation is enabled and configured on the SharePoint site. It is enabled for new publishing sites by default in SharePoint 2013.

  1. Navigate to Site Settings > Look and Feel > Navigation
  2. Make sure that Managed Navigation is selected for both Global Navigation and Current Navigation

Pages

We’ll need to create 3 new pages on the site – one top-level page listing all product models, one page that will list all products for a product model, and one page to display product details.

The first page has to be created by using the Site Actions > Add a page option so that SharePoint automatically creates and configures the navigation term.

  1. Create a new page called Products by going to Site Actions > Add a page
  2. Navigate to the Pages document library on the site
  3. Create a new page called Product by using the New Document option in the ribbon
  4. Create a new page called Product-Model by using the New Document option in the ribbon

Managed Navigation

Now is the time to configure the managed navigation to use the pages created earlier.

  1. Navigate to Site SettingsSite Administration > Term store management
  2. Expand the Site Collection node
  3. Expand the Site Navigation node
  4. Select the Products term
  5. Select the Term-Driven Pages tab
  6. Change target page for children of this term
  7. Change Catalog Item Page for this category and Change Catalog Item Page for children of this category to use the Product.aspx page
  8. Press Save to commit the changes
  9. Add a child term to the Products navigation term for each of the product model. No settings need to be customized for the child terms.
    • Mountain-100
    • Mountain-500
    • Road-150
    • Road-450
    • Touring-1000
    • Touring-2000

The navigation term set should now looks similar to this:
SiteNavigation

Content By Search

The final steps is to add and configure content search web parts to the pages we created earlier.

  1. Click the Products link in the global navigation to navigate to the Products.aspx page
  2. Edit the page and add a Content Search web part from the Content Rollup category
  3. Edit web part properties
  4. Press Change Query to bring up the Query Builder user interface
    1. On the Basics tab, switch to Advanced Mode, select ProductModel result source in the dropdown and clear the Query text
    2. Press OK to close the query builder
  5. Change the Number of items to show to 6
  6. In the Display Templates section, select Two lines as the Item display template
  7. In the Property Mappings section, select ProductModelSummary as Line 2
  8. Press OK to apply changes and save the page

The Products page should now look like this:
Products

Next, click one of the links on the page to navigate to the product model page.

  1. Edit Product-Model.aspx page
  2. Add a Content Search web part from the Content Rollup category
  3. Edit web part properties
  4. Press Change Query to bring up the Query Builder user interface
    1. On the Basics tab, switch to Advanced Mode, select Product result source in the dropdown
    2. Set Query text to productmodel:{Term.Name}
    3. Press OK to close the query builder
  5. Change the Number of items to show to 10
  6. In the Display Templates section, select Two lines as the Item display template
  7. Press OK to apply changes and save the page

Your Product Model page should now look similar to this screenshot:
Product-Model

Now follow one of the links on the page to navigate to the product detail page.

  1. Edit Product.aspx page
  2. Add Catalog-Item Reuse web part from the Search-Driven Content category
  3. Edit web part properties
  4. Press Change Query to bring up the Query Builder user interface
    1. On the Basics tab, switch to Advanced Mode, select Product result source in the dropdown
    2. Set Query text to productid:{URLToken.1}
    3. Press OK to close the query builder
  5. In the Property Mappings section, select ProductDescription managed property
  6. Press OK to apply changes and save the page

Finally, the Product page should look like this:
Product

Business Connectivity Services, External Content Types and Content By Search in SharePoint 2013 – Part 1

SharePoint 2013 makes it very easy to index data from external systems using Business Connectivity Services (BCS) and then to surface that data in SharePoint by taking advantage of the new Content By Search web part and Managed Navigation. In this blog post you’ll find step-by-step instructions on how to create External Content Types optimized for search and index data from external systems. My next blog post will build on top of that and will show how to configure Managed Navigation and Content By Search web parts to retrieve and display the external system data on a SharePoint site.

External System

In this example I’ll be using a copy of the AdventureWorks 2012 database from Codeplex. You can download the SQL Server 2012 OLTP version of the database here: http://msftdbprodsamples.codeplex.com/

External Content Types

We’ll need to create two external content types based on the database entities – Product and ProductModel.

  1. Launch Microsoft SharePoint Designer 2013.
  2. Open the SharePoint site where would you like to create the External Content Types.
  3. Select External Content Types in the left Site Objects pane and press the New External Content Type button in the ribbon.
  4. Set the Name to Product.
  5. Click the link next to the External System to bring up the Operation Designer.
  6. Press Add Connection, select SQL Server and configure the Connection Properties.
  7. In the Data Source Explorer, expand AdventureWorks2012 > Views.
  8. Generate the New Read Item Operation and New Read List Operation for vProductAndDescription view by right-clicking it. Map the ProductID column to Identifier.
  9. Save changes.
  10. Navigate back to the External Content Types screen and click the Product name to bring the content type back up.
  11. In the Fields section, select the Name field and press Set as Title in the ribbon. This will ensure that the Name field will appear as the title of the record in search results.
  12. Save changes.
  13. Repeat the steps above to create the ProductModel external content type. Use the vProductModelCatalogDescription view, ProductModelID as identifier and set the Name field as title.

Once all of the steps above are complete you’ll need to configure some additional settings in Central Administration. First we need to configure permissions.

  1. Open Central Administration.
  2. Navigate to Manage service applications and select the Business Data Connectivity Service Application then press Manage in the ribbon or simply click the service application name.
  3. Grant your search default content access account permissions to the metadata store or individual objects by using the Set Object Permissions and Set Metadata Store Permissions in the ribbon.

Now let’s add default actions to the ProductModel and Product external content types. The default action

  1. Click the ProductModel external content type.
  2. Press the Add Action button in the ribbon.
  3. Set the URL to something like http://www.contoso.com/products/{0} – this is going to be the location to the product rollup page on the publishing site.
  4. Press Add Parameter and select the Name field.
  5. Check the Default action checkbox and press OK.
  6. Repeat the steps above for the Product external content type but use http://www.contoso.com/products/{0}/{1} as the url, ProductModel field as the first parameter and ProductID field as the second parameter.

Search

At this point we are almost done and are ready to crawl and index the data.

  1. Navigate to the Search Service Application in Central Administration.
  2. Click Content Sources link in the left navigation section under Crawling.
  3. Click New Content Source.
  4. Set the Name to AdventureWorks2012.
  5. Select Line of Business Data as the Content Source Type.
  6. Select the Business Data Connectivity Service Application in the dropdown.
  7. Select the Crawl selected external data source option and check the checkbox next to AdventureWorks2012.
  8. Press OK.
  9. Start Full Crawl for the newly added AdventureWorks2012 content source.

When the crawl is done, navigate to the Search Center site and run a search query for contentsource:AdventureWorks2012. You should now be getting search results back. In my next blog post I’ll show how to surface these search results on the SharePoint site using Managed Navigation and Content By Search web parts.