Facilitating Employee Rewards Management at Microsoft Integrating business processes into Office applications by using Business Connectivity Services
Technical White Paper Published: November 2010 The following content may no longer reflect Microsoft’s current position or infrastructure. This content should be viewed as reference documentation only, to inform IT business decisions within your own company or organization.
CONTENTS Executive Summary ............................................................................................................ 3 Introduction to the Calibration Process ............................................................................ 4 Current Rewards Management Tool .................................................................................. 5 Architecture 5 Limitations
7
New Rewards Management Solution ................................................................................. 8 Business Benefits 9 Architecture
9
Design of the Excel Add-in
11
Design of ECTs for Rewards Management
13
Deployment of the ECT Model
15
Use of BCS APIs
17
Best Practices...................................................................................................................... 20 Spend Sufficient Time Designing ECTs 20 Use Strongly Typed Types and a Relatively Flat Structure as Much as Possible to Define ECTs 20 Determine How Much Data Can Be Downloaded and Cached on a User’s Computer 20 Handle External System Exceptions Carefully
20
Test and Debug ECTs
21
Understand BCS API Entity Operation Mode
21
Conclusion ........................................................................................................................... 22 For More Information .......................................................................................................... 23
Situation
EXECUTIVE SUMMARY
At Microsoft, the tool that managers and HR personnel use for managing employee rewards lacks certain capabilities because of architectural constraints. Limitations include difficulty of reward calibration, double data entry, and no offline and ad hoc reporting capabilities.
Microsoft offers many types of rewards to eligible employees, such as merit increases, salary adjustment, promotions, bonuses, and stock. Managing these rewards according to the guidelines and budgets set for each country, organization, employee salary plan, and pay level is a complex process. Every year, managers and HR personnel determine each reward’s guidelines and limits at all levels. This process, called calibration, is time-consuming and requires many calculations and close examination of statistics.
Solution To address limitations and bring benefits to the business, Microsoft IT prototyped a new rewards management solution by using Business Connectivity Services in Microsoft Office 2010 and SharePoint 2010, along with Excel.
Benefits Users can use a familiar Excel interface to model rewards in real time, see updated guidance based on changes, and see budgetary impacts. Users enter data in one application instead of two. Users can work offline and synchronize the changes later. Creation of new reports does not require assistance from the development team.
Products & Technologies Microsoft Office 2010 Microsoft SharePoint 2010 Business Connectivity Services Microsoft Visual Studio 2010 Microsoft SharePoint Designer 2010 Microsoft SharePoint Workspace 2010 Microsoft SQL Server 2008
The current rewards management tool is a Windows® Forms application. Although the tool works as designed, it presents several limitations. To address these limitations, Microsoft Information Technology (Microsoft IT), the HR department, and the Microsoft® Office and Microsoft SharePoint® product teams collaborated to create a new solution in a prototype scope. The new solution is based on Business Connectivity Services (BCS), a set of services and features in Microsoft Office 2010 and Microsoft SharePoint 2010. BCS (formerly named Business Data Catalog) provides read/write access to external data from line-of-business (LOB) systems, Web services, databases, and other external systems within SharePoint 2010 and Office 2010 applications. The new solution uses Microsoft Excel® 2010 spreadsheet software as the user interface (UI) for rewards management. It uses BCS technology to cache and synchronize employee data, in addition to various business rules, between users' local computers and the back-end systems (Web services and databases). This technical white paper covers:
Functionality and architecture of the current rewards management tool.
Architecture and design of the new rewards management solution based on BCS.
Business benefits of the new solution.
Products and technologies used in the new solution.
Deployment and installation process of the new solution.
Best practices that Microsoft IT learned from prototyping the BCS-based solution.
This document shares the experiences of Microsoft teams in creating the Office 2010 and SharePoint 2010 BCS solution. Because of the significant amount of knowledge that the teams gained, their experiences can provide relevant guidance to organizations that want to increase the effectiveness of automating business processes and help ensure alignment with business objectives through the Microsoft technologies described in this document. This document is intended for enterprise business decision makers, technical decision makers, IT architects, and managers of LOB applications. Although this document provides recommendations based on the experiences of Microsoft teams, it is not intended to serve as a procedural guide. Each enterprise environment has unique circumstances. Therefore, each organization should adapt this information to meet its specific requirements. Note: For security reasons, the names of internal resources and organizations used in this paper do not represent real names used within Microsoft and are for illustration purposes only.
Facilitating Employee Rewards Management at Microsoft
Page 3
INTRODUCTION TO THE CALIBRATION PROCESS Managing employee rewards is a complex and business-critical function at all companies. Microsoft is a global enterprise and has sophisticated business rules for rewarding employees in different countries with different incentive plans, and at different pay levels, based on employees’ performance for the review period and potential long-term contributions to Microsoft. The calibration process during the annual review period uses these business rules—set by HR—to ensure that each organization (business unit or group) rewards every employee appropriately. At the same time, the process ensures that each organization meets the guidelines and budgets set for it. Moreover, each organization customizes the calibration in accordance with its specific business strategy. Major business rules or functions for the calibration process include:
Employee rating, ranking guidelines, and related business rules.
Merit-increase guidelines, targets, and minimum/maximum ranges.
Adjustment-increase guidelines, targets, and minimum/maximum ranges.
Promotion guidelines, minimum/maximum ranges, and level-change business rules.
Bonus guidelines, targets, and minimum/maximum ranges.
Stock award guidelines, targets, and minimum/maximum ranges.
Fair Labor Standards Act (FLSA)–related business rules.
Rules and process for reward exceptions.
Rules and process for final approval.
Facilitating Employee Rewards Management at Microsoft
Page 4
CURRENT REWARDS MANAGEMENT TOOL The current tool was designed to support a cohesive experience for employee assessment, calibration, ratings, and rewards.
Architecture As shown in Figure 1, the tool is a three-tier application where the UI layer is a Windows Forms application that reads data from, and writes data to, back-end Web services. The Web services query and update employee records, business rules, and other reference data from and to databases.
Windows Forms Application
Client
Web Servers
Databases
Rewards Web Services
User Security Web Services
Employee Rewards Database
Employee Profile Database
Payroll Database
Figure 1. Architecture of the current rewards management tool
Facilitating Employee Rewards Management at Microsoft
Page 5
Client In the current architecture, Microsoft IT implemented a Windows Forms application by using the Microsoft .NET Framework 2.0. Each user must install the application on his or her computer. To minimize network traffic between a user’s computer and the Web servers that host Web services, when the tool starts, it retrieves and caches all necessary reference data, business-rule data, and employee records under an organization that the user has permissions to access. This client component implements most of the business logic and rules for rewards management. Hence, when the user assigns or changes one or more employees' rewards, the corresponding business rules validate the changes without calling Web services. Figure 2 provides additional detail about the client component of the current rewards management tool.
Figure 2. Design of the client component
Web Servers Microsoft .NET Framework 2.0 Web services return employee records and business-rule data from the employee rewards database to the Windows Forms client application, and they save data changes passed from clients to the databases. Business validation logic in the Web services helps ensure data integrity (rewards Web services) and data-access permissions (user security Web services) before the data is saved to the databases. All Web services calls are implemented as synchronous methods. Thus, users receive error messages when Web services calls fail for both business errors and system errors, and users can take actions based on the nature of the errors. In addition, the Web services
Facilitating Employee Rewards Management at Microsoft
Page 6
serialize employee records and other data records into a byte array and return the byte array to clients in order to minimize the size of data transferred on the network.
Databases In the current architecture, employee rewards data and business-rule data are stored in Microsoft SQL Server ®2008 databases. The databases store all data needed for managing employee rewards, in addition to historical data for past performance-review periods.
Limitations Because of architectural constraints, the current rewards management tool lacks some of the capabilities that the business needs.
Difficulty of Calibration During the calibration process, mangers and HR personnel need to enter and adjust numbers for employee rewards numbers before they are finalized and saved into the databases. These users must ensure that each employee receives the appropriate rewards based on his or her performance rating, and that the entire organization meets the budget and the guidelines. The tool does not effectively facilitate the process, because it does not enable users to enter different numbers for each reward and analyze the result to see the impacts to the budgets and guidelines. Often, users export calibration reports from the tool into an Excel file and use Excel to model employee rewards. However, Excel does not have any rules or guidelines for working with this data. Users cannot model the rewards in real time and see updated guidance and statistics based on employee reward assignments and impacts to the budgets.
Double Data Entry After users complete calibration and finalize each employee's reward numbers in Excel or in another application, they must re-enter the data into the tool manually and submit the data for higher-level or final approval. This double data entry is a time-consuming process for a large organization that has hundreds of employees.
No Offline Capability To use the Windows Forms–based rewards management tool, users must be online and connected to the corporate network, because the tool must connect to back-end Web services for data reads and writes. Many managers travel around the globe during the performance-review period, and they need to be able to work on employee rewards when they are offline, so that they can meet the deadline for completing the performance reviews for their organizations.
No Ad Hoc Reporting The current rewards management tool provides a standard set of reports for managers and HR personnel to see some of the reward statistics for their organizations, such as salary summary statistics (actual amounts and percentages). However, for any additional reports or charts that users want, such as bonus summary statistics by pay level or peer group, they must ask the development team to design and build it into the tool for the next release. The business needs a flexible ad hoc reporting capability to effectively manage employee rewards.
Facilitating Employee Rewards Management at Microsoft
Page 7
NEW REWARDS MANAGEMENT SOLUTION Business Connectivity Services is a set of enterprise services and features that enable Microsoft Office application (Excel, Microsoft Access® database software, Microsoft Outlook® messaging and collaboration client, Microsoft Word, Microsoft InfoPath® information gathering program, and so on) and SharePoint user experiences for enterprise business processes and data from back-end LOB systems. Through creating a business entity model, BCS enables users to read and write business data, such as employee rewards, from and to multiple external systems. These external systems can include SQL Server databases, Web services, .NET assemblies, and even third-party LOB systems like SAP and Siebel. Because BCS is part of both Office 2010 and SharePoint 2010, it resides on both client and server computers. Figure 3 shows the high-level architecture of BCS components on both client and server sides.
Figure 3. High-level architecture of BCS components The key for a BCS-enabled solution is to define an entity model that can connect to external systems and map data from external data structures into the BCS data structure. This entity model is called External Content Type (ECT). A solution can contain as many ECTs—created through Microsoft SharePoint Designer 2010 or the Microsoft Visual Studio® 2010 integrated development environment (IDE)—as necessary. ECT metadata, including the assembly (.NET code for defining the entity model) is published and stored in the Microsoft SharePoint Server 2010 metadata store. The published ECT model can then be downloaded and installed on a user's client computer through Microsoft SharePoint Workspace 2010 or a stand-alone packaging tool that the Microsoft BCS product team provides. At run time, the BCS runtime component on the client or on the server invokes the ECT model to communicate with external systems. In the case of the client, the data from external systems is cached in a Microsoft SQL Server Compact database on the user’s computer. The cache data can then be displayed and manipulated in Microsoft Office applications (Excel in this case) through the BCS application programming interface (API). The changes that a user
Facilitating Employee Rewards Management at Microsoft
Page 8
makes to the cached data through Microsoft Office applications are queued on the user's computer, in the same SQL Server Compact database. The BCS runtime component then updates the changes to external systems through the ECT model. The new rewards management tool uses the BCS framework, together with Excel as the UI, to address the limitations of the current tool. This Excel/BCS solution for rewards management includes 15 ECTs (for employee data, business-rule data, and other reference data) for the BCS runtime to connect to existing rewards Web services (external systems) and to retrieve and cache employee data and business-rule data. Employee data is rendered as an Excel worksheet through an Excel add-in via BCS local cache APIs. Managers and HR personnel can assign, calibrate, and manage their employee rewards completely within Excel. They can enforce all business rules based on rule data cached in the BCS local store, even when they are offline.
Business Benefits The new rewards management solution will improve user experience and productivity, as follows:
Users can model rewards in real time through a familiar Excel interface and see updated guidance based on rating and ranking changes, while seeing the impact to the budget.
Data updates in Excel go to the rewards back-end systems, thus eliminating the need for double data entry.
Users can work offline and synchronize the changes later. The BCS conflict detection capability prevents a user from overwriting another user's changes.
Users can see a list of selected employees (in an Excel worksheet) and various statistics (in the Excel task pane) on one screen. The statistics that the tool displays depend on the fields that the user selects. Statistics are dynamically updated as reward values change.
By using native Excel functionality, such as pivot tables and charts, users can create various reports for the data cached by BCS.
Through Excel templates, business groups can create and set up standard reports without requiring assistance from the development team. Excel templates also enable faster designing of specific user experiences, such as a separation of rewards management for managers and HR specialists.
In addition, in the prototyping of this new rewards management solution, Microsoft teams took advantage of existing rewards Web services and incorporated existing business-rule data objects and validation code into the Excel add-in solution to enforce business rules while users work in Excel to change employee ratings and rewards. As a result, the teams delivered and validated the new solution prototype in a relatively short time, and they demonstrated that BCS technology can adapt to the existing design of Web services even though the design is not optimal to the BCS framework.
Architecture Figure 4 illustrates the architecture of the new solution for rewards management. Changes to the original architecture occurred only on the client component, yet those simple changes can address the limitations in the current tool and provide business benefits. As indicated in the diagram, the green boxes are BCS components that are already installed on users'
Facilitating Employee Rewards Management at Microsoft
Page 9
computers when they install Office 2010. The blue boxes are the components developed for the new solution. Microsoft Excel 2010 Rewards Excel Add-in BCS Offline APIs
Client
BCS Sync Process (Reads and Writes) BCS Local Cache (SQL CE Database) Rewards BCS Entity Model (ECTs)
Web Servers
Databases
Rewards Web Services
User Security Web Services
Employee Rewards Database
Employee Profile Database
New Solution Component
BCS Platform Component Payroll Database
Current Rewards Tool Component
Figure 4. Architecture of the Excel/BCS rewards management solution The process of the new solution works as follows: 1.
After the ECTs developed for the new solution are installed on a user's computer, the BCS synchronization process (BCSSync.exe) immediately invokes the entity model to retrieve data for each ECT defined in the model from external rewards Web services based on the user’s data-access permissions (obtained through user security Web services). The user must be online to access the rewards Web services. Retrieved data, such as employee records for an organization, is stored in a SQL Server Compact database that the BCSSync.exe process creates as the local data cache for the user. To maintain security, the data in SQL Server Compact is encrypted through the user’s certificate.
2.
When the data for all ECTs is downloaded and stored, the BCS synchronization process runs periodically to synchronize the user’s local cache from the rewards server for the changes made on the server side. The BCS framework enables the new solution to define synchronization frequency for each ECT differently. That is, the user can define longer (hours or days) synchronization frequency for less-often-changed entities such as
Facilitating Employee Rewards Management at Microsoft
Page 10
domain-reference and business-rule data, and shorter (minutes or hours) synchronization frequency for employee rewards data that changes frequently during the review period. 3.
With employee, business-rule, and other reference data cached on the user’s computer, the user can start the rewards management tool by starting Excel. The Excel add-in (installed separately on the user's computer) for rewards uses BCS APIs to retrieve data from the SQL Server Compact database and binds the data into an Excel worksheet so that the user can see it. The user can then use native Excel functions to manipulate and analyze the data.
4.
When the user makes changes in the worksheet to assign or update one or more employee rewards, such as percentage of salary merit increase, promotion amount, or stock-award shares, corresponding business rules or logic validates and processes these changes. The solution achieves this by associating the value change event of an Excel cell to an existing business-rule or validation logic code. Moreover, the solution uses the native Excel lock feature to prevent the user from changing some employee data, such as employee number, job title, and e-mail address. In this step, all of the user’s work in Excel can occur offline.
5.
When the user is ready to submit changes to the back-end server, he or she submits the changes to a local queue stored in the SQL Server Compact database. The same BCS process (BCSSnyc.exe) selects each change from the queue and sends the change to back-end rewards Web services. If the user is offline or the back-end Web services are not available, the BCS process will retry each change until the user is online or the Web services are available. If the user is not ready to submit the changes to the back-end server and wants to save his or her changes as a draft, he or she can save the Excel file normally.
Design of the Excel Add-in In the new solution prototype, the teams redesigned the existing Windows Forms application into an Excel add-in to enable managers and HR personnel to use the Excel UI for managing employee rewards. The Excel add-in performs the following main functions:
Render employee records and related guidance and statistics as an Excel worksheet and task pane.
Use BCS APIs to retrieve employee and business-rule data from the BCS local cache, and to write back any changes to the BCS local update queue.
Invoke existing business logic code when users change employee rewards, to ensure that the changes meet corresponding guidelines and minimum/maximum ranges.
Figure 5 shows the high-level design for the Excel add-in component where it replaces Windows Forms controls with Excel controls such as ribbon, worksheet, and task pane objects; replaces Web services proxy objects with BCS API calls; and uses all other objects in the middle for business logic. This approach demonstrated the ability of BCS technology to work with existing designs and code to deliver a new solution.
Facilitating Employee Rewards Management at Microsoft
Page 11
Figure 5. High-level design for the Excel add-in component of the solution Figure 6 shows the Excel-based UI for rewards management. The upper part of the window is an Excel worksheet that displays employee records for the user’s organization. The lower part of the window is the task pane. When the user selects performance rating or ranking columns, or merit or promotion columns, or bonus or stock-award columns for an employee, the related guidelines and organization-wide statistics appear in the task pane. When the user changes the employee rating, ranking, salary, bonus, or stock awards, the corresponding guidelines and statistics are updated automatically. The user can build additional statistics and charts based on employee data by using native Excel functions such as pivot tables and pivot charts.
Facilitating Employee Rewards Management at Microsoft
Page 12
Figure 6. Excel-based UI design for the rewards management solution When a user starts Excel, the Excel add-in creates a custom Rewards tab on the Excel menu bar. When the user clicks the Rewards tab, he or she can use the various buttons on the ribbon to manage employee rewards.
Design of ECTs for Rewards Management The key for the BCS-enabled solution for managing employee rewards at Microsoft was the design and development of ECTs. Designing ECTs is a process to model data entities that the Microsoft Office or SharePoint solution will use. The number of ECTs and the data structure for each ECT depend on the nature of the application data, the uses of the data in the application, and the interfaces of the external systems. Because the data for ECT entities comes from external systems (rewards Web services in this case), the simplest way to model entities is to design them as the same as the data types that the external systems return. This method requires no data mapping from one structure to another structure. However, this method is not always possible, especially when external Web services return data types that are complex with a deep hierarchy. This is the case for
Facilitating Employee Rewards Management at Microsoft
Page 13
rewards management. The existing rewards Web services return a serialized byte array, for employee data and business-rule data, that must be deserialized on the client side into custom data-table types (extend system.Data.DataTable class). The out-of-the-box BCS framework does not support these complex and custom data types. Therefore, in the ECT implementation at Microsoft, developers define a relatively flat data structure, with simple data types, for employee and business-rule entities. Developers convert and/or map the data from the custom data tables (after deserialization) into the flat ECT structure. When users update data through ECTs, developers then convert data back in custom data-table types and send the changes to the back-end rewards Web services. Every ECT must have at least two methods defined. The first method is the Finder() method, which takes no parameters. The BCS runtime component (BCSSync.exe) invokes this method to download all data from the external systems. (In the case of the employee ECT, it returns all employee records that the user has access to.) When an ECT is installed on a client computer, the installation process creates a subscription based on the Finder() method for the BCS runtime to periodically synchronize the ECT. The second method is the SpecificFinder() method, which returns the specific instance of employee data based on entity identifier (employee number in this case). The BCS runtime uses this method to synchronize or update the employee instance from or to the back-end server. For data entities that users will update on their clients, like employee records, the developer must define an update() method on these ECTs so that the BCS runtime can invoke and send updates to the server. Similarly, if users need to create new records of an entity, the developer must define a create() method in the corresponding ECT. For all reference data entities that users will not change on the clients, such as review periods, the developer does not need to define update() or create() methods for the ECTs. In implementations of all ECT methods, a developer can add any business logic to validate or process data before he or she invokes external Web services. With BCS, a developer can put certain business logic in ECTs that run on both client and server. There are two ways to design and create ECTs:
SharePoint Designer. If external systems return relatively flat and strongly typed data types, a developer can use SharePoint Designer to generate ECTs. SharePoint Designer generates ECTs based on external system interfaces—operation and data contracts in the case of Windows Communication Foundation (WCF) Web services.
Visual Studio. The more powerful approach is to use the Visual Studio IDE to design and create ECTs. Visual Studio 2010 includes a project template specifically for creating a BCS model. This is the approach that the Microsoft teams used in their solution.
Figure 7 displays all ECTs that were designed, developed, and published into the SharePoint Server metadata store for the rewards management solution. A developer can view the published ECTs from the SharePoint Server Central Admin console.
Facilitating Employee Rewards Management at Microsoft
Page 14
Figure 7. BCS ECTs for the rewards management solution
Deployment of the ECT Model Office 2010 and SharePoint 2010 provide two ways for deploying ECTs for download and installation on users' computers. The first deployment process is through SharePoint Workspace, which is installed on users' computers as part of Office 2010 installation. The second deployment process uses a tool to deploy the new solution on user's computers. In this new solution prototype, Microsoft teams use the second deployment process, as shown in Figure 8.
Facilitating Employee Rewards Management at Microsoft
Page 15
Figure 8. ECT deployment process through the BCS solution packaging tool This deployment method involves the following process: 1.
Developers publish ECTs from the Visual Studio IDE to the SharePoint Server metadata store. For Visual Studio to publish ECT metadata to the SharePoint Server metadata store, it must run at the same server where SharePoint 2010 is installed.
2.
Developers use SharePoint Designer to export the published ECT model into a file on a shared drive. The file is an XML file with an extension of .bdcm, which is called the Business Data Connectivity (BDC) model file. Developers can run SharePoint Designer from any other computer, as long as it can connect to the SharePoint server that stores the ECT model.
3.
Developers run the BCS solution packaging tool, available at http://code.msdn.microsoft.com/odcsps14bcspkgtool, which reads the model file and generates a Microsoft Visual Studio Tools for Office (VSTO) installation package.
4.
Users then run VSTO setup.exe to install the ECT metadata and assembly into the BCS local data store (SQL Server Compact). In addition, a subscription is created for each installed ECT for BCSSync.exe to synchronize ECT data periodically with the external systems. The default frequency of data synchronization is six hours.
From the users' perspective, this is a simple and transparent installation process. Users only click once when running VSTO setup.exe to install all ECTs exported from SharePoint Designer. The installation also immediately starts the BCSSync.exe process to run ECTs and download ECT data directly from external systems into the BCS local data cache.
Facilitating Employee Rewards Management at Microsoft
Page 16
Use of BCS APIs The BCS rewards management solution uses BCS APIs to retrieve, refresh, and update employee data, and to detect conflicts in data changes. For developers, understanding and using BCS APIs is essential to building a BCS-based solution. This section provides code examples to illustrate the use of BCS APIs at Microsoft. IEntity is the main BCS interface to ECTs that developers defined for the solution. IEntityInstance represents a specific data instance for the entity that developers wanted to get data from. Each ECT has a unique entity name within an entity namespace. Each entity instance is identified by a unique ID that developers defined when they defined the ECT. In this case, it is the employee number. Figure 9 shows code for retrieving all employee records from the local cache store.
Figure 9. Example code to retrieve all employee records from the local cache store As shown in Figure 10, updates to an employee record (an employee ECT instance) through the BCS update method for an entity instance will create an update operation in the BCS local queue. Thus, changes to 10 employee records will entail 10 operations queued in the local cache. The BCS runtime component processes one operation at a time.
Facilitating Employee Rewards Management at Microsoft
Page 17
Figure 10. Example code to update employee records to the local cache store ISynchronizationManager is the main interface for the BCS entity synchronization process. Each operation (IOperation) is a change to entity data that the BCS runtime will process. After processing, each data update operation will have one of the following statuses:
Pending. Indicates that the operation is pending because external systems are unavailable. BCS will automatically retry until it is successful or encounters an error.
In Error. Indicates that external systems failed to process the operation for such reasons as business validation error, data integrity error, and data conflicting error. A data conflicting error means that the entity instance data changed on the server side before this update operation.
Note: If an operation is processed successfully, the operation is removed from the queue, and the application will not be able to query the operation through Synchronization Manager. Figure 11 shows how the new solution queries the BCS cache for all pending or failed operations because of unavailable external systems, errors, or data conflicts.
Facilitating Employee Rewards Management at Microsoft
Page 18
Figure 11. Example code to query status for pending or failed operations from the local cache store Users sometimes want to refresh their local cache explicitly from external systems, instead of waiting for the BCS runtime to synchronize periodically. Figure 12 shows the BCS APIs to force data refresh for all ECTs.
Figure 12. Example code to force data refresh from external systems The first time an ECT is deployed and installed on a user's computer, the BCS runtime creates a subscription in its local data store. The BCS runtime uses the subscription to synchronize the ECT data with external systems. In the example code in Figure 12, ISubscriptionManager is the interface to get all subscriptions (ISubscription) created in the BCS data store. With this subscription object, the new solution can enable tasks that include the following:
Change synchronization frequency for the ECT.
Add more query parameters to filter data returned from external systems.
Get the status of the last refresh.
Request a refresh immediately (as shown in Figure 12).
Get the number of instances synchronized for the ECT.
Facilitating Employee Rewards Management at Microsoft
Page 19
BEST PRACTICES During the design, development, and deployment of the new rewards management solution, Microsoft IT gained practical and real-world experience with applications based on Office 2010 and SharePoint 2010 BCS technologies. The following best practices for using BCS technologies can help Microsoft customers who are looking for solutions to address similar business problems.
Spend Sufficient Time Designing ECTs ECT design is important to a successful BCS solution. ECTs directly affect the behavior of the BCS runtime. Badly designed ECTs can cause the BCS runtime to stop responding or throw an out-of-memory exception. Microsoft IT learned that lesson when it designed a big ECT that returned all business rules as a single instance for the ECT. The single instance contained all business-rule data as a byte array of about 40 megabytes (MB), which caused the BCS runtime to peak at a memory of 600 MB. Microsoft IT then redesigned the system into a few more ECTs based on a rule category. Each category ECT returns a number of entity instances equal to the actual number of rules. The BCS runtime synchronization process works much more efficiently on smaller ECTs than it does on bigger ones.
Use Strongly Typed Types and a Relatively Flat Structure as Much as Possible to Define ECTs Strongly typed types and a relatively flat structure work well with BCS. An organization may be able to use SharePoint Designer to generate ECTs, which will save significant development time. Creating ECTs with many entity attributes is a time-consuming and tedious process in Visual Studio.
Determine How Much Data Can Be Downloaded and Cached on a User’s Computer The amount of data that a user's computer can download and cache determines how an organization implements the Finder() method for an ECT. The data returned from external system calls sets the limit for the number of records that can be downloaded. If an ECT design needs more data than the limit, the external systems must be changed to meet the requirement. In the Microsoft rewards management solution, a user can download and cache the employee records for his or her organization only. Thus, two users 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 that the user is allowed to access. In other words, the data filtering occurs at the external systems, and the Microsoft ECT implementation does not need to filter the data based on users' permissions. If this is not the case for another organization's solution, that organization must consider additional design and development time for filtering ECT data for the local cache.
Handle External System Exceptions Carefully At some point, an organization's ECT implementations for the Finder() and SpecificFinder() methods must invoke external system methods to retrieve data. And that organization will need to catch and handle exceptions thrown from external systems in its code. However, the
Facilitating Employee Rewards Management at Microsoft
Page 20
organization will need to throw an exception back to the BCS runtime after it handles the exception. Otherwise, the BCS runtime will assume that the call to the external systems is successful and return zero rows of data. It will remove the data already in the local cache because it will attempt to synchronize with the server side. Microsoft IT does not want this behavior, and it does not want the local data cache to be deleted because of the external system exception. In one of the Microsoft ECT implementations, Microsoft IT must reference an assembly developed by another group to perform additional business logic and data transformation. During the deployment of the ECT—with or without SharePoint Workspace—Microsoft IT found that the referenced assembly is not deployed at the client computer as part of the ECT installation. As a result, BCS fails to run the ECT and fails to download the data. At the time of this writing, Microsoft IT is working with the BCS product team to determine the problem and find the right solution. The workaround is to add the third-party assembly into the Global Assembly Cache as part of the installation of VSTO.
Test and Debug ECTs The BCS runtime process runs the ECTs in the background, and ECTs cannot be debugged from Visual Studio when they are running on the clients. The Microsoft IT recommendation is to test and debug ECTs on the server side by creating external lists in SharePoint Server. When an administrator views and changes the content of an external list on the SharePoint List page, he or she runs the ECT code associated with the external list. The administrator can debug the ECT from Visual Studio running on the same SharePoint server.
Understand BCS API Entity Operation Mode On the client-side application that uses BCS APIs to manipulate ECT entity instances, an administrator can use four operation modes to manage ECT data. It is important to understand the meanings of the following operation modes because they affect how the solution gets the required data:
OperationMode.CacheWithoutRefresh. When this operation mode is used for an entity instance, BCS returns the entity instance from the cache. If the entity instance is not in the cache, BCS refreshes the cache from external systems and returns the cached copy. If the BCS cannot contact the external systems, it throws an exception.
OperationMode.CacheWithImmedicateRefresh. With this operation mode, BCS refreshes the entity instance in the cache first from external systems and returns the cached copy. If BCS cannot contact the external systems, it still returns the cached copy. If the entity instance is not cached and BCS cannot contact the external systems, BCS throws an exception.
OperationMode.Offline. With offline operation mode, BCS never contacts external systems, even if the cache contains no data. BCS returns the entity instance from the cache. If the entity instance is not there, BCS throws an exception.
OperationMode.Online. For online operation mode, BCS never uses local cached data and always contacts external systems to get a copy of the entity instance. If BCS cannot contact the external systems, it throws an exception.
Facilitating Employee Rewards Management at Microsoft
Page 21
CONCLUSION The core function of Microsoft IT is to understand business problems and use the right products and technologies to design solutions to solve those problems and bring business benefits. Office 2010 and SharePoint 2010 with Business Connectivity Services provide the foundation and framework for efficiently managing employee rewards and annual performance reviews at Microsoft. Managers and HR personnel who use the solution prototype can manage enterprise data from multiple external systems through the familiar Excel UI. The solution facilitates business data and process availability anywhere and anytime, hence increasing business productivity. The BCS synchronization infrastructure solves many problems associated with data copies, changes, and conflicts. One of advantages of BCS synchronization over other data synchronization frameworks is that an organization's solutions can embed business and data validation logic into the synchronization process. Another BCS benefit is the ability to synchronize composite data from multiple disparate systems through ECT design, instead of the point-to-point data synchronization that many other synchronization frameworks provide.
Facilitating Employee Rewards Management at Microsoft
Page 22
FOR MORE INFORMATION For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Order Centre at (800) 933-4750. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the World Wide Web, go to: http://www.microsoft.com http://www.microsoft.com/technet/itshowcase For more information about Business Connectivity Services on MSDN, go to: http://msdn.microsoft.com/en-us/library/ee556826.aspx To visit the Microsoft Business Connectivity Services Team Blog on MSDN, go to: http://blogs.msdn.com/b/bcs/archive/2009/10/19/overview-of-business-connectivityservices.aspx For a list of BCS class libraries, go to: http://msdn.microsoft.com/en-us/library/ee557766.aspx For information about the BCS solution packaging tool, go to: http://code.msdn.microsoft.com/odcsps14bcspkgtool The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. 2010 Microsoft Corporation. All rights reserved. Microsoft, Access, Excel, InfoPath, Outlook, SharePoint, SQL Server, Visual Studio, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners.
Facilitating Employee Rewards Management at Microsoft
Page 23
