Partager via

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

  • Article

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.

Comments

  • Anonymous
    May 24, 2006
    Partial solution for using RTD servers in Excel Services - Part 2.
    In this part, the solution implementation is discussed and presented. Also, an attachment of the code that makes it work.

  • Anonymous
    June 02, 2006
    via JOPX
     
    Ah finally, it is here ... get your Office 2007 beta2 ... So, to get things going -...

  • Anonymous
    July 26, 2006
    So does this mean I couldn't do a live pricing sheet in Excel services ?

    http://etrading.wordpress.com/2006/07/27/excel-2007-pricing-engines/

  • Anonymous
    July 27, 2006
    Trick question.

    1. How live?
    2. What do you mean by Excel Services? Just the server, or also the EWA Web Part?

  • Anonymous
    April 10, 2008
    I am a little lost by using the UDF I don't see how the Excel Services is integrated in to actually present the realtime updates on the Web browser client When the UpdateNotify is called back from  RTD how is the web browser going to get automatically refreshed?

  • Anonymous
    April 10, 2008
    Amir: That is why this is called a "partial" solution. It knows how to get the data out of the RTD server, but not refresh Excel Services when needed. From this post: "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."

  • Anonymous
    April 10, 2008
    Is there a way to get EWA (Excel Web Access) to  to somehow get it to get it to periodically lets say every 10 secnods call  the UDF to pump the updates and then have some magic java script to refresh the browser every 10 seconds or so? You can see what I am asking for don't you?

  • Anonymous
    June 16, 2008
    Take a look at the EWA Companion on this blog.