During cultivation, an important stage is the study and analysis of all the conditions that are necessary for the normal growth and development of the plant. In the process of plant growth, it is important to take into account the optimal indicators of the microclimate of the greenhouse to increase the efficiency of the use of resources for crop cultivation [1]. Looking at the mentioned features, it is useful to use software tools for monitoring, saving and analyzing indicators that are important for increasing the efficiency of cultivation. Such developed systems provide constant control of indicators in structures of closed soil, reporting on the current state in real time and conducting analysis based on available data. Based on all available data in the system, the manufacturer can analyze all key indicators, their changes and impact over time and make appropriate decisions for their enterprise. However, the created systems expand over time, and accordingly, the information in them too, so it becomes inconvenient and inefficient to analyze previously entered data. In this case, there is a need to create a system that will analyze indicators based on accumulated data. Therefore, it is proposed to carry out analysis using OLAP and Data Mining technologies [2].
The purpose of the research is the implementation of a data warehouse using OLAP and Data Mining technologies to increase the efficiency of growing vegetables and fruits in closed soil structures.
The problem of creating a dynamic data warehouse, including for storing and processing measurement results from sensors in a greenhouse, has been addressed by many researchers in Ukraine and around the world.
The article by M. Brazhenenko et al. [3] examines trends in the integration of modern CAD systems into enterprise integration, outlines the consequences of the industry-wide transition of CAD systems to the cloud and the creation of a roadmap for cross-cloud integration of enterprise systems. Building redundant data warehouses is one of the possible ways to increase the reliability of corporate software systems, productivity and reduce the cost of maintaining software systems.
The article by V. A. Porev and G. V. Porev [4] presents the results of an experimental determination of the lower limit of the temperature range of a television pyrometer.
The study by D. Berestov et al. [5] analyzes the use of big data in agriculture based on Internet of Things (IoT) technology. An IoT platform for collecting information about agricultural land is proposed. The latest addition is IoT connected sensors installed by individual farmers in their fields. All of this provides enough input to teach algorithms to discern cloud patterns, recognize the effects of minute changes in cloud temperature and humidity, and identify potential hazards based on changes in wind direction that weather fronts from other areas may cause. The three main technologies that will contribute to the development of intelligent weather monitoring in agriculture are smart IoT sensors for data collection and analysis, satellites and weather stations, as well as artificial intelligence systems and weather forecasting learning machines.
Mexican authors Méndez-Guzmán, H.A. et al. [6] developed a monitoring system in an aeroponics greenhouse based on the Internet of Things. The system provides the greenhouse manager with the information necessary to make decisions in search of the optimal harvest, namely the state of climate variables and the appearance of the crop. The system also controls the timing of watering and the frequency of visual inspection using a developed application for Android mobile devices called Aeroponics Monitor. At the cloud level, One of the database servers analyzes information about the variables monitored in the greenhouse using its IoT analytical tools to create historical data and visualize their behavior, as well as analyze the operating state of the system [6]. Another server is used as a database to store the results of processing images taken in the fog layer to observe leaves and roots. The Aeroponics Monitor contains eight main windows: the main window [6], which allows you to quickly view the status of environmental variables of the cultural system, access through the menu section to reports, records, configurations, manual control, manual control. measurements, manual adjustments and image analysis.
Greek researchers Elvanidi, A. and Katsoulas, N. [7] solved the problem of crop stress detection based on machine learning in greenhouses. To record data on physiological parameters and microclimate, a multi-sensor tower was built, which consists of two air temperature sensors Thygro SDI-12, Symmetron and relative humidity Thygro SDI-12, solar radiation sensor SP-SS, leaf temperature sensors -T-type thermocouples and a sensor PRI type SRS-PRI. The authors evaluated two different algorithms: Gradient Boosting (GB) and MultiLayer Perceptron (MLP). Both models can be incorporated into existing greenhouse climate monitoring and control systems, replacing human expertise in detecting stress conditions for greenhouse crops.
Researchers from Cranfield University (UK) Faniyi, B. and Luo, Z. A [8] performed physical modeling and control of air temperature in a greenhouse system using Internet of Things technology. The authors developed a PID algorithm in the Arduino IDE and, after modifying it using the Arduino Create web editor, uploaded the sketch to the Arduino microcontroller directly from the Internet. The model was validated using measured data from a cloud-based IoT management system platform deployed in a greenhouse. The modified model fitted using the optimization-based model fitting method had a maximum deviation of 2 °C between the simulated and actual measured indoor air temperature.
Internet of Things approaches for monitoring and managing smart greenhouses in Industry 4.0 were summarized in a review by Italian researchers Bersani, C. et al. [9]. Monitoring and control of hydroponic systems based on the Internet of Things are described in an article by Cypriot researchers Tatas, K. et al. [10]. The system is based on three types of sensor nodes: the main one is responsible for controlling the pump, monitoring water quality in the greenhouse, aggregating and transmitting data from slave nodes. Slave nodes monitor environmental conditions in the greenhouse and transmit data to the master node. The system monitors water quality, temperature and humidity in the greenhouse, ensuring crops are growing in optimal conditions according to hydroponic guidelines. Remote monitoring for greenhouse owners is made easier by tracking these parameters through a website connection.
The system measures the quality of circulating water using four sensors: temperature, pH, electrical conductivity (EC) and dissolved oxygen (DO). Sensors from Atlas Scientific were used, and Arduino Mega 2560 was used as the SCU processor [10]. The ubidots platform was used to load the data, and the application was built using an API to transfer and display data in .csv format, as well as using javascript, css and html. The user can control several greenhouses at once. It provides a dashboard for monitoring water quality and a sidebar for alerts and alerts such as sensor and SD card errors. The user can create graphs of monitored parameters.
Greek authors C. Maraveas and T. Bartzanas [11] compiled a review regarding the application of the Internet of Things (IoT) to optimize greenhouse environments.
The article by Italian scientists Pisanu, T. et al. [12] presents the development of an electronic platform for monitoring the greenhouse environment in real time: a view of agriculture 4.0. The authors designed and implemented a prototype of an electronic platform for environmental monitoring in a greenhouse. The electronic board consists of main board, Green House core, Wi-Fi module, RS485 module, A/D converter module and USB module. The system allows you to collect data using external sensors, process and send it to external devices: laptop, smartphone and Internet gateway using a wired and wireless connection. These data relate to the main greenhouse environmental parameters: air temperature, humidity, solar radiation, air speed and CO2 concentration. A web application has been implemented that allows users to obtain information about the state of the environment in greenhouses A web application (a client-server computer program that the client runs in a web browser) is developed using a Java servlet and Java server pages (JSP). Java Servlet, it allows users to synchronize the board over the Internet, receive information about the data collected by the board, store this data in a special online database and display it through a web browser.
The article by D. Khort et al. [13] presents a control system for agricultural technology in horticulture with an automated meteorological complex. The server module is the core of the system, containing all the business logic of the software package and the data visualization system. The weather module is a complex consisting of weather sensors and a GSM modem that provides remote transmission of data from the sensors to the server. The mobile application collects data about gardens with photographic recording and location of objects and transfers them to the server. The system proposed by the authors provides rapid processing of information flows that determine the characteristics of the growth and condition of plants in critical phases of their development, from modern recording instruments (weather stations, samplers, analyzers).
Based on the reviewed articles, we can conclude that there remains an undisclosed section on organizing a data warehouse for storing information obtained from sensors of greenhouse parameters. This is what our research will be devoted to.
To take into account the peculiarities of the change in air temperature in the greenhouse, a mathematical model was refined that took into account the temperature change and made it possible to calculate the air temperature depending on the influence of external disturbances [14]. At the same time, the space of the greenhouse is conditionally divided into temperature blocks according to the width of the greenhouse, taking into account the design features of the block greenhouse.
It was assumed that each block affects the temperature balance of the greenhouse, and the amount of heat for the i-th block Qi depends on the amount of heat Qi+1 and Qi-1, given or received from neighboring blocks (i+1, i-1). Thus, you can write:
where i = 1...n; n is number of blocks;
Qt is the amount of heat coming from the heating system; Qs is amount of heat received from the sun, J; Qv is heat loss through the greenhouse roof and end walls, J; Qі+1, Q і-1amount of heat coming from neighboring temperature blocks, J; Qsr,i is the amount of heat absorbed by plants in the i-th block, J.
Taking into account the geometric dimensions of the temperature blocks of the greenhouse, its thermophysical characteristics (heat transfer coefficients from water to the pipe wall, pipe wall to the air of the greenhouse, from the air of the greenhouse to the glass of the greenhouse wall, glass to the air of the external environment) the heat balance equation for the ith block of the greenhouse will have appearance:
644341 ⋅ 𝛩 𝑖 = 1657,92 ⋅ (𝛩 𝑤,𝑖 − 𝛩 𝑖 ) + 3 ⋅ 𝑆 𝑘,і + 4 ⋅ (𝑆 𝑏,𝑖 − 𝑆 𝑘,𝑖 )(𝛩 𝑖 − 20) + 0,026 ⋅ 𝑆 𝑖−1,𝑖 (𝛩 𝑖−1 − 𝛩 𝑖 ) + 0,026 ⋅ 𝑆 𝑖+1,𝑖 (𝛩 𝑖+1 − 𝛩 𝑖 ) − 3,3 ⋅ 𝑆 𝑘,і .)
where Θi is the air temperature in the i-th block; Θ w,i is the temperature of the coolant in the i-th unit; Sb,i is the area of the side surface in the i-th block; S k,i is roof area in the i-th block.
After creating a mathematical model of the greenhouse based on the contour of the air temperature, the data storage for the decision support system was created.
For a qualitative analysis of the subject area and requirements in the design process, a precedent diagram and a deployment diagram were created to display the architecture of the decision support system (Fig. 1).
Data storage The main physical nodes in the system are the greenhouse workstation, the database server, the data warehouse server, and the analytics workstation.
For the greenhouse workstation, a monitoring subsystem was developed that works with data in an operational database, as well as hardware using temperature and humidity sensors that are connected to a Raspberry Pi single-board computer. A DHT22 air temperature and humidity sensor was used as a sensing element for measurement. The monitoring subsystem is implemented using the Python programming language, which ensures the transfer of data on the measured temperature and humidity indicators to the operational database.
The mnemonic diagram of the subsystem for monitoring technological parameters of cultivation is shown in Fig. 2.
A data warehouse server and an analyst workstation are used to perform data analysis. The data warehouse will allow analysis in different sections of the input data. The signified is provided by the presence of measurements, which are a set of reference information about the measured event. In the context of data warehouses, events are facts that describe the results of a certain business process. The structure of the data warehouse is shown in Fig. 3.
The developed data warehouse was used to deploy a multidimensional cube using SQL Server Analysis Services (SSAS). For direct data analysis, filling of the data storage (SD) was implemented, which takes place on the basis of data from the operational database. The data transfer process was implemented using SQL Server Integration Services.
A single-chamber Raspberry Pi computer is running on the basis of the pre-installed Raspbian operating system. Raspbian OS is a free operating system based on the Debian platform, optimized for Raspberry hardware, which has a wide range of basic programs and utilities. According to the given connection of the receiving element, the Raspberry Pi single-chamber computer polls the information provider and forms the measured data into a specified format for recording in the database. Data exchange between the Raspberry Pi and the DHT22 information transmitter is performed using a serial data transmission protocol -one and zero are coded by different pulse lengths. The data consists of a decimal and an integer part. A full data transfer is 40 bits, and the DHT22 sends the higher data bit first. Raspberry Pi acts as a bus master and is responsible for initiating communication (reading data). The DHT22 is always the slave. When transferring data from the DHT 22 to the Raspberry Pi, the humidity is transferred, followed by the temperature and the checksum. The checksum is 8 bits and contains the sum of all temperature and humidity data in case of successful data transfer. After collecting data, the DHT22 goes into low power mode until it receives a start signal from the controller again.
In the process of analyzing the received data, the key performance indicator was calculated [15,16], which was implemented using the MDX query language for accessing multidimensional data structures (Fig. 4). Based on the calculated data, it can be concluded that the average temperature and humidity by varieties is within the normal range, but has decreased compared to last year. Such results give reason to check the microclimate support system in greenhouses and review the microclimate regulation process. In such cases, incorrect operation of some executive mechanisms or unsatisfactory condition of greenhouses is possible. If this trend continues in the future, it will affect the future yield, considering that the recorded indicators may go beyond the optimal limits. This condition will affect the growth of plants grown on the farm.
The SQL Server Data Tools-Business Intelligence (SSDT-BI) tool was used for intellectual analysis, which includes technologies for business analysis: creation of Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports. Using the expanded structure [17], a network of dependences of the name of the crop and variety on the given ranges of temperature and humidity was built (Fig. 5). Expanding the structure of the analysis by the clustering algorithm, the model shown in Fig. 6 was obtained. As a result of the expanded structure, it can be concluded that the data is homogeneous and ordered. This is especially noticeable on the diagram of the network of connections, the clusters are almost not connected to each other. Since most of the data is concentrated in cluster 3, the average value of the maximum humidity is within 85%, the maximum temperature is about 24.5℃. The minimum humidity is about 70% and the minimum temperature is about 18℃, which is most suitable for growing pepper culture. The output of the results of the system using the time series algorithm is shown in Fig. 7. Based on the graph obtained, it can be seen that starting from the beginning of 2021, the total yield in greenhouses has decreased. For example, in the winter of 2021, the yield reached its lowest value, but the predicted value is significantly different from the actual one.
This work is a development of the project described in article [18], which describes a hardware and software implementation of an information management system applicable to various biotechnical objects (including greenhouses) based on an integrated Arduino board and the LabView visual programming environment. In addition to reading information from the sensors, an operator interface in the form of a web page was developed and the measured values were recorded in a database for further processing. Data is stored on a storage device in the form of tables unified with data processing programs, or in the cloud with the ability to remotely control technological processes. The program provides targeted polling of sensors, which makes it possible to evaluate changes in the controlled parameter at the location of the sensor.
This project can be used in robotic systems in greenhouses and gardens [19][20][21] for storing dynamic information and remotely controlling the robot.
In our further research, we plan to develop the project in the following directions: assess the quality of the data model, assess the risks of information security of the control system, optimize big data, develop correlations between the time of ripening and storage of fruits and temperature. The definition of data model quality indicators for evaluating the Data Vault 2.0 model is shown in [22]. An assessment of the information security risks of an information system can be carried out using the methodology described in article [23]. A model for increasing the execution time of unstructured big data storage, including dynamic data, is described in article [24]. Mathematical models of the time correlation coefficients of temperature and storage time in various planes, as well as an analysis of the effect of changing storage conditions on temperature correlation are described in article [25].
In the process of developing the system, the structure of the operational database was presented. Data input, storage and analysis modules were developed. During the analysis, the application of OLAP and Data Mining technologies was proposed for intellectual analysis of large volumes of information. The obtained results of the system can be used in the process of forming management decisions of the greenhouse economy. This will allow you to direct the strategy for managing individual business processes in such a way as to increase the yield in greenhouses and, accordingly, the profitability of the farm in general.