In the recent decades, databases have been used in any field of human activity to keep valuable data about ongoing processes. Large amounts of data stored in enterprise databases are useless without having a specialized software tool for data discovery or querying. Most business users that make data-driven decisions usually do not have special training and experience in database querying using special formal languages. Existing solutions are based on “query wizards” and database query forms that require knowledge of a database schema and inconvenient for users without special training. Proposed approach is based on the content-based filtering of already executed queries by usage frequency and similarity criteria in order to suggest relevant queries that may be re-used. It is a baseline of the interactive adaptive system for data analysis, which design and development is outlined in this study. Software prototype was demonstrated and its usage was discussed. Conclusions were made and future research was formulated.
Nowadays enterprise databases contain extremely large collections of transactional and aggregated analytical data records. Such data volumes contain descriptions of hundreds or even thousands of data entities, described by multiple various attributes of different data types. Without having interactive and flexible querying tools, such data collections are basically useless, since no data could be obtained for analytical processing, visualization, and decision making. Existing querying languages, such as SQL (Structured Query Language), LINQ (Language Integrated Query), DAX (Data Analysis eXpressions), or XQuery, need special training and experience in software engineering, database design, and database administration. Besides querying languages, it is required to know the database schema structure to apply these query languages. Such data preparation tasks may distract data analysts from their primary activities, which include interpretation and communication of data analysis results (e.g. using simple spreadsheets or data visualizations based on descriptive, or predictive models [1]) to stakeholders. Also improper usage of database querying languages and lack of database schema knowledge, may mislead data analysts and block problems solving.
Modern database management systems are accompanied with so-called “query wizards” intended to simplify data querying and do not require knowledge of SQL or other data querying languages. These software components are considered as user-friendly tools that could be used by non-technical users to build analytical reports and glean insights. On practice most data analysis problems require integration of enterprise databases or other corporate data sources with a specialized software tool or service. There are Business Intelligence (BI) tools focused on visual reports design (e.g. Power BI or QlikView) [2], data science methods and models provided by a standalone software tool (e.g. RStudio or Jupyter) [3] or libraries and frameworks (e.g. Pandas or Matplotlib) [4]. The first step for data visualization, or even for simple data presentation using spreadsheets, is data querying. Since query languages require skills and experience to be applied, and existing “query wizards” are limited in their functionality and mostly are not understandable for end users, the problem of development of a software tool for data querying becomes relevant. This software tool should provide interactive adaptive user interface, which can be used by such users as: data analysts (if they for some reason do not have special training in database management systems), business analysts, top managers, or board members.
Therefore, this study proposes an approach to development of interactive adaptive software tool to support data analysis activity that is considered as the research objective. Research subject includes the approach to development of interactive adaptive software tool. This study aims to decrease complexity of data analysis process by supporting related activities with the interactive adaptive software tool.
This paper consists of six sections. First section introduces research problem and is already outlined. Second section demonstrates literature review and problem relevance. In third section a formal problem statement is given. Fourth section includes description of a recommending procedure, elicited software requirements, a database structure, and a system design. Software prototype usage and its discussion is outlined in fifth section. Sixth section includes conclusions and a future research statement.
Analysis of the state-of-the-art has demonstrated that considered research topic is already covered by several studies. Tang et al. in [5] proposed a database query form (DQF) interface used to generate query forms based on captured user’s preferences. In these DQF preferences are gathered to rank query form components in order to assist users in making decisions. Query form generation process outlined in [5] is iterative and is guided by users: among proposed lists of components users may choose desired ones in order to include into the query form. At each iteration users can submit their forms and adjust DQF until they are satisfied with the obtained response [5].
The term DQF introduced in [5] then appeared in multiple research studies. Authors of [6] propose a DQF system based on user interactions captured in order to adapt questions submitted through query forms. Proposed procedure is also iterative, which means that form could be dynamically refilled till the user satisfies with query results [6]. Paper [7] proposes a survey on DQF approach and reviews its core concepts: query form interfaces, interface components ranking metrics, and estimation of ranking score. The DQF survey also surveyed in [8], where four functional modules of DQF software systems are considered: query form enrichment module, query execution module, customizable query form, and query recommendation module. It is also concluded in all of the considered survey papers that dynamic database querying approach results in higher success rate and easier usage in compare to static database querying approach [6, 7, 8].
Minor contributions to DQF proposed in [5] were made by authors of [9] and [10], while substantial contribution was made by Dagade and Bhonsle, who proposed search optimization for dynamic query form approach or SODQF, which is based on keywords in addition to user feedbacks, as the idea for further development [11]. The keywords based DQF is proposed in [12], which authors also consider queries as items for the collaborative filtering approach in order to provide recommendations. There are also many papers devoted to DQF study, such as [13, 14, 15, 16, 17, 18, 19]. All of this papers are based on previously developed concept of DQF, while proposing some adjustments and improvements to the core idea. For example, in [13] the focus is made on elaboration of differences in DQF application to SQL and NoSQL databases. In [14] authors propose to develop methods to capture user preferences to replace direct feedbacks. Authors of [15] propose an alternative to DQF called auto query forms (AQF) that take queries written in human understandable language and translate them into SQL queries using natural language processing (NLP) methods. Performance measurement of DQF approach was made in paper [16], authors of [17] elaborated the algorithm for DQF generation, and query form formalization was made in [18].
While previously mentioned papers considered mostly SQL baseline and relational databases, in study [19] authors also consider DQF with iterative search and query enhancements by users feedbacks and responses, but with the use of document-oriented NoSQL database management systems based on JSON (JavaScript Object Notation) structures like MongoDB [19]. Usage of DQF approach for NoSQL databases also considered in research [20], where obtained results of performance comparison between SQL based and NoSQL database management systems demonstrate prevalence of relational databases in terms of querying performance.
Fig. 1 demonstrates rapid increase of DQF research topic popularity after it was mentioned for the first time in 2013 by authors of [5]. However, after 5 years of presumably insufficient results or lack of practical implementations, it does not seem really popular (see Fig. 1). Publications statistics outlined in Fig. 1 was received from Google Scholar as the most comprehensive index of research papers.
The problems of DQF technique mentioned in two other survey and review papers [21, 22] related to ranking of suggested queries or form components, high complexity of formal querying languages for end users, and inconvenience when working with large database schemas. Considering such problems remaining after years of research and unfair lack of interest for such promising, by our opinion, research topic, the DQF approach should be elaborated in this study in order to develop interactive adaptive software tool to support data analysis activity.
The interactive adaptive software system for data analysis is supposed to be utilized by two types of users: administrator and analyst. Administrator should be responsible for data sources connections and configuration, analysts profiles creation, and management of their permissions. Analyst can run queries to data sources with possibility of further visualization of obtained results. Administrator should be able to use the same functionalities that analyst can use.
When using this system, administrator should configure a workspace for analysts. The configuration process starts with analysis of business requirements received from managers or other stakeholders who are responsible for data-driven decision making. Then, administrator should add required data sources using their properties. Usually these properties are server name, database name, login and password. At the next stage administrator should prepare queries (e.g. using stored procedures on the server side) and provide additional information on their parameters. Then administrator creates user profiles for analysts and grants permissions: which data sources certain analysts could access and which queries they could execute. Querying tools are essential for the proposed system. That is why prepared queries, presented as stored procedures, should be described with the metadata sufficient for their displaying as graphical user interface (GUI) forms.
Using provided GUI analyst could query connected data sources without knowledge of SQL or other database languages. Using different interface components user could provide parameters for attributes demonstration, records filtering, and other stored procedure features. When preparing queries analysts may use instructions of stakeholders and use required data source, query, and corresponding parameters. Formed using GUI query then executed and received data are displayed on the screed ready for further processing and visualization. The system of adaptive interfaces includes recommending techniques for analytical queries. System should observe user activity and capture statistics of queries execution. Using this statistics analysts may receive recommendations of queries the most similar to queries executed before. Recommendations should be formulated for each data source (e.g. stored procedure). Structural model of such system is demonstrated on the IDEF0 diagram below (Fig. 2).
Recommending algorithm should analyze query execution statistics and define frequencies of query execution in order to detect relevant queries. Three most frequently used queries should be compared to remaining queries in order to detect two most similar queries for each of the most frequently used ones. Therefore, there will be proposed nine most relevant queries for data analytics. Such number of recommended queries (9 items) originates from the statement that human perception allows to take into account 7±2 objects at once. Recommending mechanisms provide interactive querying system that may adapt to user’s preferences. Formulated suggestions are optional and not mandatory to follow.
Proposed approach is based on the content-based filtering approach. Users get recommendations of those queries, which are similar to the queries executed by them before. The underlying idea considers capturing statistics of queries execution by users and therefore it becomes possible to formulate the list of frequently executed queries for each user by each data source.
Let us briefly describe proposed recommending procedure: At first, three most frequent queries should be chosen. Then for each of the most frequent queries, selected at first step, should be chosen two most similar to them queries within corresponding data sources. Similarity between two queries could be calculated using analysis of their SQL code. As the result, the list of nine recommended queries should be formulated.
Most of recommending systems that support content-based filtering approach use keyword matching or vector space model (VSM) with simple TF-IDF weighting [23].
VSM is the algebraic representation of document collection using vectors that belong to the vector space common for the whole document collection. TF-IDF is the statistical indicator used to evaluate importance of terms (words) within the context of a document that belongs to the document collection (corpus). Weight (importance) of the term is directly proportional to the frequency of such term use in a document and inversely proportional to the frequency of such term use in remaining documents of the collection [23].
The usage of the considered recommending procedure is demonstrated in Fig. 3.
According to the proposed recommending procedure, SQL statements that correspond to queries are considered as documents. Document collection includes the set of all SQL statements accessible to the user within a corresponding data source.
Therefore, formal description of the proposed recommending procedure is based on the pair:
D, T , (
D {d1, d 2 ,, d m } is the collection of SQL statements (documents), d j , j 1, m is the SQL statement (document) represented as the vector of length n , and m is the number of SQL statements (documents) within the collection [23]; T {t1, t2 ,, tn } is the general vocabulary of the document collection, which contains n terms (pairs of parameter names and values of SQL statements that call stored procedures) [23].
Each document (SQL statement) d j , j 1, m of the collection defined in (
Documents representation using VSM allows weighting terms and detecting similarity of document vectors.
TF-IDF weighting approach is based on the following empirical assumptions [23, 24]: Rare terms are not less relevant than frequent terms (so called IDF assumption). Multiple occurrences of a term in the document make it less relevant than single occurrence of a term in the document (so called TF assumption). Documents of large size do not have advantage over small documents (so called normalization assumption).
Hence, terms that occur in one document, but rarely occur in remaining documents, are more often
relevant to the document’s topic. Also normalization of result vectors of weights allow avoiding large
documents’ advantage. These assumptions are demonstrated by the TF-IDF function [24]:
n j log m , (
s1
Cosine similarity measure could be used to calculate similarity between two vectors of normalized
TF-IDF measures (
k1 k1 k1
Besides cosine similarity, there may be used other measures, such as Jaccard similarity [26] or even adjusted cosine-based similarity measures, such as soft cosine similarity [27].
Using equation (
EXEC GetStudents @Year = '2020', @Country = ' Turkey'; (
These vectors were obtained using equations (
There is also another document exists, which similarity is greater than zero when compare it to the d1 document, it is d 7 :
d1 {0.71,0,0,0,0.71,0,0,0}, d7 {0.6,0,0,0,0,0,0,0.8}. (
Similarity between d1 and d 7 calculated using (
EXEC GetStudents @Year = '2017', @Country = ' Morocco';
Obtained queries (
Demonstrated workflow is the baseline of the interactive adaptive data analytics system proposed in this paper. Software requirements, database schema, and system design are outlined in following sub-section. Prototype demonstration and discussion are given in section 5. 4.2.
There are two kinds of users that may interact with the system of adaptive interactive interfaces: Database administrators (DBA).
Data analysts (DA).
Preliminary domain analysis has led to the functional requirements (FR) for the system under design demonstrated in Table 2.
As it is outlined in Table 2 above, DBA should be able to access the same functionality accessible to DA. Functional requirements are demonstrated on SysML requirements diagram [29] below (Fig. 5), which clarifies user roles, granted permissions, and responsibility areas.
Also there were formulated non-functional requirements for the system of adaptive interactive tool for data analysis activities. Elicited non-functional requirements (NFR) include performance, usability, security, and reliability requirements (Table 3).
Table 3
users without knowledge of SQL and database schema
For each of NFR outlined in Table 3 should be given specific formal or even numerical measures to verify future software system. Non-functional requirements are demonstrated on SysML requirements diagram [29] below (Fig. 6), which clarifies measurable quality attributes.
Recommending system should have its own database to store user profiles and privileges, connected data sources, and configured analytical queries. 4.3.
Besides storing user profiles, data sources, and queries, the database should contain captured usage statistics to utilize it for recommending purposes.
The data model, which describes database tables and relationships, is shown in Fig. 7.
Demonstrated data model includes entities for generic user, as well as for administrators and analysts with their own attributes. Administrators attach databases of different types assigned to analysts, stored procedures, and given recommendations. Stored procedures are described by parameter names and data types. Attributes of datasets produced by stored procedures are also considered. Stored procedures are assigned to analysts according to permissions configured by administrators. Each query run is stored in order to capture usage statistics for recommending purposes. 4.4.
Let us start system design description with the dynamic models based on CMMN (Case Management Model and Notation) graphical diagrams [30]. When user selects desired data source from the dropdown list with the search feature, respective stored procedures appear in the left side of application’s window, while recommended analytical queries appear in the right side of the window. Administrators can access all attached databases and stored procedures used as data sources for analytical queries, while analysts can access only databases and stored procedures according to their privileges. Both lists could be filtered by name of a database or stored procedure respectively. After certain query is executed, a modal window is displayed to provide parameters of a stored procedure (to filter produced dataset by rows) and choose desired attributes (to filter produced dataset by columns). After query is executed, result set of records is demonstrated. Recommended queries already have relevant parameters and attributes and do not need to be additionally configured.
Case management notation is the novel technique for description of ad-hoc processes [30], including ad-hoc analytical data querying. The CMMN model that describes query execution using the interactive adaptive software system for data analysis is shown in Fig. 8.
User interface structure of analytical querying could be described using the IFML (Interaction Flow Modeling Language) [31] diagram, which describes user interaction, interface structure, and behavior of the software system. The IFML model that describes analytical querying GUI structure and behavior is displayed in Fig. 9.
Obtained result dataset allows search over, export to Microsoft Excel, or visualization using graphs and charts that fit considered data.
The software prototype was implemented according to design principles of ad-hoc analytical queries support (Fig. 8) and user interaction behavior (Fig. 9) outlined in previous section. The software design is demonstrated in Fig. 10 using components deployment diagram.
As it is shown in Fig. 10, the software prototype should be implemented using the three-tier clientserver architecture consisting of the following nodes and components: Client tier containing only the web-browser as a “thin” client that provides user interface built using the Vue.js JavaScript framework. Application server tier containing Node.js back-end application that implements business logic and handles HTTP (HyperText Transfer Protocol) requests, and responses to interact with the client side. Application server uses database drivers to interact with database servers. Database server containing Microsoft SQL Server database that stores user profiles and granted permissions, data source connection properties, analytical queries, and usage statistics.
At this moment developed software prototype supports connection to Microsoft SQL Server as the source database server, while it is planned to extend its integration capabilities to support other widely used relational DBMS, such as MySQL or PostgreSQL [28]. 5.2.
The software prototype currently is under development, however, its generic functionality is already implemented. Fig. 11 demonstrates querying interface that works with connected database servers and stored procedures hosted on these database servers. The user interface is developed according to ad-hoc data querying principles and information flows outlined in CMMN (Fig. 8) and IFML (Fig. 9) models respectively. Generic GUI areas are: Database connection properties. Stored procedures displayed for each of attached databases. Recommended queries displayed for each of stored procedures. Querying form that could be filled by users manually for a certain stored procedure or could be filled automatically based on suggested relevant queries.
As it is shown in Fig. 11 above, users first of all should select a database to work with. If necessary, new database connection could be created (this feature shall be used only by DBA and will be invisible for users). It is planned to allow using multiple relational DBMS in the same environment (e.g. MySQL, Microsoft SQL Server, Oracle, and PostgreSQL as the most popular and widely used database systems). However, connected database systems should be of client-server architecture, since using of file-server databases, such as Microsoft Access or SQLite, is not planned, since they usually do not support stored procedures and used as embedded or small ledgers. Database connection properties could be configured by DBA or even removed if necessary. Also DBA can manage stored procedures and users assigned to each of database connection profiles.
When a certain database connection selected, the list of stored procedures available for this database is displayed. Besides the meaningful name that hides a system name of the stored procedure, the list of parameters is outlined for each of stored procedures together with its brief textual description. Each of demonstrated parameters is accompanied with the respective data type, multiplicity, and optionality.
For each of stored procedures users can get recommended queries suggested using recommending procedure introduced in section 4.1 (Fig. 4). The querying form is available for manual input, however, when user selects specific item from the list of recommended queries, the form is filled with respective values of filtering parameters. Such feature simplifies significantly repetitive access to certain data sets with minimum efforts required from the user (when no changes are necessary and recommended query could be executed as-is, or when one or two filtering values should be changed for “what-if” analysis or other ad-hoc purposes). The list of recommended queries includes three most popular ones, ordered by their usage frequencies, and then six queries – two most similar for each of three most popular ones, ordered by their similarity values. However, users may expand the list of suggested queries in order to access all of the relevant queries (beyond the threshold of two most similar queries for each of three most frequently used queries) ordered by their similarity values (to three most popular queries). In order to distinguish obtained recommendations, queries should be identified by their names given by users when these queries are executed for the first time.
In this paper we have proposed an approach to development of interactive adaptive software tool for data analysis based on the content-based filtering approach. Outlined recommending procedure captures usage statistics of database queries and suggests the most relevant ones among previously used queries based on usage frequency and similarity criteria. According to the proposed recommending procedure, the system works with SQL queries used to access attached data sources (we use stored procedures or routines as data sources, since they are representing a good practice of database schema securing and encapsulation for usage by multiple applications and clients). These SQL queries that call data sources are treated as documents, while pairs of parameter names and values are used as terms for content-based filtering. Suggested queries as the most relevant to a particular stored procedure then could be used as given with pre-defined parameter values or partially changed parameter values in order to reuse queries executed before, or run some ad-hoc inquires respectively. The software tool that should be developed according to the proposed approach currently is implemented as a prototype with limited functionality. However, it allows to connect Microsoft SQL Server databases to access provided data sources, execute SQL queries, and receive recommendations. In future the software should be completed to fulfill all of the elicited requirements, such as user roles and permissions, and connection to multiple DBMS servers. It is also planned to elaborate recommending procedure by considering advanced filtering techniques, similarity measures, and thresholds in order to provide suggestions adjustable by users.
[13] A. V. S. Kumar, O. Devakiran, Dynamic Query Forms for Ad-Hoc Queries on Databases,
International Journal of Scientific Engineering and Technology Research 3(36) (2014) 7176–
7179.
[14] G. H. K. Reddy, A. Bhattacharjya, S. S. Rawat, S. Reddy, Multiple Methods for Detection of
User Priority of Dynamic Query Forms on Databases, International Journal of Scientific
Engineering and Technology Research 4(31) (2015) 6005-6008.
[15] R. B. Sangore, P. P. Bhavsar, M. S. Patil, T. M. Chaudhari, An Alternative For Database
Queries:Auto Query Forms, International Journal of Advanced Research in Computer and
Communication Engineering 4(
NoSQL, International Journal of Science and Research 3(
Advanced Engineering, Management and Science 2(
Journal of Advance Research in Computer Science and Management Studies 2(
Journal of Computer Science and Information Technologies 6(
Maximum Human Interpretability, 2019. URL: https://arxiv.org/pdf/1910.09129.pdf. [28] DB-Engines Ranking. URL: https://db-engines.com/en/ranking. [29] SysML FAQ: What is a Requirement Diagram and how is it used? URL: https://sysml.org/sysmlfaq/what-is-requirement-diagram.html. [30] Case Management Model and Notation. URL: https://www.omg.org/cmmn/. [31] Scope – IFML: The Interaction Flow Modeling Language. URL: https://www.ifml.org/scope/.