Transforming several relational tables into a data set to be used as input to a Machine Learning (ML) model is a complex task since the data scientist has to derive many intermediate tables, queries and views in a disorganized manner. This process creates many SQL queries to facilitate the exploration task of the data scientist. Because the provenance of the intermediate results is not re ected, similar SQL queries tend to be written multiple times causing repeated manual work. In this paper, we propose a tool "ER4ML" assisting data scientists in modeling and visualizing the transformations applied to the relational database before obtaining a dataset feeding the ML models. ER4ML is a diagram ow based on a conceptual view of the database schema and transformations based on powerful extensions of ER diagram in UML notations. In addition, ER4ML tracks data provenance, improving query and data set reuse.
In order to feed their de ned Machine Learning (ML) models, data scientists collect, clean, aggregate and transform important amounts of relational data to elaborate a tabular dataset with many features. Pre-processing relational datasets is a complex process consuming more than 80 % of the project time.This Pre-processing needs to execute di erent SQL queries in a disorganized manner outside the DBMS, without thinking to model the entity-relationship(ER) modi cations. These transfromations result in several intermediate temporary tables or views,that are not modeled in the ER diagram. These transformations are valuable knowledge for future analytics reusing similar sets of entities. However, reusing them presents a challenge for data scientists because of the lack of a modeling and GUI tools helping them to understand what has been performed and the intermediate results of SQL queries that construct the ML model. We defend the idea of creating an extended ER model to understand such a set of disconnected tables and views at a high level.
To overcome this issue, we propose a tool "ER4ML" aiming to assist data
scientists to visualize and understand the di erent transformations using
complex SQL queries (including joins, selections, projections, aggregations and the
powerful CASE statement), applied to the relational dataset given as an input
to an ML model. ER4ML is based on a de ned model "Extended ER" detailed
in [
This paper is organized as follows: Section 2 discusses the related work. Section 3 presents the architecture of our tool and details its main modules. Section 4 presents our demonstration scenario and Section 5 concludes our paper. 2
Based on reverse engineering processes for extracting the database conceptual
model from its physical model, some studies de ned conceptual view of relational
datasets[
3.1. Extended ER model for Data Transformation: The proposed tool is based on the Extended ER model that de nes a database as D(T ; I), where T = fT1; : : : ; Tng, is a set of n tables and I is a set of integrity constraints (entity for primary key (PK), referential for foreign key (FK)), and represents the chain of transformations as a conceptual ow diagram. We use the term "table" to make a connection to the physical level and SQL querying. SQL queries can be classi ed into either aggregation or denormalization transformation. We assume well formed queries are used to create datasets, which include a PK to identify rows and at least one non-key attribute. 3.2. Layered System Architecture: Figure 3 illustrates the architecture of our system which is designed following the 3-tiers architecture based on the MVC paradigm covering three layers: Presentation layer: is the GUI presented to the user for visualizing the database schema, the di erent transformations and the used SQL queries. The system distinguishes between source and transformation entities. The type of each entity is indicated in its name (eg. Source:EntityName, Denormalization:Ti and Aggregation:Tj ). The nal obtained dataset is named "Dataset:Tx" where (i,j,x) are sequence numbers. Our diagram ow is automatically generated based on physical model notation complementary to UML notations. Our Extended ER notation does not change UML notation for ER, instead we show transformation entities in color green/Red/blue and ER entities in white. The arrows represent data ow (NOT relationships) but we still link entities (source & transformation) with FKs/PKs. This layer is developed using HTML, BOOTSTRAP, CSS, JavaScript (JS), and GOJS library used to build interactive ER diagrams where the user can hide or show selectively the transformation entities. Functional Logic layer: is the core of our solution. It contains three main modules of the system: Model extraction, ER initialization, and Transformation modules responsible for generating an extended ER model for each dataset to be given to an ML model. (i) Model extraction module: generates an ER model from a relational data source using a speci c DBMS connector. Our solution deals with any DBMS, but in our demonstration scenario we consider sqlite3 module for node JS connceting to SQLite DBMS thanks to its simplicity and overhead. This module extracts the structure of the database (tables, their columns, constraints and relationships) and generates two JSON les that de ne the model and that can be read by GOJS library. The rst and the second les contain respectively the relationships and the entities. (ii) ER initialization module: takes the two JSON les as input and extracts the detailed structures of nodes and links to initialize the ER model, to be displayed by the presentation module. (iii) Transformation module: we assume the user is responsible for specifying the components and results of each transformation and has the prior knowledge to distinguish between fact table & analytic dataset and check the validity of ER models. This module enriches the extended ER model for each new SQL query by: (a) de ning the transformation type (Aggregation or Denormalization), (b) de ning the SQL query that generates the intermediate table, the query is displayed as a comment next to the transformation entity so to zoom in the transformation applied. (c) creating the transformation entities (named "TYPE Ti" i is an auto-increment sequence number) that are displayed using di erent colors. The module also allows: (d) associating the PK, FK & other attributes to the transformation entity. (e) According to the tables joined in the From clause of the query, the module identi es the relationships to existing entities to show the provenance of the transformation entities & their attributes, using an ARROW link between transformation entities, this allows the user to folow the chain of transfromations. (f) creating cardinalities of relationships: one2many and many2one.
Data layer: after each valid transformation step, the new generated extended
ER model is stored as JSON les. The development of this tool is performed
on WebStorm IDE. GitHub is used for the versioning of the project. The source
code of our solution is available on: https://github:com/dihiaselma/ER4ML
In our system demonstration we will show our extended, elegant and intuitive ER
model obtained in a few seconds from typical SQL queries. It helps
understanding complex data transformations. Our demonstration is based on the basketball
dataset containing statistics about players, coaches, and teams in men's
professional basketball leagues from 1937 to 2012, which are extracted from the Open
Source Sports website. We constructed an SQLite database from these les and
proceeded to veri cation of integrity constraints and PK-FK links. We use 14
transformations4 deriving features using some complex Joins, aggregations and
the powerful CASE statement, resulting from the previous work [
A detailed demonstration video is available at https://youtu:be/nJkZ8aBa6co which gives a nice overview of di erent functionalities of our tool. It emphasizes on following points: (1)ER representation of temporary tables, (2)Flow of a chain of data transformations, (3)Conceptual separation between aggregation and de4 https://drive:google:com/file/d/1A29k9wjIfp0QpYgtf8dbooQH3qmbDqy6/view normalization, (4)Complex SQL queries, (5)Reusing existing queries and tables and (6)Connected view of data via primary and foreign keys. 5
In order to assist data scientists managing and understanding the di erent transformations applied to a relational database and create a tabular dataset serving as input to their ML models, we developed a new tool \ER4ML" that graphically generates an extended ER model based on UML notations. It models all the intermediate temporary tables or views resulting from SQL queries, as entities enriching the existing database schema. This tool can be seen as an interesting response to the major question discussed during the Dagstuhl seminar 18471 titled Next Generation Domain Speci c Conceptual Modeling: Principles and Methods, in last November regarding the integration of conceptual modeling in ML. Despite this, our tool presents some limitations: i) The user cannot view the ML model as entities. ii)The user cannot view data transformations done with external languages like Java, R or Python. iii) Our tool is good for data represented with tables, not for text or images. As a perspective, we are extending our tool to deal with datasets issued from di erent database formats.