# A Hybrid Quantum-Classical Approach to the Electric Mobility Problem

1st Margarita Veshchezerova
5th Sebastian Schmitt  Terra Quantum AG
St. Gallen, Switzerland
<EMAIL_ADDRESS>Honda Research Institute Europe
Offenbach am Main, Germany
<EMAIL_ADDRESS>2nd Mikhail Somov
6th Michael Perelshtein  Terra Quantum AG
St. Gallen, Switzerland
<EMAIL_ADDRESS>Terra Quantum AG
St. Gallen, Switzerland
<EMAIL_ADDRESS>3rd David Bertsche
7th Ayush Joshi Tripathi  Terra Quantum AG
St. Gallen, Switzerland
<EMAIL_ADDRESS>Terra Quantum AG
St. Gallen, Switzerland
<EMAIL_ADDRESS>4th Steffen Limmer  Honda Research Institute Europe
Offenbach am Main, Germany
<EMAIL_ADDRESS>
###### Abstract

We suggest a hybrid quantum-classical routine for the NP-hard Electric Vehicle
Fleet Charging and Allocation Problem. The original formulation is a Mixed
Integer Linear Program with continuous variables and inequality constraints.
To separate inequality constraints that are difficult for quantum routines we
use a decomposition in master and pricing problems: the former targets the
assignment of vehicles to reservations and the latter suggests vehicle
exploitation plans that respect the battery state-of-charge constraints. The
master problem is equivalent to the search for an optimal set partition. In
our hybrid scheme, the master problem is reformulated in a quadratic
unconstrained binary optimization problem which can be solved with quantum
annealing on the DWave Advantage system. On large instances, we benchmark the
performance of the decomposition technique with classical and quantum-inspired
metaheuristics: simulated annealing, tabu search, and vector annealing by NEC.
The numerical results with purely classical solvers are comparable to the
solutions from the traditional mixed integer linear programming approaches in
terms of solution quality while being faster. In addition, it scales better to
larger instances. The major advantage of the proposed approach is that it
enables quantum-based methods for this realistic problem with many inequality
constraints. We show this by initial studies on DWave hardware where optimal
solutions can be found for small instances.

###### Index Terms:

quantum annealing, hybrid quantum-classical algorithms, electric vehicles,
combinatorial optimization, column generation

##  I Introduction

Many industrial problems related to logistics, planning, scheduling, or
resource allocation can be formulated as NP-hard optimization problems over
discrete and continuous variables [1]. Employing efficient algorithms may
significantly reduce operational costs and increase profits - therefore, the
search for computational advantage is crucial for competitiveness. In
practice, operational problems are usually solved with of-the-shell commercial
solvers such as Gurobi; however, when the search for the exact solution is
time-consuming we can use heuristic algorithms to get good results in a
reasonable time.

The emergence of the quantum hardware offers new opportunities for the design
of efficient heuristics [2]. Quantum heuristics leverage the laws of quantum
mechanics to improve the approximation gap or reduce the the time-to-
solution[3]. For instance, the tunneling effect in the navigation of the
energy landscape allows quantum annealing [4] to find optimal solutions under
a certain condition [5]. This condition – namely the polynomially-bounded
spectral gap – is nevertheless difficult to guarantee, therefore, an
experimental evaluation on difficult industrial problems is necessary to
decide on the practical potential of the heuristic.

Quantum algorithms adapted to near-future quantum hardware solve problems in
so-called QUBO (Quadratic Unconstrained Binary Optimization) formulations [2],
i.e. all problem specifications are captured in a quadratic objective function
over binary variables. At first glance, the NP-hard QUBO is a powerful model:
traditional discrete problems [6] as well as some simplified industrial use-
cases [7, 8] can be represented in QUBO without a huge resource overhead.

However, most real-world industrial problems exceed this simple framework: for
instance, in the MILP (Mixed Integer Linear Program) model the solution space
is typically restricted by many inequality and equality constraints and
variables are not necessarily binary. In theory, if all variable domains are
discrete and bounded, the MILP can be reformulated as QUBO: discrete variables
are encoded as binary string, slack variables transform inequality constraints
into equalities, and quadratic penalty terms $M(a_{j}^{T}x-b_{j})^{2}$ (where
$M$ is a large number) in the objective function enforce linear equality
constraints $a_{j}^{T}x=b_{j}$.

However, these obvious transformations lead to a large overhead in the number
of variables in QUBO [8]. Thus, the size of the problems tractable on the
near-future quantum hardware becomes strictly limited. In addition, penalty
terms negatively impact the performance of quantum routines due to the
additional energy scale separating feasible and infeasible solutions [9, 10].
To address this obstacle, the works [11, 12] suggest to restrict the quantum
evolution to the feasible subspace - but the protocol is difficult to put into
practice. Alternately, a hybrid augmented Lagrangian method [13] may perform
well when the formulation has only a few constraints.

We believe that a balanced interaction between quantum and classical routines
is the most promising way to enable quantum enhancement for complex
optimization problems [14]. In this work, we introduce a hybrid approach that
delegates some operational constraints to a classical routine while leaving a
difficult selection problem to the quantum heuristic.

We consider the problem of managing a fleet of electric vehicles (EV)
previously considered in [15, 16]. In this problem, we search for an
exploitation plan for a set of EVs over a discrete time horizon
$T=\\{0,\dots,t_{\max}\\}$. We aim to fulfill as many reservations as possible
with cars from our fleet. When cars are not used we can recharge them. The
work [15] proves the NP-hardness of the problem and suggests a MILP
formulation that is further optimized with the Gurobi solver111gurobi.com. The
solver fails to find optimal (or even good) solutions in one hour already for
instances with 10 vehicles over a 48-hour time horizon - motivating the
exploration of heuristic approaches.

In the original MILP from [15] a set of inequality constraints ensure that the
state-of-charge (SoC) of each EVs battery is always non-negative and doesn’t
exceed the battery capacity. These inequality constraints are particularly
challenging for quantum routines. We decompose the problem into master and
pricing problems: the master problem coordinates the collective solution while
the pricing suggests new charging and utilization plans for individual EVs.
The master problem is equivalent to the NP-hard Set Partition problem [17]
that can be naturally formulated as QUBO [18] – following [10] in our hybrid
procedure we solve it with quantum annealing. The pricing problem deals with
the charging schedule limited by the SoC-validity constraints. It uses a graph
representation of possible individual actions at each time step, a path in the
graph corresponding to a complete exploitation plan for one vehicle. By
associating different weights to the edges of the graph in an iterative way we
encourage the pricing problem to include or not a particular reservation in
the plan.

We numerically evaluate our approach on realistic
data222https://www.ac.tuwien.ac.at/research/problem-instances/#evfcap. As the
actual quantum hardware can tackle the problems of relatively modest size, in
addition to experiments on the DWave Advantage 6.1 system for small instances
(over 8-hour time horizon) we benchmark classical and quantum-inspired meta-
heuristics on master problems for large instances (over 48-hour time horizon).

#### Structure of the paper

In section II we introduce the problem, briefly recall the structure of the
MILP formulation from [15], and present the decomposition on the master and
pricing problems. In section III we present our hybrid approach and its
potential applications. We report the results of our numerical experiments in
section III and discuss the insights in section V.

##  II Problem statement

In the Electric Vehicle Fleet Charge and Allocation Problem (EVFCAP) we
consider a set $V$ of $n$ electric vehicles on a time horizon $T$ of
$t_{\max}$ time steps each of duration $\Delta t$ (in our data $\Delta
t=15\text{min}$). Each vehicle $v\in V$ has the same battery capacity
$E^{cap}$ and an individual initial level of charge $E_{v,0}\in[0,E^{cap}]$.
Reservations $r\in R$ ($|R|=r_{\max}$) have each a starting time
$T_{r}^{start}<T$, an ending time $T_{r}^{end}\leq T$ and an expected energy
consumption $E_{r}^{res}\in[0,E^{cap}]$. When vehicles are not used, they can
be charged from the grid with power bounded by $p_{\max}\in\mathbb{R}^{+}$.
The price of the grid energy varies in time and we denote its value at
timestep $t$ with $c_{t}\in\mathbb{R}^{+}$. If a reservation is uncovered,
i.e. not served by an EV from the fleet, it is fulfilled by a fuel car for the
cost $c^{uncov}E_{r}^{res}$. Future costs are anticipated with a term
$\alpha(E^{cap}-E_{v,t_{\max}})$ that penalizes low EV charging levels at the
end of the time period. The target is to find a schedule of minimal cost
satisfying all operational constraints.

###  II-A Compact formulation

In [15] the problem is formulated as a Mixed Integer Linear Program (MILP). In
what follows we refer to this MILP as compact formulation (compact MILP). It
has $(n+1)r_{\max}$ binary variables: $x_{r,v}=1$ represent the assignment of
the reservation $r$ to the vehicle $v$ and $y_{r}=1$ ($y_{r}=0$) indicates
that a reservation is not assigned (assigned) to any vehicle. Continuous
variables represent charging powers of vehicle $v$ at time $t$ as
$p_{v,t}\in[0,p_{\max}]$. A multitude of constraints prevent conflicts, such
as the assignment of two overlapping reservations to the same vehicle. In
addition, for each vehicle inequality constraints ensure that the battery
charging levels are within allowed bounds $[0,E^{cap}]$.

###  II-B Extended formulation

In this work, we focus on a slightly modified version of the original problem
[15]: First, we restrict the charging powers to two discrete levels,
$p_{n,t}\in\\{0,p_{\max}\\}$ (instead of the continuous values in the original
formulation). Second, we discretize the energy levels
$[0,E^{cap}]\rightarrow\mathcal{E}=\\{0,\dots,i\Delta E,\dots,i_{\max}\Delta
E\\}$ with equal spacing $\Delta E$ between these levels. The initial SoC
$E_{v,0}$ and the energies for reservations $E_{r}^{res}$ are rounded to the
next lower and upper levels in $\mathcal{E}$, respectively. Finally, we ignore
”free” photovoltaic energy during the optimization process and simply
integrate it into the final solution.

This modified problem formulation still captures all the relevant aspects and
the complexity of the original problem setting.

A feasible exploitation scenario for one EV, i.e. the assignment to
reservation(s) and the charging schedule, can be represented as a path on a
weighted directed acyclic graph $G=(\mathcal{V},\mathcal{A})$ (see Fig. 1).
Nodes $\mathcal{V}$ correspond to possible charge levels $\mathcal{E}$ at
different time steps $\\{0,\dots,T\\}$; two auxiliary nodes source and sink
help to encode the selection of a vehicle from the fleet and the value of the
final SoC respectively. Arrows represent possible actions at different
timesteps: selection of a particular vehicle, charging, allocation to a
reservation, or nothing.

The number of nodes depends on the discretization step $\Delta E$ and the
number of timesteps $|T|$, while the number of arrows is linear on the size of
the problem: we add one arrow from the source per vehicle and at most
$|\mathcal{E}|$ arrows for each reservation.

The EVFCAP problem can then be decomposed into two subproblems: i) the pricing
problem which suggest new promising exploitation scenarios, i.e. the paths in
the graph ii) the master problem which selects one feasible exploitation
scenario for each EV in the fleet from the subset of feasible exploitation
scenarios. Each exploitation scenario comes with a cost, and the global
objective is to minimize the total costs of selected scenarios plus the cost
of unsatisfied reservations.

$T_{i}$$T_{i+1}$$t_{\max}$$E_{j}$$E_{v,0}$$E_{u,0}$
$E_{cap}$$c_{i}p_{\max}\Delta t$$0$$p_{\max}\Delta
t$${r_{1}}$$E_{r_{1}}^{res}$$r_{2}$$\alpha
E_{cap}$$\alpha(E_{cap}-E_{i})$sourcesink$T_{i}$$E_{i}$ Figure 1:  Graph of
feasible exploitation scenarios. For clarity we show arrows only for one
internal node. The graph contains one node per discrete SoC level per time
step (black circles) and two additional nodes called source and sink. Arrows
correspond to different actions: charging (blue arrow), allocating to a
reservation (green arrows) and nothing (pink arrow). Only feasible actions are
represented in the graph. We connect the source node to the initial SoC values
$E_{v,0},v\in V$ (one arrow for each vehicle). All nodes corresponding to the
final timestep $v_{i}=(i\Delta E,t_{\max})$ are connected to the sink node. We
assign a cost to each arrow (black subscript). Arrows corresponding to the
charging at a timestep $T_{i}$ get the cost value of $c_{i}p_{\max}\Delta t$.
The arrows going to the sink node have the cost $\alpha(E_{cap}-E_{i})$. All
other arrows have zero cost.
The black path is an example of a feasible exploitation scenario that uses the
vehicle $u$ and satisfies one reservation: in this scenario, we take the
vehicle $v$ with initial charge $E_{v,0}$, use it for one reservation starting
at $t=0$, then charge it on some time intervals.

###  II-C Master problem

We introduce one binary variable $\lambda_{p}\in\\{0,1\\}$ for each path
$p\in\mathcal{P}$ from the source node to the sink node in the graph $G$. The
cost of the path $c_{p}$ is a sum of the costs of all arrows in it: it
corresponds to the sum of the grid energy costs (internal arrows) with the
future costs (the arrow going to sink). In addition, as in the compact MILP,
we take one variable $y_{r}\in\\{0,1\\}$ per reservation $r\in R$; $y_{r}=1$
implies that the reservation $r$ is unsatisfied.

The master problem may be formulated as Set Partition:

$\displaystyle\min$
$\displaystyle\sum_{p\in\mathcal{P}}c_{p}\lambda_{p}+c^{uncov}\sum_{r\in
R}E_{r}^{res}y_{r}$ (1)
$\displaystyle\sum_{\begin{subarray}{c}p\in\mathcal{P}:\\\ r\in
p\end{subarray}}\lambda_{p}+y_{r}=1,$ $\displaystyle\forall r\in R$ (2)
$\displaystyle\sum_{\begin{subarray}{c}p\in\mathcal{P}:\\\ v\in
p\end{subarray}}\lambda_{p}=1,$ $\displaystyle\forall v\in V$ (3)
$\displaystyle\lambda_{p}\in\\{0,1\\},$ $\displaystyle\forall p\in\mathcal{P}$
(4)

where

  * •

$r\in p$ in (2) means that for some $E_{j}$ the arrow
$a_{r}:(E_{j},T_{r}^{start})\rightarrow(E_{j}-E_{r}^{res},T_{r}^{end})$
corresponding to the reservation $r$ is in the path $p$. The constraint (2)
implies that the reservation can be satisfied at most once.

  * •

In (3) the notation $v\in p$ means that the arrow taken from the source node
corresponds to the vehicle $v$. The constraint (3) means that we have to
select precisely one path for each vehicle (it can be trivial).

The Set Partition problem (alternately called Exact Set Cover) is NP-hard [17]
as is its approximation within the factor $\ln n$ [19]. Typical instances
issued from the EVFCAP data are difficult for the of-the-shell solvers: for
example, the Gurobi solver does not find optimal solutions within one hour for
$12$ out of $30$ instances with $t_{\max}=192$, $n=20$ and $r_{\max}=320$ from
our dataset. On the other side, no inequality constraints are present in this
formulation, which allows for an efficient mapping to a QUBO form. Therefore,
if the quantum annealing for the QUBO formulation rapidly returns good-quality
solutions, it can improve both the gap and the runtime of the proposed
decomposition scheme.

###  II-D Pricing problem

The number of paths $|\mathcal{P}|$ in the graph $G$ is exponential in the
size of our instance. Therefore, we can’t directly solve even the relaxed
version (where $\lambda_{p}\in[0,1]$). We use so-called column generation [20]
originally introduced in [21] to circumvent this obstacle. In column
generation, while solving the relaxation we do not consider all variables
$\mathcal{P}$ at once, but rather a restricted subset
$\mathcal{P}^{\prime}\subset\mathcal{P}$. We add new variables to
$\mathcal{P}^{\prime}$ only if they can improve the solution of the
relaxation.

Promising variables are found in the pricing routine that searches for
violated cuts in the dual problem. Indeed, each variable in the primal problem
(whether in $\mathcal{P}^{\prime}$ or not) corresponds to a constraint in the
dual problem, and by the duality theorem if the dual solution is feasible,
then the primal solution is optimal [22].

In our case, the pricing is equivalent to the search of the shortest path
between source and sink nodes where edges corresponding to vehicle selection
and reservations get costs determined by the dual solution of the restricted
problem.

##  III Hybrid quantum-classical approach

Master Problem (variables $\mathcal{P}$) Solving the relaxation Restricted
Master Problem (variables $\mathcal{P}^{\prime}$) Relaxation
($\lambda_{p}\in[0,1]$) Get dual values Solve Pricing problem $p$ improves the
solution?
YesNo$\mathcal{P}^{\prime}\rightarrow\mathcal{P}^{\prime}\cup\\{p\\}$Solve$p$Relaxationsolved$\mathcal{P}^{\prime}$BranchingExact
Branch & PriceSolve IntegerMaster Problem over variables in
$\mathcal{P}^{\prime}$Heuristic Figure 2: The Hybrid quantum-classical
algorithm for the EVFCAP problem. The red node (solution of the integer master
problem) is delegated to the quantum annealing. Dashed part shows the workflow
of the traditional Branch & Price.

We suggest a hybrid approach that uses a classical column generation technique
to build an instance of the Set Partition problem, that is further transformed
into a QUBO form and solved with quantum heuristics (see Figure 2).

In the first (classical) part we solve the relaxed version of the master
problem: we start from a restricted set of variables that guarantee the
existence of a feasible solution (trivial plans) and iteratively add variables
that may improve the relaxed solution. If the pricing problem fails to find a
”promising” variable, the relaxation is solved to optimality. In such case we
take all generated variables ($\mathcal{P}^{\prime}$) and consider an integer
master problem over them - this ILP is equivalent to a search of an optimal
set partition.

In the traditional Branch & Price [20], a fractional solution for relaxation
leads to the branching, and the variables may be regenerated in every node of
the branching tree. The regeneration is necessary to find an exact optimum as
an optimal integer solution may involve variables that don’t appear in the
solution process for the relaxed linear program. The quantum-assisted
procedure presented in [10] integrates the quantum solver as a primal
heuristic in the traditional Branch & Price scheme.

In contrast, in our approach, the variables are generated only once in the so-
called root master problem. Compared to Branch & Price we significantly reduce
the running time at the cost of the optimality guarantees. In a nutshell, we
obtain a heuristic method, where the column generation presents candidate
exploitation plans for individual vehicles that are further combined in the
master problem.

Our hybrid approach can be applied in the same contexts as the Branch & Price
(or heuristic Branch & Price) when, in addition, the time-to-solution is an
important performance metric. We recall that the Branch & Price is
particularly suitable for complex planning and logistics problems where
difficult (nonlinear) constraints restrict the set of possible solutions [23].

##  IV Numerical results

Figure 3: Solution quality and runtime (in seconds) on instances for various
numbers $n$ of EVs, maximal number of reservations $r_{\max}$ and for
$t_{\max}=192$. Relative cost changes are given with respect to the 1-hour
compact MILP solution. We report the values for the Bender-decomposition-based
heuristic from [15] (BDH) and for the approach proposed here (EF) on instances
with $t_{\max}=192$.
The first three plots demonstrate the relative difference in the cost value
obtained by both heuristics. We observe that on most difficult instances (with
$n=20$ or with $n=10$ and $r_{\max}>8n$) the solution quality of the EF
heuristic is systematically higher compared to BDH. For the largest instances
($n=20$), the negative relative cost (with respect to the MILP solution)
demonstrates the advantage of the heuristics over the time-limited Gurobi
solution.
The rightmost plot demonstrates the runtime of the EF heuristic. On the
largest instances ($n=20$, $r_{\max}=16n$) it hits the one-hour limit.
However, it doesn’t disqualify the EF approach: we recall that the BDH runtime
always equals 1 hour (3600 seconds), and the compact MILP is solved to
optimality before the one-hour time limit only for $n=5$.

Figure 4: Performance of classical metaheuristics on the generated instances
of the Set Partition problem. (SA) stands for the simulated annealing, (Tabu)
for the tabu search, (VA) for the vector annealing by NEC.

###  IV-A Evaluation of the decomposed formulation

On Figure 3 we compare the quality of solutions returned with the proposed
decomposition scheme (”EF” for extended formulation) to the results found
within one hour by the Benders-decomposition-based heuristic from [15]. As a
baseline, we take the solution obtained by Gurobi within one hour on the
compact MILP formulation ($C^{MILP}$). In this experiment, we aim to evaluate
the relevance of the decomposition (disregarding the performance of quantum
solvers), so we delegate the master problem to the Gurobi solver (version
9.5). We report the relative difference in the cost value,
$(C^{h}-C^{MILP})/C^{MILP}$, where $C^{h}$ is the value of the objective
function in the solution returned by heuristics $h:$ (BH) and (EF).

We observe that on difficult instances the proposed EF approach leads to
solutions of comparable quality while being significantly faster, except for
the largest instances where the time needed for solution hits the one hour
(3600 seconds) limits. We recall that on instances with $t_{\max}=192$ the
Benders-decomposition-based heuristic always runs for one hour disregarding
the values of other parameters [15]. Moreover, for large instances with $n=20$
vehicles the performance of EF approach scales better since the relative cost
difference decreases with increasing $r_{\max}$. However, this is most likely
due to the degrading performance of the reference MILP approach rather than an
improved performance of the proposed heuristic approaches.

###  IV-B Metaheuristics for the master problem

On large instances ($n=20$, $r_{\max}=16n=320$ ) the Gurobi solver reaches the
1-hour time limit while solving the integer master problem (Figure 3, last
panel). Therefore, we accelerate this step by moving from the exact Gurobi
solver to the quantum annealing as well as to classical heuristic solvers.

We test the performance of the classical metaheuristics simulated annealing
[24] and tabu search [25] (Figure 4)333We use the implementation from the
dwave-neal module. We also benchmark the quantum-inspired vector annealer by
NEC444https://www.nec.com/en/global/quantum-computing/ on our instances of the
Set Partition Problem. The vector annealer performs the simulated annealing
(on a vector supercomputer) but restricts local moves to the feasible
subspace. We observe that this modification significantly changes the behavior
of the metaheuristic: while the standard simulated annealing on the QUBO
formulation finds better solutions with an increasing number of reservations
(and, as a consequence, the number of constraints in the master problem), the
opposite is true for the vector annealer.

Comparing the approximate solutions with the solutions found by Gurobi in one
hour (Fig. 4), we observe that the quality of the solution decreases
substantially. The cost difference is always positive (no improvement) and for
large instances is at least 10% worse. However, given the substantially
reduced runtime - each heuristic takes no more than 5 minutes - it might be
reasonable to use heuristics and trade solutions quality for runtime
improvement. As quantum annealers may further reduce the time-to-target [3],
in a close-to-online regime (where new reservations appear during the time-
horizon) the hybrid approach is a promising option for cost-efficient
planning.

###  IV-C Quantum annealing for the master problem

Finally, we evaluate the potential of our quantum-classical hybrid scheme on
instances with $t_{\max}=32$ where we use the DWave Advantage 6.1 for solving
the Set Partition problem of the proposed decomposition scheme. If the quantum
annealer returns an infeasible solution we restore feasibility in a greedy
fashion: subsets from the infeasible solution are iteratively added to a
partial solution if the addition doesn’t violate any constraints.

We remark that even if we are able to run experiments only on the smallest
instances from the benchmark dataset, we used the real data and not the
simplified one as in most papers [10, 8] that benchmark the quantum annealer
on industrial use-cases. We compared the obtained results to the ones found by
QuEnc - the variational quantum algorithm for gate-based quantum computers
based on amplitude encoding [26].

TABLE I: Gap on $90$ instances for the Quantum Annealing (QA) and QuEnc $n$ | $r_{\max}$ | QA Gap (%) | #physicala | #logical a | QuEnc Gap (%)
---|---|---|---|---|---
1 | 4 | 0.00 | 4.70 | 4.70 | 0.00
1 | 8 | 0.00 | 5.30 | 5.30 | 0.00
1 | 16 | 0.00 | 5.80 | 5.60 | 0.00
2 | 8 | 0.21 | 21.10 | 14.80 | 22.99
2 | 16 | 0.00 | 15.90 | 13.20 | 6.31
2 | 32 | 0.01 | 18.60 | 14.80 | 7.31
5 | 20 | 23.75 | 239.70 | 61.20 | 27.78
5 | 40 | 9.59 | 251.00 | 80.80 | 9.50
5 | 80 | 5.25 | 256.10 | 121.20 | 4.17
a number of physical and logical qubits used by the quantum annealer.

In the DWave Advantage hardware physical qubits interact only with their local
neighbors in the Pegasus layout. The QUBO has to be embedded in the hardware
architecture, which leads to an overhead since a logical qubit $x_{i}$ has to
be represented by a chain of physical qubits
$\\{q_{i}^{1},\dots,q_{i}^{k}\\}$, see, e.g., [27].

We observe (see Tab. I) that quantum annealing is able to find optimal or
near-optimal solutions for very small instances of $n=1$ and $n=2$ EVs. For
$n=5$ the relative performance is worst for the smallest number of
reservations $r_{\max}=20$ and improves with larger $r_{\max}$. Interestingly,
this trend correlates with the embedding overhead. The QuEnc approach shows
the same qualitative behavior as a function of $r_{\max}$ for fixed $n$, but
already fails to find optimal solutions for $n=2$ EVs. The possible cause
might be the generic hardware-efficient structure of QuEnc ansatz – contrary
to the problem-specific annealing evolution or cost-dependent QAOA ansatz[28].

##  V Discussion

We suggested a new approach to address real-world problems with hybrid
quantum-classical routines. Instead of formulating the problem as one MIP, we
separate it into master and pricing problems; the NP-hard master problem is
further delegated to a quantum (or hybrid) algorithm. Constraints that are
difficult for quantum routines are managed inside the classical pricing
routine.

We tested our approach on the EVFCAP problem. The proposed decomposition of
the original problem into two sub-problems enables hybrid quantum-classical
approaches despite the many inequality constraints in the compact formulation.
Additionally, for larger instances, it allowed us to find better solutions in
a shorter time while using only classical methods. Our numerical experiments
also confirm that quantum annealing is in principle capable to solve the
master problem. This spurs the hope that the integration of quantum routines
can further accelerate the search for a good-quality approximate optimum in
the future. However, experiments on hardware with more qubits and better
connectivity are necessary to further evaluate the potential of a quantum
advantage for this problem.

In this regard, the proposed approach provides a promising route to solve
planning problems with difficult constraints with hybrid quantum-classical
schemes.

## References

  * [1] R. J. Vanderbei, _Applications_. Boston, MA: Springer US, 2014, pp. 225–239. [Online]. Available: https://doi.org/10.1007/978-1-4614-7630-6_15
  * [2] J. Preskill, “Quantum computing in the NISQ era and beyond,” _Quantum_ , vol. 2, p. 79, aug 2018. [Online]. Available: https://doi.org/10.22331%2Fq-2018-08-06-79
  * [3] J. King, S. Yarkoni, M. M. Nevisi, J. P. Hilton, and C. C. McGeoch, “Benchmarking a quantum annealing processor with the time-to-target metric,” 2015. [Online]. Available: https://arxiv.org/abs/1508.05087
  * [4] T. Kadowaki and H. Nishimori, “Quantum annealing in the transverse ising model,” _Physical Review E_ , vol. 58, no. 5, pp. 5355–5363, nov 1998. [Online]. Available: https://doi.org/10.1103%2Fphysreve.58.5355
  * [5] E. Farhi, J. Goldstone, S. Gutmann, and M. Sipser, “Quantum computation by adiabatic evolution,” 2000. [Online]. Available: https://arxiv.org/abs/quant-ph/0001106
  * [6] A. Lucas, “Ising formulations of many NP problems,” _Frontiers in Physics_ , vol. 2, 2014.
  * [7] C. Dalyac, L. Henriet, E. Jeandel, W. Lechner, S. Perdrix, M. Porcheron, and M. Veshchezerova, “Qualifying quantum approaches for hard industrial optimization problems. a case study in the field of smart-charging of electric vehicles,” _EPJ Quantum Technology_ , vol. 8, no. 1, p. 12, 2021\.
  * [8] A. I. Pakhomchik, S. Yudin, M. R. Perelshtein, A. Alekseyenko, and S. Yarkoni, “Solving workflow scheduling problems with qubo modeling,” 2022.
  * [9] A. Verma and M. Lewis, “Penalty and partitioning techniques to improve performance of qubo solvers,” _Discrete Optimization_ , vol. 44, p. 100594, 06 2020.
  * [10] M. Svensson, M. Andersson, M. Gronkvist, P. Vikstaal, D. P. Dubhashi, G. Ferrini, and G. Johansson, “A hybrid quantum-classical heuristic to solve large-scale integer linear programs,” 2021.
  * [11] I. Hen and F. M. Spedalieri, “Quantum annealing for constrained optimization,” _Phys. Rev. Applied_ , vol. 5, p. 034007, Mar 2016. [Online]. Available: https://link.aps.org/doi/10.1103/PhysRevApplied.5.034007
  * [12] I. Hen and M. S. Sarandy, “Driver hamiltonians for constrained optimization in quantum annealing,” _Physical Review A_ , vol. 93, no. 6, jun 2016. [Online]. Available: https://doi.org/10.11032%Fphysreva.93.062312
  * [13] K. Yonaga, M. J. Miyama, and M. Ohzeki, “Solving inequality-constrained binary optimization problems on quantum annealer,” 2020. [Online]. Available: https://arxiv.org/abs/2012.06119
  * [14] M. Perelshtein, A. Sagingalieva, K. Pinto, V. Shete, A. Pakhomchik, A. Melnikov, F. Neukart, G. Gesek, A. Melnikov, and V. Vinokur, “Practical application-specific advantage through hybrid quantum computing,” _arXiv:2205.04858_ , 2022.
  * [15] J. Varga, G. R. Raidl, and S. Limmer, “Computational methods for scheduling the charging and assignment of an on-site shared electric vehicle fleet,” _IEEE Access_ , vol. 10, pp. 105 786–105 806, 2022.
  * [16] S. Limmer, J. Varga, and G. R. Raidl, “An evolutionary approach for scheduling a fleet of shared electric vehicles,” in _Applications of Evolutionary Computation_ , J. Correia, S. Smith, and R. Qaddoura, Eds. Springer Nature Switzerland, 2023, pp. 3–18.
  * [17] M. R. Gary and D. S. Johnson, “Computers and intractability: A guide to the theory of np-completeness,” 1979.
  * [18] B. Alidaee, G. A. Kochenberger, K. R. Lewis, M. W. Lewis, and H. Wang, “A new approach for modeling and solving set packing problems,” _Eur. J. Oper. Res._ , vol. 186, pp. 504–512, 2008.
  * [19] E. Melder, “A chronology of set cover inapproximability results,” 2021.
  * [20] F. Vanderbeck, _Decomposition and Column Generation for Integer Programs_. Université catholique de Louvain, 1994. [Online]. Available: https://books.google.fr/books?id=0z_YNwAACAAJ
  * [21] P. Gilmore and R. Gomory, “A linear programming approach to the cutting stock problem—part ii,” _Operations Research_ , vol. 11, 12 1963.
  * [22] S. I. Gass and C. M. Harris, _Strong duality theorem_. New York, NY: Springer US, 2001, pp. 790–790.
  * [23] G. Desaulniers, J. Desrosiers, and M. Solomon, _Column Generation_ , 01 2005\.
  * [24] S. Kirkpatrick, C. D. Gelatt, and M. P. Vecchi, “Optimization by simulated annealing,” _Science_ , vol. 220, no. 4598, pp. 671–680, 1983. [Online]. Available: https://www.science.org/doi/abs/10.1126/science.220.4598.671
  * [25] G. Palubeckis, “Multistart tabu search strategies for the unconstrained binary quadratic optimization problem,” _Annals of Operations Research_ , vol. 131, pp. 259–282, 10 2004.
  * [26] B. Tan, M.-A. Lemonde, S. Thanasilp, J. Tangpanitanon, and D. G. Angelakis, “Qubit-efficient encoding schemes for binary optimisation problems,” _Quantum_ , vol. 5, p. 454, 2021.
  * [27] S. Yarkoni, E. Raponi, T. Bäck, and S. Schmitt, “Quantum annealing for industry applications: Introduction and review,” _Reports on Progress in Physics_ , vol. 85, no. 10, p. 104001, 2022.
  * [28] E. Farhi, J. Goldstone, and S. Gutmann, “A quantum approximate optimization algorithm,” 2014. [Online]. Available: https://arxiv.org/abs/1411.4028


Int. J. Business Process Integration and Management

Ngo, V.M., Le-Khac, N.A. and Kechadi M.T. Int. J. Business Process Integration
and Management 10 1 2020

Vuong M. Ngo E-mail<EMAIL_ADDRESS><EMAIL_ADDRESS>
Nhien-An Le-Khac E-mail<EMAIL_ADDRESS>
M-Tahar Kechadi E-mail<EMAIL_ADDRESS>

Ho Chi Minh City Open University, HCMC, Vietnam
University College Dublin, Belfield, Dublin 4, Ireland

# Data Warehouse and Decision Support on
Integrated Crop Big Data

###### Abstract

In recent years, precision agriculture is becoming very popular. The
introduction of modern information and communication technologies for
collecting and processing Agricultural data revolutionise the agriculture
practises. This has started a while ago (early 20th century) and it is driven
by the low cost of collecting data about everything; from information on
fields such as seed, soil, fertiliser, pest, to weather data, drones and
satellites images. Specially, the agricultural data mining today is considered
as Big Data application in terms of volume, variety, velocity and veracity.
Hence it leads to challenges in processing vast amounts of complex and diverse
information to extract useful knowledge for the farmer, agronomist, and other
businesses. It is a key foundation to establishing a crop intelligence
platform, which will enable efficient resource management and high quality
agronomy decision making and recommendations. In this paper, we designed and
implemented a continental level agricultural data warehouse (ADW). ADW is
characterised by its (1) flexible schema; (2) data integration from real
agricultural multi datasets; (3) data science and business intelligent
support; (4) high performance; (5) high storage; (6) security; (7) governance
and monitoring; (8) consistency, availability and partition tolerant; (9)
cloud compatibility. We also evaluate the performance of ADW and present some
complex queries to extract and return necessary knowledge about crop
management.

Data warehouse, decision support, crop Big Data, smart agriculture.

to this paper should be made as follows: Ngo, V.M., Le-Khac, N.A. and Kechadi,
M.T. (2020) ‘Data Warehouse and Decision Support on Integrated Crop Big Data’,
Int. J. Business Process Integration and Management, Vol. 10, No. 1, pp.
17–28.

Vuong M. Ngo received the B.E, M.E and PhD degrees in computer science at HCMC
University of Technology in 2004, 2007 and 2013 respectively. He is currently
a Senior Researcher at UCD and HCMC Open University. His research interests
include information retrieval, sentiment analysis, data mining, graph matching
and data

Nhien-An Le-Khac is currently a Lecturer at the School of Computer Science,
UCD and a Programme Director of MSc programme in forensic computing and
cybercrime investigation. He obtained his PhD in computer science in 2006 at
the Institut National Polytechnique Grenoble, France. His research interest
spans the area of cybersecurity and digital forensics, data mining/distributed
data mining for security, grid and high performance computing.

M-Tahar Kechadi was awarded PhD and Master degrees in computer science from
University of Lille 1, France. He joined the UCD School of Computer Science in
1999. He is currently Professor of Computer Science at UCD. His research
interests span the areas of data mining, data analytics, distributed data
mining, heterogeneous distributed systems, grid and cloud Computing,
cybersecurity, and digital forensics. He is a Principal Investigator at
Insight Centre for Data Analytics and CONSUS project. He is a member of IEEE
and ACM.

##  1 Introduction

Annual world cereal productions were $2,608$ million tons and $2,595$ million
tons in $2017$ and $2018$, respectively (USDA report, 2018; FAO-CSDB report,
2018). However, there were also around $124$ million people in $51$ countries
faced food crisis and food insecurity (FAO-FSIN report, 2018). According to
United Nations (UN document, 2017), we need an increase $60\%$ of cereal
production to meet $9.8$ billion people needs by $2050$. To satisfy the huge
increase demand for food, crop yields must be significantly increased using
modern farming approaches, such as smart farming also called precision
agriculture. As highlighted in the European Commission report (EC report,
2016), precision agriculture is vitally important for the future and can make
a significant contribution to food security and safety.

The precision agriculture’s current mission is to use the decision-support
system (DSS) based on Big Data approaches to provide precise information for
more control of waste and farming efficiency, such as soil nutrient (Rogovska
and et al., 2019), early warning (Rembold and et al., 2019), forecasting
(Bendre and et al., 2015), irrigation systems (Huang and et al., 2013),
evapotranspiration prediction (Paredes and et al., 2014), soil and herbicide,
insecticide optimisation (Ngo and Kechadi, 2020), awareness (Lokers and et
al., 2016), supply chain (Protopop and Shanoyan, 2016) and financial services
(Ruan and et al., 2019). Normally, the DSSs implement a knowledge discovery
process also called data mining process, which consists of data collection and
data modelling, data warehousing, data analysis (using machine learning or
statistical techniques), and knowledge deployment (Dicks and et al., 2014).
Hence, designing and implementing an efficient agricultural data warehouse
(ADW) is one of the key steps of this process, as it defines a uniform data
representation through its schema model and stores the derived datasets so
that they can be analysed to extract useful knowledge. However, currently,
this step was not given much attention. Therefore, there are very few reports
in the literature that focus on the design of efficient ADWs with the view to
enable Agricultural Big Data analytics and mining. The design of large scale
ADWs is very challenging. Because, the agricultural data is spatial, temporal,
complex, heterogeneous, non-standardised, high dimensional, collected from
multi-sources, and very large. In particular, it has all the features of Big
Data; volume, variety, velocity and veracity. Moreover, the precision
agriculture system can be used by different kinds of users at the same time,
for instance by farmers, policymakers, agronomists, and so on. Every type of
user needs to analyse different information, sets thus requiring specific
analytics.

Unlike in any other domains; health-care, financial data, etc, the data and
its warehousing in precision agriculture are unique. This is because, there
are very complex relationships between the agricultural data dimensions. The
data sources are very diversified and varying levels of quality. Precision
agriculture (PA) warehousing has many decision-making processes and each needs
different levels of data access and different needs of analysis. Finally,
there are many stakeholders involved in the data ownership and exploitation.
So, the data has significant number of uncertainties. For examples, the
quality of data collected by farmers depends directly on their knowledge,
routines and frequency of information recording, and support tools, etc. All
these issues make the PA data unique when it becomes to its storage, access,
and analysis. These issues may exist in other domains, but not at the same
scale and as in agriculture practices.

In this research, we firstly analyse real-world agricultural Big Data to build
the effective constellation schema. From this schema, some simple questions
can be easily answered directly from the modelled data. These questions
include: (1) For a given field, what kind of crops are suitable to grow? (2)
Which companies can purchase a specific crop with the highest price in the
past season? (3) List the history of soil texture and applied fertilisers for
a given field; (4) List costs of production for wheat and barley in the last 5
years, and so on. Secondly, the proposed ADW has enough main features and
characteristics of Big Data Warehouse (BDW). These are (1) high storage
capacity, high performance and cloud computing compatibility; (2) flexible
schema and integrated storage structure; (3) data ingestion, monitoring, and
security to deal with the data veracity. Besides, an experimental evaluation
is conducted to study the performance of ADW storage.

The rest of this paper is organised as follows: in the next Section, we
reviewed the related work about decision support systems and data warehouses
in agriculture. In Sections 3, 4 and 5, we presented big data aspects of PA,
our ADW architecture and its modules. In Sections 6, 7, 8 and 9, the quality
criteria, implementation, performance analysis and decision-making
applications of the proposed ADW are presented respectively. Section 10 gives
some concluding remarks and future research directions. Finally, a concrete
example about the ADW and its operational average run-times are shown in the
appendix.

##  2 Related Work

In precision agriculture, DSSs are designed to support different stakeholders
such as farmers, advisers and policymakers to optimise resources, farms’
management and improve business practices (Gutierreza and et al., 2019). For
instance, DSSs were built to 1) manage microbial pollution risks in dairy
farming (Oliver and et al., 2017); 2) analyse nitrogen fertilisation from
satellite images (Lundstrom and Lindblom, 2018); 3) control pest and disease
under uncertainty in climate conditions (Devitt and et al., 2017); 4) manage
drip irrigation and its schedule (Friedman and et al., 2016); 5) predict and
adopt climate risks (Han and et al., 2017). However, the datasets that were
used in the mentioned studies are small. Besides, they focused on using
visualisation techniques to assist end-users understand and interpret their
data.

Recently, many papers have been published on how to exploit intelligent
algorithms on sensor data to improve agricultural economics Pantazi (2016),
Park and et al. (2016), Hafezalkotob and et al. (2018), Udiasa and et al.
(2018) and Rupnik and et al. (2019). In Pantazi (2016), the authors predicted
crop yield by using self-organising-maps; namely supervised Kohonen networks,
counter-propagation artificial networks and XY-fusion. In Park and et al.
(2016), one predicted drought conditions by using three rule-based machine
learning; namely random forest, boosted regression trees, and Cubist. To
select the best olive harvesting machine, the authors in Hafezalkotob and et
al. (2018) applied the target-based techniques on the main criteria, which are
cost, vibration, efficiency, suitability, damage, automation, work capacity,
ergonomics, and safety. To provide optimal management of nutrients and water,
the paper Udiasa and et al. (2018) exploited the multi-objective genetic
algorithm to implement an E-Water system. This system enhanced food crop
production at river basin level. Finally, in Rupnik and et al. (2019) the
authors predicted pest population dynamics by using time series clustering and
structural change detection which detected groups of different pest species.
However, the proposed solutions are not scalable enough to handle agricultural
Big Data; they present weaknesses in one of the following aspects: data
integration, data schema, storage capacity, security and performance.

From a Big Data point of view, the papers Kamilaris and et al. (2018) and
Schnase and et al. (2017) have proposed “smart agricultural frameworks”. In
Kamilaris and et al. (2018), the authors used Hive to store and analyse sensor
data about land, water and biodiversity which can help increase food
production with less environmental impact. In Schnase and et al. (2017), the
authors moved toward a notion of climate analytics-as-a-service, by building a
high-performance analytics and scalable data management platform, which is
based on modern cloud infrastructures, such as Amazon web services, Hadoop,
and Cloudera. However, the two papers did not discuss how to build and
implement a DW for a precision agriculture.

The proposed approach, inspired from Schulze and et al. (2007), Schuetz and et
al. (2018), Nilakanta and et al. (2008) and Ngo and et al. (2018), introduces
ways of building agricultural data warehouse (ADW). In Schulze and et al.
(2007), the authors extended entity-relationship concept to model operational
and analytical data; called multi-dimensional entity-relationship model. They
also introduced new representation elements and showed how can be extended to
an analytical schema. In Schuetz and et al. (2018), a relational database and
an RDF triple store were proposed to model the overall datasets. The data is
loaded into the DW in RDF format, and cached in the RDF triple store before
being transformed into relational format. The actual data used for analysis
was contained in the relational database. However, as the schemas used in
Schulze and et al. (2007) and Schuetz and et al. (2018) were based on entity-
relationship models, they cannot deal with high-performance, which is the key
feature of a data warehouse.

In Nilakanta and et al. (2008), a star schema model was used. All data marts
created by the star schemas are connected via some common dimension tables.
However, a star schema is not enough to present complex agricultural
information and it is difficult to create new data marts for data analytics.
The number of dimensions of the DW proposed in Nilakanta and et al. (2008) is
very small; only 3-dimensions – Species, Location, and Time. Moreover, the DW
concerns livestock farming. Overcoming disadvantages of the star schema, the
authors of Ngo and et al. (2018) and Ngo and Kechadi (2020) proposed a
constellation schema for an agricultural DW architecture in order to satisfy
the quality criteria. However, they did not describe how to design and
implement their DW.

##  3 Crop Big Data

###  3.1 Crop Datasets

The datasets were primarily obtained from an agronomy company, which extracted
it from them operational data storage systems, research results, and field
trials. Especially, we were given real-world agricultural datasets on iFarms,
Business-to-Business (B2B) sites, technology centres and demonstration farms.
Theses datasets were collected from several European countries and they are
presented in Figures 1 and 2 (Origin report, 2018). These datasets describe
more than $112$ distribution points, $73$ demonstration farms, $32$
formulation and processing facilities, $12.7$ million hectares of direct farm
customer footprint and $60,000$ trial units.

Figure 1: Data from UK and Ireland.  Figure 2: Data in Continental Europe.

There is a total of 29 datasets. On average, each dataset contains $18$ tables
and is about $1.4$ GB in size. Each dataset focuses on a few information that
impact the crop. For instance, the weather dataset includes information on
location of weather stations, temperature, rainfall and wind speed over time.
Meanwhile, soil component information in farm sites, such as mineral, organic
matter, air, water and micro-organisms, were stored in the soil dataset. The
fertiliser dataset contains information about field area and geographic
position, crop name, crop yield, season, fertiliser name and quantity.

###  3.2 Big Data Challenges

Raw and semi-processed agricultural datasets are usually collected through
various sources: Internet of Thing (IoT) devices, sensors, satellites, weather
stations, robots, farm equipment, farmers and agronomists, etc. Besides,
agricultural datasets are very large, complex, unstructured, heterogeneous,
non-standardised, and inconsistent. Hence, it has all the features of Big
Data.

  1. 1.

Volume: The amount of agricultural data is increasing rapidly and is
intensively produced by endogenous and exogenous sources. The endogenous data
is collected from operational systems, experimental results, sensors, weather
stations, satellites, and farming equipment. The systems and devices in the
agricultural ecosystem can be connected through IoT. The exogenous data
concerns the external sources, such as government agencies, retail
agronomists, and seed companies. They can help with information about local
pest and disease outbreak tracking, crop monitoring, food security, products,
prices, and knowledge.

  2. 2.

Variety: Agricultural data has many different forms and formats, structured
and unstructured data, video, imagery, chart, metrics, geo-spatial, multi-
media, model, equation, text, etc.

  3. 3.

Velocity: The collected data increases at very high rate, as sensing and
mobile devices are becoming more efficient and cheaper. The datasets must be
cleaned, aggregated and harmonised in real-time.

  4. 4.

Veracity: The tendency of agronomic data is uncertain, inconsistent, ambiguous
and error prone because the data is gathered from heterogeneous sources,
sensors and manual processes.

###  3.3 ADW Schema

Figure 3: A part of ADW schema for Precision Agriculture

The DW uses schema to logically describe the entire datasets. A schema is a
collection of objects, including tables, views, indexes, and synonyms which
consist of some fact and dimension tables (Oracle document, 2017). The DW
schema can be designed based on the model of source data and the user
requirements. There are three kind of models, namely star, snowflake and fact
constellation. With the its various uses, the ADW schema needs to have more
than one fact table and should be flexible. So, the constellation schema, also
known galaxy schema should be used to design the ADW schema.

Figure 4: Field and Crop dimension tables Figure 5: Soil and Pest dimension
tables

We developed a constellation schema for ADW and it is partially described in
Figure 3. It includes few fact tables and many dimension tables. FieldFact
fact table contains data about agricultural operations on fields. Order and
Sale fact tables contain data about farmers’ trading operations. The key
dimension tables are connected to their fact table. There are some dimension
tables connected to more than one fact table, such as Crop and Farmer.
Besides, CropState, Inspection, Site, and Weather Reading dimension tables are
not connected to any fact table. CropState and Inspection tables are used to
support Crop table. While, Site and Weather Reading tables support Field and
WeatherStation tables. FieldFact fact table saves the most important facts
about teh field; yield, water volume, fertiliser quantity, nutrient quantity,
spray quantity and pest number. While, in Order and Sale tables, the important
facts needed by farm management are quantity and price.

Table 1: Descriptions of other dimension tables No. |  Dim. tables |  Particular attributes
---|---|---
1 |  Business |  BusinessID, Name, Address, Phone, Mobile, Email
2 |  CropState |  CropStateID, CropID, StageScale, Height, MajorStage, MinStage, MaxStage, Diameter, MinHeight, MaxHeight, CropCoveragePercent
3 |  Farmer |  FarmerID, Name, Address, Phone, Mobile, Email
4 |  Fertiliser |  FertiliserID, Name, Unit, Status, Description, GroupName
5 |  Inspection |  InspectionID, CropID, Description, ProblemType, Severity, ProblemNotes, AreaValue, AreaUnit, Order, Date, Notes, GrowthStage
6 |  Nutrient |  NutrientID, NutrientName, Date, Quantity
7 |  Operation Time |  OperationTimeID, StartDate, EndDate, Season
8 |  Plan |  PlanID, PName, RegisNo, ProductName, ProductRate, Date, WaterVolume
9 |  Product |  ProductID, ProductName, GroupName
10 |  Site |  SiteID, FarmerID, SiteName, Reference, Country, Address, GPS, CreatedBy
11 |  Spray |  SprayID, SprayProductName, ProductRate, Area,Date, WaterVol, ConfDuration, ConfWindSPeed, ConfDirection, ConfHumidity, ConfTemp, ActivityType
12 |  Supplier |  SupplierID, Name, ContactName, Address, Phone, Mobile, Email
13 |  Task |  TaskID, Desc, Status, TaskDate, TaskInterval, CompDate, AppCode
14 |  Trans Time |  TransTimeID, OrderDate, DeliverDate, ReceivedDate, Season
15 |  Treatment |  TreatmentID, TreatmentName, FormType, LotCode, Rate, ApplCode, LevlNo, Type, Description, ApplDesc, TreatmentComment
16 |  Weather Reading |  WeatherReadingID, WeatherStationID, ReadingDate, ReadingTime, AirTemperature, Rainfall, SPLite, RelativeHumidity, WindSpeed, WindDirection, SoilTemperature, LeafWetness
17 |  Weather Station |  WeatherStationID, StationName, Latitude, Longitude, Region

The dimension tables contain details on each instance of an object involved in
a crop yield or farm management. Figure 4 describes attributes of Field and
Crop dimension tables. Field table contains information about name, area, co-
ordinates (being longitude and latitude of the centre point of the field),
geometric (being a collection of points to show the shape of the field) and
site identify the site that the field it belongs to. While, Crop table
contains information about name, estimated yield of the crop (estYield), BBCH
Growth Stage Index (BbchScale), harvest equipment and its weight. These
provide useful information for crop harvesting.

Figure 5 describes attributes of Soil and Pest dimension tables. Soil table
contains information about PH value (a measure of the acidity and alkalinity),
minerals (nitrogen, phosphorus, potassium, magnesium and calcium), its texture
(texture label and percentage of Silt, Clay and Sand), cation exchange
capacity (CEC) and organic matter. Besides, information about recommended
nutrient and testing dates ware also included in this table. In Pest table
contains name, type, density, coverage and detected dates of pests. For the
remaining dimension tables, their main attributes are described in Table 1.

##  4 ADW Architecture

A DW is a federated repository for all the data that an enterprise can collect
through multiple heterogeneous data sources; internal or external. The authors
in Golfarelli and Rizzi (2009) and Inmon (2005) defined DW as a collection of
methods, techniques, and tools used to conduct data analyses, make decisions
and improve information resources. DW is defined around key subjects and
involves data cleaning, data integration and data consolidations. Besides, it
must show its evolution over time and is not volatile.

The general architecture of a typical DW system includes four separate and
distinct modules; Raw Data, Extraction Transformation Loading (ETL),
Integrated Information and Data Mining (Kimball and Ross, 2013), which is
illustrated in Figure 6. In that, Raw Data (source data) module is originally
stored in various storage systems (e.g. SQL, sheets, flat files, …). The raw
data often requires cleansing, correcting noise and outliers, dealing with
missing values. Then it needs to be integrated and consolidated before loading
it into a DW storage through ETL module.

Figure 6: Agricultural Data Warehouse Architecture.

The Integrated Information module is a logically centralised repository, which
includes the DW storage, data marts, data cubes and OLAP engine. The DW
storage is organised, stored and accessed using a suitable schema defined by
the metadata. It can be either directly accessed or used to create data marts,
which is usually oriented to a particular business function or an enterprise
department. A data mart partially replicates DW storage’s contents and is a
subset of DW storage. Besides, the data is extracted in a form of data cube
before it is analysed in the data mining module. A data cube is a data
structure that allows advanced analysis of data according to multiple
dimensions that define a given problem. The data cubes are manipulated by the
OLAP engine. The DW storage, data mart and data cube are considered as
metadata, which can be applied to the data used to define other data. Finally,
Data Mining module contains a set of techniques, such as machine learning,
heuristic, and statistical methods for data analysis and knowledge extraction
at multiple level of abstraction.

##  5 ETL and OLAP

The ETL module contains Extraction, Transformation, and Loading tools that can
merge heterogeneous schemata, extract, cleanse, validate, filter, transform
and prepare the data to be loaded into a DW. The extraction operation allows
to read, retrieve raw data from multiple and different types of data sources
systems and store it in a temporary staging. During this operation, the data
goes through multiple checks – detect and correct corrupted and/or inaccurate
records, such as duplicate data, missing data, inconsistent values and wrong
values. The transformation operation structures, converts or enriches the
extracted data and presents it in a specific DW format. The loading operation
writes the transformed data into the DW storage. The ETL implementation is
complex, and consuming significant amount of time and resources. Most DW
projects usually use existing ETL tools, which are classified into two groups.
The first is a commercial and well-known group and includes tools such as
Oracle Data Integrator, SAP Data Integrator and IBM InfoSphere DataStage. The
second group is famous for it open source tools, such as Talend, Pentaho and
Apatar.

OLAP is a category of software technology that provides the insight and
understanding of data in multiple dimensions through fast, consistent,
interactive access, management and analysis of the data. By using roll-up
(consolidation), drill-down, slice-dice and pivot (rotation) operations, OLAP
performs multidimensional analysis in a wide variety of possible views of
information that provides complex calculations, trend analysis and
sophisticated data modelling quickly. The OLAP systems are divided into three
categories: 1) Relational OLAP (ROLAP), which uses relational or extended-
relational database management system to store and manage the data warehouse;
2) Multidimensional OLAP (MOLAP), which uses array-based multidimensional
storage engines for multidimensional views of data, rather than in a
relational database. It often requires pre-processing to create data cubes. 3)
Hybrid OLAP (HOLAP), which is a combination of both ROLAP and MOLAP. It uses
both relational and multidimensional techniques to inherit the higher
scalability of ROLAP and the faster computation of MOLAP.

In the context of agricultural Big Data, HOLAP is more suitable than both
ROLAP and MOLAP because: 1) ROLAP has quite slow performance and does not meet
all the users’ needs, especially when performing complex calculations; 2)
MOLAP is not capable of handling detailed data and requires all calculations
to be performed during the data cube construction; 3) HOLAP inherits
advantages of both ROLAP and MOLAP, which allow the user to store large data
volumes of detailed information and perform complex calculations within
reasonable response time.

##  6 Quality Criteria

The accuracy of data mining and analysis techniques depends on the quality of
the DW. As mentioned in Adelman and Moss (2000) and Kimball and Ross (2013),
to build an efficient ADW, the quality of the DW should meet the following
important criteria:

  1. 1.

Making information easily accessible.

  2. 2.

Presenting consistent information.

  3. 3.

Integrating data correctly and completely.

  4. 4.

Adapting to change.

  5. 5.

Presenting and providing right information at the right time.

  6. 6.

Being a secure bastion that protects the information assets.

  7. 7.

Serving as the authoritative and trustworthy foundation for improved decision
making. The analytics tools need to provide right information at the right
time.

  8. 8.

Achieving benefits, both tangible and intangible.

  9. 9.

Being accepted by DW users.

The above criteria must be formulated in a form of measurements. For example,
with the 8th criterion, it needs to determine quality indicators about
benefits, such as improved fertiliser management, cost containment, risk
reduction, better or faster decision, and efficient information transaction.
In the last criterion, a user satisfaction survey should be used to find out
how a given DW satisfies its user’s expectations.

##  7 ADW Implementation

Currently, there are many popular large-scale database types that can
implement DWs. Redshift (Amazon document, 2018), Mesa (Gupta and et al.,
2016), Cassandra (Hewitt and Carpenter, 2016; Neeraj, 2015), MongoDB
(Chodorow, 2013; Hows and et al., 2015) and Hive (Du, 2018; Lam and et al.,
2016). In Ngo and et al. (2019), the authors analysed the most popular no-sql
databases, which fulfil most of the aforementioned criteria. The advantages,
disadvantages, as well as similarities and differences between Cassandra,
MongoDB and Hive were investigated carefully in the context of ADW. It was
reported that Hive is a better choice as it can be paired with MongoDB to
implement the proposed ADW for the following reasons:

  1. 1.

Hive is based on Hadoop which is the most powerful cloud computing platform
for Big Data. Besides, HQL is similar to SQL which is popular for the majority
of users. Hive supports well high storage capacity, business intelligent and
data science more than MongoDB or Cassandra. These Hive features are useful to
implement ADW.

  2. 2.

Hive does not have real-time performance so it needs to be combined with
MongoDB or Cassandra to improve its performance.

  3. 3.

MongoDB is more suitable than Cassandra to complement Hive because: 1) MongoDB
supports joint operation, full text search, ad-hoc query and second index
which are helpful to interact with the users. Cassandra does not support these
features; 2) MongoDB has the same master – slave structure with Hive that is
easy to combine. While the structure of Cassandra is peer - to - peer; 3) Hive
and MongoDB are more reliable and consistent. So the combination of both Hive
and MongoDB adheres to the CAP theorem.

Figure 7: Agricultural Data Warehouse Implementation

The ADW implementation is illustrated in Figure 7 which contains three
modules, namely Integrated Information, Products and Raw Data. The Integrated
Information module includes two components; MongoDB and Hive. MongoDB receives
real-time data; as user data, logs, sensor data or queries from Products
module, such as web application, web portal or mobile app. Besides, some
results which need to be obtained in real-time will be transferred from the
MongoDB to Products. Hive stores the online data and sends the processed data
to MongoDB. Some kinds of queries having complex calculations will be sent
directly to Hive.

In the Raw Data module, almost data in Operational Databases or External Data
components, is loaded into Cassandra. It means that we use Cassandra to
represent raw data storage. Hence, with the diverse formats of raw data;
image, video, natural language and sql data, Cassandra is better to store them
than SQL databases. In the idle times of the system, the updated raw data in
Cassandra will be imported into Hive through the ELT tool. This improves the
performance of ETL and helps us deploy ADW on cloud or distributed systems.

##  8 Performance Analysis

The performance analysis was conducted using MySQL 5.7.22, JDK 1.8.0_171,
Hadoop 2.6.5 and Hive 2.3.3 which run on Bash, on Ubuntu 16.04.2, and on
Windows 10. All experiments were run on a desktop with an Intel Core i7 CPU
(2.40 GHz) and 16 GB memory. We only evaluate the performance of reading
operation as ADW is used for reporting and data analysis. The database of ADW
is duplicated into MySQL to compare performance. By combining popular HQL/SQL
commands, namely Where, Group by, Having, Left (right) Join, Union and Order
by, we created 10 groups for testing. Every group has 5 queries and uses one,
two or more commands (see Table 2). Moreover, every query uses operators; And,
Or, $\geq$, Like, Max, Sum and Count, to express complex queries.

Table 2: Command combinations of queries Group | Commands
---|---
$G_{1}$ | Where
$G_{2}$ | Where, Group by
$G_{3}$ | Where, Left (right) Join
$G_{4}$ | Where, Union
$G_{5}$ | Where, Order by
$G_{6}$ | Where, Left (right) Join, Order by
$G_{7}$ | Where, Group by, Having
$G_{8}$ | Where, Group by, Having, Order by
$G_{9}$ | Where, Group by, Having, Left (right) Join,
| Order by
$G_{10}$ | Where, Group by, Having, Union, Order by
$0$$10$$20$$30$$40$$50$$0$$10$$20$$30$1Queries ($q_{i}$)Different times
($Times_{q_{i}}$)Group 1Group 2Group 3Group 4Group 5Group 6Group 7Group 8Group
9Group 10 Figure 8: Different times between MySQL and
ADW in runtime of every Query

All queries were executed three times and we took the average value of the
their execution timess. The difference in runtime between MySQL and ADW for a
query $q_{i}$ is calculated as
$Times_{q_{i}}=RT^{mysql}_{q_{i}}/RT^{ADW}_{q_{i}}$. Where,
$RT^{mysql}_{q_{i}}$ and $RT^{ADW}_{q_{i}}$ are average runtimes of query
$q_{i}$ on MySQL and ADW, respectively. Moreover, with each group $G_{i}$, the
difference in runtime between MySQL and ADW is
$Times_{G_{i}}=RT^{mysql}_{G_{i}}/RT^{ADW}_{G_{i}}$. Where,
$RT_{G_{i}}=Average(RT_{q_{i}})$ is average runtime of group $G_{i}$ on MySQL
or ADW.

Figure 8 describes the time difference between MySQL and ADW for every query.
Although running on one computer, but with large data volume, ADW is faster
than MySQL on 46 out of 50 queries. MySQL is faster for three queries
$12^{th}$, $13^{th}$ and $18^{th}$ belonging to groups $3^{rd}$ and $4^{th}$.
The two systems returned the same time for query $24^{th}$ from group
$5^{th}$. Within each query group, for fair performance comparison, the
queries combine randomly fact tables and dimensional tables. This makes
complex queries taking more time and the time difference is significant. When
varying the sizes and structures of the tables, the difference is very
significant; see Figure 8.

$0$$2$$4$$6$$8$$10$$2$$4$$6$Mean$6.24$$2.92$$1.22$$2.86$$2.27$$4.66$$3.36$$4.63$$3.16$$1.56$$3.19$Groups
($G_{i}$)Different times ($Times_{G_{i}}$) Figure 9:  Different times between
MySQL and
ADW in runtime of every group

Beside comparing runtime in every query, we aslo compare runtime of every
group presented in Figure 9. Comparing to MySQL, ADW is more than at most
(6.24 times) at group $1^{st}$ which uses only Where command, and at least
(1.22 times) at group $3^{rd}$ which uses Where and Joint commands.

12345678910Mean$0$$500$$1{,}000$$1{,}081.5$$599.7$$111.7$$790.4$$776.6$$1{,}109.2$$483$$1{,}057.3$$297.9$$571.1$$687.8$$173.4$$205.2$$91.2$$276.4$$342.8$$238$$143.7$$228.3$$94.2$$366.4$$216.1$Groups
($G_{i}$)Average runtimes (seconds)MySQLADW Figure 10: Average Runtimes of
MySQL and
ADW in every Groups

Figure 10 presents the average runtime of the 10 query groups on MySQL and
ADW. Mean, the run time of a reading query on MySQL and ADW is 687.8 seconds
and 216.1 seconds, respectively. It means that ADW is faster 3.19 times. In
the future, by deploying ADW solution on cloud or distributed systems, we
believe that the performance will be even much better than MySQL.

##  9 Application for Decision Making

The proposed ADW and study its performance on real agricultural data, we
illustrated some queries examples to show how to extract information from ADW.
These queries incorporate inputs on crop, yield, pest, soil, fertiliser,
inspection, farmer, businessman and operation time to reduce labour and
fertiliser inputs, farmer services, disease treatment and also increase
yields. These query information could not be extracted if the Origin’s
separate 29 datasets have not been integrated into ADW. The data integration
through ADW is actually improve the value of a crop management data over time
to better decision-making.

Example 1: List fields, crops in the fields, yield and pest in the field with
conditions: (1) the fields do not used ’urea’ fertilizer; (2) the crops has
’yellow rust’ or ’brown rust’ diseases; (3) the crops were grown in 2015.

    select CR.CropName, FI.FieldName, FF.Yield,
           PE.CommonName, FF.PestNumber, PE.Description
    from FieldFact FF, Crop CR, Field FI, Pest PE,
         Fertiliser FE, Inspection INS, OperationTime OP
    where FF.CropID = CR.CropID and
          FF.FieldID = FI.FieldID and
          FF.PestID = PE.PestID and
          FF.FertiliserID = FE.FertiliserID and
          CR.CropID = INS.CropID and
          FF.OperationTimeID = OP.OperationTimeID and
          FE.FertiliserName <> ’urea’ and
          (INS.Description = ’Yellow Rust’ or
                 INS.Description = ’Brown Rust’) and
          Year(INS.Date) = ’2015’ and
          Year(OP.StartDate) = ’2015’ and
          Year(OP.EndDate) = ’2015’

Example 2: List farmers and their crop quantities were sold by Ori Agro
company in 08/2016.

    select FA.FarmerID, FA.FarmerName, CR.CropName,
           SF.Unit, SUM(SF.Quantity)
    from Salefact SF, business BU, farmer FA, crop CR
    where SF.BusinessID = BU.BusinessID and
          SF.FarmerID = FA.FarmerID and
          SF.CropID = CR.CropID and
          Month(SF.SaleDate) = ’08’ and
          Year(SF.SaleDate) = ’2016’ and
          BU.BusinessName = ’Ori Agro’
    group by CR.CropName

Example 3: List Crops and their fertiliser and treatment information. In that,
crops were cultivated and harvested in 2017, Yield $>$ 10 tons/ha and attached
by ’black twitch’ pest. Besides, the soil in field has PH $>6$ and Silt $<=50$
mg/l.

    Select CR.CropName, FE.FertiliserName,
           FF.FertiliserQuantity, TR.TreatmentName,
           TR.Rate, TR.TreatmentComment
    From FieldFact FF, Crop CR, OperationTime OT,
         Soil SO, PEST PE, Fertiliser FE, Treatment TR
    Where FF.CropID = CR.CropID and
          FF.OperationTimeID = OT.OperationTimeID and
          FF.SoildID = SO.SoilID and
          FF.PestID = PE.PestID and
          FF.FertiliserID = FE.FertiliserID and
          FF.TreatmentID = TR.TreatmentID and
          Year(OT.StartDate) = ’2017’ and
          Year(OT.EndDate) = ’2017’ and
          FF.Yield > 10 and
          SO.PH > 6 and SO.Silt <= 50 and
          PE.CommonName = ’Black twitch’

Example 4: List crops, fertilisers, corresponding fertiliser quantities in
spring, 2017 in every field and site of 10 farmers (crop companies) who used
the large amount of $P_{2}O_{5}$ in winter, 2016.

To execute this request, the query needs to exploit data in the FieldFact fact
table and the six dimension tables, namely Crop, Field, Site, Farmer,
Fertiliser and OperationTime. The query consists of two subqueries which
return 10 farmers (crop companies) that used the largest amount of Urea in
spring, 2016.

    Select FI.FieldName, SI.SiteName, FA.FarmerName,
           CR.CropName, FE.FertiliserName,
           FF.FertiliserQuantity, FE.Unit, OT.StartDate
    From FieldFact FF, Crop CR, Field FI, Site SI,
         Farmer FA, Fertiliser FE, Operationtime OT
    Where FF.CropID = CR.CropID and
          FF.FieldID = FI.FieldID and
          FF.FertiliserID = FE.FertiliserID and
          FF.OperationTimeID = OT.OperationTimeID and
          FI.SiteID = SI.SiteID and
          SI.FarmerID = FA.FarmerID and
          OT.Season = ’Spring’ and
          YEAR(OT.StartDate) = ’2017’ and
          FA.FarmerID IN(
           Select FarmerID
           From
           (Select SI.FarmerID as FarmerID,
             SUM(FF.FertiliserQuantity) as SumFertiliser
            From FieldFact FF, Field FI, Site SI,
                 Fertiliser FE, OperationTime OT
            Where FF.FieldID = FI.FieldID and
                  FF.FertiliserID = FE.FertiliserID and
                  FF.OperationTimeID =
                                 OT.OperationTimeID and
                  SI.SiteID = FI.SiteID and
                  FE.FertiliserName = ’SO3’ and
                  OT.Season = ’Spring’ and
                  YEAR(OT.StartDate) = ’2016’
             Group by SI.FarmerID
             Order by SumFertiliser DESC
             Limit 10
            )AS Table1
          )

##  10 Conclusion and Future Work

In this paper, we presented a schema herein optimised for the real
agricultural datasets that were made available to us. The schema been designed
as a constellation so it is flexible to adapt to other agricultural datasets
and quality criteria of agricultural Big Data. Based on some existing popular
open source DWs, We designed and implemented the agricultural DW by combining
Hive, MongoDB and Cassandra DWs to exploit their advantages and overcome their
limitations. ADW includes necessary modules to deal with large scale and
efficient analytics for agricultural Big Data. Moreover, through particular
reading queries using popular HQL/SQL commands, ADW storage outperforms MySQL
by far. Finally, we outlined some complex HQL queries that enabled knowledge
extraction from ADW to optimize of agricultural operations.

In the future work, we shall pursue the deployment of ADW on a cloud system
and implement more functionalities to exploit this DW. The future developments
will include: (1) experimentation and analyzation the performance of MongoDB
and the affectation between MongoDB and Hive; (2) The sophisticated the data
mining and the spreading activation algorithms (Ngo, 2014) to determine crop
data characteristics and combine with expected outputs to extract useful
knowledge; (3) Predictive models based on machine learning algorithms; (4) An
intelligent interface and graph representation (Helmer and et al., 2015) for
data access; (5) Combination with the ontology to extract knowledge (Ngo and
et al., 2011; Cao and et al., 2012).

## Appendix

The followings are HQL/SQL scripts of 10 queries which are representative of
10 query groups. The average runtimes of these queries on MySQL and ADW are
shown in Figure 11.

1) The query $5^{th}$ belongs to the group $1^{st}$:

    SELECT fieldfact.FieldID, crop.cropname,
           fieldfact.yield
    FROM fieldfact, crop
    WHERE fieldfact.cropid = crop.cropid and
          SprayQuantity = 7 and
          (crop.CropName like ’P\%’ or
           crop.CropName like ’R\%’ or
           crop.CropName like ’G\%’);

2) The query $10^{th}$ belongs to the group $2^{nd}$:

    SELECT soil.PH, count(*)
    FROM fieldfact, soil
    WHERE fieldfact.SoildID = soil.SoilID and
          fieldfact.sprayquantity = 2
    GROUP by soil.PH;

5101520253035404550$0$$1{,}000$$2{,}000$$97.9$$754.8$$52.7$$2{,}297$$1{,}192$$2{,}188.4$$95.4$$265.9$$439.5$$892.4$$3$$233.2$$3.6$$479$$422.6$$226.7$$5.2$$7.6$$212.3$$472.1$Queries
($q_{i}$)Average runtimes (seconds)MySQLADW Figure 11: Average runtimes of
MySQL and
ADW in 10 typical queries

3) The query $15^{th}$ belongs to the group $3^{rd}$:

    SELECT fieldfact.yield,
           fertiliser.fertiliserName,
           fertiliser.fertiliserGroupName
    FROM fieldfact
    RIGHT JOIN fertiliser on
         fieldfact.fertiliserID = fertiliser.fertiliserID
    WHERE fieldfact.fertiliserQuantity = 10 and
          fertiliser.fertiliserName like ’%slurry%’;

4) The query $20^{th}$ belongs to the group $4^{th}$:

    SELECT sprayproductname
    FROM fieldfact, spray
    WHERE fieldfact.sprayid = spray.sprayid and
          fieldfact.watervolumn > 5 and
          fieldfact.watervolumn < 20
    UNION
    SELECT productname
    FROM product, orderfact
    WHERE product.ProductID = orderfact.ProductID
          and (orderfact.Quantity = 5 or
               orderfact.Quantity = 6);

5) The query $25^{th}$ belongs to the group $5^{th}$:

    SELECT fieldfact.fieldID, field.FieldName,
           field.FieldGPS, spray.SprayProductName
    FROM fieldfact, field, spray
    WHERE fieldfact.FieldID = field.FieldID and
          fieldfact.SprayID = spray.SprayID and
          fieldfact.PestNumber = 6
    ORDER BY field.FieldName;

6) The query $30^{th}$ belongs to the group $6^{th}$:

    SELECT fieldfact.FieldID, nutrient.NutrientName,
           nutrient.Quantity, nutrient.‘Year‘
    FROM fieldfact
    RIGHT JOIN nutrient on
         fieldfact.NutrientID = nutrient.NutrientID
    WHERE fieldfact.NutrientQuantity = 3 and
          fieldfact.fertiliserquantity = 3
    ORDER BY nutrient.NutrientName
    LIMIT 10000;

7) The query $35^{th}$ belongs to the group $7^{th}$:

    SELECT crop.cropname,
           sum(fieldfact.watervolumn) as sum1
    FROM fieldfact, crop
    WHERE fieldfact.cropid = crop.cropid and
          fieldfact.sprayquantity = 8 and
          crop.EstYield >= 1 and crop.EstYield <=10
    GROUP BY crop.cropname
    HAVING sum1 > 100;

8) The query $40^{th}$ belongs to the group $8^{th}$:

    SELECT crop.cropname,
           sum(fieldfact.fertiliserquantity) as sum1
    FROM fieldfact, crop
    WHERE fieldfact.cropid = crop.cropid and
          fieldfact.nutrientquantity= 5 and
          crop.EstYield <=1
    GROUP by crop.cropname
    HAVING sum1 > 30
    ORDER BY crop.cropname;

9) The query $45^{th}$ belongs to the group $9^{th}$:

    SELECT nutrient.NutrientName,
           sum(nutrient.Quantity) as sum1
    FROM fieldfact
    LEFT JOIN nutrient on
        fieldfact.NutrientID = nutrient.NutrientID
    WHERE nutrient.nutrientName like ’%tr%’ and
         (fieldfact.pestnumber = 16 or
          fieldfact.pestnumber = 15)
    GROUP by nutrient.NutrientName
    HAVING sum1 <300
    ORDER BY nutrient.NutrientName;

10) The query $50^{th}$ belongs to the group $10^{th}$:

    SELECT sprayproductname as name1,
           sum(fieldfact.watervolumn) as sum1
    FROM fieldfact, spray
    WHERE fieldfact.sprayid = spray.sprayid and
          fieldfact.Yield > 4 and fieldfact.Yield < 8
    GROUP by sprayproductname
    HAVING sum1 > 210
    UNION
    SELECT productname as name1,
           sum(orderfact.Quantity) as sum2
    FROM product, orderfact
    WHERE product.ProductID = orderfact.ProductID and
         (orderfact.Quantity = 5 or
          orderfact.Quantity = 6)
    GROUP by productname
    HAVING sum2 > 50
    ORDER BY name1;

## Acknowledgment

This research is an extended work of Ngo and et al. (2019) being part of the
CONSUS research program. It is funded under the SFI Strategic Partnerships
Programme (16/SPP/3296) and is co-funded by Origin Enterprises Plc.

## References

  * Adelman and Moss (2000) Adelman, S. and Moss, L. (2000).  Data warehouse project management, 1st edition.  Addison-Wesley Professional.
  * Amazon document (2018) Amazon document (2018).  Amazon Redshift database developer guide.  Samurai ML.
  * Bendre and et al. (2015) Bendre, M. R. and et al. (2015).  Big data in precision agriculture: Weather forecasting for future farming.  In International Conference on Next Generation Computing Technologies (NGCT). IEEE.
  * Cao and et al. (2012) Cao, T. and et al. (2012).  Semantic search by latent ontological features.  International Journal of New Generation Computing, Springer, SCI, 30(1):53–71.
  * Chodorow (2013) Chodorow, K. (2013).  MongoDB: The definitive guide, 2nd edition (powerful and scalable data storage).  O’Reilly Media.
  * Devitt and et al. (2017) Devitt, S. K. and et al. (2017).  A cognitive decision tool to optimise integrated weed management.  In Proceedings of International Tri-Conference for Precision Agriculture.
  * Dicks and et al. (2014) Dicks, L. V. and et al. (2014).  Organising evidence for environmental management decisions: a ‘4s’ hierarchy.  Trends in Ecology & Evolution, 29(11):607–613.
  * Du (2018) Du, D. (2018).  Apache Hive essentials, 2nd edition.  Packt Publishing.
  * EC report (2016) EC report (2016).  Europeans, agriculture and the common agricultural policy.  Special Eurobarometer 440, The European Commission.
  * FAO-CSDB report (2018) FAO-CSDB report (2018).  Global cereal production and inventories to decline but overall supplies remain adequate, release date: December 06, 2018.  Cereal Supply and Demand Brief, FAO.
  * FAO-FSIN report (2018) FAO-FSIN report (2018).  Global report on food crises 2018.  Food Security Information Network, FAO.
  * Friedman and et al. (2016) Friedman, S. P. and et al. (2016).  Didas – user-friendly software package for assisting drip irrigation design and scheduling.  Computers and Electronics in Agriculture, 120:36–52.
  * Golfarelli and Rizzi (2009) Golfarelli, M. and Rizzi, S. (2009).  Data warehouse design: modern principles and methodologies.  McGraw-Hill Education.
  * Gupta and et al. (2016) Gupta, A. and et al. (2016).  Mesa: a geo-replicated online data warehouse for google’s advertising system.  Communications of the ACM, 59(7):117–125.
  * Gutierreza and et al. (2019) Gutierreza, F. and et al. (2019).  A review of visualisations in agricultural decision support systems: An HCI perspective.  Computers and Electronics in Agriculture, 163.
  * Hafezalkotob and et al. (2018) Hafezalkotob, A. and et al. (2018).  A decision support system for agricultural machines and equipment selection: A case study on olive harvester machines.  Computers and Electronics in Agriculture, 148:207–216.
  * Han and et al. (2017) Han, E. and et al. (2017).  Climate-agriculture-modeling and decision tool (camdt): a software framework for climate risk management in agriculture.  Environmental Modelling & Software, 95:102–114.
  * Helmer and et al. (2015) Helmer, S. and et al. (2015).  A similarity measure for weaving patterns in textiles.  In In the 38th ACM SIGIR Conference on Research and Development in Information Retrieval, pages 163–172.
  * Hewitt and Carpenter (2016) Hewitt, E. and Carpenter, J. (2016).  Cassandra: the definitive guide, 2nd edition (distributed data at web scale).  O’Reilly Media.
  * Hows and et al. (2015) Hows, D. and et al. (2015).  The definitive guide to MongoDB, 3rd edition (a complete guide to dealing with big data using MongoDB.  Apress.
  * Huang and et al. (2013) Huang, Y. and et al. (2013).  Estimation of cotton yield with varied irrigation and nitrogen treatments using aerial multispectral imagery.  International Journal of Agricultural and Biological Engineering, 6(2):37–41.
  * Inmon (2005) Inmon, W. H. (2005).  Building the data warehouse.  Wiley.
  * Kamilaris and et al. (2018) Kamilaris, A. and et al. (2018).  Estimating the environmental impact of agriculture by means of geospatial and big data analysis: the case of Catalonia, pages 39–48.  Springer.
  * Kimball and Ross (2013) Kimball, R. and Ross, M. (2013).  The data warehouse toolkit: the definitive guide to dimensional modeling (3rd edition).  Wiley.
  * Lam and et al. (2016) Lam, C. P. and et al. (2016).  Hadoop in action, 2nd edition.  Manning.
  * Lokers and et al. (2016) Lokers, R. and et al. (2016).  Analysis of big data technologies for use in agro-environmental science.  Environmental Modelling & Software, 48:494–504.
  * Lundstrom and Lindblom (2018) Lundstrom, C. and Lindblom, J. (2018).  Considering farmers’ situated knowledge of using agricultural decision support systems (agridss) to foster farming practices: the case of cropsat.  Agricultural Systems, 159:9–20.
  * Neeraj (2015) Neeraj, N. (2015).  Mastering Apache Cassandra, 2nd edition.  Packt Publishing.
  * Ngo (2014) Ngo, V. (2014).  Discovering latent information by spreading activation algorithm for document retrieval.  International Journal of Artificial Intelligence & Applications, 5(1):23–34.
  * Ngo and et al. (2011) Ngo, V. and et al. (2011).  Discovering latent concepts and exploiting ontological features for semantic text search.  In In the 5th Int. Joint Conference on Natural Languag Processing, ACL, pages 571–579.
  * Ngo and et al. (2018) Ngo, V. and et al. (2018).  An efficient data warehouse for crop yield prediction.  In The 14th International Conference Precision Agriculture (ICPA-2018), pages 3:1–3:12.
  * Ngo and et al. (2019) Ngo, V. M. and et al. (2019).  Designing and implementing data warehouse for agricultural big data.  In The 8th International Congress on BigData (BigData-2019), pages 1–17. Springer-LNCS, Vol. 11514.
  * Ngo and Kechadi (2020) Ngo, V. M. and Kechadi, M. T. (2020).  Crop knowledge discovery based on agricultural big data integration.  In The 4th International Conference on Machine Learning and Soft Computing (ICMLSC), pages 1–5. ACM.
  * Nilakanta and et al. (2008) Nilakanta, S. and et al. (2008).  Dimensional issues in agricultural data warehouse designs.  Computers and Electronics in Agriculture, 60(2):263–278.
  * Oliver and et al. (2017) Oliver, D. M. and et al. (2017).  Design of a decision support tool for visualising e. coli risk on agricultural land using a stakeholder-driven approach.  Land Use Policy, 66:227–234.
  * Oracle document (2017) Oracle document (2017).  Database data warehousing guide.  Oracle12c doc release 1.
  * Origin report (2018) Origin report (2018).  Annual report and accounts.  Origin Enterprises plc.
  * Pantazi (2016) Pantazi, X. E. (2016).  Wheat yield prediction using machine learning and advanced sensing techniques.  Computers and Electronics in Agriculture, 121:57–65.
  * Paredes and et al. (2014) Paredes, P. and et al. (2014).  Partitioning evapotranspiration, yield prediction and economic returns of maize under various irrigation management strategies.  Agricultural Water Management, 135:27–39.
  * Park and et al. (2016) Park, S. and et al. (2016).  Drought assessment and monitoring through blending of multi-sensor indices using machine learning approaches for different climate regions.  Agricultural and Forest Meteorology, 216:157–169.
  * Protopop and Shanoyan (2016) Protopop, I. and Shanoyan, A. (2016).  Big data and smallholder farmers: Big data applications in the agri-food supply chain in developing countries.  International Food and Agribusiness Management Review, IFAMA, 19(A):1–18.
  * Rembold and et al. (2019) Rembold, F. and et al. (2019).  Asap: A new global early warning system to detect anomaly hot spots of agricultural production for food security analysis.  Agricultural Systems, 168:247–257.
  * Rogovska and et al. (2019) Rogovska, N. and et al. (2019).  Development of field mobile soil nitrate sensor technology to facilitate precision fertilizer management.  Precision Agriculture, 20(1):40–55.
  * Ruan and et al. (2019) Ruan, J. and et al. (2019).  A life cycle framework of green iot-based agriculture and its finance, operation, and management issues.  IEEE Communications Magazine, 57(3):90–96.
  * Rupnik and et al. (2019) Rupnik, R. and et al. (2019).  Agrodss: a decision support system for agriculture and farming.  Computers and Electronics in Agriculture, 161:260–271.
  * Schnase and et al. (2017) Schnase, J. and et al. (2017).  Merra analytic services: meeting the big data challenges of climate science through cloud-enabled climate analytics-as-a-service.  Computers, Environment and Urban Systems, 161:198–211.
  * Schuetz and et al. (2018) Schuetz, C. G. and et al. (2018).  Building an active semantic data warehouse for precision dairy farming.  Organizational Computing and Electronic Commerce, 28(2):122–141.
  * Schulze and et al. (2007) Schulze, C. and et al. (2007).  Data modelling for precision dairy farming within the competitive field of operational and analytical tasks.  Computers and Electronics in Agriculture, 59(1-2):39–55.
  * Udiasa and et al. (2018) Udiasa, A. and et al. (2018).  A decision support tool to enhance agricultural growth in the mékrou river basin (west africa).  Computers and Electronics in Agriculture, 154:467––481.
  * UN document (2017) UN document (2017).  World population projected to reach 9.8 billion in 2050, and 11.2 billion in 2100.  Department of Economic and Social Affairs, United Nations.
  * USDA report (2018) USDA report (2018).  World agricultural supply and demand estimates 08/2018.  United States Department of Agriculture.