Return to Newsletter Contents...

New From SharePoint:  Excel Services

by: Michael Mullin, BI Consultant

The Business Case for Excel Services

Excel is one of the world’s most popular programs. It is used for many purposes, some intended and some invented by the users. It provides users with a visual access to their data that most programs can not match and its interface is intuitive and ‘real world’ even for technically challenged users. It can read many data formats and many programs support exporting data to it. No wonder it is so popular.

But the program is centered on a single user. You create a workbook, and if another user wishes to use it, you send a copy of the workbook. This has several disadvantages. First is the proliferation of workbooks with no way to track them. If a workbook is changed how can you be sure all the copies were updated? And did anyone change any of the formulas in their workbook?

Most complex workbooks are going to access external data; perhaps it will read data in from a SQL Server database. To ensure the data is always current, this data could be retrieved every time the workbook is opened, but then it can only be opened on a computer connected to the network. Or the data could be read in from the database and saved with the spreadsheet. This would allow the workbook to be opened on any computer, but the data will get stale. How can you be sure someone is not making decisions with stale data?

Furthermore, that data can be sensitive and proprietary. You may not want just anybody to be able to cast their eyes over it. For that matter, the formulas can be sensitive. Wouldn’t you like to know how your competitors calculate their costs?

A business is likely to have hundreds of spreadsheets all containing various business models. These models can be quite complex. Therefore a lot of effort will be invested in developing them. Frequently these different models have to calculate the same values. One can easily understand how Marketing, Finance and Materials Handling might all need a cost model. It is cheaper to develop it once and share it among the various departments than to have each department develop their own. This highlights another problem, if each department creates their own worksheet for computing costs will they get the same answer? If there is only one spreadsheet, then everyone will be working with the same values.  Microsoft calls this concept the “Single Version of Truth”.

These problems can be addressed with Excel Services.

 Excel Services

When describing Excel Services it is easiest to start by describing what it is not. It is not a spreadsheet on a server nor is it a collaboration. SharePoint can be used to provide collaboration services while developing a spreadsheet, but these services have been available for sometime. It can also provide check-in/check-out, versioning and document approval services, but this is not Excel Services.

Excel Services allows the spreadsheet author to publish his spreadsheet to the server where multiple users can view it. But the users do not open the workbook; they are presented with a read only in memory instance of the worksheet. Excel Services provides each user with a separate instance of the worksheet which is independent of any other instance. This allows any number of users to be viewing the worksheet at one time, entering data, setting filters, sorting tables and other operations without disturbing another user’s worksheet. I say any number of users because Excel Services is scalable.

A key phrase in the foregoing statement is “read only”. The spread sheet is read only in the sense that the underlying worksheet that was published to SharePoint can not be altered. The user is allowed to enter new data, if the author tells SharePoint to allow data entry, but only in areas and cells designated by the author. The point is the user can fully use the workbook, but can not alter it.

When publishing a workbook to the server, the author has the ability to control which parts of the spreadsheet can be viewed and can limit the way in which the user can manipulate the spreadsheet. The author can allow the user to enter data, for instance to run ‘what if’ scenarios. This data entry does not change the underlying spreadsheet.

A user’s ability to view or edit a workbook can be controlled at several levels: workbook, worksheet, or named object. (Named objects would include tables, charts, a range of cells or even one cell.)  Therefore, it is possible to hide formulas and to prevent the formula form being edited. The Excel workbook ceases to be an ad hoc worksheet which anyone can alter, and becomes more of an application created an author and used by a controlled set of users.

Publishing a workbook does not require any great effort on the part of the author. The author continues to use the desktop Excel application as he has always done. When ready to publish, the author uses the publish to Excel Services option provided by Excel 2007. Once published, a workbook can be edited with the desktop application too.

Excel Services is comprised of three services. The base service is Excel Calculation Services. Obviously this service runs the calculations contained in the workbook, which means the calculation is performed on the server and not on the client machine. It also loads the workbooks, maintains connections to external data sources, and maintains session state. It is the heart of Excel Services.

The data in a published workbook can be accessed via a web user interface or programmatically via an API. Excel Web Access Service provides the web interface. If a user opens a workbook, Excel Web Access will provide a web version of the spreadsheet for the user to view and manipulate. However, this is a true web page, not an ActiveX control. Nothing has to be installed on the client machine, not even Excel.

Excel Web Service, the third service in the group, provides a web service which provides an API that developers may use to retrieve data from a workbook. However, data retrieval through the Excel Web Service is subject to the same author imposed restrictions as the Excel Web Access Service. This service allows developers to access Excel Workbooks from other applications and take advantage of Excel’s calculating prowess. This can provide great flexibility since the workbook being accessed can be edited and changed by the author. Formulas can be modified and as long as the end points accessed using the Excel Web Access Service are maintained, no developer intervention is needed.

Hopefully, this brief overview gives you an idea of what Excel Services is and why you might want to use it. Take a few moments and consider the number, complexity and importance of the Excel worksheets in your organization. You may find the Excel Services can help solve variety of data integrity problems.

Go to Top  |  Return to Newsletter Contents