talk at vl/hcc14
DESCRIPTION
Paper talk: Embedding Model-Driven Spreadsheet Queries in Spreadsheet Systems Melbourne, AustraliaTRANSCRIPT
![Page 1: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/1.jpg)
Rui Pereira, Jácome Cunha, João Paulo Fernandes,Jorge Mendes and João Saraiva
Embedding Model-Driven Spreadsheet Queries in Spreadsheet Systems
VL/HCC 2014Melbourne, Australia
![Page 2: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/2.jpg)
Problem: Querying Spreadsheets
1
› “poor man’s database”› No way to query what data they contain
Spreadsheets are generally used to store information
Attempts to replicate traditional querying systems
› Highly restrict user’s spreadsheet data› Denormalization › Headers present› Column letters ≠ Column labels› Data evolution not supported
![Page 3: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/3.jpg)
2
Budget Spreadsheet
Question – What was the total per year, in decreasing order, from 2010 onwards?
![Page 4: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/4.jpg)
3
Google QUERY Function
Question – What was the total per year, in decreasing order, from 2010 onwards?
=query(A1:E53;”SELECT A, sum(E) WHERE A >= 2010 GROUP BY A ORDER BY sum(E) DESC”)
![Page 5: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/5.jpg)
4
QuerySheet
Question – What was the total per year, in decreasing order, from 2010 onwards?
SELECT Year, sum(Total)WHERE Year >= 2010GROUP BY YearOrder BY sum(Total) DESC
![Page 6: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/6.jpg)
5
Previous Empirical Study
› Easier to construct queries› Intuitive and Understandable queries› Productive/Faster
Our goal with Model-Driven Querying:
› Semantic errors› Syntax errors› Low SQL knowledge Construction still difficult
Recurring problem among participants:
![Page 7: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/7.jpg)
6
Embedded Spreadsheet SQL› Guide end-users in constructing queries› Reduce the number of errors› Continue taking advantage of positive aspects of
QuerySheet› SQL knowledge not needed
![Page 8: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/8.jpg)
7
ES-SQL - Environment
Question – What was the total per year, in decreasing order, from 2010 onwards?
![Page 9: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/9.jpg)
8
ES-SQL - Synchronization
ClassSheet
Spreadsheet
conforms to
![Page 10: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/10.jpg)
8
ES-SQL - Synchronization
ClassSheet
Spreadsheet
ClassSheet’
Spreadsheet’
Qm + Q Qm’ + Q’
![Page 11: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/11.jpg)
9
ES-SQL - Synchronization
![Page 12: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/12.jpg)
10
Conclusions and Future Work
› Approach built on model-driven querying language› Visual query construction› Elimination of construction errors› Fully embedded model-driven querying system› Synchronization between model, instance, and query
Embedded Spreadsheet SQL
We still need to:
› Add missing expressions to the Embedded Query› Validation through empirical study
![Page 13: Talk at VL/HCC14](https://reader035.vdocuments.us/reader035/viewer/2022070319/55835e1bd8b42afe418b5192/html5/thumbnails/13.jpg)
Rui Pereira, Jácome Cunha, João Paulo Fernandes,Jorge Mendes and João Saraiva
Embedding Model-Driven Spreadsheet Queries in Spreadsheet Systems
VL/HCC 2014Melbourne, Australia