talk at vl/hcc13
DESCRIPTION
Paper talk: Querying Model-Driven Spreadsheets San Jose, USATRANSCRIPT
![Page 1: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/1.jpg)
Jácome Cunha
João Paulo Fernandes
Jorge Mendes
Rui Pereira
João Saraiva
Querying Model-Driven Spreadsheets
Universidade do MinhoBraga, Portugal
![Page 2: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/2.jpg)
Problem: Querying Spreadsheets
![Page 3: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/3.jpg)
Problem: Querying Spreadsheets
• Spreadsheets are generally used to store information• Much like database systems• Have no way to query what data they contain
• Attempts have been made to replicate traditional querying systems for spreadsheets• MS-Query• Google QUERY Function
![Page 4: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/4.jpg)
Problem: Querying Spreadsheets
• Highly restrict user’s spreadsheet data• Requires data to be in a tabular format• Requires headers to be present (in first
row)• Column letters ≠ Column labels• Counter intuitive queries• Data evolution not supported
![Page 5: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/5.jpg)
Proposed Solution: Example
![Page 6: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/6.jpg)
Proposed Solution: Example
• Model-Driven spreadsheet representing orders, clients, and products
![Page 7: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/7.jpg)
Proposed Solution: Example
• Spreadsheet data instance
![Page 8: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/8.jpg)
Proposed Solution: Example
• Query: How much have we profited from each client?
![Page 9: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/9.jpg)
Proposed Solution: Example
Google QUERY Function :
Denormalization
![Page 10: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/10.jpg)
Proposed Solution: Example
• Google Query:
Google QUERY Function :
![Page 11: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/11.jpg)
Proposed Solution: Example
Google QUERY Function :
Denormalization
![Page 12: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/12.jpg)
Proposed Solution: Example
• Google Query:
Google QUERY Function :
![Page 13: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/13.jpg)
Proposed Solution: Example
• Proposed Query:
Proposed Solution:
![Page 14: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/14.jpg)
Proposed Solution: Example
Results:
• Query: How much have we profited from each client?
![Page 15: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/15.jpg)
Proposed Solution: Example
Results:
Model Inference:
![Page 16: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/16.jpg)
Proposed Solution: Example
Results:
Model Inference:
Spreadsheet data instance:
![Page 17: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/17.jpg)
Recap
![Page 18: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/18.jpg)
Recap
• Take advantage of the powerful Google query engine while adding extra functionality
• Allow more humanized queries
• Have ClassSheets supported
• Automatically structure the data into the needed format
• Allowing spreadsheet evolution
• Automatically deal with problems caused by denormalized data querying
![Page 19: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/19.jpg)
Architecture
![Page 20: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/20.jpg)
QSArchitecture
![Page 21: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/21.jpg)
Conclusions
![Page 22: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/22.jpg)
Conclusions
• Incremental Denormalization• Spreadsheet Synchronization• Evaluation
Future Work
Accomplished• Spreadsheet querying is more:• Humanized• Understandable• Robust• Productive
• More functionality
![Page 23: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/23.jpg)
Questions?
![Page 24: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/24.jpg)
Preliminary Study
Question 1
Question 2
Question 3
Question 4
0:00
0:28
0:57
1:26
1:55
2:24
2:52
3:21
3:50
0.123611111111111
0.136111111111111
0.108333333333333
0.13125
0.0458333333333333
0.0166666666666667
0.0222222222222222
0.0784722222222222
Google QuerySheet
• QuerySheet was found to be approx. ~70% faster than Google’s QUERY function
Min
utes
![Page 25: Talk at VL/HCC13](https://reader033.vdocuments.us/reader033/viewer/2022061119/546b47c7af79599b248b6cd5/html5/thumbnails/25.jpg)
Preliminary Study
• After each question, students were asked to choose which system they preferred in regards to being:• Intuitive• Faster to write• Easier to write• Understandable
• Almost all preferred QuerySheet on every aspect