Variety and variability are an inherent source of information wealth in schemaless sources, and executing OLAP sessions on multidimensional data in their presence has recently become an object of research. However, all models devised so far propose a “rigid” view of the multidimensional content, without taking into account variety and variability. To fill this gap, in this paper we propose V-ICSOLAP, an extension of the ICSOLAP UML profile that supports extensibility and type/name variability for each multidimensional element, as well as complex data types for measures and levels. The real case study we use to motivate and illustrate our approach is that of trajectory analysis for agricultural robots. As a proof-of-concept for V-ICSOLAP, we propose an implementation that relies on the PostgreSQL multi-model DBMS and we evaluate its performances. We also provide a validation of our UML profile by ranking it against other meta-models based on a set of quality metrics.
Big Data have now pervaded most applications domains
(marketing, industry, agriculture, health, etc.) thanks to the enhanced
analysis capabilities enabled by the 6Vs feature [
Big Data analysis rests on several methods lent from OnLine
Analytical Processing (OLAP), as well as from Artificial
Intelligence, Data Mining, and Statistics. In particular, OLAP enables
the interactive exploration of data represented according to the
multidimensional model, which relies on the concepts of analysis
subjects (facts), analysis coordinates (dimensions), and numerical
indicators (measures). Dimensions are organized into hierarchies
of levels, which allow measures to be aggregated at diferent
granularities. Multidimensional design has been extensively
investigated for almost three decades, at both the logical and the
conceptual levels. At the logical level, the most remarkable
result lies in the definition of star and snowflake schemata as best
practices for implementing multidimensional data on relational
databases [
Carrying out multidimensional modeling at the conceptual
level has several advantages; for instance, it streamlines design
because accurate conceptual schemata can be quickly derived
using a data-driven approach [
To fill this gap, in this work we propose V-ICSOLAP, an
extension of the ICSOLAP UML profile [
one of the most remarkable advantages of conceptual schemata
is that they allow designers to focus on the information content by
abstracting all implementation details away. Indeed, V-ICSOLAP
could be adopted to accurately set a multidimensional view of
data in diferent architectural scenarios:
• Data warehouses. A data warehouse (DW) is a database
aimed at supporting decision-makers in analyzing useful
data from heterogeneous sources [
Importantly, whichever the architectural scenario adopted,
VICSOLAP should be considered as the first step of a process
aimed at enabling decision makers to query multidimensional
content. Although automating this process is out of the scope
of this paper, we provide a proof-of-concept for V-ICSOLAP by
introducing a case study concerning the analysis of the activities
of agricultural robots and showing how it can be implemented in
a multi-model DW so as to take full advantage of the flexibility
introduced by this new profile. Specifically, we rely on the
PostgreSQL MMDBMS and we evaluate its performances for a set of
representative OLAP queries. Besides, to prove the efectiveness
of the proposed profile, we provide a metrics-based validation
based on the quality framework proposed by [
The paper is organized as follows. Section 2 discusses the related work; Section 3 presents the motivation and the requirements by means of a real case study; Section 4 presents the V-ICSOLAP profile; Section 5 provides a metrics-based assessment of V-ICSOLAP; Section 6 describes our proof-of-concept for V-ICSOLAP. Finally, Section 7 draws the conclusion and outlines our future work. 2
Introducing complex data in multidimensional conceptual models
has received a lot of attention in the spatio-temporal context.
Many proposals extend the Entity/Relationship model and UML
to take into account spatial, temporal, and spatio-temporal data
in DWs [
Table 1 shows that spatio-temporal complex types received an
important attention, but no work has focused on generic complex
data and graph data. Moreover, extensibility is not supported by
any work, while variability is only (partially) supported by a
specific work on multi-representation of spatial DWs. Indeed, to
the best of our knowledge, no approach gives explicit support
to variability. The closest we could find are some works about
multi-resolution in the context of spatial DWs, which support
multi-valued attribute values according to some semantics (such
as scale and usage) [
In the context of transactional databases, some works propose
conceptual models for graph data [
Finally, as already mentioned, some recent papers have
proposed approaches to enable OLAP queries on schemaless data in
presence of variety [
To conclude, how to take into account variety at a conceptual
level has not been investigated yet [
Variety and variability are among the main features of Big Data.
Commonly, variety refers to non-relational data (semi-structured
and unstructured data such as graph, images, etc.), while
variability refers to the coexistence diferent representations of the
same data entity in terms of name and type within the same data
set [
To motivate the need for a conceptual multidimensional model that supports variety and variability, in this section we present a case study concerning the analysis the data produced by autonomous agricultural robots in smart farming.
Autonomous agricultural robots are unmanned ground vehicles equipped with sensors and actuators and capable of safely and autonomously performing one or more tasks while moving in a plot following a predefined trajectory. A trajectory is an ordered list of triples, each consisting of GPS coordinates, timestamp, and speed. Analyzing the actual trajectories followed by robots is very useful to adjust the reference trajectory, avoid recurrent problems caused by persistent obstacles in the fields, etc. Robot are powered by electronic engines. The cost for recharging robot batteries, called trajectory cost, is directly proportional to the working time of the robot. An analysis of the trajectory cost and its comparison with the performed work, also considering the total distance covered, are crucial for the farmer. Note that a farmer can either have a monthly subscription with an energy operator or use an on-demand energy recharge.
Usually, robots embed ROS (Robot Operating System), a software platform used to program the main functionalities of the robots. ROS uses a topic message system to exchange information among the physical components of each robot. The exchange of these messages is represented as a graph, whose analysis is useful to reveal recurrent problems. Noticeably, robots rely on diferent implementations to sense odometry values, thus the data they produce have diferent types and names. For example, the total distance can be represented using either an integer or a double, and the corresponding attribute name can be either total distance or distance. Besides, the ROS functions that collect odometry data are periodically updated, so it is realistic to think that additional measures will be made available for analyses in the future.
Finally, farmers currently ask to analyze the trajectories and the related costs by robot type and farm (a farm is a set of plots). However, new requirements may easily emerge in the future. For instance, farmers might find out that analyzing data by robot engine power is interesting. Also, should farms be grouped into cooperatives, analyzing data by cooperative will become relevant.
From what said above we can conclude that a variety/variabilityaware multidimensional model should support: (1) complex multidimensional elements, i.e., measures that are not simple numerical values and levels that are not simple categorical attributes; (2) variability of multidimensional elements, in terms of both names and types; (3) extensible multidimensional elements, i.e., elements whose structure can evolve in the future. 4
A UML profile provides a generic extension mechanism for customizing UML models for particular domains and platforms. It is defined using stereotypes, tagged values definitions, and constraints applied to specific model elements, like Classes, Attributes, and Operations. Note that in, UML 2.0, tagged values are named meta-class properties. In this work, to avoid confusion with classical properties we will use the old terminology of tagged values.
Some UML profiles for DWs have been proposed in
literature, as mentioned in the previous section ([
ICSOLAP represents dimensions and hierarchies with package stereotypes. Levels are represented with the AggLevel class stereotype. A level is composed of descriptive attributes (DescriptiveAttribute property stereotype). A fact is modeled using the Fact class stereotype and it contains measures (NumericalMeasure). Dimensions, hierarchies, and levels are specialized according to the types of the data they represent. ICSOLAP also defines the concept of BasicIndicator, which represents how measures are aggregated along dimensions.
V-ICSOLAP is the extension to ICSOLAP we propose to cope with variety and variability. It enables a formal and non-ambiguous conceptual design of facts characterized by variable and complex elements, and supports their extensibility. Noticeably, our extension adds few new elements to ICSOLAP, so as to let its readability intact. We do not present the entire meta-model, but only focus on the multidimensional elements of ICSOLAP that we have specialized. The new elements are shown in Figure 1. We recall that, in the UML notation, boxes represent classes (abstract classes in italics), solid lines are associations, solid lines with white triangular arrow are specializations, and solid lines with black diamonds are compositions. Finally, each stereotype is connected to the classifier it enhances using an extension (solid line with black arrow).
As a working example, in the remainder of the paper we will rely on the Trajectories fact, at the core of the case study presented in Section 3. Its measures are: Trajectory (the trajectory of the robot as composed of a set of GPS/timestamp/speed tuples; Trajectory cost (the cost for the battery energy used by the robot), Total duration (the time duration of the trajectory covered by the robot), Total distance (the total length of the robot trajectory), and Message (the graph of ROS topics). These measures are analyzed according to three main dimensions: Robot (the robot used for the work; robots are grouped by their type), Location (the plots and the farms), Time (a temporal dimension with day and year granularities). The instance of V-ICSOLAP representing the Trajectories fact is shown in Figure 2; in red, the multidimensional elements that rely on extensions to ICSOLAP.
The main new elements of V-ICSOLAP are DocumentFact, DocumentDimension, DocumentHierarchy, and DocumentAggLevel. These elements specialize, respectively, the Fact, Dimension, Hierarchy, and AggLevel ICSOLAP elements by introducing a tagged value that represents the possibility of extending the corresponding multidimensional element with additional components.
First of all, variability in schemaless data implies extensibility. DocumentFact includes two Boolean tagged values (DimensionExtensibility and MeasureExtensibility) that state whether a fact can be extended with new dimensions and measures, respectively. For example, fact Trajectories has DimensionExtensibility and MeasureExtensibility set to true.
A DocumentDimension can be extended by including additional hierarchies (tagged value HiearchyExtensibility), while a DocumentHierarchy can be extended by including new levels (tagged value LevelExtensibility). Finally, the possible presence of additional DescriptiveAttributes of a DocumentLevel is represented via the tagged value DescriptiveAttributeExtensibility. For example, the package hierarchy RobotH has HiearchyExtensibility set to true, meaning that new levels can be added to aggregate the Robot level.
Variability also occurs in terms of types and names of multidimensional elements. In particular, VariableTypeDescriptiveAttribute is a specialized stereotype of a DescriptiveAttribute that is characterized by several possible types. In the same way, the Measure property stereotype is extended (VariableTypeMeasure). Note that, since any data type may be chosen to be used in OLAP queries, it is important to specify type conversion methods (ConversionMethods operation stereotype). Indeed, conversion methods for complex data (one for each ordered couple of diferent types allowed) must be defined during design by decision makers, who are expert of the application domain. VariableTypeDescriptiveAttribute and VariableTypeMeasure present then an “Undefined type” 1. An example of VariableTypeMeasure is Total distance, which comes with two conversions methods CastDistanceIntToDouble and CastDistanceDoubleToInt.
Finally, name variability is supported by VICSOLAP extension with new stereotypes for measures and level attributes: VariableNameMeasure and VariableNameDescriptiveAttribute, respectively. For example, Total duration has two names in the tagged value Names: Total duration and Duration. Figure 3 shows an OCL constraint defined in the context of the VariableNameMeasure stereotype to check that Names contains at least two items.
Note that variability in the structure of documents in terms of missing levels is already supported by ICSOLAP.
As to complex data modeling implied by variety, ICSOLAP already handles alphanumeric, spatial, and temporal types for measures and levels. However, in the context of Big Data new complex types can appear, therefore, in V-ICSOLAP we introduce some new complex types, namely, Graph and ComplexObject. A Graph class is a composition of Vertex and Arc (i.e. edge) classes stereotypes. These classes may have some fixed properties, but they can also be extended with additional ones; this is represented with the VertexExtensibility tagged value. An example of Graph is the Ros Graph Topic class. ComplexObject is a class extension meant to be used by ComplexMeasure and ComplexDescriptiveAttribute, which represent measures and levels whose type is not atomic but class. Two examples of ComplexObject are the Odometry and EnergyCost classes. EnergyCost is abstract and is specialized in two classes, namely, Subscription and CostUnit.
Our UML profile implemented with Papyrus is available as open-source project. 2 5
In this section we provide a metrics-based validation of the quality
of our UML profile based on the framework proposed in [
Our goal is to rank V-ICSOLAP and ICSOLAP, in terms of quality,
against other meta-models in the literature. Thus, we proceed by
ifrst computing these metrics for both ICSOLAP and V-ICSOLAP
based on the numbers of elements of the meta-model (such as
meta-classes, properties, associations, etc.). Then, we evaluate
ICSOLAP and V-ICSOLAP against the 2500 meta-models considered
in [
The results are shown in Figures 4 and 5. Figure 4 shows
that V-ICSOLAP performs better than ICSOLAP as to reusability,
extensibility, and functionality; it is roughly as well-structured
as ICSOLAP, while it is worst for understandability —which is
not surprising, considering that the domain it models is more
complex than the one modeled by ICSOLAP. On the other hand,
Figure 5 positions V-ICSOLAP and ICSOLAP with their percentile
values amongst the 2500 meta-models considered in [
Finally, since V-ICSOLAP is very close to ICSOLAP and ICSOLAP has been efectively used in several real projects, we can argue that also V-ICSOLAP could be successfully applied in real complex situations. 6
We have implemented V-ICSOLAP using Papyrus, an open-source UML tool based on Eclipse. Papyrus allows for defining UML profiles by means of stereotypes and OCL constraints. Figure 6 shows how constraints work on Papyrus. The example refers to measure Total duration with VariableNamesMeasure stereotype; the number of defined Names is only one, hence, the constraint in Figure 3 is violated.
In this section we provide a proof-of-concept for V-ICSOLAP by showing how the class diagram for the trajectory case study can be faithfully translated into a logical schema on the PostgreSQL DBMS and eficiently queried. 6.1
In this section we explain how the class diagram for the Trajectories fact, shown in Figure 2, can be implemented in a database. As 7 6 5 4 3 2 1 0 --12 Reusability
explained in the following, the translation of the class diagram into a logical schema has been carried out manually; although partially automating this translation towards some target logical
Reusability
model is clearly possible given a set of design best practices, it is out of scope for this paper and is left for future work. We also show some OLAP queries on this database that address variability issues on the one hand, deal with complex data on the other.
A classical implementation relying on the relational model would not properly cope with the peculiar features of this fact, thus, we opted for the multi-model paradigm, which has been recently introduced for DBMSs to take advantage of diferent models. Specifically, we chose the PostgreSQL MMDBMS (plus the AgensGraph extension, bitnine.net/agensgraph/) since it handles relational, graph, and JSON data, which are the types of data used in our case study. Indeed, source data for the Trajectories fact come in diferent formats: (i) graph for ROS topic messages data, (ii) JSON for odometry data, and (iii) CSV with diverse structures for the remaining data.
Starting from the class diagram of Figure 2 and following
the design guidelines defined in [
JSON allows to handle the name and type variability of measures, levels, and descriptive attributes by means the oneOf JSON keyword, which forces each document to have only one of the listed attributes. An example of use of the oneOf keyword is measure total_distance for both name and type variability, since it can have either number or integer type.
To model extensibility, the idea is to add a JSON attribute to the table representing the fact or the dimension. An example is the JSON attribute measures_extens, added to the fact table to cope with the fact extensibility. 6.2
In this section we show some OLAP queries over this logical schema, and we highlight how the particular JSON keywords used to handle complex types, variability, and extensibility also influence the query definition. The goal is to show that querying a multi-model database created starting from a V-ICSOLAP class diagram is feasible with limited efort, and that an analyst can uniformly query all the data despite their variability. Clearly, this requires that the analyst knows the naming conventions in the diferent source documents; making this transparent to the user would require ofering a layer of generic names that cover all variability, and is outside the scope of this paper.
Measure name and type variability are solved using in the select statement the concat operator and the cast operator, respectively. For instance, Figure 8 shows the SQL statement that returns the average duration per robot. When name variability concerns a level in an aggregated query, formulation becomes more complex. For example, assuming that the robot level is affected by name variability, a subquery solving name variability on the robot_name/robot level should be defined to be then used for the group by.
The concat and cast operators provide general solutions to name and type variability, respectively, for JSON data. In presence of complex measures, this is not enough and ad-hoc statements must be used to cope with the particularities of measure structures. As a first example, Figure 9.a shows the statement that computes the energy cost per day. We recall from Section 3 that farmers can either have a monthly subscription with an energy operator or use an on-demand energy recharge; the subscription price is not defined at the day, robot, and plot level. Following our multi-model approach, data can be loaded from sources into the fact table in their native format, so there is no need to apply a transformation to trajectory_cost measure to artificially disaggregate subscription costs. As a result, decision makers can choose at query time the transformation they prefer; in Figure 9.a, subscription costs per day are assumed to be 0.
Another example is the trajectory complex measure, for which we have implemented a query that finds the odometry data with maximum speed value per robot, farm and year, thus allowing farmers to understand the mechanical behaviour of their robots (Figure 9.b). This query is implemented with the concat operators previously mentioned. Moreover, this query uses the JSON operator jsonarrayelementstext that transforms the JSON array into a set of tuples, in order be able to use the max aggregation operator.
For the graph complex measure, we have implemented the graph intersection as shown in Figure 9.c. This query uses the Cypher language of AgensGraph combined to classical SQL operators. Interestingly, the join between the fact table and the graph nodes is obtained with the Cypher match operator. 6.3
In this section we provide some tests to assess the eficiency of OLAP queries over the logical schema of Figure 7. In particular, as shown in Table 2, we defined a set of OLAP queries that involve name and type variability for measures and levels, as well as complex measures. Some of these queries are the ones described in the previous section. Table 2 shows the variety/variability features involved in the query, the attributes used for grouping data (i.e., the GROUP BY clause), and the attributes used for selection (WHERE clause). In particular, 1 (Figure 8), 2, and 3 involve name and type variability for numerical measures, while 4 groups data by robot name —which presents a name variability. Queries 5, 6, and 7 use the complex measure (we recall that can be an on-demand cost, which is daily measured, Dim_Date <pk> date_id : Integer day year : Date : Date :RosTopic frequency : Double message_age : Double value : String :rosNode node_id : Integer value : String
Fact_Trajectories <pk,fk> date_id <pk,fk> plot_id <pk,fk> robot_id node_id total_duration total_distance trajectory trajectory_cost dimension_extens : JSON measure_extens : JSON : Integer : Integer : Integer : Integer : JSON : JSON : JSON : JSON and a monthly cost associated to a subscription by the farmer). Specifically, 5 (Figure 9.a) uses only the on-demand cost without taking into account the subscription price. Query 6 aggregates data by day; then, a simple ratio by the number of days per month
Dim_Plot <pk> plot_id
: Integer plot_name : String farm_name : String geo extensibility : JSON
: Geometry trajectory { "$schema": "http://json-schema.org/draft-04/schema#", "type": "object", "properties": { "trajectory": { "type": "array", "items": [ { "type": "object", "properties": { "gps": { "type": "string" }, "timestamp": { "type": "string" }, "speed": { "type": "number" } }, "required": [ "gps", "timestamp", "speed" ] } ] } } } }
Dim_Robot <pk> robot_id
: Integer robot_name : String type_name : String engine
: JSON extensibility : JSON engine { "$schema": "http://json-schema.org/draft-04/schema#", "type": "object", "properties": { "engine": { "type": [ "integer", "number" ], "minimum" : 0 } }, "required": [ "engine" ] } is used to translate the monthly cost of subscription to a daily one. Finally, 7 sums all (daily and monthly) costs per year. Query 8 (Figure 9.b) uses the complex measure representing the trajectory (set of Odometry objects), and finally 9 (Figure 9.c) involves the graph measure as previously described. Overall, these queries represents a realistic workload covering all the features that can be present in a variety/variability-aware DW.
Using the logical schema in Figure 7, we have simulated a fact table with 1.57 million tuples. Each trajectory contains 1440 points. Figure 10 shows the execution time of the diferent queries in the workload. The tests have been done using an i3 with 2CPU @ 2.20 GHz laptop with 8GB RAM and SSD running on Windows10 version 10H2. Note that queries that involve classical measures and levels with name and type variability have small computation time. Queries 8 and 9 yield worse performances since aggregation is more complex (8 aggregates 1440 points and 9 1000 trajectories), nevertheless they are still in line with the interactivity required by OLAP. 7
Big Data is characterized by the 6Vs, among which variety and variability are very relevant. Taking into account them in DW and OLAP systems is challenging, since multidimensional schemata are typically characterized by a rigid schema and simple data types. Shifting towards variety/variability-aware DWs is promising, as several recent works deal with new approaches for formulating OLAP queries on schemaless datasets or multi-model databases. In this paper, motivated by the need for a conceptual design step before the implementation of these complex DW and OLAP systems, we proposed a UML profile for multidimensional schemata taking into account name and type variability, extensibility, and complex data. We described our proposal using a real-case study issued from a smart farming application. We also showed a validation of the quality of the UML model, and we perform some tests to assess the implementation of our metamodel using a multi-model logical schema implemented with PostgreSQL.
We are currently working to define a set of best practices for the implementation of multi-model DWs taking time and space performance into account. This work will then be used to set up a complete design pipeline from our UML profile towards a PostgreSQL implementation. Another interesting direction for our future work consists in defining a classification of all possible complex data that can be defined using V-ICSOLAP. This will allow us to set a formal framework for an assessment of the modeling capabilities associated to our UML profile. Variety/Variability Variability
Multidim. element Measure Measure
Name Total duration
Acknowledgements. This work is supported by the French National Research Agency project 20-PCPA-0002 BEYOND, and French gov-ernment IDEX-ISITE initiative 16-IDEX-0001 (CAP 20-25)