Data-intensive processes must deal with the problem of monitoring the quality of data. To this end, metadata can be exploited in order to highlight errors and support the cleaning of data. In this paper, we present a novel DBMS plugin for validating profiling metadata during data insertions, aiming to assist the user in a-priori checking the quality of data being inserted into a database. It has been implemented within the MySQL Workbench client and is able to intercept and validate data insertion queries. More specifically, using such a plugin it is possible to verify in real-time whether the data to be inserted into a database instance will produce some violations on specific metadata, such as unique column combinations or functional dependencies. A user study highlighted the efectiveness of the proposed plugin by also emphasizing its strengths and weaknesses.
Data quality is becoming a vital activity in most application
domains. The presence of errors and inconsistencies in the data
drastically reduce the value of data and processing capabilities
on them [
Many data cleaning approaches exploit profiling metadata , that
permit to describe properties that must hold on data, and
consequently they can be used to monitor the quality of the data
themselves. Examples of metadata are unique column combinations,
functional dependencies, denial constraints, and so on.
Moreover, nowadays the exploitation and validation of these metadata
are enhanced thanks to recent data profiling algorithms, which
enable their automatic discovery from “big” datasets [
DataBase Management Systems (DBMSs) have been
recognized as a useful tool enabling easy interactions between the user
and the stored data. In particular, they permit to inquire data
with little efort [
In this paper we introduce a novel visual tool named inditio (INteracting with metaData during data InserTIOns), which permits to monitor in real-time, and possibly validate, specific profiling metadata upon data insertion operations. In particular, the tool has been implemented as a plugin of the graphical client MySQL Workbench, and it enables the user to intercept data insertion queries, in order to validate specified (or uploaded) metadata before the insertion is committed. In this way, a user can check the correctness of the data s/he is planning to insert, and decide either to force the insertion nonetheless the data, or try to fix them. Consequently, inditio manages the specification (uploading) of metadata and the visualization of validation results together with values inducing possible violations. Moreover, diferent statistical counters and visual components enable users to have an overview of the general impact of data insertions on the considered metadata. The current implementation of the plugin is able to manage unique column combinations (uccs) and functional dependencies (fds), but it could be extended to include other kinds of profiling metadata.
In general, the goal that guided such work is threefold: i) improve the quality of data by driving users in the data insertion process, ii) sensitize more users to the data profiling concepts, and iii) support an implicit analysis on the significance of metadata from domain experts.
The paper is organized as follows. Section 2 describes approaches to data quality and data profiling tools. Section 3 presents the theoretical foundations of considered profiling metadata. Section 4 presents inditio, whereas Section 5 reports a user study we performed to analyze its efectiveness and usability. Finally, summary and future directions are included in Section 6.
Metadata have been recognized as a fundamental tool for
guaranteeing good levels of quality to the extension of data, mainly
due to their involvement in data cleaning processes [
Data cleaning activities have also been included in several
data preparation commercial tools [
Data cleaning and metadata verification activities have also
been included into, mainly commercial, data governance suites,
such as Talend [
The possibility of exploiting metadata is given by the existence
of algorithms capable of discovering them from big data sources
[
Nevertheless, since automatic discovery processes could
output many metadata, some novel proposals started to visually
manage the complexity related to an efective visualization of
metadata holding on a given dataset, by using novel metaphors
for representing metadata at diferent levels of detail [
All of the above-mentioned approaches try to repair/analyze available datasets aiming at improving their quality and/or detect/visualize possible holding metadata. On the contrary, the proposed inditio plugin aims at improving the quality of data, trying to directly make users, i.e., who define data, aware of the possible errors that they can introduce into database instances. For this reason, we propose a tool that could be properly integrated into a DBMS. In this way, users and/or domain experts can approach this kind of concepts by simply using the environment they are familiar with. In other words, inditio aims at including data profiling discovery results within DBMSs, facilitating their interpretation to users through a plugin that also includes several visual components. To the best of our knowledge, in the literature, there are no other DBMS integrated plugins considering the interaction among data definition statements and metadata. 3
Collecting metadata from big datasets is the goal of the data
profiling research area [
A relational database schema R is defined as a collection of relation schemas (1,. . ., ), where each is defined over a set ( ) of attributes (1,. . ., ). Each attribute has associated a domain ( ), which can be finite or infinite. A relation instance (or simply a relation) of is a set of tuples (1, . . . , ) such that ∀ ∈ ( ) [ ] ∈ ( ), where [ ] denotes the projection of onto . A database instance of R is a collection of relations (1,. . ., ), where is a relation instance of , for ∈ [1, ].
In the context of relational databases, one of the main property is represented by candidate keys. They permit to define possible tuple identifiers of a relation instance since no repetition in value combinations are allowed. They can be efectively identified by exploiting unique column combinations (uccs).
ucc definition. A ucc over a relation schema is a sets of attributes ⊆ () such that given an instance of , for every pair of tuples (1, 2) in then 1 [ ] ≠ 2 [ ].
Another relevant property of a relational database is represented by functional dependencies (fds), which are used to improve the quality of database schemas and to reduce manipulation anomalies.
fd definition. An fd over a database schema R is a statement → ( implies ) defined between two sets of attributes , ⊆ (R), such that, given an instance of R, → is satisfied in if and only if for every pair of tuples (1, 2) in , whenever 1 [ ] = 2 [ ], then 1 [ ] = 2 [ ]. and represent the Left-Hand-Side (LHS) and Right-Hand-Side (RHS) of the fd, respectively.
In general, an fd is said to be non-trivial if and only if ∩ = ∅. Moreover, an fd is said to be minimal if and only if there is no attribute ∈ such that \ → holds on . 4
In this section, we describe inditio a MySQL Workbench plugin
capable of validating profiling metadata upon the insertion of new
tuples in a database. MySQL Workbench1 is the oficial graphical
client of MySQL [
inditio has been implemented by exploiting the libraries exposed from Oracle, to directly interact with the main components of the software. Thanks to the most recent versions of the MySQL Workbench source packages, it was possible to develop the proposed plugin using Python 2.7. Although this is not the latest version of this programming language, it is the only supported version by MySQL Workbench. In fact, it is important to notice that MySQL Workbench has been developed by exploiting C++ libraries, and supports Python libraries only by means of a wrapper that is able to translate Python code into C++ code. This makes
MySQL Workbench a real challenge for external developers looking to extend its functionalities, since it is not always possible to install new modules. For this reason, the most popular currently available plugins created by external developers 2 are divided into two categories: ) MySQL database documentation generation; and ) MySQL Workbench export. Both types of plugins work in batch and are bound to execute a single operation. On the contrary, inditio fully exploits the functionalities of MySQL Workbench as described in the following section. 4.1
inditio is able to intercept data insertion queries provided by users into the SQL Editor. Due to the possible big number of errors that can be introduced during the insertion of new tuples, DBMSs should enable users to visualize profiling metadata that newly inserted data could possibly invalidate, by also giving the possibility to interact with them. For this reason, it should be necessary to evaluate such metadata upon data insertion operations. To this end, inditio extends MySQL Workbench functionalities by enabling users to validate uccs and fds upon the insertion of new tuples.
Figure 2 shows the general visual interface of inditio. In general, it permits to evaluate the impact of new data on a set of holding metadata. Thus, it enables users to visualize the new tuples being inserted (Figure 2(a)), the metadata to validate (Figure 2(b-d)), and the results of the metadata validation process (Figure 2(e-h)). Moreover, inditio also provides several functionalities enabling users to interact with both SQL statements and metadata, as described in the following. A demonstration video of inditio is available on YouTube3.
4.1.1 Analysis of metadata. The main novelty introduced by inditio is the possibility to evaluate some metadata directly into the MySQL Workbench. In fact, a user can upload uccs or fds by selecting the type of metadata s/he plans to monitor. In particular, the metadata will be directly uploaded through a default file, but they can be changed by selecting a new JSON file (Figure 2(b)).
The uploaded metadata are shown in the middle form (Figure 2(d)), which is customized according to the type of metadata the user selects. For instance, fds are divided into LHS and RHS, each containing some attributes, in order to graphically visualize the implication property. Instead, the ucc customized form visualizes each metadata by considering a single group of attributes (Figure 3). Aside from the metadata uploaded via file, a user can always add new metadata. Moreover, through this form it is possible to select which metadata must be considered during the validation. Indeed, each metadata can be selected by means of the check box, and/or by using the “Select All” or “Unselect All” buttons.
Finally, it is always possible to “Copy Selected Metadata” as text by means of a specific button.
Notice that metadata are described through letters or numbers, e.g., alias, in order to identify attributes. This facilitates users in focusing on attributes and/or in defining new metadata. In fact, possible long (or inappropriate) attribute names could confuse the user. However, inditio provides a suitable form to show the mapping between attribute names and their associated alias (see Figure 2(c)).
All selected metadata can be validated by clicking on “Run Validation”, which triggers the execution of a validation module whose aim is to check if the new tuples violate the selected metadata. According to the validation process, each metadata can be classified in one of the following categories: • Valid Metadata, when the new tuples do not produce any violation; • Not Valid Metadata, when the new tuples entail at least one violation; or • Impossible to validate, when the metadata cannot be validated. This occurs when the user introduces errors in the metadata, such as when the attribute names do not exist in the considered database.
Example 4.1. Let us consider a database storing smartphone characteristics. Figure 2(e-g) show validation results of the considered fds (Figure 2(d)) according to the new tuples the user is planning to insert (see Figure 2(a)). In particular, three out of six fds are valid, two are invalid, and one cannot be validated. In fact, K,M → D includes attribute M that does not appear in the considered database. Instead, C → E, i.e., ram → display_ppi, is invalidated if the new tuples are inserted.
In general, the impact of the new tuples on the considered metadata is summarized by inditio in a new form, named report form, shown in Figure 4. This form graphically shows the percentage of validation/invalidation produced on the selected metadata by the tuples the user is planning to insert. Moreover, the report form ranks database attributes in descending order according to the number of invalidated metadata containing them. More specifically, the form represents this kind of information according to the type of metadata, i.e., by splitting the information about invalidation on LHS and RHS when considering fds (see Figure 4(a)).
Example 4.2. Figure 4(a) shows the fd validation report for the validation results represented in Figure 2. In particular, the form shows that the impact of invalidations is 33% of the analyzed metadata. Moreover, among the attributes involved in the invalidated metadata, attribute C (e.g., ram) is involved in two invalidated fds; whereas attributes E (e.g., cpu) and D (e.g., display_ppi) are involved in one invalidated fd. This could suggest to verify the values of attributes ram, cpu, and display_ppi of the new tuples.
4.1.2 Interacting with data insertions. inditio not only enables users to visualize the impact of new tuples on holding metadata, but it also permits them to interact with the new data. First of all, INSERT INTO statements can always be modified within the inditio interface (see Figure 2(a)), triggering subsequent validation processes with modified tuples. As said above, inditio freezes the execution of INSERT INTO statements while verifying the possibility to correct values being inserted so as not to invalidate holding metadata. Nevertheless, inditio always gives users the possibility to overlook possible violations of metadata and to force the execution of data insertion operations by means of the “Execute Statements” button (see Figure 2(a)).
On the other hand, one of the main goals of inditio is to help users in correcting possible errors. To this end, after a validation process (i.e., by clicking “Run Validation”), a user can visualize data yielding violations by interacting with the “Not Valid Metadata” form (see Figure 2(f)). More specifically, by clicking on any metadata in such form, inditio shows a violation detail form, as shown in Figure 5. In particular, concerning fds, apart from the details of the selected metadata, the violation form describes for each LHS value combination involved in a violation: ) the value combination of the LHS, ) the corresponding distinct values found on the RHS, and ) their total number (see Figure 5(a)). Instead, concerning uccs, apart from the details of the selected metadata, the violation form describes for each value combination involved in value duplication: ) the value combination involved in a duplication, ) the number of duplications (see Figure 5(b)). The latter should represent the functionality that drives users in accomplishing the best possible correction of errors.
Example 4.3. Figure 5(a) shows the violation details of the fd C → E (e.g. ram → display_ppi) according to the validation results represented in Figure 2. In particular, the form shows that four specific values on attribute C (e.g. ram), i.e. 1Gb, 2Gb, 3Gb, 512mb, each implies diferent values of attribute E (e.g. display_ppi). Moreover, it is also possible to see that the value 2Gb is the one implying the highest number of distinct values. Instead, Figure 5(a) shows that for the ucc K,E,L (e.g. id_brand, display_ppi, id_os) there are six specific value combinations inducing duplicate values. In general, this form could suggest to correct values on the new tuples concerning attributes involved in the considered violated metadata, or to exclude the metadata from the validation process. 5
The user study presented in this section aims to show that inditio makes metadata validation a simple and efective process for improving data quality. 5.1
We recruited 86 students majoring in Computer Science who just attended the Fundamentals of database. We also recruited 3 Ph.D. students and 1 Ph.D., all of which were familiar with the given domain. Statistics about participants have been collected through a background survey, as shown in Table 1(Q1-Q4), and whose results are reported in Figure 6. In particular, about 85% of the recruited people were men, 15% were women, and most of them were undergraduate students. Moreover, on average they declared, through a Likert scale, a medium level of knowledge concerning MySQL and MySQL Workbench. Before the evaluation started, participants underwent a 45-min tutorial on the theoretical foundations of data profiling and inditio.
Each of the 90 participants was given a database concerning personal data, three data insertion statements, and two sets of uccs and fds metadata, respectively. Moreover, we requested (a) A form showing validation statistics after the fd validation process. (b) A form showing validation statistics after the ucc validation process. (a) A form showing values that invalidate metadata after the fd validation process. (b) A form showing values that invalidate metadata after the ucc validation process. them to check the correctness of data insertion statements according to the provided metadata, and if necessary, to correct statements aiming to guarantee the validity of the provided metadata. More specifically, we conducted a within subjects study by considering two scenarios: with and without inditio, and requested to accomplish the task in one scenario first, and then with the other one. Half participants considered first the scenario without inditio, while the remaining ones used inditio first. Notice that, the provided data insertion statements were diferent but equivalent in complexity. In particular, in the case participants performed the tasks without the tool, they were able to validate the metadata by directly analyzing the data source or using the SQL language to compose specific queries. To this end, they could interact only with the tools already integrated into MySQL Workbench, both to validate the metadata and to correct the values within the data.
After completing the assigned tasks, participants were requested to fill some questionnaires, aiming to highlight the advantages and drawbacks of inditio (see Table 1(Q4-Q20)). More specifically, questions from Q4 to Q8 have been filled after participants performed each task (with and without inditio, or vice versa), whereas the remaining ones have been included in a final survey. Moreover, questions from Q4 to Q20 are quantitative, and they have been measured through a Likert scale, ranging from 1, mapping “Strongly disagree” response, to 5, mapping “Strongly agree” response. Finally, to further evaluate the efectiveness of both processes (with and without inditio) we measured the time required for completing the task and the number of errors. 5.2
Figure 8 shows the results achieved from each participant while executing the assigned tasks with both the compared scenarios (without and with the tool). The results achieved from the tasks performed without the tool have been considered as the comparative baseline (purple line), while the results obtained with the
Question d n u o r g k c a B e v i t a r a p m o C l a n i F
Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22 Q23 Q24
Gender Qualification Level of knowledge of MySQL Level of knowledge of MySQL Workbench I completed the tasks quickly and easily The instructions for completing the tasks are clear and easy to read The metadata validation process has been simple The values that invalidated the metadata have been easy to find proposed tool were described by the bars. In particular, the plot highlights the improvement obtained by using inditio, e.g., a value of 2 indicates that the results achieved with inditio are 2 times better than those achieved without it, whereas a value less than 1 indicates the opposite case. In general, it can be observed that most of the participants performed better with inditio, even if satisfactory results have been achieved also without the use of the plugin. Concerning the time employed to complete the assigned tasks, on average participants took thirty minutes with inditio, ranging from five to sixty-seven minutes, and forty-five minutes without it, ranging from five to eighty-four minutes. In general, we noticed that the manual validation task times were particularly long, especially for users with less knowledge of MySQL. On the contrary, with inditio almost all users have reduced the time of the validation processes by more than 50%.
Figure 7 depicts the box plots derived from the answers (on a Likert scale from 1 to 5) to questions ranging from Q5 to Q8. In particular, users answered the same questions after performing tasks for each considered scenario (with and without the tool).
A boxplot shows the median (horizontal lines), the interquartile ranges (boxes), the largest and the smallest observations (whiskers). By comparing the results achieved without the plugin (the first four box plots in Figure 7) against those with the plugin, (the remaining box plots) we can conclude that participants felt more comfortable and efective when working with inditio.
Concerning the quantitative questions in the final questionnaire, inditio obtained the general agreement of participants, while evaluating its usability and efectiveness (see Figure 9).
In particular, according to answers for Q20, the capability of simplifying the metadata validation process has been widely recognized to inditio. The latter has turned out to be comfortable and useful according to answers to questions to Q18 and Q19, simple to learn, and pleasant to use (see Figure 9: answers for Q10 and Q13). Nevertheless, some work should be made to further improve the general usability of the plugin and the transparency of the validation process, according to answers to questions to Q11, Q12, and Q15.
The open questions in the final questionnaire (see Q21-Q24 in Table 1) aimed at highlighting the strengths and weakness of inditio. In particular, concerning the general impressions about the plugin (see Q21 in Table 1), many participants said that inditio shows a simple and intuitive interface, in which the components appear well organized in the frame. Another part of them expressed their opinion on the usefulness and eficiency of the
Without INDITIO
With INDITIO 5.0 4.5 4.0 3.5 3.0 2.5 2.0 1.5 1.0
Q5
Q6
Q7
Q8
Q5
Q6
Q7
Q8 1 2 3 4 5 6 7 8 9 01 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 54 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90
Person ID plugin, claiming that “The tool has certainly been successful in its intent, significantly speeding up the time for validating metadata ”.
Moreover, some of the most interesting comments have been provided from users that are less familiar with the research context and with MySQL Workbench. In fact, they report that inditio is able to improve the understanding of MySQL Workbench and to show that metadata potentially allows them to extract further knowledge from data, which often is not clearly visible. Only a small part of inexperienced users asserted that the plugin interface might be initially unclear, claiming that “Initially, the tool seems dificult to understand. Then, once I understand how to use it, it is very useful for checking the validity of fd and ucc”. However, by practising with it, inditio allowed them to easily understand its features and to become familiar with its environment.
In addition, concerning the specific features that participants liked the most and the least (see Q22-Q23 in Table 1), experienced users have greatly appreciated the functionality of identifying values yielding the invalidation of metadata. In fact, some of them claimed that “One of the most interesting features is identifying the values that invalidate the metadata. This functionality could be directly integrated into the MySql Workbench suite.” Instead, inexperienced users have shown interest for the report forms (see Figure 5) and for the simplicity through which the plugin could be integrated within the MySQL Workbench suite as a simple plugin.
Although most of the comments were positive, we also investigated the features they liked the least. Among them, many users have highlighted that the Query Editor component appears small and does not clearly show statements. However, this is limited by the graphics components included in the MySQL Workbench.
For these reasons, we allow the users to directly interact with the SQL editor of MySQL Workbench, and to import their statements. Other participants have suggested adding further reports in the interface in order to enhance their understanding of how data insertion statements afect metadata. Only few users have proposed to extend the interface of inditio with new graphical components in order to improve the interaction with both the plugin and the MySQL Workbench.
Finally, we have asked users some suggestions for enhancing inditio (see Q24 in Table 1). To this end, some users have suggested integrating new metadata, also allowing them to simultaneously validate multiple metadata. Other users have suggested improving the integration with systems based on the Linux architecture. In fact, it has been found that some of the users using these operating systems tend to view some reports diferently from users who use Windows systems. However, this is due to the compatibility problems between the technologies underlying the MySQL Workbench and diferent operating systems. In the future, these compatibility issues might be solved with new software versions.
In summary, the four open questions of the final questionnaire revealed that some participants remarked some limitations of the inditio user interface. Moreover, they would like to receive more hints during the statement modification process, according to validation results. Conversely, they positively judged the intuitiveness the metadata validation and the error detection processes. Moreover, they welcomed the tool and recognized its usefulness.
In this paper we presented inditio, a MySQL plugin enabling users to assess the quality of data they planned to insert into a database. In particular, the plugin enables a user to observe how the new tuples can afect the validation of some metadata, e.g. fds and uccs, assisting him/her in correcting them, if needed. We evaluated inditio by involving more than eighty participants in a user study, which demonstrated its usefulness, giving us the possibility to detect the main characteristics of the tool to be improved, mainly focusing on the improvement and/or extension of some visual components.
The potentiality of inditio yields many possible future
directions. In particular, we are currently working on the enrichment
of the set of possible metadata that can be taken into
consideration, with particularly emphasis on relaxed fds (rfds) [
28 37 36 39
40 49 37 41 30 33 33 63 47 44 34 33 30
27 29
17
19
23
21
Moreover, we would like to directly connect the plugin to
incremental or continuous discovery algorithms [