Hi Guys,
I need suggestions on best approach we should follow to stream bulk live data from sensors into excel sheet.
We have sensors data coming in sockets and we need to analyze it using excel. We already have a dll created in VC++ which reads these sensors data and dumps raw data in text file. But now we need to analyze them while data is arriving itself. We get about 50000 updates in a second and for excel analysis only about 1000 important sensors would be used.
For this we are thinking of approaches like
I. Creating a service with the dll and re-broadcasting the data from it using socket multicast. In excel, implement socket reader code.
II. Putting data in database and reading in excel from database.
III. Publish webservice and let excel download data from the webservice.
IV. Use Excel RTD (RealTimeData) service to fetch data.
Seeing the size of the data and considering future growth, what is the best way to implement this? Experiences regarding freezing of Excel UI during such bulk computations (1000 request to database, webservice), dropping of packets in Excel UDP sockets, etc are welcomed.
Thank you ppolymorphe. Below are the replies to your queries:
1.Purpose of excel sheet:
Plotting of data. Computing different calculations on different sensors for studying if a particular combination does show a behavior.
2. Is Data continuous:
Yes, data is continuous. Storing of data for future use is already done by storing the raw data in file. We don't want to touch that component as it's already stable and good for doing post processing after 2 hours delay. We now want to start real time analysis.
3. Rebuilding the sequence flow of data can be achieved just by replaying the existing data file. It's not our objective in this scope.
4. User doesn't intent to read the data. He will say add 5 sensors data and plot it's value against time and just observe it. Problem is more like a Stock Market data where stock prices are coming in, and user plots data of different stocks.
5. Yes, approaches we currently played around with are:
a. We rebroadcasted the data from a server. In excel wrote a C++ socket program to read this data in another thread and keep it in memory. Then, we wrote a VBA for excel to integrate this dll. We have tested this dll with C++ application and VB.net application and it works perfectly. But when we integrate it with excel, it crashes randomly after reading excel reads the data. If we keep the same thread as excel, it doesn't crash, but then we drop a lot of packets.
b. We created webservice for excel to read these data from a central location after every few seconds. But updating 1000 cells in each second is too slow. Same looks would be an issue with database. Aggregating calls and results is an option, but would still require about 100 calls a second.
We are open to suggestion on different frameworks like web based analytics provided it's open source (if excel is not the best choice here).
Thanks,