Three Ways To Be Productive Using SharePoint and SQL Server (Part 1)

Introduction
In the first part of my three-part series on increasing productivity when using SharePoint and SQL Server, we will focus on searching product data from the Adventure Works database, including the use of meta-data and managed properties. The second part will show how to link the search results to a page highlighting some of the Business Intelligence (BI) features of SharePoint based on product data. The third will focus on maintaining expertise in MySites in a managed fashion.

Best Microsoft MCTS Certification, Microsoft MCITP Training
at certkingdom.com
Searching Product Data from the Adventure Works Database
One of the great benefits of using SharePoint is that it provides search, business intelligence, collaboration and portals on a unified platform. This provides a huge benefit in terms of cost-savings and productivity. To take advantage of the search functionality and provide the capability to search products in an intranet or public-facing website, it can be done by defining the taxonomy, putting that taxonomy into SharePoint and tying it to a crawl of the products database.

The products in the Adventure Works database are broken down into categories and sub-categories. Go into your Managed Metadata Service and define an Adventure Works Group.

Then, create a term set for both the categories and sub-categories. Now, add the items from the database. These can also be imported using the term store import functionality.

The next step is to create a view in the database that joins the products, sub-categories and categories to be indexed.

In order to connect to the data, go into your secure store application and create a target application to connect to the database. Name the target application “Adventure Works”. Use a Target Application Type of Group. Set the user name and password for Windows. Then, map the Members group to an AD Group that should have access to connect to those credentials.

Now open SharePoint Designer 2010 and connect to your site to create an External Content Type.

Click on External Content Types and choose the option to create a new one.

Enter the name of the content type. In this case, we will name it Adventure Works Products.

Choose Generic List as the Office Item List Type.

Hit the link named “Click Here to Discover External Data Sources and Define Operations” and choose SQL Server.

Enter the database server and database name and choose Connect with Impersonated Custom Identity. Enter the name of the desired secure store application used to connect to the AdventureWorks database.

Create “Read Item” and “Read List” operations for the view we created to expose the products, sub-categories and categories for searching by right-clicking the view name and choosing the new option for each. Accept all defaults on both.

Choose “Create Lists and Form” and name the list “Adventure Works Products”. Now, browse the list to ensure it pulls the products from the database.

Next, go into the Search Service Application to create a crawl of the external content type.

In central administration, open the search service application you wish to use.

Click on Content Sources, then choose New Content Source.

Once the full crawl completes, the next step is to map the Metadata Properties. Click the link to Metadata Properties under Queries and Results in the Search Service Application. Also, ensure the service account used to crawl the products has access to the Adventure Works BCS service application.

Click “Categories”, then “Business Data”.

At this point, there will be a list of properties from the products view.

Click the ProductCategory property and map it to the ProductCategory Managed property. Do the same for ProductSubCategory.

Run the full crawl again on the Adventure Works content source in the search service application.

Next, setup the action to view the product once it is returned by the search. Go to your business data connectivity service for Adventure Works Products, open it, and click the “View Profile” action and set it as follows:

Now we are set on the search of the products. Go to a search center or create one in your SharePoint environment. Add refinement filters to include the product and product subcategories.

Edit the search web page and modify the Refinement Panel web part.

Expand the Refinement grouping in the web part and de-select the Use Default Configuration option.

Add two <Category> tags to the XML in the Filter Category Definition property:

<Category Title=”Product Category” Description=”Use this filter to restrict results authored by a specific category” Type=”Microsoft.Office.Server.Search.WebControls.ManagedPropertyFilterGenerator” MetadataThreshold=”1″ NumberOfFiltersToDisplay=”4″ MaxNumberOfFilters=”20″ SortBy=”Frequency” SortByForMoreFilters=”Name” SortDirection=”Descending” SortDirectionForMoreFilters=”Ascending” ShowMoreLink=”True” MappedProperty=”ProductCategory” MoreLinkText=”show more” LessLinkText=”show fewer” />

<Category Title=”Product Subcategory” Description=”Use this filter to restrict results authored by a specific sub-category” Type=”Microsoft.Office.Server.Search.WebControls.ManagedPropertyFilterGenerator” MetadataThreshold=”1″ NumberOfFiltersToDisplay=”4″ MaxNumberOfFilters=”20″ SortBy=”Frequency” SortByForMoreFilters=”Name” SortDirection=”Descending” SortDirectionForMoreFilters=”Ascending” ShowMoreLink=”True” MappedProperty=”ProductSubCategory” MoreLinkText=”show more” LessLinkText=”show fewer” />

Search for Accessories and view the results. You can now search for products by product information, category and sub-category. Hover over the link to the product results and view the URL.

In the second part of the article series, we will create a product page with some BI features. .

Best Microsoft MCTS Certification, Microsoft MCITP Training
at certkingdom.com