Business Intelligence (BI) with Excel Services in SharePoint 2010

  • Article

This is the third article of a series to review the following five BI vehicles in SharePoint 2010:

First introduced in Microsoft Office SharePoint Server 2007, Excel Services provides server-side calculations and browser-based rendering of Excel workbooks. On the right is the architectural concept of Excel Services. In the core is Excel Calculation Service (ECS) which is the calculation engine. Excel Web Access (EWA) is a web part which displays and interacts with a workbook. The access to methods and objects is through APIs provided by Excel Web Services (EWS) hosted in SharePoint Services.

Excel Services allows a user to publish a workbook or selected spreadsheet cells as a webpage. Because the content is published without exposing the underlying business logic, intellectual properties are protected and as well applied in a standardized/consistent fashion. The motivation is to publish "one version of the truth" such that users always view a consistent set of values if published as read only, and results derived on business logic that is consistently defined. In a large organization, consistency and synchronicity are key productivity enablers which are many SharePoint features are about. Both Excel 2010 and Excel 2007 have the ability to publish an Excel workbook to a SharePoint site. 

 By naming selected cells in an Excel workbook, an author can then indirectly letting a user change the cell values and apply them as parameters of an analytical model. For example, as shown on the left, a user provides interest rate, loan period, and loan values in the Parameters Task Pane to calculate a monthly mortgage payment. While any of the three parameters varies its value, the derived monthly mortgage payment changes accordingly. Since the business logic, i.e. formulas, embedded in these cells are not exposed; Excel Services can display the results with the business logic implemented in a consistent and protected way. In this example, the mortgage calculation happens to be a well-known formula and the protection may appear trivial. However in a production application, this may be a work order estimate or a marketing program discount rate calculator. In this case, Excel Services not only can protect the underlying business logic perhaps based on proprietary knowledge, but as well ensure the logic is applied in a consistent and predictable fashion.

In other words, in addition to publishing one version of the truth as read-only data like KPIs, charts, and tables, Excel Services can also allow a user to enter values as parameters to a protected analytical model and carry out what-if analysis.

(A cross-posting from Microsoft SharePoint Experts Blog)