Chapter 2. Configuring and Using Excel Services in SharePoint 2010

Excel Services is a shared service application within SharePoint 2010 that allows for the sharing and presentation of Excel spreadsheets and workbooks within SharePoint via the web browser. Excel Services was first introduced in Microsoft Office SharePoint Server 2007 and continues to evolve within the 2010 platform. A SharePoint Enterprise license is required to deploy and implement Excel Services.

Excel Services may be used to present business data to the enterprise and can be considered a piece of the business intelligence (BI) puzzle. However, because it is not one of the main tools provided by the integration of SharePoint and SQL Server, this chapter covers the basics for configuring and using Excel Services.

Excel Services is actually made up of three core components: Excel Calculation Services, Excel Web Access, and Excel Web Services. The Excel Calculation Services component is the main component and enables the loading and calculations of the spreadsheets, and the Excel Web Access component provides the delivery mechanism for the Excel workbooks. The Excel Web Services component enables customizations of Excel solutions by providing web methods hosted within SharePoint that can be exploited by custom-coded applications.

Configuring Excel Services

The Excel Services configuration is performed at the service application level within SharePoint Central Administration. Managing Service Applications provides access to all the service applications running on the SharePoint farm, including Excel Services, as shown in Figure 2.1. Managing the Excel Services Application provides various groups of settings, as shown in Figure 2.2. This section covers each of these areas.

Figure 2.1. Managing Service Applications in Central Administration.

Figure 2.2. Managing Excel Services Application.

Global Settings

In the Global Settings section, you can modify and configure overall settings for the Excel Services application, including security, session management, and various cache settings.

Security

The Security settings determine how Excel Services handles authentication, as shown in Figure 2.3. The File Access method is used when accessing trusted Excel Services locations that are not hosted within SharePoint. If you set the method to Impersonation, Excel Services impersonates the current user when accessing the non-SharePoint location. To access the locations using the service account that is running Excel Services, just set the method to Process Account.

Figure 2.3. Security settings of the Excel Services application.

The Connection Encryption setting determines whether the communication between Excel Services components within SharePoint and the user’s local machine should require encryption. Requiring encrypted communication ensures that the Secure Sockets Layer (SSL) protocol is being used and data is secured. Leaving this option as Not Required leaves your data less secure.

Allowing cross-domain access enables Excel Services to render Excel workbooks and components that are located on different domains. These locations still need to be registered as trusted file locations, which are explained in the next section.

Load Balancing

The Load Balancing settings determine how the Excel Calculation Services (ECS) feature manages its processing of user requests. You have three options: Workbook URL, Round Robin with Health Check, and Local.

The Workbook URL option ensures that the same ECS process handles requests from the same workbook at all times regardless of the user. This allows for a workbook to be loaded into memory only once. When ECS is running across multiple web front ends, this method provides a more efficient use of hardware.

If you select the Round Robin with Health Check option, each request to open a workbook goes to the next ECS in line. Even if another user selects the same workbook that someone else has already opened, the next ECS opens it again. This balances the requests and limits central processing unit (CPU) and input/output (I/O) strain. The health check portion ensures that CPU and I/O usage do not overpeak, which could occur when opening workbooks with long-running calculations. This option is good for balancing traffic and server resources.

Selecting Local ensures that the workbook is processed by the same ECS that received the open workbook request. To use this option, ECS must be running on each web front end. This option provides performance to the end user by streamlining server communication; however, in this configuration, the same workbook could be open by multiple ECS processes.

Session Management

The Session Management setting is the maximum number of sessions in which a user can invoke Excel Services, namely the ECS. Each open workbook instance produces a session. If the maximum number of sessions is reached, older sessions are removed such that new ones can be generated. Typically, each user should have just a handful of sessions at one time. The default of 25 maximum sessions does seem a bit excessive. To keep your SharePoint farm performing well right from the start, reduce the maximum session setting to 10.

Memory Utilization

The Memory Utilization settings assist in configuring the physical memory consumption by Excel Calculation Services. There are three settings: Maximum Private Bytes, Memory Cache Threshold, and Maximum Unused Object Age.

The Maximum Private Bytes setting determines how much of the server memory that the ECS process may use exclusively if needed. The default of –1 indicates that up to 50% of the server memory can be allocated. Again, this default seems a bit excessive and could reduce memory available for other processes. If your organization will use Excel Services heavily, the default might be warranted. Otherwise, you might want to start out with a number closer to 25% of the server memory. So if you have the minimum of 8GB, the setting here should be around 2048MB. You can always increase this value if users complain of nonresponsiveness.

The Memory Cache Threshold determines what percentage of the available memory (determined by the Maximum Private Bytes) can be used to maintain inactive objects. If there are many users using a small number of workbooks, it is important to keep the cache up so that repeated instantiation of similar objects is not necessary and user experience is very responsive. The default is 90%, which might be valid if Excel Services is heavily used within your organization with a small amount of distinct workbook instances. Otherwise, you can ensure that at least 25% of the maximum memory can be used for new sessions and objects by reducing the threshold to 50% to 75%.

The Maximum Unused Object Age determines how many minutes need to transpire before unused objects are released. The default entry of –1 indicates that there is no limit. Depending on the usage and performance of Excel Services in your farm, you might want to modify this setting such that the maximum age is only a few days. To account for weekends, set this value between 4320 and 5760 minutes (3 to 4 days).

Workbook Cache

The Workbook Cache settings enable you to configure how temporary files used in rendering and processing workbooks are handled on the server. It is important to take note of these options because they affect both memory and disk usage.

The Workbook Cache Location defaults to a nonentry that tells Excel Services to use the system temporary directory for writing temporary files to disk. This is probably not the best option. Having too many disk hits on your system drive could cause performance issues within your farm. The system drive is being used to run the web front ends and other services, and so it is warranted to use a separate drive for caching the workbooks. Doing so keeps the disk I/O isolated to the specified drive and reduces the number of hits to where the system is operating. You can use the separate drive for other purposes, too, because the workbook cache uses only the amount of space allocated by the next setting: Maximum Size of Workbook Cache.

The Maximum Size of Workbook Cache setting determines how many megabytes (MB) of disk space can be used for the temporary workbook files. The default value of 40960 equates to 40GB of space. This is a large amount of disk space but may be warranted depending on the size and complexity of the workbooks being used by Excel Services. If you have an allocated drive for the workbook cache, set the maximum size to 75% to 80% of the drive capacity. If server disk space is limited, set this value to 25% to 50% of the available space on the drive that houses the workbook cache location.

The Caching of Unused Files check box determines whether files should remain in memory if they are no longer being used. Depending on the memory available on your web front-end servers, you might want to uncheck this box. Although this could reduce the performance of Excel Services, if server memory is limited, keeping the box checked could affect overall SharePoint performance.

External Data

The External Data settings determine how external connections are managed and authenticated. The Connection Lifetime value determines how long a connection to an external system should remain opened. The default is 1,800 seconds, which equates to 30 minutes. Depending on external data usage and Excel Services usage, this value may be reduced to limit potential pegging of the system housing the external data.

The Unattended Service Account is used to authenticate with external systems to retrieve external data accessed by Excel Services. You must set up this account using the Secure Store Service in SharePoint. The application ID for the account should be entered into the Application ID entry within the External Data settings.

Trusted File Locations

The Trusted File Locations determine the locations where Excel workbooks may be loaded from within Excel Services, which include SharePoint locations or locations accessible via Universal Naming Convention (UNC) or Hypertext Transfer Protocol (HTTP). The default location entry is configured as a SharePoint location set to http://, which means that Excel Services can render any workbook within your SharePoint instance. (This is also because the default entry states that children locations are trusted.) When editing the default location entry or creating a new trusted file location, you must configure many settings.

Location

The Location section defines the actual location that should be trusted. Enter the address or path into the Address setting. The type of location can be SharePoint, UNC, or HTTP. Select the appropriate type in the Location Type area. The Trust Children check box determines whether folders, directories, or libraries located underneath the entered location should also be trusted (so, in other words, whether everything under the path inherits the trust).

Session Management

For each location, you can configure Session Management settings that determine the tolerance of the Excel Services requests and interaction with workbooks in the specified location, as shown in Figure 2.4.

Figure 2.4. Session Management settings of a trusted file location.

There are several timeout settings. The Session Timeout is the overall setting that determines how long an inactive ECS session can remain open. The Short Session Timeout is a threshold for new sessions that never receive any activity. This could happen if an error occurs or if a user navigates through pages and never interacts with the workbook. The New Workbook Session Timeout determines the number of seconds in which a new workbook session can remain open while being inactive.

The Maximum Request Duration is similar to a timeout setting but is a stop measure to prevent long-running requests or processes. This could happen if requests get hung or become unresponsive. Similar to this setting is the Maximum Chart Render duration, which determines how long the ECS should spend rendering any chart within a workbook. The default is 3 seconds. So, if any complex charts take several seconds to render, you might need to increase this value to somewhere between 5 and 10 seconds.

Workbook Properties

In the Workbook Properties section, you can limit the size of the workbook and any chart or image that may be embedded within the workbook. The settings are in megabytes and default to 10 and 1, respectively. Although typically workbooks and images should be well under these settings, the workbook properties are used to limit or manage network traffic and bandwidth from the trusted file location.

Calculation Behavior

The Calculation Behavior settings determine how calculations are handled within the workbook when being rendered via Excel Services.

The Volatile Function Cache Lifetime setting determines how long Excel Services waits before it recalculates worksheets that contain volatile functions for new sessions. The volatile function cache stores the results of previously calculated values such that when a new session is activated the values are not recalculated (until this lifetime setting is reached).

The Workbook Calculation Mode determines how the workbooks are recalculated when being rendered by Excel Services. The default is File, which means however the recalculations are configured within the actual workbook is how Excel Services recalculates the workbook. The other settings, which configure manual calculations or automatic calculations, override the workbook settings.

External Data

The External Data section is used to configure how the workbooks on the trusted location interact with data from external sources, as shown in Figure 2.5. The first setting, Allow External Data, determines whether external data can be used at all in the first place. The default is to allow data connection access from trusted data connection libraries and any connections embedded within the Excel workbook. To manage workbook data connections appropriately, it is best to remove any embedded connections and only use the connection files that have been created within trusted data connection libraries. This reduces overall troubleshooting and administration of connectivity to external sources (by allowing them to be centrally managed).

Figure 2.5. External data settings of a trusted file location.

The Warn on Refresh option notifies users that external data is being refreshed within the workbook. It is a good idea to leave this checked. If data changes after the refresh, users need to understand why. If there are any errors within the external data retrieval, leaving the next option (Display Granular External Data Errors) checked propagates the error to the workbook and notifies users of an issue.

The Stop When Refresh on Open Fails setting prevents workbooks from being opened if a refresh of external data needs to occur during the opening of the workbook and the retrieval of the data is not successful. This prevents older cached values from being displayed. The explanation in the setting is a bit confusing as to users having access to open the workbook. If they don’t have access to open the workbook, the workbook will not open regardless of whether the refresh of external data fails.

The External Data Cache Lifetime determines how long Excel Services should use cached data results from external data sources. This is broken out into two refresh settings: Automatic and Manual. The defaults are set to 300 seconds, equating to 5 minutes. If the data does not change too frequently, increasing this number limits the number of queries that need to occur against the external system. This reduces network traffic and bandwidth usage. So, for example, if the external data source performs updates only overnight, the same queries throughout the day produce the same data results (and so 5 minutes or 5 hours does not make a difference from a data refresh perspective).

The Maximum Concurrent Queries per Session setting determines how many data requests can occur concurrently within a given session. The default setting of five is probably good for most organizations. Standard workbooks accessing external data typically contain one to three data requests, whereas more complex workbooks could require three to five requests that occur simultaneously. It all depends on the actual use and requirements of the workbook itself.

The Allow External Data Using REST option is unchecked by default. This option determines whether the REST application programming interface (API) can be used to refresh the external data within the workbook. The REST API, a new feature in SharePoint 2010, allows access to Excel Services objects and resources.

The User Defined Functions option determines whether the Excel workbooks in the trusted file location can run user-defined functions. User-defined functions are created by coding functions within managed code that gets built as an assembly (dynamic linked library, or DLL) deployed to the SharePoint farm.

Trusted Data Providers

Trusted data providers determine what types of data connections can be implemented within Excel Services. The most common data providers are provided out of the box. When editing or adding a new data provider, you have only a few settings, as shown in Figure 2.6.

Figure 2.6. Trusted data provider settings.

The Provider ID is the data provider identifier. This is the value used within a data connection string. When creating a new provider entry, review sample data connection strings for the provider to determine how it is identified.

Three types of providers are supported: OLE DB, ODBC, and ODBC DSN. Determine what type of provider is being added and select the appropriate option.

The Description is an optional entry used to identify the trusted data provider. When adding a new provider, it is probably a good idea to explain what it is and why it was added.

Trusted Data Connection Libraries

The Trusted Data Connection Libraries are the locations of the data connection libraries that Excel Services is allowed to use to obtain external system connectivity information. Adding a new entry is as simple as providing the URL address of the data connection library, as shown in Figure 2.7.

Figure 2.7. Adding a trusted data connection library entry.

User-Defined Functions

If user-defined functions are going to be used within Excel Services workbooks, the assemblies containing the managed code for the functions need to be registered with Excel Services.

Adding a new user-defined function entry is performed by entering the assembly details, as shown in Figure 2.8. It is recommended to strongly name the assemblies and deploy them to the global assembly cache (GAC) within the farm.

Figure 2.8. Entering assembly details for UDF support.

Using Excel Services

Using Excel Services is easier than configuring the service application. The overall process is creating or uploading Excel documents to a specified (and trusted) library and then using web parts to display the Excel workbooks on pages within a site.

Creating a Library for Excel Workbooks

For each site that may display Excel workbooks, you should create a library to house the documents. Although this can be a standard document library, the report library is set up to handle Excel documents (.xlsx).

To create a library for your Excel workbooks, follow these steps:

1. Navigate to the site collection or site in which you want to display Excel workbooks.
2. From the Site Actions menu, select More Options. The Create dialog appears.
3. Select Library in the Filter section on the left side of the dialog.
4. Select the Report Library template, as shown in Figure 2.9.

   Figure 2.9. Creating a report library for Excel workbooks.

   

5. Enter a name for the library. (Optionally, click More Options to configure other settings.)
6. Click Create.

Adding Excel Workbooks to the Library

Excel workbooks may be added to library by creating a new document or uploading an existing one. Navigating to the library and selecting the Documents tab from the ribbon provides the options available.

To create a new document, follow these steps:

1. From the Documents tab of the ribbon, select the pull-down from the New Document button, as shown in Figure 2.10.

   Figure 2.10. Creating a new Excel document.

   

2. Select Report. The New Report screen appears.
3. Enter the name, title, and description of the report.
4. Optionally, enter the owner of the document.
5. Select the report category and status using the drop-downs.
6. Click OK.

The new Excel document is created within the library.

To upload a document, follow these steps:

1. From the Documents tab of the ribbon, click the Upload Document button. The Upload Document dialog appears.
2. Click the Browse button and locate the Excel document.
3. Select the file and click Open.
4. Optionally, enter version comments.
5. Click OK.

The Excel document is uploaded to the library.

Accessing Excel Workbooks from the Library

Users may simply navigate to the library and click the Excel document to open the workbook. The workbook is launched using the SharePoint Excel Viewer and renders within the browser. It is presented in a read-only fashion and is designed to display data and calculation results within the spreadsheets of the workbook.

Using the Excel Web Access Web Part

The other common reason to access Excel workbooks stored within a SharePoint library is to display them on a page using web parts. The web part that enables you to render Excel workbooks from a trusted file location using Excel Services is called the Excel Web Access web part.

To add the Excel Web Access web part on a page, follow these steps:

1. Navigate to the page that you want to use for the Excel Web Access web part.
2. From the Site Actions menu, select Edit Page.
3. Click the Web Part button from the Insert tab on the ribbon.
4. Select the Business Data category, and then select the Excel Web Access web part, as shown in Figure 2.11.

   Figure 2.11. Adding the Excel Web Access web part.

   

5. Click Add. The web part is added to the page.

After the web part has been added, the selected workbook information is rendered within the web part contents. The web part needs to be configured to access an Excel workbook.

To configure the Excel Web Access web part on the page, follow these steps:

1. Select Edit Web Part from the Web Part drop-down menu or click the Click Here to Open the Tool Pane link. The web part tool pane is displayed, as shown in Figure 2.12.

   Figure 2.12. Excel Web Access web part tool pane.

   

2. Enter the location of the workbook. Click the ellipsis (...) button to navigate through the SharePoint site and locate the workbook location.
3. Enter a named item if you want to show only a specific piece of the selected workbook.
4. Modify the other options as desired.
5. Click OK.

The web part renders the workbook selected.

Summary

This chapter covered the configuration and usage of Excel Services within SharePoint 2010. Excel Services can be exploited to provide business data interaction to end users using a familiar end-user tool. Many configuration settings affect the behavior and usage of Excel Services within your SharePoint farm, and each one should be carefully analyzed.

Best Practices

The following are best practices from this chapter:

• Requiring encrypted communication ensures that the SSL protocol is being used and data is secured. Leaving this option as Not Required leaves your data less secure.

Allowing cross-domain access enables Excel Services to render Excel workbooks and components that are located on different domains. These locations still need to be registered as trusted file locations.

• The Workbook URL load-balancing option provides a more efficient use of hardware when ECS is running across multiple web front ends, where the Round Robin with Health Check option assists in limiting traffic and CPU utilization.

To keep your SharePoint farm performing well right from the start, reduce the maximum session setting from 25 to 10.

• Set the value of Maximum Private Bytes to a number closer to 25% of the server memory if your organizational usage of Excel Services is not considered heavy.

• Reducing the Maximum Cache Threshold to 50% to 75% ensures that at least 25% of the maximum memory can be used for new sessions and objects.

• Set the Maximum Unused Object Age to a value between 4320 and 5760 minutes (3 to 4 days). This takes weekends into account.

• If you have an allocated drive for the workbook cache, set the maximum size to 75% to 80% of the drive capacity. If server disk space is limited, set this value to 25% to 50% of the available space on the drive that houses the workbook cache location.

• The Caching of Unused Files check box determines whether files should remain in memory if they are no longer being used. Depending on the memory available on your web front-end servers, you might want to uncheck this box. Although this could reduce the performance of Excel Services, if server memory is limited, keeping the box checked could affect overall SharePoint performance.

• The Connection Lifetime value may be reduced to limit potential pegging of external systems (depending on external data usage and Excel Services usage).

• To manage workbook data connections appropriately, it is best to remove any embedded connections and only use the connection files that have been created within trusted data connection libraries. This reduces overall troubleshooting and administration of connectivity to external sources.

• Increasing the External Data Cache Lifetime setting can help reduce network traffic and bandwidth usage if the data on the external system does not change too frequently.

• User-defined functions should be compiled into a strongly named assembly that is deployed to the GAC within the SharePoint farm.