Chapter 11: Configuring Office SharePoint Server Excel Calculation Services

Excel Calculation Services is part of Microsoft's business intelligence (BI) platform provided by Microsoft Office SharePoint Server 2007 Enterprise Edition. Excel Calculation Services supports the server-side loading, recalculation, and rendering of Office Excel 2007 workbooks in a browser-based version. It addresses a problem faced by many organizations in which users work with large Excel workbooks that contain complex formulas to generate data values. When the files are stored on a file server, these workbooks can take a long time to load and recalculate on a workstation. With Excel Calculation Services, the workbook file is stored in a SharePoint Server document library, thereby allowing Excel Calculation Services to calculate the results on the server. Excel Calculation Services then renders the results in a Web browser by generating an HTML version of the spreadsheets and charts in the workbook. This functionality allows users who do not have Office Excel 2007 installed on their workstation to view the information. In addition, Excel Calculation Services can provide a single location where users work with spreadsheets, preventing the need and desire to share spreadsheets via e-mail. This functionality assists some organizations with regulatory compliancy and presents a single version of the truth.

Getting Started with Excel Calculation Services

Excel Calculation Services is part of a set of components known as Excel Services that allow server-side access to Office Excel 2007 workbooks. These components include Excel Web Access for rendering and displaying workbook contents, Excel Calculation Services for recalculating formulas in workbooks and executing server-side queries to external data, Excel Calculation Services Proxy for managing load balancing of service calls in a multiserver environment, and Excel Web Services for providing programmatic access to workbook data from remote clients. This section reviews some of the key planning issues related to implementing Excel Services.

Key Planning Concepts

Excel Services requires that you install the Enterprise Edition of SharePoint Server or upgrade to this edition from the Standard Edition. Planning an Excel Calculation Services implementation begins with assessing the total number and size of workbooks to be served by Excel Services and the resource requirements of processing these workbooks. Each workbook that is accessed uses RAM and processor resources for calculating and rendering the workbook, as well as RAM and disk space for caching its output. Adding more resources to the server and load balancing Excel Services across multiple servers can improve scalability and performance.

Office Excel 2007 workbooks can be stored either in SharePoint Server document libraries, in shared folders on file servers, or on any standard Web server that provides access to the workbook through a URL. Storing workbooks in document libraries is the most secure option, whereas using file shares connected to Excel Calculation Services will require the least retraining of your users.

Implementation Checklist

The key steps involved in planning the implementation of Excel Calculation Services are summarized in this section. Each is covered in more detail later in the chapter.

• Enable Excel Calculation Services in your farm Before working with Excel Calculation Services you must configure the service to run on one or more servers in your SharePoint Server farm. Your options are as follows:

  1. Single-server or small farm: This model is appropriate for relatively small workbooks that are accessed infrequently.

  2. Dedicated server: This scenario works well for larger, complex workbooks or those that are accessed frequently throughout the day. This is the most common configuration for businesses that are implementing Excel Services.

  3. Load-balanced servers: Having multiple Excel Services servers is ideal for organizations that need fault-tolerance in their Excel Services environment or that are accessing workbooks programmatically through Excel Web Services.

• Determine storage locations Excel 2007 workbooks can be stored in any document library in a SharePoint site or can be stored in a file share or on a standard Web site. SharePoint document libraries offer the greatest security and flexibility, whereas file share storage gives you the convenience of not having to move existing workbooks off their current server.

• Define trusted file locations Excel Calculation Services only loads and processes workbooks stored in document libraries or non-SharePoint sources that are specifically configured as trusted locations. Keep the number of these trusted locations to a minimum.

• Configure security on document libraries Most users require only View Items access to files in document libraries in order to view the output from Excel Calculation Services. Other users require Add and Edit access to publish and modify files through Office Excel 2007.

• Publish Excel 2007 workbooks Using the Publish feature in Office Excel 2007, users can upload workbooks with specific options that control which parts of the workbook are displayed in the browser. Consider which sheets or named ranges within workbooks should be displayed and whether users will be allowed to input values for recalculating results.

• Configure a SharePoint Server to display workbook content Using dashboards and Excel Web View Web Parts, you can configure sites to display targeted portions of workbook content to users and decision makers. Design your dashboards around specific business goals, and display data that give decision makers a snapshot view of performance or progress toward those goals.