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.

Related: Delivering BI through SQL Server and SharePoint

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.

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.

Click Add Trusted Data Connection Library and enter the location of your DCL. Add a description for the library and click OK.

Creating a Data Connection Using the Excel Client

Now that the Excel Services environment is set up and ready to go, we need to create and deploy content. First, to create dynamic Excel spreadsheets that connect to MOSS 2007 and SQL Server 2005, we need to create an .odc file to place in the new trusted DCL. Although you can create an .odc file from different Office applications, we will use the Excel client since we are going to create an Excel spreadsheet to publish later.

Open Microsoft Office Excel 2007. We’ll be working with the SQL Server AdventureWorks sample database; however, you can substitute your own data connection for this one. Click the Data tab, then From Other Sources, then From Data Connection Wizard. In the Data Connection Wizard, choose Microsoft SQL Server Analysis Services (or the data connection of your choice) and click Next. On the Connect to Database Server screen, enter your server name and click Next. On the Select Database and Table screen, select the AdventureWorks DW database and the AdventureWorks cube for the specific connection, and click Next.

The Save Data Connection File and Finish screen is the key to managing a large volume of .odc files. Fill in the information for your data connection and click Authentication Settings.

You have three authentication options to let Excel Services connect to your database, retrieve live data, and render it in your Excel report. Windows Authentication is the default selection. However, if you use this selection with the default SharePoint farm install, it will fail. The default SharePoint farm installation sets the access method of the global access control to trusted subsystem. SQL Server doesn’t trust Excel Services to validate the credentials requesting the data and won’t return any data. For Windows Authentication to work, you need to change the global access control to delegation mode (this includes a constrained Kerberos setup). This change requires running a simple Stsadm command, which I’ll discuss shortly.

The second option is single sign-on (SSO). To use SSO, you must enable it in Central Administration on the SharePoint server and set up an application ID for Excel Services. The third option for authentication is None. If you’re making a connection to an ODBC datasource or another data system that will use an authentication method embedded in the connection string, choose None, and the credentials in the string will be passed through to the data store.

Because we want to walk through changing the default global access control setting, select Windows Authentication and click OK, then Click Finish on the Save Data Connection File and Finish screen. We’re only creating an .odc file to upload into our DCL, so on the Import Data window, select Only Create Connection and click OK to save the new data connection file to the My Data Sources folder in your Documents folder (My Documents in Windows XP). Now we need to upload this file to our library, so that it’s available for use.

Uploading the .odc File to the DCL

Launch your SharePoint site and navigate to the DCL. On the library toolbar, click New to upload the .odc file. On the Upload Document screen, click Browse. Next, in the Choose file menu box, navigate to the My Data Sources folder, select the ODC file you just created, and click Open. On the Data Connections: Filename edit screen, click OK.

One distinctive characteristic of a DCL is that everything you place in it goes to a pending status. This constraint forces you to approve the data connections. Approve the new data connection file.

If you consider from a business perspective what we just did by uploading this simple file, you can see some of the benefits of MOSS 2007 and Excel Services integration. We not only made a data connection file that will no longer need to be created by every business or data analyst in the company, but also placed it in a secure library, the security of which you can manage at the root level or at the individual file level.

This approach provides several benefits. First, you won’t have 20 to 100 of these files floating around your network drives. Second, the data connection for a particular data store will be consistent throughout the organization. Third, if you use item-level security, the analyst will see only the data connections he or she needs to work with. Not only is this a security measure, it’s also a huge benefit to the analyst, who no longer has to search through numerous shared drives and files with cryptic names.

By making good use of the connection file’s metadata, a company can greatly enhance the management, security, and use of data connection files. Good metadata will let business analysts and other consumers of the connection file quickly find the correct file with the data connections they need.

Now that we have an .odc file in our trusted DCL, we need to make sure that we use this particular file when we use Excel to analyze the data. To do so, we’ll open Excel 2007 and add a data connection using the Existing Connections button, which you can find by clicking Data from the menu bar, Get External Data from the Office ribbon, then Existing Connections from the pop-up menu.

On the Select Data Source screen, which Figure 3 shows, navigate to the DCL by entering its URL, for example http://sitename/sites/reports/Data Connections. You’ll see the contents of the DCL.

Select your .odc file and click OK. On the Import Data screen, select a PivotTable Report, place the table in the Existing Workbook, and click OK. Select a couple of items from the PivotTable Field List to create a report. For the AdventureWorks example, we’ll use the Internet Gross Profit and Internet Sales Amount for the Values, and we’ll select Customer Geography, Country as our Row Labels.

Before we actually publish this report to our Reports Library, we want to name the PivotTable. Click PivotTable Tools, Options and give the Pivot- Table a distinct name. While you’re at it, you might want to add some visualization to the report. Select the values for Internet Gross Profit (cells B3:B8), then select Home, Conditional Formatting, Data Bars and select the blue data bars. Repeat the same process for the Internet Sales Amount (cells C3:C8), but choose the green data bars this time.

One last piece of formatting that will give the report a little nicer look is a PivotTable style. Click PivotTable Tools, Design and select Pivot Style Medium 16.

Now that the report is all dressed up, let’s give it somewhere to go. Click the Office Button, then Publish, Excel Services. In the Save As dialog box, change the location to Reports Library. If you don’t have a direct link to the Reports Library, paste in its URL and press the Tab key to display it. Next, configure Excel Services Options. When you click the Excel Services Options button, you have two tabs to choose from. Choose the first tab, which lets you select which workbook parts to publish.

You have three options. You can publish the entire workbook, which will render everything in the workbook in Excel Services. You can also publish individual sheets from the workbook. This option is useful if you’re doing calculations based on some proprietary business logic that’s located on a separate sheet. You can publish the result of that logic in your report without exposing the actual calculations that produce the report.

The option we’ll select for our example is to publish individual named items in the workbook. I named my PivotTable ISALES so that when I select Items in the Workbook, I’ll see that option under All PivotTables. Select the PivotTable you created, as Figure 4 shows. If we had created a report that had parameters (e.g., year), we could publish them as parameters and make them available to the users of this report by adding them on the Parameters tab. For our example, we do not need to add any parameters, so simply click OK.

Name your report and click Save. If you’re saving your report in the Reports Library, you’ll be prompted to choose a document type. There are two views in the Reports Library: One is called Dashboards and is used for the dashboard pages and the other is called Current Reports and is for reports. For our document, we want to choose Current Reports.

Two things happen when you click OK at this step. Obviously, the report will be saved to the library. Not so obviously, a copy of the report will be cached for display in Excel Services. Because we didn’t clear the Open in Excel Services check box when we saved the document, the report will automatically open in Excel Services. Figure 5 shows a report in full-screen mode; you can also view it as part of a dashboard page.

There’s one small problem left to deal with. In the Excel Services rendering of the PivotTable, click Update and select Refresh All Connections. You’ll get an error stating the data refresh failed.

Because the Excel Services server was set up in a farm, the Access Model defaulted to Trusted Subsystem when SharePoint was installed. You must set the Access Model to Delegation to allow the data in the new workbook to be refreshed. Change the Access Model by using the Stsadm command-line tool. Open a command prompt and navigate to C:Program FilesCommon FilesMicrosoft Shared Web server extensions12BIN. To change the settings we need, enter (on a single line)

stsadm -o set -ecssecurity -accessmodel delegation  -ssp sharedservices1

The only variable here is the SSP. If you’re working with a different SSP, be sure to replace sharedservices1 with that name. Otherwise, you should be able to type the command, press Enter, and get a response that says Operation completed successfully.

Stsadm jobs are timer jobs and might take a few moments to execute. If you want to force the jobs in the timer queue to run immediately, you can type the following command:

stsadm -o execadmsvcjobs

Important: After the job has executed, you must also run iisreset.exe, as follows:

C:/>iisreset /noforce

Now the access model will be in Delegation mode, and your report should execute and refresh.

A Robust Solution

Microsoft says it invests more than $700 million each year in research and development in its Office programs, servers, and services. There can be no doubt that the company spent a good amount of that effort on Excel Services. As a management tool, it’s a robust, centrally managed document system with individual file-level security. As a delivery tool, it’s a high-end Excel rendering engine that maintains a high level of visual fidelity and doesn’t require Excel 2007. As a development platform, the new EWS provides a reusable framework that provides a foundation for custom development with Excel data.

All of these things combine to make the first generation Excel “server.” It’s a tool many developers will agree was far too long in coming.