Spreadsheet programs such as Microsoft Excel or Google spreadsheets are widely used in everyday business. However, a major problem with spreadsheets is the fact that the data is stored locally and transported manually via a clipboard or CSV file. This leads to handling errors and to a decoupling of the data from the current process.

The obvious idea is to retain the advantages of spreadsheets in terms of acceptance and usability, but to develop a new, server-based technology that processes data not offline, but automatically as a real-time data stream via a continuously running server service. And this in the millisecond range, so that not only IT applications can be connected, but also sensors and actuators can be controlled on a cycle basis. And this is exactly what streamsheets do. The basis remains a formula-based calculation engine à la Excel. This is so universal and powerful that it can actually be used as a rule engine for an analysis or even control program for industrial smart factory applications.

Figure 1: Streamsheets use Excel-like rules and formulas to receive, process and send messages every millisecond.

© Cedalo

To make this work, streamsheets are proficient in common IoT protocols such as OPC UA, AMQP or MQTT. Streamsheets can also use the REST API, the Mongo database or the Apache Kafka stream processing platform to receive or feed data from business applications in real time.

Despite these new possibilities, the ease of use remains the same. Unlike in full or low-code development environments (Codesys, Node-RED, etc.), users do not need any programming knowledge, but can use their existing Excel know-how to achieve what was previously only possible with code development. And at a much lower cost and with a significantly reduced time horizon. Another advantage of the spreadsheet concept is the interactive modeling process. The application logic is built up step by step with direct return of results, because the process data is already running through the spreadsheet during modeling.

Possible use case for streamsheets

An exemplary industrial use case that could be implemented using the streamsheets describes an intelligent quality control system that not only records data from sensors and cameras within milliseconds during the inspection process, but also data from MES systems and cloud services in order to use this additional information - while maintaining the same production quality - to reduce the reject rate compared to traditional quality control. The reject rate is therefore not reduced here by improving production quality, but by making quality control more intelligent and thus enabling parts that have been prematurely rejected to be classified as conforming to quality standards.

The use case was simulated using OPC UA-capable factory simulation software (Factory IO) and sensors for humidity and temperature connected via MQTT. In addition, statistical data is published to a standard IoT visualization app on a smartphone via MQTT.

Another participant in this process is an MES system that supplies data via REST API. Finally, all the information is fed into a query to an AI system, which makes an intelligent decision based on a previously trained machine learning algorithm: can a part pass the inspection, must it be rejected (because processing is not possible) or should it be transferred to an automatic post-processing center?

Figure 2: General view of the simulated production plant and overview of the structure of the use case.

© Cedalo

The simulated scenario looks as follows: A plant produces the blocks shown in Figure 2. A special feature of this company is that the blocks react very sensitively to humidity and temperature in terms of length and width due to the material. In the past, this has led to complaints being made about a product that does not show the correct dimensions at the quality control location, but does show the correct dimensions at the temperatures and humidity levels at the subsequent place of use. Furthermore, statistics on returns were continuously compiled, assigning a tolerance class (TC) to each customer. The same statistics also show the humidity and temperatures to be expected at each customer's production site.

With the help of the streamsheets, the company now implements intelligent quality control, which leads to a significantly lower reject rate with the same production tolerance. The following steps are carried out:

• The parts entering the conveyor belt are recognized by QR code.
• At the same time, the width and length are measured by an intelligent HD camera.
• Sensors record the humidity and temperature.
• The average values for humidity and temperature at the customer's location are retrieved from the MES system.
• The customer's tolerance class is also queried from the MES.

The streamsheet is connected to the production line via an OPC UA-based gateway and also pulls the order-specific data from the MES system. The combined data is transferred to an external machine learning-based AI system. This service carries out a previously trained assessment in milliseconds as to whether the corresponding part is ready for delivery, needs to be sorted out or can be reworked.

In the simulated scenario, the workpieces are first sent to quality control. If the parts are larger than the target size, they are reworked. If they are smaller, they are marked as rejects. Otherwise, they go straight to the warehouse. The decision is made in the short time between sensor detection and the pusher to the post-processing center. As the streamsheet can not only receive data, but also send data, it sends a command directly to the pusher or pivot arm on the conveyor belt via OPC UA during reworking or sorting. At the same time, the data can be sent directly from the streamsheet via AMQP or MQTT to a cloud for further evaluation (stream analytics).

How streamsheets work in detail

To get an impression of how streamsheets work, a few excerpts from the modeling are explained below.