Excel Services – Key Scenarios
We targeted three scenarios for this initial release of the product (note – “scenario” is development-team speak for “what we expect customers will want to do with a set of features in a product”).
- Sharing spreadsheets through the browser
- Building business intelligence (BI) dashboards
- Reusing the logic encapsulated in Excel spreadsheets in custom applications
Sharing spreadsheets through the browser ...
Let’s take a bit of a more detailed look at each one of those, starting with sharing spreadsheets and related issues around controlling and managing spreadsheets. Today, we see people use a variety of methods to share spreadsheets … email, file shares, Save As Web Page, etc. For certain classes of spreadsheets – spreadsheets that contain critical business information or logic, calculation-intensive spreadsheets, or spreadsheets that are shared with customers or partners, these methods pose some challenges. For example, it is easy for different people to change their versions of the spreadsheet, resulting in multiple copies of “the truth”. As another example, it is very hard for spreadsheet authors to protect parts of their work such as key logic/modeling or data that is considered confidential or proprietary intellectual property while sharing other parts of the spreadsheet, such as the completed analysis they want a client to see. As a final example, large calculation-intensive spreadsheets are not the type of thing that lend themselves to email – every user that opens it has to wait for the spreadsheet to download and then calculate before they can continue doing their work.
Excel Services gives users a way to deal with these and other challenges related to sharing spreadsheets. Using Excel 12, a spreadsheet author will be able to save their spreadsheet to a SharePoint document library and give other users browser-based access to the server-calculated version of that spreadsheet. When a user accesses the spreadsheet, Excel Services will load the spreadsheet, refresh external data if needed, calculate it if necessary, and send the resulting output view back through the browser (DHTML only – no ActiveX, no client install of Excel 12 necessary). Those users will always be viewing the latest version of the spreadsheet, and they will be able to interact with it in the browser (more on how in a couple of posts.) Moreover, the spreadsheet author can limit access to specific sheets or ranges in the spreadsheet, enabling the author to hide intellectual property from the folks looking at the results. In addition, the user accessing the file in the browser only sees cell values and not the underlying formulas. If multiple users are accessing the same spreadsheet, it will be loaded and calculated only once by the server, saving the network bandwidth and the wait time for long-running calculations. In addition, spreadsheet access can be logged and audited. Overall, this provides quite a powerful story for sharing spreadsheets (via the browser) in a controlled and secure way.
One thing I will cover further in another post but that I want to briefly touch this post is the fact that Excel Services is built on SharePoint products and technologies. This means, for example, that when a user saves a spreadsheet to Excel Services, they are really saving it to SharePoint document library, and they get all the benefits of SharePoint document library features. In addition, there are a whole range of additional features in SharePoint and Excel Services that together provide good tools to help organizations share, manage and control spreadsheets. These include capabilities like workflow and approval, versioning, auditing, and taking snapshots of spreadsheets at specific points in time.
Building business intelligence dashboards ...
Next, let’s take a look at the second scenario – building BI dashboards. As you may have already picked up from the BI announcement made a few weeks ago, one of the key focus areas for Excel this release is BI, and that includes both Excel 12 and Excel Services. The browser-based sharing of spreadsheets I just described is a part of that effort. In addition we have done a lot of work to make building BI dashboards fast and easy. In a future post, I will discuss how users will be able to build dashboard and reporting portals quickly and without code using Excel Services and some new features in SharePoint Portal Server. For now, let me just show you a picture of a browser-based dashboard that can be created using Excel 12, Excel Services, and SharePoint Portal Server without a single line of code.
(Click to enlarge)
Reusing the logic encapsulated in Excel spreadsheets …
Finally, let’s take a look at the third scenario of reusing logic in a spreadsheet. In addition to a browser-based interface to the server, Excel Services also provides a web-service-based interface. The same spreadsheet that was published by our spreadsheet author can also be accessed programmatically by any application that can speak web services, and the calling application can change values, calculate the spreadsheet, and retrieve some or all of the entire updated spreadsheet using that interface, subject of course to security permissions. We think this opens a whole new range of interesting possibilities and solutions. Today, we often see customers rewriting the logic represented in spreadsheets in code to support running in a server-grade environment. Excel Services allows the logic to be maintained in the spreadsheet and reused robustly in the context of an application. A goal of ours has been to give customers the best of both- much easier and more effective modeling in Excel as compared to custom code, but with good application integration, scalability, and manageability.
What Excel Services is not …
When explaining Excel Services to customers, we have found that it is important to explain not only what Excel Services is, but what it is not. This is a first release for us, and we are very excited about the feature set. At the same time, we are also aware that when the words “Excel” and “server” are brought together, different people envision many possibilities. Here are a few areas that Excel Services does NOT cover in this release:
- A spreadsheet creation tool – You will not be able to create or edit spreadsheets in a browser – that requires Excel 12. You will however be able to “interact” with the spreadsheet in the browser in a few specific ways that the author intends– more on that later.
- Multi-user spreadsheet authoring – Excel Services does allow many users to open and interact with a spreadsheet at the same time, but they each have their own session. If they “interact” with the spreadsheet (for example, set a filter on a PivotTable), other users do not see those changes, and their changes are not saved back to the original file. The server opens the spreadsheet as read-only, and each user has their own session state in the server’s memory. Note that the workbook is only loaded once in server memory no matter how many users are accessing it, but each user gets a session in memory that maintains the specific interactions (i.e. filters) they’ve performed and how the workbook results should be calculated and returned to that user. The behaviour is the same when calling through the web services or through the web UI. So, Excel Services does not attempt to address the scenario of multiple users who want to jointly author a spreadsheet in real time and see each other’s edits.
- Breaking apart a single spreadsheet calculation across multiple server boxes – Excel Services does load balance separate spreadsheet calculations across multiple boxes and calculates more than one spreadsheet on a box (each requests runs on a different thread). But it does not break out a single spreadsheet across multiple boxes. The design is optimized for scaling to large numbers of spreadsheets and requests. For example, many users viewing a dashboard with a number of spreadsheets on it, or programmatically running a large parametric sweep on a spreadsheet or group of spreadsheets.
That concludes my two-post introduction to Excel Services. Over the next few weeks, I will provide an overview of the architecture, explain publishing spreadsheets to the server, discuss browsing and interacting with spreadsheets on the server, and review writing applications using the new Web Services API. I will also spend some time on building BI dashboards and controlling/securing spreadsheets using the Excel Services and SharePoint.
PS Updated point about multi-user spreadsheet authoring to clarify a few things.
PS2 Updated "Breaking apart a single spreadsheet calculation across multiple server boxes" to make the explanation clearer.
