Solution Outline

KTC have designed and developed a sophisticated SharePoint 2010 BCS based application suite for a household name broadcasting company.  Originally designed in a SharePoint 2007 environment, the initial version of the application retrieved external data via BDC into temporary tables and then populated lists with the data which was subsequently combined with other native lists to providea reporting capability.

In contrast the replacement system stores all data in an operational data store (SQL database) as depicted in the folowing schematic:

High Level Solution Architecture Diagram

The following is a description of the approach taken to migrate the application and data from the SharePoint 2007 environment to the SharePoint 2010 environment.

Backend Data Storage

• Rationalise the existing SQL data access by using a cleaner approach whereby source data is read into a staging area and then processed via an ETL operation to populate an ODS.
• Replace the usage of SharePoint lists with relational tables in the ODS.
• Extract the existing BDC/Content Type schema and map to a SQL schema.
• Implement the required relationships in the ODS relational model to support the reporting requirements.
• Develop the ETL routines to populate the data model.
  

Frontend Data Presentation

• Usage of tools to generate external content types from the ODS model (BCS MetaMan).
• Usage of OOB BCS web parts for data presentation such as master detail views.
• Where needed, customisation on top of the OOB list edit/views to implement required features such as lookups or custom validation. It is anticipated that this would be achieved via a combination of custom BCS field types with 3rd party controls and client side jQuery.
• Provisioning of web part pages to deliver the UI through a simple publishing site portal.
  

ETL Processing

SSIS was used to perform the ETL processing.  A SharePoint timer job was used as a "listener" to accept manually initiated data refresh requests and also handle the scheduled overnight data refresh.  The following diagram depicts the "master" SSIS package which runs under the SQL Agent service and invokes the sequence of child packages in turn if it is detected that a job is ready for processing:

Custom BCS Field Types

The OOB form SharePoint 2010 editing experience leaves a lot to be desired in terms of usability and there is a serious lack of validation. For this solution, the standard method of editing the forms with InfoPath to add desired logic and field controls was rejected as an unnecessary overhead. Howvever, one of the overarching aims of the application was to leverage OOB functionality whenever possible.

To this end, a set of custom field controls has been developed to fill the gap in the OOB forms. As discussed in the previous section a utility has been developed that will register custom field controls within the BCS model.

The following custom fields were implemented, leveraging the Telerik RadAjax control set.

Searchable Lookup Combo Control

A replacement for the BCS picker control, this control is a lookup for all of the foreign keys in the table which are typically integer fields.
The control implements type ahead searching, paging and sorting and can easily handle very large result sets. This is because the control fetches only a single page of data at a time “on demand”. Furthermore when the display and edit forms render only the currently selected item is rendered in HTML minimising page weight.

Multi Select List Boxes

This control is a replacement for the SharePoint OOB control that implements multiple selected detail items as a “;#” delimited list of type string.

List of Values Control

Similar in appearance to the Searchable Lookup Combo Control as the LOV field type also utilises the RadCombo box control, however this control is designed to read its list of values from a table as shown below. There is no foreign key relationship enforced and the stored value (lov_value) in the table column is a string and may be the same as the display value (lov_display) as can be seen in the following image. Also as the data is likely to be quite small, it is read in its entirety for LOV type controls rather than on demand and paging is therefore not required.

Sample SharePoint External List Form