Partial Real-Time Data (RTD) support in Excel Services – Part 2
In a previous post, I explained the basics behind adapting the RTD mechanism Excel Client has into the server. The basics of it were that instead of having actual Real-Time updates (which don’t make much sense in V1 of the server), we can still use data coming from RTD sources inside a UDF.
In this post, I will explain how we will recreate the RTD infrastructure in managed code and then how to use that infrastructure from within a UDF.
The Infrastructure
As described, the first parameter to the RTD function is the ProgID of the COM server that needs to be created. The other parameters are passed to the RTD server to tell it what data we are interested in (their meaning is completely up to the RTD server). Each unique COM server instance will be represented by the RtdServerSite class which are contained in the RtdServerSiteCollection class.
Each RtdServerSide instance will contain multiple TopicSite instances – each corresponding to a single RTD “topic”. A topic is uniquely identified by the collection of parameters passed to the RTD function – these are wrapped inside the TopicId class. That way, if two cells contain RTD calls and both pass the exact same set of parameters, the call will be made only once.
RtdServerSiteCollection
When a call is made with a new RTD server ProgID, the RtdServerSiteCollection.GetServer() method will check to see if a server with that ProgID was already created. If it was not created, it will create it and return it. Otherwise, it will return the existing one.
RtdServerSite
The RtdServerSite class is slightly more complex and is where most of the action actually takes place – it contains the logic of preparing topics for the RTD Servers to consume and it makes sure to make the appropriate calls when new data is available. The RtdServerSite class maintains a few objects that are interesting to us:
* List of Topics (m_topicsByIndex) – Each topic is identified by its server with a unique integer ID. This list maps the topic ID with an actual TopicSite instance.
* Dictionary of TopicSite by TopicId – This is used when searching for specific topics based on a set of parameters.
* Timer – The timer is used to clean up after ourselves after a given time has passed where a specific topic was not used.
The RtdServerSite class also implements the COM IRTDUpdateEvent interface – if you read the Microsoft RTD how-to document, you noticed that RTD servers call into Excel to notify it about new data by using that interface. Our RtdServerSite will implement that interface so that RTD servers can call into it and notify it.
RtdServerSite.GetTopic()
This method returns a TopicSite instance based on a set of parameters. If a topic does not exist for the given set of parameters, a new one will be created and wired up to the RTD server.
Note that this method calls the DoUpdate() method before returning the topic of interest. This makes sure the topic contains information that is up to date.
RtdServerSite.DoUpdate()
When an RTD Server calls into our infrastructure via the IRTDUpdateEvent interface, we only set a flag that says that there’s new information available – we don’t actually go and ask for the information. This method hits the RTD server and asks it for new information and then goes ahead and fills that information in the relevant TopicSite instances.
TopicSite
TopicSite instances contain information about a single topic – the most important information is the topic id and the actual data that was acquired.
TopicId
Each TopicSite instance is identified by a TopicId instance which basically contains a list of strings (the paramrters) and implements the GetHash() and Equals() methods on it.
The UDFs
Finally, we reach the actual UDFs . Once we have the infrastructure, these guys are actually pretty easy to do. Our UDF class will contain two functions – one for session-level RTD calls and one for global RTD calls (read more about global and session-level UDFs). The first is useful if you do not want sessions to share data (say, it’s too sensitive, or may yield different results to different users). The latter is useful if you do not need that level of personalization.
Helper Method – the ServerRtdUdf.ServerRtd()
This method is used by both UDFs – it is the one that makes use of the RtdServerSiteCollection class and grabs the value from the TopicSite object.
Global RTD functionality – ServerRtdUdf.SharedServerRtd()
This method is static and it makes use of a static member called m_sharedServers which is of the RtdServerSiteCollection. It simply passes it to the aforementioned helper method and returns the result.
Session RTD functionality – ServerRtdUdf.PrivateServerRtd()
Similar to the static method, only this one uses an instance member instead of a static member, making sure it only applies for a given session.
Conclusion
While not really as useful on the server as it is on the client, this code will allow you to execute existing RTD servers in Excel Services and get their data into your spread sheet. The code attached to this post is nowhere near production code – it needs some work on error handling and could use some other tweaking (such as when to clear up old servers etc).
Furthermore, depending on how your COM object is programmed, you may or may not run into problems with apartments and threading. This will have to be resolved on a different level.