Monday 14 February 2011

Searching Database Content with FAST Search for SharePoint 2010 - Part 3 - Using the BCS







To help with the BCS example, I first create an SQL View using the following SQL.






SELECT

p.ProductKey,

p.ProductName,

p.ProductDescription,

p.Manufacturer,

p.BrandName,

p.ClassName,

p.ColorName,

p.UnitCost,

p.UnitPrice,

psc.ProductSubcategoryName,

pc.ProductCategoryDescription

FROM Contoso_Retail_DW.dbo.DimProduct p

LEFT OUTER JOIN Contoso_Retail_DW.dbo.DimProductSubcategory psc

on p.ProductSubcategoryKey = psc.ProductSubcategoryKey

LEFT OUTER JOIN Contoso_Retail_DW.dbo.DimProductCategory pc

on psc.ProductCategoryKey = pc.ProductCategoryKey


















SQL View FAST Search

Next we fire up SharePoint Designer to create the External Content Type.



SharePoint Designer BCS External Content Type



Click the External Content Type ribbon action.



SharePoint Designer BCS External Content Type



Click the Click here to discover external data sources and define operations.



SQL View FAST Search



Select SQL Server as the data source.



SQL View FAST Search



Enter the connection info



SQL View FAST Search



SQL View FAST Search



Right click on SearchView and select New Read Item Operation



SQL View FAST Search



Enter details about the operation



SQL View FAST Search



Next



SQL View FAST Search



Then Finish



SQL View FAST Search



Now create a New Read List Operation



SQL View FAST Search



Enter operation details



SQL View FAST Search



Next, note the warning regarding specifying a limit filter.



SQL View FAST Search



Finish



SQL View FAST Search



Now click Summary View in the Ribbon, select ProductName from the Fields List and click the Set As Title ribbon action.





SQL View FAST Search



SQL View FAST Search





Now save your external content type



SQL View FAST Search



Now if we browse to our site collection, there's a Contoso Product list and we can see the BCS in action!



SQL View FAST Search



The BCS option has a nice feature that the JDBC connector didn't have - Profile Pages.



You can create a site to host profile pages, and then in your Business Connectivity Services Service Application, you can create Profile pages for your external content type



SQL View FAST Search



SQL View FAST Search



If I browse to the profile page, I get this error.



SQL View FAST Search



That's a good error :) because I haven't specified a product id as a query string parameter the error tells me it can't find the item, that suggests that its at least querying the DB.



Now navigate to your FAST Content Search Service Application



SQL View FAST Search



Create a new Content Source and specify Line of business data as the content source type, and select Contoso_Retial_DW



SQL View FAST Search



Now start a crawl! When a crawl is running you can check if it's working in the crawl log.



SQL View FAST Search



SQL View FAST Search



If crawls fail to work you can get a useful message through the crawl log



I also suggest you fire up ULSViewer to see what the cause is. ULSViewer is THE BEST TOOL for troubleshooting BCS and FAST Crawling using BCS.



Now that it looks like our crawl has successfully finished, lets try a test search



SQL View FAST Search





I get two results, the first is from the BCS and the second item is from the JDBC connector. I wonder why the teaser description is different between the two? The BCS also has a different icon due to search resolving the JDBC item target as a folder.



Thanks to profile pages, I can actually click the BCS result which takes me to the profile page for the product.



SQL View FAST Search



This is one area which would require a bit of extra work and development if you go with the JDBC connector approach.



Finally, we want to map some search properties. The managed properties from the previous demo are still setup, so I'll map the new BCS crawl properties to these same managed properties.



Now when you are in the FAST Query Search Service Application, don't click on the Metadata properties link in the left menu, 'these aren't the search properties you're looking for' /waveshand.



SQL View FAST Search



If you mess with property mappings in there you won't see your BCS properties, instead click on the FAST Search Administration link.



Then click on the Managed Properties link.



SQL View FAST Search



Click on Crawled property categories



SQL View FAST Search



You should notice the Business Data category with a larger number of properties, click the Business Data link.



SQL View FAST Search



There's a list of our crawl properties!



You can map crawled properties through to managed properties through here too.



SQL View FAST Search





After mapping all the crawled properties to managed properties, perform a full crawl.



Now retest the same search



SQL View FAST Search



Note the refiner counts have all incremented compared to the previous test search, so the property mappings and refiners are now setup, it's as easy as that!



Thanks for reading! I've got one more post to go for this series, I'll perform a closer comparison of the pros and cons of the the JDBC Connector and BCS Connector and why you might use either depending on your requirements and priorities.


Friday 4 February 2011

Presenting at the Australia SharePoint Conference on March 8+9 and I'm off to the Democratic Republic of Congo



That's right folks. I'm presenting at the Australia SharePoint Conference in Sydney on March 8+9.

More info here:

My presentation topic is related to searching databases with FAST Search Server for SharePoint 2010!
http://www.sharepointconference.com.au/AU2011/SolutionPages/AllSpeakers.aspx

I'll also be in two 'Ask the Experts' panels
http://www.sharepointconference.com.au/AU2011/SolutionPages/ATE.aspx

So if you're attending the conference, come up and say hello!


This Sunday I am flying out to Africa! for 1 week.

I'll be flying over to South Africa, and then the Democratic Republic of Congo to a copper mine in the south of the country to run some SharePoint Server 2010 demos and to gather requirements for a system we're implementing for the mining company. It should be an experience :)