Excerpt from Professional Excel Services
INSIDE EXCEL SERVICES
by Shahar Prish
There are various technical details one needs to understand about Excel Services. Even though some of this information may not have an immediate impact on how you use, or even program against, Excel Services, it will help you understand what happens under the hood.Session, State, and Workbooks
When programming Excel Services, it is incredibly important to understand exactly how sessions fit in.
All interaction with Excel Services revolves around workbooks that are loaded, queried, and manipulated. When a workbook is needed, it is loaded into a session. What happens internally is this:
- The server brings up the file and copies it locally (more on that when I discuss workbook caches) if it has not already done so.
- It loads the workbook into memory if it has not already done so. This loaded workbook will be used as a "template" or as an "initial state."
- Finally, a session is opened, and the workbook "template" is assigned to the session where it will be used. The session will have its own private copy as needed, and it will not affect the globally loaded workbook. In that way, the changes users make to the workbooks they load are isolated. A user making a change to a workbook will not see changes made to that same workbook by a different user.
No interaction with workbooks can be achieved without a session, and no session can exist that does not refer to a specific workbook. When a session expires (for any reason), all the information it holds expires with it. That means that if you make changes to a workbook and the session you work against is closed, all your changes will be lost. Open sessions on the server have a unique ID (Session ID) associated with them. That ID is used to interact with the session throughout its lifetime.
To further explain this, consider the simple workbook shown in Figure 1. It has two cells, A1 and A2.
A2 contains a very simple formula. In this case, it would be equal to 2. For this example, say that you have two users loading the workbook, one after another and manipulating it:
- Jay opens a session asking for Workbook1.xlsx.If the workbook is not available, the server will load it and go through the stages of making it available for users.
- Jay gets the cell in A2. The value Jay will see is "2" since that value was originally loaded with the workbook.
- Jay sets the cell in A1 to "40.9" and gets the cell in A2. The value Jay will get from A2 will now be "41.9", just as if Jay were to open the file in Excel and place "40.9" in A1.
- From a different computer, Zoe also opens a session asking for Workbook1.xlsx.
- Zoe gets the value from A2. Zoe will see the value "2" — she will not see any of the changes Jay made to the workbook since the two versions are isolated; they start out the same but they do not affect each other in any way.
It is important to understand that, from the users' point of view, they are the only one interacting with the workbook — nobody else can affect what they see in the workbook. The server makes sure to keep the workbooks isolated.
Generally speaking, Excel Services will not allow more than one request to a session at the same time. That is why the EWA may sometimes display a message saying that "there is currently a request running on this session, please try again later." That said, there are a few types of requests that can be made concurrently on a session. For more information about that, see the API references in Chapter 5, "Hello World Sample," from Professional Excel Services (Wrox, 2007, ISBN: 978-0-470-10486-6).Caches
Excel Services employs multiple levels of caching to ensure that requests can be executed as fast and with as little impact on the system as possible.
Excel Services caches what data it can to provide faster response to users. Workbooks are cached on various levels.Workbook Disk Cache
When a user asks for a workbook for the first time, that workbook is fetched from its remote location to the local disk. Once in the disk cache, the workbook will be deleted only when it is stale (i.e., when a newer workbook is available) or if space is needed in the cache (and since the cache defaults to 40GB, it is rare that additional space will be needed). Because memory is scarcer than disk space, there are cases in which workbooks that were in memory will be discarded (since they are not being used anymore) only to be reloaded from the local disk when requested again. This saves us from having to bring the file over the network every time the file is needed. The server also makes sure that it has current files. Whenever a workbook is opened, the server checks the "Last modified" property of the workbook. If there is a new workbook available, the server will fetch the new one.Loaded Workbook Cache
Once a workbook has been brought to the local server, it will be loaded into memory. This loaded representation of the file will continue to exist in memory for as long as somebody is still using the workbook. Like most Excel Services caches, it may continue to exist in memory as long as there is no memory pressure on the server. Only the first request for the workbook will take the hit of going through the loading process — subsequent requests will use the loaded workbook.
Workbooks that are loaded under two different locales or in different time zones may need to be loaded more than once into memory. For example, a workbook loaded by a user using the Japanese locale and one loaded by a user using the Hebrew locale will cause two copies of the workbook to be loaded into memory. This is due to the fact that some very basic things behave differently with some locales and so have potentially completely different memory representations.
It often happens that multiple users can share the same workbook. The obvious example is a workbook that is completely devoid of data queries or other volatile data. All users who open such a workbook will get the same result, no matter what. In those cases, Excel Services will only ever have one copy of that workbook in memory, which will be shared among users. This holds true in more complex cases, too. If Excel Services detects that two different users can share the same piece of data (and from that deduce that the whole workbook is identical for both users), it will make sure that only one copy of that workbook exists in memory.
When workbooks contain external data, that data may or may not be sharable by two different users. Moreover, say that a workbook contains two data queries — one sharable and one not. Excel Services will manage separate caches for each data source, allowing users to share results where possible.
Calculations are cached as part of the Shared Workbook Cache described previously. The important thing to note is that Excel Services is configured by default to minimize calculations of volatile functions.
Volatile functions are defined as functions that may return different results for the same parameters. A good example is the Excel built-in
NOW() function. It has no parameters and yet it returns different results each time it gets called. The Volatile Function Cache Lifetime setting determines how often Excel Services make calls to volatile functions. The default setting is 300 seconds, which means that, in the example of
NOW(), a workbook will only be recalculated for users asking for the workbook (or for users manually recalculating it) once every 5 minutes.
Some solutions in this book will require that you reduce this number to "0," which means that, whenever a user requests a workbook, that workbook will be recalculated to get the new value for the volatile functions.
Changing this setting to "0," however, also means that Excel Services is not reusing the workbooks it caches as often as it could be. This, in turn, can reduce the throughput of the server. Note that because this setting (Volatile Function Cache Lifetime) is bound to a trusted location, it is easy to partition files in such a way that only the ones that need to be recalculated often will be allowed to be.
This article is excerpted from Chapter 3, "Inside Excel Services," from Professional Excel Services (Wrox, 2007, ISBN: 978-0-470-10486-6), by Shahar Prish. Shahar was born and raised in Israel. After a life-long interest in computers, in 1998 he was the first employee in a small company called "Maximal Innovative Intelligence" that developed Business Intelligence software. In 2001, Maximal was purchased by Microsoft, and the product it was selling was rebranded and sold as "Microsoft Data Analyzer." From Maximal, 11 employees moved to the US, where they started in the Office group. A year after moving, Shahar started working with the rest of the team on Excel Services (then called Excel Server). After spending most of his life writing code, Shahar intends to continue until the medics pry the keyboard from his dead cold hands.