February 2011

Volume 26 Number 02

Business Connectivity Services - Managing Employee Rewards with Office and SharePoint BCS

By Ying Xiong | February 2011

Managing employee rewards is a business-critical function at all companies. This is especially true for large enterprises such as Microsoft. At Microsoft, we offer many types of rewards to eligible employees, such as merit-based pay increases, promotions, bonuses, stock and other awards. Managing these rewards according to company guidelines and budgets (set for each region, business unit or organization, employee salary plan, and pay level) is a complex process.

Many business rules are involved when giving rewards to employees. Among other goals, these rules ensure that our top performing employees at all levels receive highest rewards while the organizations at all levels meet their goals and remain within their budgets and guidelines. Every year, managers and HR personnel analyze the numbers and determine each reward’s guidelines and limits at all levels. This process (called calibration) is time-consuming and complicated.

The current rewards-management tool used by our managers and HR personnel is a Windows Forms application. The tool works as designed, but it doesn’t meet all of our needs—there are a number of ways it could be improved to simplify and streamline the calibration process.

In response to suggestions for improving the calibration tools, the Microsoft IT (MSIT), HR Business and Microsoft Office and SharePoint product teams collaborated to create a new solution based on Microsoft Business Connectivity Services (BCS), a feature of Microsoft Office 2010 and SharePoint 2010. BCS provides read/write access to external data from line-of-business (LOB) systems, Web services and databases, as well as SharePoint and Office applications.

The new solution uses Microsoft Excel 2010 as the rewards-management UI, and leverages BCS technology to cache and synchronize employee data and business rules between the user’s local machine and the back-end systems (Rewards Web Services and a SQL Server database).

This article shares the experiences of the Microsoft teams in developing and deploying the rewards-management BCS solution.

Where We Started

The existing rewards-management tool was designed to support an integrated performance management experience for overall employee assessment, calibration, ratings and rewards through a single cohesive toolset. Figure 1 shows the architectural design of the existing tool. This tool is a typical three-tier application where the UI layer is a Windows Forms application that reads data from and writes data to back-end Web services. These services query and update employee records, business rules and other reference data in a SQL Server database.

Figure 1 Architecture of Current Rewards-Management Tool

The solution was designed to minimize network traffic between users’ computers and Web servers hosting the Web services component. When the client app starts, it retrieves and caches all necessary reference data, business rule data and employee records in the organization to which the user has access permissions. Most of the rewards-management business logic and rules are implemented in the client component. Hence, when the user assigns or changes employee rewards, the corresponding business rules are fired to validate the changes without calling Web services at the back end.

The Web services component was built with the Microsoft .NET Framework 2.0 Web services framework (ASP.NET Web Services). As mentioned earlier, it returns employee records and business rules data to the client application and saves data changes passed from clients to the database. There’s business validation logic in the Web service to ensure data integrality before the data is saved to the database. All Web services calls are designed as synchronous methods.

Users get error messages when Web services calls fail, and users can take action based on the nature of the error. In addition, the Web service was designed to serialize employee and other data records into a byte array and return the byte array to clients in order to minimize the size of data transferred between Web services servers and the client app.

Finally, the rewards data was stored in a relational database implemented in Microsoft SQL Server 2005. This database stores all data needed for managing employee rewards, as well as historical data for past performance-review periods.

As I mentioned previously, this application architecture worked largely as planned and was a significant improvement over previous rewards and calibration systems. However, in use, we discovered many areas where we could improve the tool to make calibration easier and less time-consuming. So that you’ll understand the reasoning behind design decisions for the new service, let’s take a quick look at some of the issues discovered in the old one.

The first, and probably most important, issue encountered in the old calibration tool involved the difficulty of modeling rewards. During the calibration process, managers need to balance two variables. Each employee must receive appropriate rewards based on a performance rating. However, the entire organization needs to meet a predefined rewards budget. Getting this balance right is a time-consuming process. Managers need to enter employee rewards numbers, see how they affect the team rewards budget, and adjust rewards to balance both reward rules and budget. This can take multiple iterations before the rewards are finalized.

The existing tool didn’t facilitate the rewards-versus-budget-balancing process. It didn’t provide capabilities for managers to enter different numbers for each reward and analyze the result to see the impact to the budgets and guidelines. Instead, managers often had to export the employee data into an Excel spreadsheet and manually model rewards and budget figures.

When calibration is complete and each employee’s rewards are finalized, managers have to enter the data into the tool manually and submit the rewards for approval. This double data entry is also a time-consuming process.

The Windows Forms-based tool requires users to be online and on the corporate network because the tool needs access to back-end Web services for data reads and writes. This adds another level of hassle for employees with busy schedules or who travel frequently.

Finally, while the existing rewards-management tool provided a built-in set of reports, we had many requests for additional reports and flexible ad hoc reporting capabilities to make the calibration process more transparent and efficient.

BCS Basics

BCS lets you use Office applications (Excel, Outlook, InfoPath, Word and so on) and SharePoint to process data from back-end systems. As I already mentioned, BCS is a component for both Office 2010 and SharePoint 2010, so it’s available on both client and server machines. (See msdn.microsoft.com/library/ee556826 for more information about the BCS tooling and runtime components.)

When designing a BCS-enabled solution, you start by defining an entity model that can connect to external systems and map data from those external data structures into the BCS data structure. This entity model is called an External Content Type (ECT). You can create as many ECTs as you want for your solution through SharePoint Designer or Visual Studio.

ECT metadata, including the code for defining the entity model, is published and stored in a SharePoint metadata store. The published ECT model can be downloaded and installed on user client machines through a SharePoint workspace or via a standalone packaging tool provided by BCS.

At run time, the BCS runtime component on either the client or on the server invokes the ECT model to communicate with external systems. On the client, the data from any external systems is cached on the user’s machine in a SQL Server Compact Edition (SQL CE) database. The cached data can then be displayed and manipulated in Office applications through the BCS APIs. The changes made to the cached data by users will be queued on users’ machines in the same SQL CE database. The BCS runtime component is then responsible for updating the changes to external systems through the ECT model.

The New Rewards Tool

For the new rewards-management tool, we created a solution using the BCS framework, with Excel as the rewards-management UI, to address business issues described earlier. In this Excel/BCS solution, we defined a total of 15 ECTs for employee information, business rules and other reference data. The BCS runtime uses these ECTs to connect to existing rewards-management Web services (as external systems) and to retrieve and cache employee and business rules data.

The employee data is rendered as an Excel worksheet through an Excel add-in that uses BCS local cache APIs. Managers can assign, calibrate and manage their employee rewards completely within Excel and still be able to enforce all business rules based on rule data cached in the BCS local store—even if the managers are offline. With the Excel and BCS solution, managers are able to see a list of selected employees (in the Excel worksheet) and various statistics (in the Excel Task panel) on one screen. The statistics at the bottom are dynamically updated as reward values change, and different statistics are displayed depending on the field selected by the user. This is a greatly improved user experience.

Using native Excel functionality, such as pivot tables and charts, managers and HR personnel can create and visualize various reports for the data cached by BCS, thus improving productivity. Through Excel templates, users can create reports more easily, and the reports don’t require IT development.

Aside from business benefits, an interesting technical point we discovered while developing this new rewards-management solution was that we could leverage the existing Rewards Web Services component in the BCS solution even though the Web services weren’t designed or optimized for BCS. We were also able to leverage existing business rule data objects and validation code within our Excel add-in solution. As a result, we are able to deliver and demo the new solution in a relatively short period of time.

Solution Architecture

The new solution architecture is shown in Figure 2. As you can see, the changes to the original architecture were made only for the client architectural component. In the diagram, the green boxes are BCS components that are already installed on users’ machines when they install Office 2010. The blue boxes are the components developed for the new solution, and the light-blue boxes are components of the existing rewards-management tool.

Figure 2 Architecture of the New Rewards Solution

When the ECTs developed for the new rewards solution are installed on a user machine, the BCS sync process (BCSSync.exe) immediately invokes the entity model to retrieve data for each ECT defined in the model from the external rewards Web services. The actual data synced is determined based on the user’s data-access permissions (obtained via user security Web services). This step requires that the user be online in order to access Web services.

Retrieved data such as employee records for an organization is stored in a SQL CE database created by the BCS sync process as the local data cache for the user. The data in SQL CE is encrypted using the user’s certificate and the data is secure at the user level.

The BCS sync process runs periodically to synchronize the user’s local cache with the rewards server for any changes made on the server side. The BCS framework enables the ability to define synchronization frequency for each ECT differently. This means we can define a longer (hours or days) sync frequency for infrequently changed entities such as business rules, and shorter (minutes or hours) sync frequency for employee rewards data that can change frequently during the review period.

With employee, business rules and other reference data cached on the user’s machine, the user can start the rewards-management tool by simply launching Excel. The rewards Excel add-in uses BCS APIs to retrieve data from the SQL CE database and binds the data into an Excel worksheet. From that point the user can see the employee records and their rewards data in Excel. This Excel file is no different than other Excel files you normally create or use, and the user can employ native Excel functions to manipulate and analyze the data.

When the user makes changes in the worksheet to assign or update employee rewards such as salary merit increase percentage, promotion amount or stock award shares, corresponding business rules are fired to validate and process these changes. We’re able to achieve this by hooking the value change event of an Excel cell to existing business rule code.

Moreover, some employee data such as employee number, job title and e-mail address can’t be changed by users. We accomplish this requirement by using a native Excel lock feature.

When the user is ready (online or offline) to submit changes to the back-end server, the changes are submitted into a local queue stored in the SQL CE database. The BCS sync process picks up changes one at a time from the queue and sends the changes to the back-end Rewards Web Services. If the user is offline or the back-end Web services are not available, the BCS process will retry the change until the user is online or the Web services are available.

If the user isn’t ready to submit the changes to the back-end server and wants to save changes as a draft, this is handled as a normal Excel file save operation.

Defining ECTs

As stated previously, an important first step for any BCS-enabled project is to create the ECTs for your solution. Designing ECTs is the process of modeling the data entities you’ll use in your Office or SharePoint solution. The number of ECTs and the data structure for each ECT depends on the nature of your application data, how the data is used in your application and the interfaces provided by your external systems.

Because the data for ECT entities come from external systems (the Rewards Web Services, in our case), the simplest way to model your entities is to design them the same as the returned types from your external systems. This involves no data mapping from one structure to another structure. However, you can’t always do this, especially when external Web services return data types that are complex and have a deep hierarchy. This was the case for the rewards-management system.

The existing Rewards Web Services return a serialized byte array (for employee and business rule data) that needs to be deserialized on the client side into custom System.Data.DataTable types. These custom data types aren’t supported by default in BCS.

Our ECT implementation for the new rewards system defines a relatively flat data structure (with simple data types) for employee and business rule entities. It also converts or maps the data from the custom data tables (after deserialization) into the flat ECT structure. When users update data through ECTs, we then convert data back to custom data table types and send the changes to the back-end Web services.

Every ECT must have at least two methods defined. The Finder method is invoked by the BCS runtime to download all data. In the case of the employee ECT, it returns all employee records the user has permission to access. When an ECT is installed on a client machine, the installation process will create a subscription based on the Finder method for the BCS runtime to periodically synchronize the ECT.

The SpecificFinder method returns the specific instance of data (employee) based on the entity identifier (employee number). SpecificFinder is used by the BCS runtime to synchronize the employee instance with the back-end server. For data entities that users update on the client side, such as employee records, the ECTs include an update method that the BCS runtime can invoke to send updates to the server. Similarly, if users need to create new records of an entity, a create method is defined in the ECT. Reference data entities that users won’t change don’t need the update or create methods for their ECTs.

In your own ECT implementations, you can add any business logic you need to validate or process data before invoking external Web services. This is a great design option with BCS that lets you put business logic right in your ECTs and run it on both the client and the server.

There are two ways you can design and create ECTs. If your external systems return relatively flat and strongly typed data types, you can use SharePoint Designer to generate your ECTs. SharePoint Designer generates ECTs based on external system interfaces—operation and data contracts in the case of Windows Communication Foundation (WCF) Web services. However, at this time, SharePoint Designer doesn’t support complex and custom types such as custom data sets and data tables.

The second and more powerful approach is to use Visual Studio to design and create ECTs. Visual Studio 2010 includes a project template specifically for creating BCS entity models. This is the approach we used in our rewards solution.

ECT Deployment and Installation

To make the rewards-management system work, the ECT models need to be downloaded and installed on the client machine. Microsoft Office and SharePoint 2010 provide two ways for deploying ECTs. Figure 3 shows a deployment process using SharePoint Workspace, which is installed on users’ computers as part of the Office 2010 installation. Figure 4 illustrates how it works.

Figure 3 ECT Deployment Process Through SharePoint Workspace

Figure 4 ECT Deployment Process Through BCS Solution Packaging Tool

First, ECTs are published from Visual Studio to the SharePoint Server metadata store (step 1). For Visual Studio to publish ECT metadata into the SharePoint data store, it must run on the same server where SharePoint 2010 is installed.

Next, you create an external list for each published ECT (step 2). You can create the external list through the SharePoint homepage from any computer that can connect to SharePoint Server.

After you create the external list for an ECT, SharePoint immediately runs the ECT, which connects to and retrieves data from the external systems and displays the data as list content on the SharePoint site. With this deployment process, you can view the data returned from your ECT on the SharePoint Server, in addition to your office application on the client. In some situations this is a desirable feature, but it’s not required. The rewards-management solution doesn’t use this feature because we don’t want sensitive employee-rewards data to be shown through SharePoint.

Now download the external lists created from SharePoint Server in step 2 to your local machine (step 3). You can start the downloading process by connecting to the SharePoint homepage from Internet Explorer and selecting Site Actions | Sync to SharePoint Workspace. This will launch the SharePoint Workspace program on your client machine and start downloading external lists one at a time. With the current release of Workspace 2010, the user will have to click the Install button for each external list to be downloaded.

By downloading an external list, SharePoint Workspace will automatically install the ECT (metadata and assembly) into the BCS local data store (step 4). In addition, a subscription is created for the installed ECT for BCS runtime to synchronize ECT data periodically with the external systems. The default data sync frequency is six hours.

The rewards-management solution uses a second deployment process to install ECTs onto users’ client machines (as shown in Figure 4).

In this deployment method we use the BCS Solution Packaging Tool (code.msdn.microsoft.com/odcsps14bcspkgtool) and there are no external lists involved in the deployment process. Therefore, there’s no need for SharePoint homepage and SharePoint Workspace, though we still employ the SharePoint Server metadata store.

In this deployment process, after the ECTs are published into SharePoint metadata store, we use SharePoint Designer to export the ECTs into a model file (.bdcm). You can run SharePoint Designer either on the server machine or on your client machine and you can place the exported model file in a shared folder. The BCS Solution Packaging Tool reads the model file and generates a Visual Studio Tools for Office (VSTO) installation package that can be run by users.

From the user’s perspective, this is a simple, easy and transparent installation process. Users only click once when running the setup.exe to install all ECTs exported from SharePoint Designer. The installation will also immediately start BCSSync.exe to run the ECTs and download ECT data directly from external systems into the BCS local data cache.

The Excel Add-In

As described earlier, we redesigned the existing Windows Forms application component into an Excel add-in component and used Excel as the UI for managing employee rewards. The Excel add-in performs the following main functions:

• Renders employee records and related guidance and statistics as an Excel worksheet and task pane.
• Uses BCS APIs to retrieve employee and business rule data from the BCS local cache, and writes back any changes to the BCS local update queue.
• Invokes existing business logic code when users make changes to employee rewards to ensure the changes meet corresponding guidelines and minimum/maximum ranges.

Figure 5 shows the new Excel-based UI for rewards management. The top half of the screen is an Excel worksheet displaying employee records for the users’ organization. The bottom half of the screen is a task pane that shows the guidelines for the highlighted employee (selected row) and statistics for the organization. Guidelines and statistics for selected columns will be displayed on the bottom screen.

Figure 5 Excel Design for the Rewards-Management Solution

This simple and easy-to-use Excel interface helps managers and HR personnel effectively manage their employee rewards. They’ll perform all their work on one screen and still see the overall rewards picture for their organizations. Of course, they can build additional statistics and charts based on worksheet employee data using native Excel functions such as pivot tables and pivot charts.

When the Excel program is started, there’s a custom Rewards tab added by the Excel add-in in the menu bar. Users click the Rewards tab and a ribbon is shown with various buttons for users to start and use the rewards-management solution.

Using the BCS APIs

Of course, the BCS APIs lie at the center of any BCS-based solution. Let’s walk through some of our sample code to illustrate the use of BCS APIs.

First, let’s take a look at some code used to retrieve all employee records from the local cache store (see Figure 6).

Figure 6 Retrieving Employee Records

string entityNameSpace = "MSIT.HR.Rewards.BCS";
string entityName = "EmployeeDetails";
// Initialize and connect to BCS local cache
RemoteSharedFileBackedMetadataCatalog catalog = 
  new RemoteSharedFileBackedMetadataCatalog();
// Get the ECT for employee entity
IEntity entity = catalog.GetEntity(@entityNameSpace, entityName);
// Get the finder method defined for employee ECT
string methodName = entity.GetMethodInstance(
  MethodInstanceType.Finder)[0].Value.Name;
IMethodInstance mi = entity.GetMethodInstance(
  methodName, MethodInstanceType.Finder);
// Get external system instance
ILobSystemInstance LobSysteminstance = 
  entity.GetLobSystem().GetLobSystemInstances()[0].Value;
// Retrieves all instances of employee ECT
IEntityInstanceEnumerator instanceEnumerator = 
  entity.FindFiltered(entity.GetDefaultFinderFilters(),
  mi, LobSysteminstance, OperationMode.CachedWithoutRefresh);
// Loop each employee and add it to Excel worksheet list object
while (instanceEnumerator.MoveNext()) {
  ... // Loop each instance of employee record and
      // add it to Excel worksheet list object
}

IEntity is the main BCS interface for ECTs you define for your solution. IEntityInstance represents a specific data instance for the entity you want to get data from. Each ECT has a unique entity name within an entity name space. Each entity instance is identified by a unique ID you define when you define the ECT. In this case, it’s the employee number.

You can update an employee record (or, more specifically, an employee ECT instance) through a BCS entity instance update method. This creates an update operation in the BCS local queue. Thus there will be 10 operations queued in local cache if you make changes to 10 employee records. BCS sync will process one operation at a time, like so:

// Query the employee instance you want to change
Identity identity = new Identity(employeeNumber);
IEntityInstance myEmployee = 
  entity.FindSpecific(identity, LobSysteminstance);
// Update the employee bonus amount
myEmployee["BonusAmount"] = 1000;
// Submit the changes to BCS local cache
myEmployee.Update();

The code in Figure 7 shows how to query the BCS cache for all pending or failed operations due to external system unavailable, error and data conflicts.

Figure 7 Checking for Pending or Failed Operations

// Initialize and connect to offline cache store
RemoteOfflineRuntime offlineRuntime = new RemoteOfflineRuntime();
ISynchronizationManager syncManager = 
  offlineRuntime.GetSynchronizationManager();
IMetadataCatalog catalog = 
  offlineRuntime.GetMetadataCatalog();
// Get all current operations from local operation queue
IOperationCollection allOps = syncManager.GetOperations();
foreach (IOperation op in allOps) {
  // ECT associated with the operation
  IEntity entity = op.Entity;
  // Query for operation status
  string operationstatus = op.OperationStatus.ToString();
  // Query for operation retry count
  int retryCount = op.RetryCount;
  // Get last exception message if operation errored out
  string errMsg = op.LastException.Message;
  // Retry the operation if status is pending or error
  op.Retry();
  // Decide if error is due to data conflict
  if (errMsg.Contains("ConflicDetectedException"))
    // Then the error is due to data conflict
}

ISynchronizationManager is the main interface for the BCS entity-synchronization process. Each operation (IOperation) is a change to entity data to be processed by the BCS runtime. After processing, each data update operation will have a status of either Pending or In Error. If an operation is processed successfully, the operation is removed from the queue and you won’t be able to query the operation through the synchronization manager.

A Pending status indicates the operation is pending due to external system unavailability. BCS will automatically retry until it’s successful or encounters an error.

An In Error status indicates that the operation failed to be processed by external systems due to a validation error, a data integrity error or a conflicting data error.

There will be a time when users want to explicitly refresh their local cache from external systems, instead of waiting for the BCS runtime to sync periodically. Here’s how you force a refresh of data for all ECTs:

// Initialize and connect to offline cache
RemoteOfflineRuntime offlineRuntime = new RemoteOfflineRuntime();
ISubscriptionManager subManager = 
  offlineRuntime.GetSubscriptionManager();
// Get all subscriptions
ISubscriptionCollection mysubs = subManager.GetSubscriptions();
IEnumerator<ISubscription> ie = mysubs.GetEnumerator();
while (ie.MoveNext()) {
  ISubscription mySub = ie.Current;
  mySub.RequestRefresh(true);
}

As mentioned earlier, the first time an ECT is deployed and installed on a user’s machine, the BCS runtime creates a subscription to the data from external systems in its local data store. The subscription is being used by the BCS runtime to synchronize the ECT data with external systems.

In the sample code, ISubscriptionManager is the interface to get all subscriptions (ISubscription) created in the BCS data store. With a subscription object you can do a number of things, including changing the sync frequency for the ECT, adding additional query parameters, getting the last refresh status, requesting a refresh immediately (as in the example) and getting the number of instances synced for the ECT.

Lessons Learned

During the design, development and deployment of the new rewards-management solution, we gained much practical, real-world experience with both Office add-in development and SharePoint 2010 BCS implementation. Here are some of the insights we gleaned along the way:

First, spend as much time as you can designing the ECTs for your solution. ECT design is key to a successful BCS solution. ECTs directly affect the behavior of BCS runtime, and a badly designed ECT can cause the BCS runtime to crash or throw out-of-memory exceptions.

We learned this lesson the hard way. We initially designed a big ECT that returned all business rules as a single instance. The single instance contained a 40MB array that caused the BCS runtime to peak at 600MB of memory use when the array was de-serialized to objects.

To mitigate memory-use problems, we then redesigned the big ECT into a few separate ECTs based on rule categories. Each category ECT returns a number of entity instances equal to the actual number of rules. With these new, more-focused ECTs, the BCS sync process works much more efficiently.

Another best practice is to use strong typing and a relatively flat structure as much as possible to define your ECTs. If you can use SharePoint Designer to generate ECTs, it will save a lot of development time. Creating ECTs in Visual Studio with a lot of entity attributes gives you more flexibility, but it can be a time-consuming and tedious process.

Think through your requirements as to how much data can be downloaded and cached to a user’s local machine. This determines how you’ll implement the finder method for your ECT. Of course, the data returned from your external system calls sets the limit for the number of records you can download. If your ECT design needs more data than the limit, then the external systems will need to be changed to meet your requirement.

In our rewards solution, a manager can download and cache the employee records for his organization only. Thus, two different users are likely to have two different sets of employee data downloaded to their local cache. The existing Rewards Web Services authenticate the user and return only the employee data the user is allowed to access. In other words, the data filtering is done at the external systems and the ECT implementation doesn’t have to filter the data based on user permissions. If this is not the case for your solution, you’ll need to consider additional design and development time for filtering ECT data for the local cache.

In your ECT implementation, handle external system exceptions carefully. At some point, your ECT implementations for the Finder and SpecificFinder methods will need to invoke external system methods to retrieve data. You’ll need to catch and handle exceptions thrown from external systems in your code. However, you’ll need to throw the exception back to the BCS runtime after you handle it. Otherwise, the BCS runtime will think the call to the external systems was successful, return zero rows of data and remove the data already in the local cache. You definitely don’t want the local data cache deleted because of an external system exception.

There are additional considerations regarding the use of third-party libraries in your ECT implementation. In one of our ECTs, we had to reference an assembly developed by another group to perform additional business logic and data transformation. During the deployment of the ECT, we found that the referenced assembly wasn’t deployed to client machines as part of the ECT installation. As a result, BCS failed to run the ECT and download the data.

As I write this, we’re working with the BCS product team to engineer a solution. The workaround is to add the assembly to the Global Assembly Cache as part of the VSTO installation.

It’s very important to test and debug your ECTs. They’re run by the BCS runtime in the background and you can’t debug ECTs from Visual Studio when they’re running on client machines. Our recommendation is that you test and debug your ECTs on the server side by creating external lists in SharePoint. When you view and change the content of an external list on a SharePoint List page, you’re running the ECT code associated with the external list and you can debug the ECT from a Visual Studio instance running on the same SharePoint server.

Finally, it’s vitally important that you understand the BCS API entity operation mode. In the client-side application using BCS APIs to manipulate ECT entity instances, there are four operation modes you can use to manage ECT data. Here’s a quick overview of how they work:

• OperationMode.CacheWithoutRefresh: When this operation mode is used for an entity instance, BCS returns the entity instance from the cache. If data isn’t in the cache, BCS refreshes the cache from external systems and returns the cached copy. If the external systems can’t be contacted, BCS throws an exception.
• OperationMode.CacheWithImmedicateRefresh: With this operation mode, BCS refreshes the entity instance in the cache first from the external system and then returns the cached copy. If the external system can’t be contacted, BCS still returns the cached copy. If the entity instance is not cached and the external system can’t be contacted, BCS throws an exception.
• OperationMode.Offline: With offline operation mode, external systems are never contacted even if there’s no data in the cache. BCS returns the entity instance from the cache. If it’s not there, BCS throws an exception.
• OperationMode.Online: For online operation mode, BCS will never use local cached data and will always contact external systems to get a copy of the entity instance. If the external system can’t be contacted, BCS throws an exception.

Wrapping Up

Microsoft Office and SharePoint 2010 with BCS provide the foundation for implementing a simple, easy-to-use and powerful solution for businesses to manage enterprise data from multiple external systems with familiar Office UIs. This foundation facilitates business data and process availability anywhere and anytime, therefore increasing business productivity.

BCS synchronization infrastructure solves many problems associated with data copies, changes and conflicts. One of the advantages of BCS synchronization over other data-synchronization frameworks is that BCS allows your solutions to embed business and data-validation logic in the synchronization process through ECT implementations. Another benefit of BCS is the ability to synchronize composite data from multiple disparate systems (again through ECT design), instead of the point-to-point data synchronization that many other synchronization frameworks provide.

Ying Xiong is a principal architect on the Microsoft IT team, architecting and designing enterprise applications and integrations within Microsoft. He works on a variety of Microsoft technologies for building distributed enterprise applications. You can reach Xiong at yingx@microsoft.com.

Thanks to the following technical experts for reviewing this article: Rolando Jimenez Salgado and Satish Thatte