The Intentional Analytics Model (IAM) has been devised to couple OLAP and analytics by (i) letting users express their analysis intentions on multidimensional data cubes and (ii) returning enhanced cubes, i.e., multidimensional data annotated with knowledge insights in the form of models (e.g., correlations). Five intention operators were proposed to this end; of these, describe and assess have been investigated in previous papers. In this work we enrich the IAM picture by focusing on the explain operator, whose goal is to provide an answer to the user asking “why does measure show these values?”. Specifically, we propose a syntax for the operator and discuss how enhanced cubes are built by (i) finding the polynomials that best approximate the relationship between and the other cube measures, and (ii) highlighting the most interesting one. Finally, we test the operator implementation in terms of eficiency.
Despite the huge success of the OLAP paradigm, it is now clear that this paradigm, alone, does
no longer meet the sophisticated requirements of new-generation decision makers. Among the
directions taken by research to enhance OLAP, the Intentional Analytics Model (IAM) suggests to
couple it with analytics [
Among the five intention operators, describe and assess have been investigated in previous
papers [
As envisioned in [
with SALES explain revenue by type for year=’2022’ First, the subset of facts for 2022 are selected from the SALES cube and aggregated by product type (in OLAP terms, a slice-and-dice and a roll-up operator are applied). Then, regression analysis is used to compare the revenue measure with each other cube measure and find the polynomials that best approximates their relationship. Finally, a measure of interest is computed for the components (i.e., for the polynomials) obtained, and the most interesting one is shown to the user (in Figure 1, the one showing that revenue is roughly proportional to quantity).
The paper outline is as follows. After introducing models and enhanced cubes in Section 2, in Section 3 we give the syntax of explain and illustrate how models are built. Then, in Section 4 we explain how enhanced cubes are visualized. Finally, in Section 5 we discuss the related literature and in Section 6 we test the operator implementation and draw the conclusions.
Models are concise, information-rich knowledge artifacts that represent relationships hiding in the cube facts. The possible models range from simple functions and measure correlations to more elaborate techniques such as decision trees, clusterings, etc. A model is bound to (i.e., is computed over the levels/measures of) one cube, and is made of a set of components, each component being a specific relationship among cube facts.
Definition 1 (Hierarchy and Cube Schema). A hierarchy is a pair ℎ = (ℎ, ⪰ ℎ) where ℎ is a set of categorical levels, each coupled with a domain including a set of members, and ⪰ ℎ is a roll-up total order of ℎ. The top level of ⪰ ℎ is called dimension. A cube schema is a pair = (, ) where is a set of hierarchies and is a set of numerical measures, each coupled with one aggregation operator.
Example 2. For our working example we will use the SALES cube, which includes three hierarchies and three measures. Formally, SALES = (, ) with = {ℎDate, ℎProduct, ℎStore}; = {quantity, revenue, cost}; date ⪰
month ⪰ year; product ⪰ type ⪰ category; store ⪰ city ⪰ country
Aggregation is the basic mechanism to query cubes, and it is captured by the following definition of group-by set.
Definition 2 (Group-by Set and Coordinate). Given cube schema = (, ), a group-by set of is a set of levels, at most one from each hierarchy of . The partial order induced on the set of all group-by sets of by the roll-up orders of the hierarchies in , is denoted with ⪰ . A coordinate of group-by set is a tuple of members, one for each level of .
Example 3. Two group-by sets of SALES are 1 = {date, type, country} and 2 = {month, category}, where 1 ⪰ 2. 1 aggregates sales by date, product type, and store country, 2 by month and category. Example of coordinates of the two group-by sets are, respectively, 1 = ⟨2022-04-15, Fresh Fruit, Italy⟩ and 2 = ⟨2022-04, Fruit⟩.
The instances of a cube schema are called cubes and are defined as follows.
Definition 3 (Cube). A cube over is a triple = ( , , ) where is a group-by set of , ⊆ , and is a partial function that maps the coordinates of to a numerical value for each measure ∈ .
Each coordinate that participates in , with its associated measure values, is called a fact of . With a slight abuse of notation, we will write ∈ to state that is a fact of . The value taken by measure in the fact corresponding to is denoted as . . A cube whose group-by set includes all and only the dimensions of the hierarchies in and such that = , is called a base cube, the others are called derived cubes. In OLAP terms, a derived cube is the result of either a roll-up, a slice-and-dice, or a projection made over a base cube; this is formalized as follows.
Definition 4 (Cube Query). A query over cube schema is a triple = (, , ) where is a group-by set of , is a (possibly empty) set of selection predicates each expressed over one level of , and ⊆ .
Example 4. The cube query over SALES used in Example 1 is = (, , ) where = {type}, = {year = ’2022’}, and = {revenue}. A coordinate of the resulting cube is ⟨Batteries⟩ with associated value 3320 for revenue.
Definition 5 (Model). A model is a tuple ℳ = (, , , , , ) where: (i) is the model type; (ii) is the algorithm used to compute ; (iii) is the cube to which the model is bound; (iv) is the measure in to be explained; (v) is the tuple of levels/measures of and parameter values supplied to to compute the model; (vi) is the set of model components. In this paper, to give a proof-of-concept of the explain operator, we restrict to consider a single type of model, namely, the one that establishes a polynomial relationship between two measures via regression analysis. In this case, is the set of measures whose relationship with is described; besides, each component ∈ shows the relationship of with one measure ∈ .
Definition 6 (Component). For a model of type polynomial, a component is a triple = (, , coeff ) where: (i) is the measure in whose relationship with is described; (ii) is the degree of the polynomial used to describe the relationship between and ; (iii) coeff is an array of the + 1 coeficients of the polynomial () that best approximates with reference to the facts in .
= revenue; = {quantity, cost}; = {1, 2}; 1 = (quantity, 1, [0.98, 4909.52]); 2 = (cost, 2, [1.1, 22.78, 1409.33]) According to this model, the relationships of revenue with quantity and cost are described, respectively, as revenue = 1(quantity) = 0.98 · quantity + 4909.52 revenue = 2(cost) = 1.1 · cost2 − 22.78 · cost + 1409.33
As the last step in the IAM approach, cube is enhanced by associating it with a set of models bound to and with a highlight, i.e., with the most interesting model component: Definition 7 (Enhanced cube). An enhanced cube is a triple of a cube , a set of models {ℳ1, . . . , ℳ} bound to , and a highlight = {∈⋃︀=1 }(()).
In our scenario only polynomial models are considered, so an enhanced cube includes a single
model with one component for each measure in . Let be the component associated to
; we evaluate the interest of , (), as the coeficient of determination R2 [
Example 6. With reference to Example 5, it is (1) = 0.61 and (2) = 0.99. Thus, the highlight is 2.
The explain operator provides an answer to the user asking “why is this happening?” “why does measure show these values?” by describing the relationship between and the other cube measures, possibly focused on one or more level members, at some given granularity. The cube is enhanced by showing the polynomials that best approximate these relationships, with a highlight on the most interesting one.
Let 0 be a base cube over cube schema = (, ). The syntax for explain is (optional parts are in brackets):
with 0 explain [ for ] by 1, . . . , [ against 1 [ degree 1 ], . . . , [ degree ] ] where ∈ is a measure of ; is a set of selection predicates, each over one level of ; {1, . . . , } is a group-by set of ; 1, . . . , are measures of (diferent from ); the ’s ( > 0) are integers denoting, for each , the degree of the polynomial to be computed. Example 7. Examples of explain intentions on the SALES cube are, besides the one in Example 1, with SALES explain cost by date, product against quantity degree 1 with SALES explain revenue by year against cost
The execution plan corresponding to a fully-specified intention, i.e., one where all optional
clauses have been specified, is as follows:
1. Execute query = (, , ), where = {1, . . . , }, = , and =
{, 1, . . . , }. Let = (0) be the cube resulting from the execution of over 0.
2. Compute model ℳ = (polynomial, Polyfit , , , {1, . . . , }, {1, . . . , }), where =
(, , coeff ). The best approximating polynomial of degree is determined via ordinary
least squares [
Partially-specified intentions are interpreted as follows:
• If the for clause is not specified, we consider = .
• If the against clause is not specified, a component is created for each measure in
(except ).
• If the degree clause is not specified for one or more measures, the value of is determined
automatically by polynomial fitting [
Example 8. The first intention in Example 7 is executed by first computing the derived cube that aggregates SALES by {date, product} and projects on measures cost and quantity. Then, a model ℳ including a single component (a linear polynomial approximating cost in function of quantity) is determined. Finally, the enhanced cube including , ℳ, and the highlight is returned.
As previously done for the describe and assess IAM operators, to give an efective visualization of the enhanced cubes built for explain intentions we couple a text-based representation (a pivot table and a ranked component list) with a graphical one (a chart) and with an ad-hoc interaction paradigm. Specifically, the visualization of enhanced cube = (, ℳ, ) relies on three distinct but inter-related areas: a table area that shows the facts of using a pivot table; a component area that shows a list of model components (i.e., approximating polynomials) sorted by their interest, with at the top; a chart area that uses a scatter chart to display, for each component of ℳ, the relationship between and as well as the function plotting the approximating polynomial.
The interaction paradigm we adopt is component-driven: clicking on one component in the component area leads to show the corresponding approximating polynomial in the chart area. The highlight is selected by default.
Example 9. Figure 2 shows the visualization obtained when the intention in Example 1 is formulated. On the left, the table area; on the right, the chart area; in the middle, the component area. The highlight is a quadratic polynomial that approximates revenue in function of cost, so the chart area shows the relationship between these two measures and the approximating parabola.
The idea of coupling data and analytical models was born in the 90’s with inductive databases,
where data were coupled with patterns meant as generalizations of the data [
The coupling of the OLAP paradigm and data mining to create an approach where concise
patterns are extracted from multidimensional data for user’s evaluation, was the goal of some
approaches commonly labeled as OLAM [
In an attempt to develop tools for helping users understand data, there have been several
eforts in the research community to devise techniques to model explanations for observations
made on data; see [
The approach we propose is not competing with the ones mentioned above, but should rather be seen as a modular framework where any approach to explanation of aggregate data could be plugged. The added value lies in the IAM paradigm, i.e., in giving users the possibility of explicitly expressing intentions, in letting the system select the most interesting/suitable explanations, and showing these explanations together with data.
In this paper we have given a proof-of-concept for explain intentions formulated inside the IAM framework. The explain syntax is flexible enough to suit users who wish to verify a specific hypothesis they made about an inter-measure relationship, as well as users who have no clue so they will let the system find the most interesting relationship.
The prototype we developed to test our approach relies on the MySQL DBMS to execute queries on a star schema based on multidimensional metadata. The algorithms used for regression analysis are imported from the Scikit-Learn Python library. Finally, the web-based visualization is implemented in JavaScript and exploits the D3 library for chart visualization.
To verify the feasibility of our approach from the computational point of view, we made some scalability tests. Two main factors afect performances: the cardinality of the cube to which a model is bound, || (which determines the time required to compute a single model component), and the number of cube measures, | | (which determines the number of model components to be computed).
To evaluate scalability with reference to cube cardinality, we populated the SALES cube using the FoodMart data (https://github.com/julianhyde/foodmart-data-mysql) and considered 10 intentions with increasing cardinalities; in each intention we explained the revenue measure against both quantity and cost. The tests were run on an Intel(R) Core(TM)i7-6700 CPU@3.40GHz CPU with 8GB RAM; each intention was executed 10 times and the average results are considered. Remarkably, it turns out that less than one second is necessary to explain a cube of almost 87000 facts. Additionally, we measured the complexity (as the number of characters) of writing explain intentions vs. the underlying cube query. It turns out that our approach saves 85% of complexity with respect to writing cube queries (and without considering the complexity of extracting regression models, which would make our approach even more convenient).
To evaluate scalability with reference to the number of measures, we created a cube with || = 106 facts and | | = 10 measures. As expected, our approach scales linearly in the number of measures, and given 9 measures and 106 facts, the computation of an explanation takes less than 7 seconds, thus fulfilling the requirement of near-real-time response typical of analytical workloads. The detailed results of the tests can be found in [25].
We close the paper by mentioning that the main direction for future research we wish to pursue is to generalize the definition of model to cope with additional model types. [22] Z. Miao, A. Lee, S. Roy, LensXPlain: Visualizing and explaining contributing subsets for aggregate query answers, Proceedings of VLDB Endow. 12 (2019) 1898–1901. [23] K. E. Gebaly, P. Agrawal, L. Golab, F. Korn, D. Srivastava, Interpretable and informative explanations of outcomes, Proceedings of VLDB Endow. 8 (2014) 61–72. [24] F. Savva, C. Anagnostopoulos, P. Triantafillou, Explaining aggregates for exploratory analytics, in: Proceedings of BigData, Seattle, WA, USA, 2018, pp. 478–487. [25] M. Francia, S. Rizzi, P. Marcel, The whys and wherefores of cubes, in: Proceedings of DOLAP, Ioannina, Greece, 2023, pp. 43–50.