The democratization of data access and the adoption of OLAP in scenarios requiring hand-free interfaces push towards the creation of smart OLAP interfaces. In this paper, we describe COOL, a framework devised for COnversational OLap applications. COOL interprets and translates a natural language dialog into an OLAP session that starts with a GPSJ (Generalized Projection, Selection, and Join) query and continues with the application of OLAP operators. The interpretation relies on a formal grammar and on a repository storing metadata and values from a multidimensional cube. In case of ambiguous text description, COOL can obtain the correct query either through automatic inference or user interactions to disambiguate the text.
Sales by Customer and
Month
Interpretation Parse tree Log
OLAP Stop-eTeecxht- Rteaxwt Fouplleqrauteorry pAarnsneoftoatreedst &DiEsanSmhtaabtniisgctuiecamstieonnt Ptarersee Online Offline
SQL SQL Execution & generation Visualization
In this paper, we describe COOL [4, 5] to convert natural language into COnversational OLap sessions composed of GPSJ queries and analytic operators. GPSJ [6] is the main class of queries used in OLAP. COOL is the first proposal addressing a full-fledged implementation for OLAP analytical sessions that is: • Automated and portable: by reading metadata (e.g., hierarchy structures, measures, attributes, and aggregation operators) from a ROLAP engine, COOL automatically builds the minimal lexicon involved in the translation. • Robust to user inaccuracies in syntax, OLAP terms, and attribute values, by exploiting metadata and implicit information. • Extendable and easily configurable on a data warehouse (DW) without a heavy manual definition of the lexicon. The minimal lexicon is extendable by importing known ontologies in the Knowledge Base.
The ofline phase automatically extracts the set of entities ℰ , i.e., the DW-specific terms used to express the queries. Such information is stored in the knowledge base (KB), which relies on the DFM expressiveness [7]. This phase runs only when the DW undergoes modification (e.g., cube schemas or data instances) and extracts all multidimensional metadata. A cube modeled as a star schema in a ROLAP engine consists of dimension tables (DTs: the cube hierarchies) and a fact table (FT: the cube). The Automatic KB feeding extracts measures (FT columns not in the primary key), attributes (DT columns), values (distinct instances of the DT columns), and hierarchies (either coded or inferred). These elements represent the lexicon necessary to translate natural language into conversational sessions. COOL supports lexicon extension with external synonyms that can be automatically imported from open data ontologies (e.g., Wordnet [8]) to widen the understood language. Besides the domain-specific terminology, the KB also includes the set of standard terms that are domain independent and that do not require any feeding (e.g., group by, where, select). Further enrichment can be optionally carried out manually (e.g., when the physical names of tables/columns do not match a standard vocabulary).
The online phase runs every time a natural language query is issued. In a hand-free scenario (e.g., [9]), the spoken query is initially translated to text by the Speech-to-text software module. Since this task is out of our research scope, we exploited a public Web Speech API. The uninterpreted text is then analyzed by the Interpretation step, refined in the Disambiguation & Enhancement step, translated by the SQL generation step, and finally executed and visualized by the Execution & Visualization step.
Interpretation consists of two alternative steps. Full query interprets the texts describing full queries (which happens when an analytic session starts). OLAP operator modifies the latest query when the user states an OLAP operator across a session (i.e., roll-up, drill-down, and slice&dice). The switch between the two steps to manage the conversation (i.e., a dialog between the user and COOL) is modeled by two states: engage (in which the system expects a Full Query to be issued) and navigate (in which the dialogue evolves by iteratively applying OLAP operators that refine the initial query). When COOL achieves a successful interpretation of a full query (i.e., it is able to run the query), it switches to the navigate state. Both Full query and OLAP operator follow these steps: (i) Tokenization & Mapping, (ii) Parsing, and (iii) Checking & Annotation.
Tokenization & mapping. A raw text is a sequence of single words = ⟨1, ..., ⟩. The goal of this step is to identify the entities in , i.e., the only elements that will be involved in the Parsing step. Turning a text into a sequence of entities means finding a mapping between words in and ℰ .
Definition 1 (Mapping & mapping function). A mapping function ( ) is a partial function that associates sub-sequences (or -grams)1 from to entities in ℰ such that: (i) sub-sequences of have length at most; (ii) the mapping function determines a partitioning of ; and (iii) a sub-sequence ′ = ⟨, ..., ⟩ ∈ (with | ′| ≤ ) is associated to an entity if and only if ( ′, ) > (where () is a similarity function, later defined) and ∈ (ℰ , ′) (where (ℰ , ′) is the set of entities in ℰ that are the most similar to ′ according to ( ′, )). The output of a mapping function is a sequence = ⟨1, ..., ⟩ on ℰ that we call a mapping.
The similarity function () is based on the Levenshtein distance and keeps token permutation into account to make similarity robust to token permutations (e.g., sub-sequences ⟨., ⟩ and ⟨, , ⟩ must result similar).
Several mappings might exist between and ℰ since Definition 1 admits sub-sequences of variable lengths (corresponding to diferent partitionings of ) and associates the top similar entities to each sub-sequence. This increases the interpretation robustness since COOL chooses the best mapping through a scoring function. Given a mapping = ⟨1, ..., ⟩, its score 1The term -gram is used as a synonym of sub-sequence in the area of text mining.
⟨GPSJ⟩ ::= ⟨MC⟩⟨GC⟩⟨SC⟩ | ⟨MC⟩⟨SC⟩⟨GC⟩ | ⟨SC⟩⟨GC⟩⟨MC⟩ | ⟨SC⟩⟨MC⟩⟨GC⟩
| ⟨GC⟩⟨SC⟩⟨MC⟩ | ⟨GC⟩⟨MC⟩⟨SC⟩ | ⟨MC⟩⟨SC⟩ | ⟨MC⟩⟨GC⟩ | ⟨SC⟩⟨MC⟩ | ⟨GC⟩⟨MC⟩ | ⟨MC⟩ ⟨MC⟩ ::= (⟨Agg⟩⟨Mea⟩ | ⟨Mea⟩⟨Agg⟩ | ⟨Mea⟩ | ⟨Cnt⟩⟨Fct⟩ | ⟨Fct⟩⟨Cnt⟩ | ⟨Cnt⟩⟨Attr⟩ | ⟨Attr⟩⟨Cnt⟩)+ ⟨GC⟩ ::= “ ” ⟨Attr⟩+ ⟨SC⟩ ::= “ℎ” ⟨SCA⟩ ⟨SCA⟩ ::= ⟨SCN⟩ “” ⟨SCA⟩ | ⟨SCN⟩ ⟨SCN⟩ ::= “” ⟨SSC⟩ | ⟨SSC⟩ ⟨SSC⟩ ::= ⟨Attr⟩⟨Cop⟩⟨Val⟩ | ⟨Attr⟩⟨Val⟩ | ⟨Val⟩⟨Cop⟩⟨Attr⟩ | ⟨Val⟩⟨Attr⟩ | ⟨Val⟩ ⟨Cop⟩ ::= “ = ” | “ <> ” | “ > ” | “ < ” | “ ≥ ” | “ ≤ ” ⟨Agg⟩ ::= “” | “” | “” | “” | “” ⟨Cnt⟩ ::= “” | “ ” ⟨Fct⟩ ::= Domain-specific facts ⟨Mea⟩ ::= Domain-specific measures ⟨Attr⟩ ::= Domain-specific attributes ⟨Val⟩ ::= Domain-specific values ( ) (i.e., the sum of entity similarities) is higher when includes several entities with high similarity values. Intuitively, the higher the mapping score, the higher the probability to determine an optimal interpretation.
Parsing validates the syntactical structure of a mapping against a formal grammar and outputs a data structure called parse tree that is later used to translate a mapping into SQL.
In Full query, Parsing is responsible for the interpretation of a complete GPSJ query stated in natural language. A GPSJ query contains a measure clause (MC) and optional group-by (GC) and selection (SC) clauses. Parsing a full query means searching in a mapping the complex syntax structures (i.e., clauses) that build up the query. Given a mapping , the output of the parser is a parse tree , i.e., an ordered tree that represents the syntactic structure of a mapping according to the grammar from Figure 2. To the aim of parsing, entities are terminal elements in the grammar.
For the sake of brevity, we omit the grammar of the OLAP Operator and we refer the user to [4]. Intuitively, our conversation steps are inspired by well-known OLAP visual interfaces (e.g., Tableau https://www.tableau.com/). To apply an OLAP operator, COOL must be in the state navigate, i.e., a full GPSJ query has been already successfully interpreted into a parse tree that acts as a context for the operator. The output of the OLAP Operator parser is a parse tree that is used to update .
Both GPSJ grammars are LL(
SQL generation translates a full-query parse tree into an executable SQL query. If an OLAP operator has been submitted, such tree must be updated accordingly. Given a full query parse tree, the generation of its corresponding SQL requires to fill in the SELECT, WHERE, GROUP BY and FROM statements. The SQL generation applies to both star and snowflake schemas [ 7] and is done as follows: SELECT (measures and aggregation operators from ⟨MC⟩ and attributes in the group by clause ⟨GC⟩); WHERE (predicates from the selection clause ⟨SC⟩); GROUP BY (attributes from the group by clause ⟨GC⟩); and FROM (measures, attributes, and values identify the fact and the dimension tables). The join path is identified by following the referential integrity constraints.
Tests are carried out on a real-world benchmark of analytics queries [11]. Since queries from [11] refers to private datasets, we mapped the natural language queries to the Foodmart schema3. The Automatic KB feeding populated the KB with 1 fact, 39 attributes, 12337 values and 12449 synonyms. Additionally, only 50 synonyms where manually added in the KB enrichment step (e.g., “for each", “for every", “per" are synonyms of the group by statement). While mapping natural language queries to the Foodmart domain, we preserved the structure of the original queries (e.g., word order, typos, etc.). Overall, 75% of the queries in the dataset are valid GPSJ queries, confirming how general and standard GPSJ queries are. The filtered benchmark includes 110 queries. We consider token sub-sequences of maximum length = 4 (i.e., the [1..4]-grams) as no entity in the KB is longer than 4 words. Each sub-sequence is associated with the top similar entities with similarity higher than such that at least a percentage of the tokens in is covered. is fixed to 70% based on an empirical evaluation. For the sake of brevity, only the major results are reported.
Efectiveness is evaluated as the tree similarity [ 12] ( , * ) between the parse
tree produced by our system and the correct one * , which is manually written by us.
Although only one parse forest is involved in the disambiguation phase; for testing purposes, it
3A dataset of food sales (https://github.com/julianhyde/foodmart-data-mysql).
(a) Efectiveness by varying the similarity threshold (b) Eficiency by varying the number | | of entities
and the - returned queries (with = 6). in the optimal tree and the top similar entities
(with = 0.4).
is interesting to see if the best parse tree belongs to the top-k ranked ones. Efectiveness is more
afected by the entity/token similarity threshold and ranges in [0.83, 0.89]. In all cases, the
best results are obtained when more similar entities are admitted and more candidate mappings
are generated. Independently of the chosen thresholds the system results very stable (i.e., the
efectiveness variations are limited) and even considering only one query to be returned its
efectiveness is at the state of the art [ 2, 13, 14]. This confirms that (
As the previous tests do not include disambiguation, only 58 queries out of 110 are not ambiguous and produce parse trees that can be fed as-is to the generation and execution phases. Starting from the best configuration from the previous tests (for = 6 and = 0.4), by applying an increasing number of correcting actions the efectiveness increases from 0.89 up to 0.94. Unsolved diferences are mainly due to missed entities in the mappings.
As to eficiency, we ran the tests on a machine equipped with Intel(R) Core(TM) i7-6700 CPU @ 3.40GHz CPU and 8GB RAM, with the framework implemented in Java. Figure 3b shows the average execution time by varying | | and the number of allowed top similar entities . The execution time increases with the number of entities included in the optimal parse tree, as such also the number of top similar entities impacts the overall execution time. We recall that efectiveness remains high also for = 2 corresponding to an execution time of 1 second, raising to 10 seconds in the worst case.
Conversational business intelligence can be classified as a natural language interface (NLI) to business intelligence systems to drive analytic sessions. Despite the plethora of contributions in each area, to the best of our knowledge, no approach lies at their intersection.
NLIs to operational databases enable users to specify complex queries without previous training on formal programming languages (such as SQL) and software; a recent and comprehensive survey is provided in [15]. Overall, NLIs are divided into two categories: question answering and dialog. To the best of our knowledge, no dialog-based system for OLAP sessions has been provided so far. SQLizer [13] generates templates over the issued query and applies a “repair” loop until it generates queries that can be obtained using at most a given number of changes from the initial template. Domain-specific approaches add semantics to the translation process through domain-specific ontologies and ontology-to-database mappings. SODA [ 16] uses a simple but limited keyword-based approach that generates a reasonable and executable SQL query based on the matches between the input query and the database metadata, enriched with domain-specific ontologies. ATHENA [ 14] maps natural language into an ontology representation and exploits mappings crafted by the relational schema designer to resolve SQL queries. Analyza [17] integrates the domain-specific ontology into a “semantic grammar”(i.e., a grammar with placeholders for the typed concepts such as measures, dimensions, etc.) to annotate and ifnally parse the user query. Unfortunately, by relying on the definition of domain-specific knowledge and mappings, the adoption of these approaches is not plug-and-play as an ad-hoc ontology is rarely available and burdensome to create.
In this paper, we proposed COOL, a conversational OLAP framework supporting the translation of a natural language conversation into an OLAP session. COOL supports both the interpretation of GPSJ queries and OLAP operators.
Besides proposing a technical solution and a reference architecture, the contribution of the paper lies in the discussion of specific issues related to conversational OLAP systems. Since its conception, OLAP analysis aims to allow users to analyze data without requiring technological skills. Conversational OLAP represents a step forward in this direction. We believe that conversational OLAP can be particularly useful in the context of hand-free applications such as the ones we proposed in [3]: adding conversational capabilities to an augmented OLAP solution would be highly desirable whenever the user is working in the field (e.g., a warehouse or a factory) and is not in front of a computer. To close the loop, we are working towards enabling access to OLAP results in a conversational and hand-free fashion. The idea is to create query summaries (e.g., [18]) that can fit augmented-reality devices or (vocal) smart assistants. [5] M. Francia, E. Gallinucci, M. Golfarelli, Conversational OLAP in action, in: Proceedings of the 24th International Conference on Extending Database Technology, EDBT 2021, Nicosia, Cyprus, March 23 - 26, 2021, OpenProceedings.org, 2021, pp. 646–649. doi:10.5441/002/ EDBT.2021.74. [6] A. Gupta, V. Harinarayan, D. Quass, Aggregate-query processing in data warehousing environments, in: Proc. VLDB, Morgan Kaufmann, San Francisco, CA, USA, 1995, pp. 358–369. [7] M. Golfarelli, D. Maio, S. Rizzi, The dimensional fact model: A conceptual model for data warehouses, Int. J. Cooperative Inf. Syst. 7 (1998) 215–247. doi:10.1142/ s0218843098000118. [8] G. A. Miller, Wordnet: A lexical database for English, Commun. ACM 38 (1995) 39–41.
doi:10.1145/219717.219748.
[9] M. Francia, M. Golfarelli, S. Rizzi, Augmented business intelligence, in: Proceedings of
the 21st International Workshop on Design, Optimization, Languages and Analytical
Processing of Big Data, co-located with EDBT/ICDT Joint Conference, DOLAP@EDBT/ICDT
2019, Lisbon, Portugal, March 26, 2019, volume 2324 of CEUR Workshop Proceedings,
CEURWS.org, Lisbon, Portugal, 2019, pp. 1–10.
[10] J. C. Beatty, On the relationship between LL(