Partial Real-Time Data (RTD) support in Excel Services – Part 1

In this first post, I will describe the reasons behind not supporting RTD, I will explain how RTD works and how we will make it partially work on the server. The second post will contain information about the actual implementation and will also contain the code sample that will make it work.

 

The first version of Excel Services does not support the RTD function.  There are a few compelling reasons  for this, but the main one being that RTD for our v1 server just did not make sense.

 

In Excel Client, RTD works by actively making calls into the RTD server and applying the changes (if any occur) into the cell. This in turn may cause other things to change and recalculate on the  workbook. These updates  occur by default every  2 seconds (don’t quote me on this). The interesting part though is that the model is a push model – the RTD server detects a change, notifies Excel about it and Excel updates the data (this is different from regular in-cell formulas . Regular in-cell formulas are more of a pull model – Excel decides to call them when a change is made in something that they depend on).

 

The following sugary chart shows the way things work in Excel Client:

 

 

Now, the reason this model is that last part of the sequence – where Excel Client places the new piece of data in the cell. This implies that the user can see it, because, if they cannot, there is really very little reason to do it. In contrast, Excel Services cannot really do this in v1 – when  data changes on the server, there is no way to connect to the browser of the user watching it and telling it to refresh. Same goes for API calls – to see if there’s new data, the API caller needs to make a call to actually see the new data. For these reasons (and others), supporting RTD on the server seemed like a somewhat lower priority.

 

That said, you may want the server to be able to get information from an RTD server. Maybe because you have existing code you need to use or maybe because the model works for you and you want to make use of it. Whatever the reason, this post and the one after it will give you an example of how to write a UDF that will behave much like RTD, only without the “Realtime” aspect of it.

 

The solution will comprise of a mechanism that will simulate Excel’s RTD functionality and a set of UDFs that will allow Excel Services to make use of the mechanism. First though, we need to understand how Real-time Data works in Excel. The RTD function in Excel looks like this:

 

=RTD(progId, param1, param2…)

 

Basically, Excel will create an instance of the COM object corresponding to the ProgID (that’s the RTD Server). For each unique set of topics, Excel will create a “topic” which will be used by the RTD server to tell Excel when new data is available. Once Excel is ready for new data, it will call into the RTD servers that said they have new information and grab that information and place it in a cell. The fact that the RTD server calls into Excel and informs it of new data availability (and the fact that Excel then knows it actively needs to take the data and place it in a cell) is what makes this into a “push model”.

 

With Excel Services, we will keep the RTD model as it is – all except for the last step. Instead of the last step that the client does, we will wait for a UDF call to be made (because somebody, for example, called Recalc on the workbook, or because there’s periodic reclac). When the UDF call is made, it will do one of two things – it will either return the old value that was returned before or, if a new value is available, the UDF will call into the RTD server to get the new value. So the new cycle will look more like this:

 

Tomorrow I will post some specifics about the code and how it actually works. The code sample will also be published tomorrow.