talk at quatic'14
DESCRIPTION
Paper talk: Refactoring meets Model-Driven Spreadsheet Evolution Guimarães, PortugalTRANSCRIPT
![Page 1: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/1.jpg)
Rui Pereira, Jácome Cunha, João Paulo Fernandes,Pedro Martins and João Saraiva
Refactoring meets Model-Driven Spreadsheet Evolution
QUATIC 2014Guimarães, Portugal
![Page 2: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/2.jpg)
Spreadsheets
1
› Programming language and environment of choice for many people/companies
› 50% of all spreadsheets are the basis of decisions› 85% of companies use them for financial reporting› 11 million end-users in the USA
![Page 3: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/3.jpg)
Spreadsheets
2
› Very error prone › Research papers on spreadsheet problems› Websites on spreadsheet problems› Google: “spreadsheet errors”
![Page 4: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/4.jpg)
Spreadsheet Errors
3
› Only recently has this been researched (~10 years!!)› Various techniques were developed› One of them is ClassSheets
![Page 5: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/5.jpg)
ClassSheets
4
Spreadsheets Models
ClassSheets
Instance
Instance
Instance Instance
Instance
![Page 6: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/6.jpg)
ClassSheets: Example
5
conforms to
![Page 7: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/7.jpg)
ClassSheets
6
› Everything is according to the model› Control of every spreadsheet› You only have to do it right once› What if you don’t do it right once?
![Page 8: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/8.jpg)
ClassSheets Quality
7
› ClassSheets still suffer from traditional problems› Readability› Maintainability› Extensibility
› A ClassSheet may be hard to understand› May have complicated design› New rules can force changes to spreadsheet structure › Refactorings!
![Page 9: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/9.jpg)
MDSheet
8
ClassSheet
Spreadsheet
ClassSheet’
Spreadsheet’
![Page 10: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/10.jpg)
MDSheet
9
data ModelOperation : Model Model =-- add a new column
addColumnM Where Index-- delete a column
| delColumnM Index-- add a new row
| addRowM Where Index-- delete a row
| delRowM Index-- set a label
| setLabelM (Index, Index) Label-- set a formula
| setFormulaM (Index, Index) Formula-- replicate a class
| replicateM ClassName Direction Int Int-- add a static class
| addClassM ClassName (Index, Index)-- add an expandable class
| addClassExpM ClassName Direction (Index, Index) (Index, Index)
![Page 11: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/11.jpg)
MDSheet
10
data ModelRefactoring: Spreadsheet [ModelOperation] =-- add a formula and shift cells
AddShiftForm ClassName Value Index Label Index-- add an attribute and shift cells
| AddShiftAtt ClassName Value Index Label Index-- delete a cell and shift cells
| DeleteShift ClassName Value Label-- add a reference and shift cells
| AddShiftRef ClassName ClassName Index-- delete a reference and shift cells
| DeleteShiftRef ClassName ClassName-- create a new class
| CreateClass ClassName Direction Index-- delete a class and shift cells
| DeleteClassShift ClassName
![Page 12: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/12.jpg)
Refactorings
11
1. Move Formula
2. Move Attribute
3. Extract Class
4. Inline Class
5. Remove Middle-Man
![Page 13: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/13.jpg)
Refactorings: Example
12
![Page 14: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/14.jpg)
Refactorings: Move Formula
13
› When/Why› Feature Envy› Semantically makes more sense elsewhere
…
› Refactoring
…
![Page 15: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/15.jpg)
Refactorings: Move Attribute
14
› When/Why› Visually enhance readability› Information evolution› Incorrect normalization in a relational class
…
…› Refactoring
![Page 16: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/16.jpg)
Refactorings: Extract Class
15
› When/Why› Complicated and hard to understand models› Neglected subset of information
› Refactoring
![Page 17: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/17.jpg)
Refactorings: Inline Class
16
› When/Why› Insufficient justification of the existence of a class› Not pulling its own weight› Often consulted information
› Refactoring
![Page 18: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/18.jpg)
Refactorings: Remove Middle-Man
17
› When/Why› Delegator class with little responsibility or purpose› Useless class which only complicated structure
› Refactoring
![Page 19: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/19.jpg)
Refactorings: Final Model
18
refactorings
![Page 20: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/20.jpg)
Refactorings: Final Instance
19
yrefactorings
![Page 21: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/21.jpg)
Quality of Refactored Models: Quick Analysis
20
Model› Removed one class
› Organized data to be semantically correct
› Readability
› Joining attributes closer to their formulas
› Placed often used attributes in easier to access areas
Instance› 14 less data cells (15% reduction)
› One more Client 22 less cells (17% reduction)
› Two more Clients 30 less cells (18% reduction)
› Larger our instances, more impactful are the refactorings
› Spreadsheet bad smell detector also showed a reduction in smells
![Page 22: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/22.jpg)
Conclusions and Future Work
21
› Presented a set of refactorings for ClassSheets› Implemented in a tool› First analysis shows model quality improvement› Further validation
› Quality assessment metrics› Empirical studies with professionals who use models daily
› More info?› http://ssaapp.di.uminho.pt› Read the paper› Ask me!
![Page 23: Talk at QUATIC'14](https://reader035.vdocuments.us/reader035/viewer/2022081603/558a2a07d8b42a32028b465b/html5/thumbnails/23.jpg)
Rui Pereira, Jácome Cunha, João Paulo Fernandes,Pedro Martins and João Saraiva
Refactoring meets Model-Driven Spreadsheet Evolution
QUATIC 2014Guimarães, Portugal