Multiple WCF "topics" in one addin using Rx

[Josh Nyce]

Govert/anyone:

First of all, thanks for all the great work. ExcelDNA is a major contribution to the Excel ecosystem.

I implemented the following C# code: https://www.codeproject.com/Articles/662009/Streaming-realtime-data-to-Excel. It works well with a single Excel UDF, but I need to use it for multiple datasets (let's call them "topics" - they could be published on separate channels, or on one channel with some tag to identify them). Ideally, it would be flexible so client code doesn't have to be changed when new topics are added, but that's not a deal-breaker.

Could have all the topics on the same channel and use one UDF for all topics by passing names for each "topic" to the UDF and determining which messages to process in the UDF, but then the UDF would calculate after every message is received, regardless of the topic. That would mean a UDF that only needs to update a few times a day would calculate every time another topic is updated, even if the other topic is constantly "ticking". My understanding is that there is no way to exit out of the UDF calculation. Could cache the data and return the prior array/values, but it would still be calculating, which would be very inefficient for large arrays of data. 

So I tried to use separate observables for each topic. This is how the UDF starts (you can get the rest of the code from the link above):

        [ExcelFunction("Gets realtime data from server")]

        public static object[,] GetDataWcf(string Topic) {

            if (!_topics.ContainsKey(Topic))

            {

                _topics.Add(Topic, new WcfInstance(Topic));

            }

            WcfInstance cls = _topics[Topic];

As you can see, I'm creating separate instances of a class called "WcfInstance". That would solve the problem, but the class "RxExcel" is static, so all instances end up observing the same "topic". I've tried using separate UDFs for each topic, hoping it would observe them separately, but that didn't work.

Any thoughts? Do I really need to use a separate addin (or separate static RxExcel class, I guess) for each topic? I'm new to WCF and Rx, so I may be missing something obvious.

Thank you,

Josh

[Josh Nyce]

I'm still looking for some guidance, but I think there is another solution that would work reasonably well... Could use send all datasets ("topics") on the same "queue" (channel, endpoint, or whatever the exact term is for WCF), but cache the data with a background thread in the Excel addin rather than passing it directly through UDFs. Then setup a second queue dedicated to update times and use that to trigger pulling data from the cache in any number of static UDFs. It would require two addins (unless there is a way to observe multiple queues in one addin), but there might be way to pack two DLLs together, so there's only one XLL. Would need some way of making the static UDFs keep trying until the data is available in the cache, since there would be a race condition, but that shouldn't be very difficult. It might be more performant because it would reduce the number of RTD topics (not sure if that's a concern with this WCF solution though).

Would probably use a VBA worksheet calculate event to monitor the array of update times, rather than passing values from the array directly into the static UDFs as recalc triggers. Otherwise, all static UDFs would recalc every time any value changes in the update times array. That VBA could also be used to deal with the race condition.

I know it's a long way to go. Could ditch Rx and just try to make standard RTD code more efficient by using a background thread so it doesn't block the Excel UI thread as much (not sure if you've posted any examples for that), or try the alternative to Rx you posted (https://github.com/Excel-DNA/Samples/tree/master/LimitedConcurrencyAsync) if it supports multiple topics in one DLL. I'll probably explore both of those options, but I've already put time into this solution and plan on using it in production for a while before spending time on a new solution.

Thank you

[Govert van Drimmelen]

Hi Josh,

It's quite a big topic with different aspects, so not easy to give a quick answer on.

I don't know anything about WCF, and am not sure that's still the way to do things today, unless the back-end or other policy forces that on you.

For getting streaming data into Excel, the abstraction to focus on is the IObservable.

If you can implement a client library that exposes IObservable functions matching the functions you want to call from the worksheet, then the glue to Excel via Excel-DNA is very easy.

So you want some functions that look like this:

public static class LivePricesClient

{

    public static IObservable<double> GetLiveSharePrice( string shareCode )

    {

        // The implementation in here has nothing to do with Excel or Excel-DNA

    }

}

Then we'll use some magic glue to make an Excel function like this:

public static class LivePricesExcelFunctions

{

    [ExcelFunction("Returns a real-time stream of share prices")]

    public static object GetLiveSharePrice( string shareCode )

    {

        string callerFunctionName = nameof(GetLiveSharePrice);

        object[] callerParameters = new object[] { shareCode };

        return ObservableRtdUtil.Observe( callerFunctionName, callerParameters, 

                                                          () => LivePricesClient.GetLiveSharePrice(shareCode) );

    }

}

You can define many of these functions in an add-in.

The question now is how to implement the IObservable functions.

It depends a lot on whether you control the back end and protocol.

One approach is to have a list of shareCodes that you are subscribed to (and some API calls to add / remove items from this list).

Your client-side management is then a Dictionary that maps shareCodes to IObservables.

Then when you get called with new data for your subscriptions, you get the matching IObservables and you call OnNext on each of these. 

Lifetime management is done in the normal IObservable way - the IObservable's Subscribe function lets you add the shareCode to the list of live ones, and the IDisposable that your Subscribe function returns give you the mechanism to unsubscribe when a shareCode is no longer used in the sheet.

Nice thing is you can test the whole client (the IObservable parts) without Excel, just in a Console app or in LinqPad script.

With this plan you don't need to pay any attention to recalculation issues, need no other add-in or VBA, and performance should be fine.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Josh Nyce [josh...@gmail.com]
Sent: 02 August 2018 08:56 PM
To: Excel-DNA
Subject: [ExcelDna] Re: Multiple WCF "topics" in one addin using Rx

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Josh Nyce]

Govert,

Thank you very much for the suggestions. I'm taking your advice and switching from WCF (to NetMQ). We will need the message queue to work in Java soon, and I haven't found any attractive ways of using WCF in Java, so would have switched from WCF soon anyway. 

I chose the WCF project as a starting point because I'm still learning this material and wanted a working project as a starting point, but a colleague just wrote a very elegant solution using NetMQ that doesn't use much DNA code. I wish I could share it here to help other people, but, as you pointed out, understanding the iObservable design pattern is the key, which has nothing to do with DNA.

Thank you,

Josh