Querying industrial stream-temporal data: An ontology-based visual approach 1

2.1. The Siemens use case

OPEN IN VIEWER

Fig. 2.

Siemens service process for reactive tasks.

Siemens produces a wide range of complex appliances, such as gas and steam turbines, generators, and compressors, which are used in business-critical processes, including power generation, in energy sector. Therefore, in order to prevent high downtime costs, Siemens runs several service centres, each responsible for remote monitoring and diagnostics of such many thousands of appliances. Data is stored in several thousand databases with varied schemas and the size of the data is in the order of hundreds of terabytes, e.g., there is about 15 GB of data associated to a single turbine, and it currently grows with the average rate of 30 GB per day [26]. Service centres have two main categories of tasks:

Figure 2 presents the service process triggered after a malfunction of a unit (i.e., reactive scenario) [26]. In this case, a service ticket requesting assistance is created either manually or automatically by a diagnostic system. The ticket often has very limited information concerning the location and cause of the problem. Service engineers query databases containing sensor and event data (i.e., data acquisition) through manipulating 4.000 predefined queries and query patterns. In case these are not sufficient, an IT expert has to be involved to create a new query or query pattern. Standard diagrams are used to visualise sensor data and event messages are listed in excel spreadsheet with timestamps and other attributes. Data then is pre-processed manually using generic procedures in order to, for example, see whether the sensor data quality is appropriate or not. The engineer uses sophisticated diagnostic models and tools, such as principal component analysis or other statistical methods, to analyse and detect the given problem based on the pre-processed data. Finally, the process is terminated when an explanation for the problem is found. The process for predictive analysis is similar, but have to be applied online to streaming data with minimal user intervention.

For situations not initially anticipated, new queries are required, and an IT expert familiar with both the power plant system and the data sources in question (e.g., up to 2.000 sensors in a part of appliance and static data sources) has to be involved to formulate these queries. Thus, unforeseen situations may lead to significant delays of up to several hours or even days due to miscommunication, high workload of IT personnel, complexity of query formulation, and long query execution times. In average, up to 35 queries require modification every month, up to 10% of queries are changed throughout a year, and several new queries are developed monthly [26].

With few built-in features for manipulating time intervals, traditional database systems often offer insufficient support for querying time series data, and it is highly non-trivial to combine querying techniques with the statistics-based methods for trend analysis that are typically in use in such cases. By enabling engineers to formulate complex stream-temporal queries on their own with respect to an expressive domain vocabulary, IT experts will not be required anymore for adding new queries, and manual pre-processing steps can be avoided. This would lead to (i) timely-decision making, (ii) augmented value creation by redeploying freed-up time, and (iii) previously unforeseen uses of data through ad-hoc querying.

A significant amount of world’s enterprise data resides in relational databases rather than triple stores. Therefore, ontology-based visual query formulation would not be a pragmatic solution for industry without technologies for ontology-based data access over relational databases. OBDA technologies, such as Ontop [9], Mastro [14], and Ultrawrap [43], make it possible to virtualise RDF graphs from relational databases and enable in-place access to relational data over ontologies without migrating or duplicating any data. Moreover, thanks to OBDA, while using well-established query optimisation and evaluation support available for traditional database systems, one could also (i) integrate data from multiple databases with different schemas by relating each to a common ontology, and (ii) utilise implicit information in query the answering process by relating the whole set of implied information with logical reasoning.

OBDA over relational databases is based on two key mechanisms:

OPEN IN VIEWER

Fig. 3.

An example OBDA scenario.

Figure 3 presents a simplified OBDA scenario. The example involves an ontology, in which a turbine could be either gas or diesel turbine. A diesel turbine could have different types depending on the fuel type, such as biodiesel turbine. The corresponding relational database includes two tables, one for gas turbines and other for diesel turbines. The latter has fuel attribute to differentiate between different diesel turbine types. There are three mappings indicating how to construct RDF triples given the data that comes from an SQL query over the data source. A user submits a SPARQL query Q asking for all Turbines. At this point, several query transformations take place (cf. [10]). First, the query is rewritten into Q ^I taking ontological constrains into account in order to retrieve both explicit and implicit answers. Second, by using mappings, Q ^I is unfolded into Q ^II , which is the query in the language of the underlying database system. Finally, the Q ^II is optimised and transformed into Q ^III . The example is described considering the Ontop6

In order to ensure that these transformation steps preserve the right semantics of query answering, one should ensure the right combination of ontology and mapping languages. In particular, it has been shown that the combination of the DL-Lite as the ontology language (e.g., OWL 2 QL7

A key benefit of the mapping and query transformation approach is avoiding the representation controversy [33] between ontologies and relational databases (i.e., impedance mismatch) due to their semantic and syntactic differences [46]. By separating transactional and domain perspectives, end users can formulate their information needs into queries in an intelligible manner using the terms coming from an ontology, and get their queries answered efficiently and effectively with OBDA and traditional mature database technologies. Another advantage is that there is no need to chase GBs of data for reasoning to generate all the implicit facts derived by the ontology, since this problem is handled by embedding the consequences of the ontology into the mappings (cf. [19]) – see Fig. 3 for an example.

3.1. STARQL

STARQL [34–36] provides an expressive declarative interface to both historical and streaming data. In STARQL, querying historical and streaming data proceeds in an analogous way and in both cases the query may refer to static data, i.e., data that do not have a timestamp and hence are considered to hold at every time point. The answers coming from the static sub-query are used for the stream processing in the remainder of the query. This separation between the static and dynamic aspects provides a useful abstraction which eases the query building process.

OPEN IN VIEWER

Fig. 4.

An example diagnostic task in STARQL.

The relevant slices of the temporal data are specified with a window, a sliding parameter that determines the rate at which snapshots of the data are taken, and a window width. The window, both in the case of historical data and in the case of streaming data, is a moving window containing a reference to the developing time NOW. The difference is that in the case of historical data, the data is read from an ordinary database (such as a PostgreSQL database), whereas in the real-time case the data is coming from a real-time stream source. Moreover, in the historical case, it may make sense to specify windows with a right end-point bigger than the running time NOW (as the data is available) whereas in the stream case this is not possible.

The contents of the temporal data are grouped according to a sequencing strategy into a sequence of small graphs that represent different states. For each state i, (referenced by the keyword GRAPH i), one may ask whether some assertion holds in it. On top of the sequence, relevant patterns and aggregations are formulated in the HAVING clause, using a highly expressive template language. In Fig. 4, an example STARQL query is given, which asks for a train with turbine named “Bearing Assembly”, and queries for the journal bearing temperature reading in the generator. It uses a simple echo to display the results.

OptiqueVQS (and its underlying engine) follows the OBDA paradigm. Hence, it has to be configured with data sources, an ontology, and last but not least with mappings. The followings illustrate these components and how they are actually used w.r.t. the STARQL query in Fig. 4. The focus is on the historical-data scenario, that is, it is assumed that the stream measurement referenced in the STARQL query is read from an ordinary relational table such as a PostgreSQL table.

The source data is assumed to be stored in relational tables. For example, historical measurement data are stored in a table measurement containing an ID, an attribute for the time of the measurement, the source sensor of the measurement, and the measured value. The structure of trains, turbines, and sensors are stored in various tables containing information regarding which component is attached to which component.

For example, there is a ternary relation for sensors including an identifier, the assembly at which they are attached and a human-readable name, and a similar relation exist for measurements:

These tables and schemata may be quite complex and should be hidden from the user of the VQS. The user should access data via an ontology, a conceptual model containing relevant concepts (such as Sensor, Turbine, Train etc.) and properties/roles (such as hasValue) interrelated via constraints. For example, one constraint of the ontology is invoked in Fig. 4 and it states that all temperature sensors are sensors. Bridging the conceptual (ontology) level with the real-world data (relational databases) is handled by mappings that roughly say how the concepts and properties are populated by objects from the database.

A simple mapping for the static (i.e., non-temporal) concept sensor is given with the following rule:

The right-hand side is an SQL query that selects all sensors identified by an SID and having a name that starts with “TC” (for temperature sensors). The attribute SID is used as a template to generate (virtually) all assertions saying that an object with identifier SID is an instance of the concept TemperatureSensor. The SID is wrapped into a functional term f(SID) in order to overcome the so-called impedance mismatch between the real-world data (such as the values of SID) and that of abstract objects (real temperature sensor, represented by f(SID)).

The mappings for temporal concepts and properties are slightly more complex. In STARQL, temporal concepts and roles (such as hasValue) are used in the HAVING clause in states. One first needs to specify how non-temporal data are mapped in a general mapping schema and then instantiate them with concrete window parameters.

For example, a classical mapping relates the temporal property hasValue to the table measurement for storing sensor IDs and measurement values as follows:

The concrete mapping using the window parameters then is the following:

The left-hand side contains a template on a state and the right-hand side extends the mapping for hasValue by applying the function Slice describing the relevant finite temporal slice of the table measurement from which the ontology level assertions are produced and using the window parameters such as range r, slide sl , the sequencing strategy st and the index i (see Neuenstadt et al. [34] for further details).

In classical OBDA, queries are answered by transforming the query according to the ontology and the mappings. This is done automatically by the engines underlying the VQS according to a correct and complete algorithm. The outcome of the transformation may become considerably larger than the more abstract STARQL query. This is illustrated by a simple transformation to PostgreSQL in the evaluation section. For more information on STARQL itself, readers are referred to Özçep et al. [36].

OptiqueVQS is a visual query system rather than a visual query language (VQL), that is, it is based on a system of interactions rather than a formal visual syntax and notation (cf. [17]). This allows OptiqueVQS to offer familiar and informal presentation and interaction styles, while constraining the user interaction so as to enforce the formulation of valid queries.

OPEN IN VIEWER

Fig. 5.

OptiqueVQS with stream-temporal querying.

OptiqueVQS is designed as a widget-based user-interface mashup (UI mashup) [49,55]. Widgets are standalone, full-fledged, and re-usable applications, which are put together in a common graphical space in order to build a new interface augmenting the user experience [56]. Widgets communicate with each other by broadcasting event messages as a user interacts with them and widgets react automatically to the events depending on their signatures. Such an approach offers flexibility, modularity, and adaptability and enables us to combine multiple representation paradigms, such as forms, diagrams and icons, and interaction paradigms, such as schema navigation, range selection, and matching [12]. A multi-paradigm approach is important to support different user and task types as stated earlier [47,50].

OPEN IN VIEWER

Fig. 6.

OptiqueVQS with stream-temporal querying – parameter selection.

Since the HAVING clauses of typical STARQL queries are comparatively complex, in OptiqueVQS stream query formulation is separated into two tasks: (i) selecting the exact data stream to query (which values from which sensors, etc.), and (ii) what to do with these values. The second is done by letting the user choose one from a set of templates. Technically, a template computes a HAVING clause, and a list of selected variables for output, based on a variable from the WHERE part, a dynamic property (i.e., whose extension is time dependent) of that variable, and possible parameters instantiated by the user (range intervals, etc.). Some available templates are “echo” for copying values to the result stream, “range” for checking that values are within a given parametrisable interval, and “gradient” for checking that the derivate does not exceed a given value.

OptiqueVQS with stream-temporal querying presents five widgets to a user:

In a typical scenario8

OPEN IN VIEWER

Fig. 7.

OptiqueVQS with stream-temporal querying – template selection.

In a stream-temporal scenario, dynamic properties are presented in blue by W3. As soon as a dynamic property is selected, OptiqueVQS switches to STARQL mode. A stream button appears on top of the W2 and lets the user to activate W4 for parameter configuration. If the user clicks on the “Result Overview” button, W5 appears for selecting a template for each dynamic attribute. Finally, the user can register the query through W5 by clicking on the “Register query” button. The user can also save and load queries to the query catalogue, that is particularly important since users could use and extend queries written by others as well (i.e., passive collaboration [32]). The example query depicted in Figs 5, 6 and 7 presents the query example given in Fig. 4.

OptiqueVQS has a backend composed of several components [53]. The core component is graph projector [54] enabling a graph-based navigation over an ontology during the query formulation process. It adapts a technique called navigation graph to extract a suitable graph-like structure from a set of OWL 2 axioms [4]. A data sampler component is also a part of the backend and it is used to enrich a given ontology with additional axioms to capture values from data that are frequently used and rarely changed. This includes the list of values and numerical ranges in an OWL data property range. Such an approach allows presenting attributes in different form elements, such as sliders, multi-select boxes, date pickers etc., with respect to the underlying data. Moreover, backend harvests the query log for ranking and suggesting query extensions as the user formulates a query, that is, the W1 and W3 lists concepts and properties adaptively with respect to the partial query the user has formulated so far [48].

OptiqueVQS is free of any SPARQL or OWL jargon and its usability is based on several design choices. W1, W2, and W3 provide a fine combination of ontology exploration (gradual and on-demand) and query formulation. W1 presents valid object property and range concept combinations in pairs for the pivot concept in order to reduce the number of navigational steps. W2 employs a tree-shaped query representation, rather than an arbitrary graph representation, to improve comprehensibility, and inverted object properties to restrict arcs to a single direction (i.e., left to right). Finally, W3 simplifies the type refinement by presenting the subclasses of a pivot in an ordinary form element. Regarding the expressiveness, OptiqueVQS currently supports tree-shaped conjunctive queries (including aggregation and excluding negation) and a restricted fragment of STARQL (e.g., no support for correlating multiple dynamic properties).

4.1. Transformation of STARQL queries to PostgreSQL

The proof of concept implementation presented in this section is meant to show that the evaluation of STARQL is even possible on standard SQL engines like PostgreSQL. The evaluation strategy is based on the implementation of a system for answering historical queries. This system relies on recorded data and evaluates all temporal windows, which are created by a sliding widow operator, in a single calculation step. Therefore, the implemented transformation process consists of two phases. First, a view is generated that represents the temporal data linked to an additional window identifier (called windowID) that is produced by a window and pulse operator. In the second step the WHERE and HAVING clauses are translated into a second SQL view that evaluates the respective view based on the data aligned to each windowId. In the following, the translation result of the window operator is explained first.

WindowFunction The window operator F winOp takes a temporal table with a timestamp column and additional data columns as input and generates a SQL view of windows as output. The view groups all timestamp columns into a window group that is referenced by an additional column of windowIDs. The window operator view can be seen as three applied functions, namely F seqMeth ( F dataJoin ( F windowGen ) ) . By the inner function F windowGen , temporal borders are generated for each window, based on window parameters width w and slide sl that are defined in the window expression of each stream. The resulting view includes a sequence of windowIDs with additional columns for temporal borders, i.e., the left and right borders of each window. Timestamps of the input table are used for a reference to the start and endpoint of each window, where start and end of the current window is given for every timestamp t by t start = ⌊ t / sl ⌋ × sl and t end = max { t start − w , 0 } .

As PostgreSQL includes functions for time series generation, the window generation step can be evaluated directly by the PostgreSQL processor (see view Measurement_window in the example of Fig. 8).

OPEN IN VIEWER

Fig. 8.

A PostgreSQL transformation result for the STARQL window operator.

JoinStream In general, the implementation for a join on different input streams js can be seen as a simple join in SQL, but as there are different window parameters for each stream, they cannot be simply joined on the windowID . Furthermore, there is a need for a function that chooses at each pulse in time the related windowID of each input stream. Therefore, an additional global pulse function is calculated, which is defined by the user in the STARQL query, to synchronise the windows of all input streams.

All input streams are finally joined together based on the pulse frequency pulse_freq (in the example of Fig. 8 from view pulse_pulseA) into the new recalculated joined pulse pWindowID that references the same windows for all streams (see view Measurement_window_pulse in the example of Fig. 8). The recalculation is defined as follows: pWindowID = ⌊ WindowID × s l pulse freq ⌋ .

DataJoin As soon as the historical window sequence is generated by the function F windowGen , the second function F dataJoin can be applied on the window view by joining the result of the window generation with the actual input-data of the incoming stream (see view Measurement_data in the example of Fig. 8).

SequencingFunction The third function ( F seqMeth ), which serves as a sequencing operator, adds an additional ABoxID column to the sliding window view (see view Measurement_stream in the example of Fig. 8). In the current example this can be seen as a simple recalculation of the windowed timestamps on a granularity parameter of the sequencing method. Based on the parameter the time is rounded to seconds (or minutes) and entries with the same timestamp are then merged into identical ABoxes afterwards.

The schematic transformation result of the transformed window operator for PostgreSQL is shown in Fig. 8.

Transformation of WHERE and HAVING clause The second transformation phase transforms the WHERE and HAVING clauses into a SQL view according to the algorithm presented by Neuenstadt et al. [34]. The resulting view evaluates the calculated windows of the window operator based on the given constraints in the STARQL query. The transformation result of the example from Fig. 4 is given in Fig. 9 (simplified) by the S_out_having view.

OPEN IN VIEWER

Fig. 9.

A PostgreSQL transformation result for STARQL WHERE and HAVING clauses.

The SQL view consists of two parts: the transformation of the WHERE clause (SUB_WHERE) and the transformation of the HAVING clause (SUB_HAVING) that are both connected by a natural join. Both parts rely on mappings to a database schema of different tables on the PostgreSQL server, e.g., the sensormetadata table, which stores detailed information on all available sensors; while the HAVING clause also evaluates the previous described view of the window operator transformation result by referring to the Measurement_stream view. One can also see how the selection of sensors is restricted in the WHERE clause of the static part to temperature sensors on the BearingAssembly, while the transformed HAVING clause itself does not restrict any sensor types.

This section explained the transformation of historical STARQL queries into SQL that can be directly executed on PostgreSQL servers. The execution times of STARQL queries on a Postgres server are evaluated in the following subsection.

During the evaluation, the STARQL query given in Fig. 4 has been taken and transformed based on the Optique prototype into a SQL based result (see Fig. 9). It has been executed on a reference dataset that includes the signals of 25 sensors. These sensors emit values irregularly over a time of approximately one month. The dataset, which is sampled in seconds, can be seen as a representation of real-world scenarios that also occur in Siemens’ data centres.

The query has been evaluated over a PostgreSQL installation on a machine with a i7 2.4 GHz core and 8 GB of RAM. The standard SQL database executes queries in a process based way, and therefore, cannot distribute its execution directly over cores or machines. The execution time has been measured in two steps. First, the time that is used by the window operator to generate the complete sequence of windows on the whole input data set has been measured and second, the evaluation of all windows with respect to the given queries has been measured.

The results for different window and data sizes can be found in Table 1. They basically show that the query can even be executed on standard SQL engines in the range of one minute, while a larger amount of time is used for the execution of the window operator. That is as expected, because the PostgreSQL engine does not natively support the execution of a window operator.

Furthermore, STARQL has been also tested on distributed and scalable environments having a native window operator like Exareme9

A user experiment has been conducted at Siemens for OptiqueVQS with stream-temporal support [52,54]. The experiment was designed as a think-aloud study, since the goal of the experiment was not purely summative, but to a large extent formative. The experiment was built on a “turbine ontology” with 40 concepts and 65 properties.

Three participants, who cover the relevant occupation profiles, have taken part in the experiment; the profiles of participants are summarised in Table 2. A brief introduction on the topic and tool was delivered to the participants along with a simple example. Then they were asked to fill in a profile survey. The survey asks users about their age, occupation and level of education, and asks them to rate their technical skills, such as on programming and query languages, knowledge on the semantic web technologies, and their familiarity with similar tools on a Likert scale (i.e., 1 for “not familiar at all,” 5 for “very familiar”). Participants were then asked to formulate a series of information needs into actual queries with OptiqueVQS, given at most three attempts for each query. Each participant performed the experiment in a dedicated session, while being observed by a surveyor. Participants were instructed to think aloud, including any difficulties they encountered, while performing the given tasks. Table 3 lists the tasks representing the information needs used in the experiment (tasks 3–5 are stream queries).

Once users were done with the tasks, they were asked to fill in an exit survey asking about their experiences with the tool in order to measure user satisfaction. The survey asks users to rate whether the questions were easy to do with the tool (S1), the tool was easy to learn (S2), was easy to use (S3), gave a good feeling of control and awareness (S4), was aesthetically pleasing (S5), was overall satisfactory (S6), and was enjoyable to use (S7) on a Likert scale (again, 1 for “strongly disagree” and 5 for “strongly agree”). Users were also asked to comment on what they did like and dislike about the tool and to provide any feedback which they deem important.

Since OptiqueVQS is a data retrieval (DR) tool, where a single missing or irrelevant object implies a total failure contrary to information retrieval (IR) [5,61], effectiveness is measured in terms of a binary measure of success (i.e., correct/incorrect query) and efficiency is measured in terms of total time taken to formulate a query [47,50]. The results of the experiment are presented in Table 4. In total, 15 tasks were completed by the participants with 100 percent correct completion rate and 66 percent first-attempt correct completion rate; and, on average, a task took 143 seconds to complete in 1.3 attempts. First-attempt correct completion refers to the cases where a user formulates a correct query for a given task at his/her first attempt. One should be aware that query formulation is an iterative process [32,60]; therefore, query reformulation is a natural step. Nevertheless, the results indicate that domain experts could formulate queries with high efficiency and effectiveness by using OptiqueVQS. The feedback provided by the participants through the exit survey is presented in Tables 5 and 6. The usability scores given by participants are quite high and their comments suggest that they did like the design of interface. Users generally praised the capabilities and the design of OptiqueVQS.

R&D engineers asked for advanced operators such as “OR” and negation, since they, compared to diagnosis engineers, often need to formulate more complex queries. The participants wished for the ability to combine multiple queries and connect concepts that are not directly linked (i.e., non-local navigation [39,50]). Participants also wanted attributes and attribute values in W3 to be filtered automatically with respect to previously selected constraints (including type refinement) – i.e., similar to faceted-search (cf. [59]). Non-local navigation and faceted-search like filtering are challenging with large ontologies and data sources. This is because, for the former, most relevant connections between two concepts must be found, and for the latter, selected constraints must be checked against data to filter out attributes and attribute values.

Overall, the high completion and satisfaction rates suggest that domain experts are quite comfortable with OptiqueVQS’s stream-temporal capabilities. The study also shows that the learnability of OptiqueVQS is high as participants did not receive any substantial training. Earlier, three other user experiments were conducted: one with casual users [49] and two others with domain experts at Statoil and Siemens on non-streaming scenarios [26,53]. These studies revealed similar results and confirmed that OptiqueVQS could address various user types and different scenarios with high efficiency, effectiveness and user satisfaction.