Introducing Excel Services for SharePoint

Excel Services is a key new business intelligence (BI) feature—not of Excel per se, but of Microsoft Office SharePoint Server (MOSS) 2007 Enterprise Edition—that integrates data-analysis capabilities into MOSS. Excel Services lets businesses share Microsoft Office Excel spreadsheets in a SharePoint environment, while centralizing the Excel processing, security, and presentation in MOSS. I’ll introduce you to Excel Services, explain how to configure it and set up the necessary data connections, then show you how you can use it to do BI reporting and collaboration in MOSS 2007.

Excel Services Basics
Excel Services consists of three components: Excel Calculation Services (ECS), Excel Web Access (EWA), and Excel Web Services (EWS). ECS performs all the calculations and data refreshes, maintains the user sessions, and lets you publish the Excel look and feel to an end user who lacks the Excel client. EWA is the client side of Excel Services. It consists of the full-page report viewer and a Web Part viewer for the Excel spreadsheets. EWS is a MOSS 2007 Web service that developers can use to develop custom applications for Excel Services.

Excel Services, along with the Business Data Catalog (which provides business-data search functionality) and InfoPath Forms Services make a strong case for the additional capital investment required to upgrade from the standard edition of MOSS 2007 to the Enterprise version. You can find more information about licensing and the Enterprise-version features at office.microsoft.com/en-us/sharepointserver.

Excel Services Prerequisites
We’ll assume that a default Report Center site has been created using the Report Center site template, and use that as the baseline for our discussion and demonstrations. You’ll also need SQL Server Analysis Services on the SQL server (with all logins configured) and you’ll need to download and deploy a sample AdventureWorks DW database (www.codeplex.com/MSFTDBProdSamples).

Configuring Excel Services
To get started using Excel Services, you’ll need to perform some configuration steps. After you’ve installed, configured, and licensed the SharePoint instance on your server farm, you must also set up the Shared Service Provider (SSP) in MOSS 2007’s Central Administration console.

To enable the shared service, you need to set up a trusted file location for a document library. The location can be a SharePoint document library, Universal Naming Convention (UNC) path, or Web site, but it must be explicitly trusted before ECS can access it. This document library is more processing intensive than a typical SharePoint document library. It stores documents and calculates and renders as a Web page every spreadsheet that’s requested. Because of the potential for resource management problems, the site collection administrator for the SSP must explicitly set up each document library that runs Excel Services reports as a trusted file location. He or she also manages the shared resources on the SharePoint farm.

You set up a trusted file location in Central Administration. Click the Shared Services Provider link listed below the Shared Services Administration link as Figure 1. The default name for the first provider is SharedServices1.

Once Shared Services opens, click Trusted file locations in the right column under Excel Services Settings. Doing so opens the Excel Services Trusted File Locations page. Click Add Trusted File Location to add your document library to the trusted locations.

In the Location section, add your document library URL to the Address field. Using the default Report Center site template, this location would be http://sitename/ReportsLibrary. If you include subdirectories in your document library, be sure to select the Children trusted check box. The default Location Type is Windows SharePoint Services.

You can change the default selections in the Session Management and Workbook Properties sections. The default settings are sufficient for our demonstration. In the Calculation Behavior section, choose Automatic to update all dependent values whenever a value changes.

There are a few changes to make in the External Data section. First, if you’re securing a production environment, choose Trusted data connections libraries only. This selection ensures that each spreadsheet uses an approved data connection in a trusted location. (I discuss how to set up those trusted data connections in a moment.) None is the most secure option because it doesn’t allow any connections, but it’s also the least usable option. Trusted data connection libraries and embedded lets spreadsheet authors embed any data connection they want; this is the least secure option.

Next, clear the Refresh warning enabled check box unless you enjoy seeing a warning each time external data is refreshed. For our example, we’ll leave the remaining defaults. Note that the last section, User- Defined Functions, lets you allow user-defined functions (UDFs). Click OK after you’ve completed your selections to create the Trusted File Location.

Now we need to set up the trusted data connection library (DCL), where we store the Office Data Connection (.odc) file for our Excel Services workbooks to connect to the SQL Server database. This is much simpler than setting up the trusted file location.

Above the Excel Services Trusted File Locations header, click Shared Services Administration: Shared-Services1 (Figure 2 shows this link) to return to the Shared Services Administration page. Next, click Trusted data connection libraries in the Excel Services Settings section of the page.

Continue to page 2