Spreadsheet engineering

These tutorial notes present a methodology for spreadsheet engineering. First, we present data mining and database techniques to reason about spreadsheet data. These techniques are used to compute relationships between spreadsheet elements (cells/columns/rows). These relations are then used to infer a model defining the business logic of the spreadsheet. Such a model of a spreadsheet data is a visual domain specific language that we embed in a well-known spreadsheet system. The embedded model is the building block to define techniques for modeldriven spreadsheet development, where advanced techniques are used to guarantee the model-instance synchronization. In this model-driven environment, any user data update as to follow the the model-instance conformance relation, thus, guiding spreadsheet users to introduce correct data. Data refinement techniques are used to synchronize models and instances after users update/evolve the model. These notes brie y describe our model-driven spreadsheet environment, the MDSheet environment, that implements the presented methodology. To evaluate both proposed techniques and the MDSheet tool, we have conducted, in laboratory sessions, an empirical study with the summer school participants. The results of this study are presented in these notes

Embedding, evolution, and validation of model-driven spreadsheets

This paper proposes and validates a model-driven software engineering technique for spreadsheets. The technique that we envision builds on the embedding of spreadsheet models under a widely used spreadsheet system. This means that we enable the creation and evolution of spreadsheet models under a spreadsheet system. More precisely, we embed ClassSheets, a visual language with a syntax similar to the one offered by common spreadsheets, that was created with the aim of specifying spreadsheets. Our embedding allows models and their conforming instances to be developed under the same environment. In practice, this convenient environment enhances evolution steps at the model level while the corresponding instance is automatically co-evolved.Finally,wehave designed and conducted an empirical study with human users in order to assess our technique in production environments. The results of this study are promising and suggest that productivity gains are realizable under our model-driven spreadsheet development setting.The authors of this paper would like to express their gratitude to Dr. Nuno Alpoim, CEO of Agere, for providing us and our study with a spreadsheet under usage in industry. This work is funded by ERDF-European Regional Development Fund through the COMPETE Programme (operational programme for competitiveness) and by National Funds through the FCT-Fundacao para a Ciencia e a Tecnologia (Portuguese Foundation for Science and Technology) within projects FCOMP-01-0124-FEDER-020532 and FCOMP-01-0124-FEDER-010048. This work was also supported by Fundacao para a Ciencia e a Tecnologia with grants SFRH/BPD/73358/2010 and SFRH/ BPD/46987/2008

From relational ClassSheets to UML+OCL

Spreadsheets are among the most popular programming languages in the world. Unfortunately, spreadsheet systems were not tailored from scratch with modern programming language features that guarantee, as much as possible, program correctness. As a consequence, spreadsheets are populated with unacceptable amounts of errors. In other programming language settings, model-based approaches have been proposed to increase productivity and program efectiveness. Within spreadsheets, this approach has also been followed, namely by ClassSheets. In this paper, we propose an extension to ClassSheets to allow the specification of spreadsheets that can be viewed as relational databases. Moreover, we present a transformation from ClassSheet models to UML class diagrams enriched with OCL constraints. This brings to the spreadsheet realm the entire paraphernalia of model validation techniques that are available for UML.(undefined

DataBasic: Design Principles, Tools and Activities for Data Literacy Learners

The growing number of tools for data novices are not designed with the goal of learning in mind. This paper proposes a set of pedagogical design principles for tool development to support data literacy learners.  We document their use in the creation of three digital tools and activities that help learners build data literacy, showing design decisions driven by our pedagogy. Sketches students created during the activities reflect their adeptness with key data literacy skills. Based on early results, we suggest that tool designers and educators should orient their work from the outset around strong pedagogical principles

Refactoring smelly spreadsheet models

Identifying bad design patterns in software is a successful and inspiring research trend. While these patterns do not necessarily correspond to software errors, the fact is that they raise potential problematic issues, often referred to as code smells, and that can for example compromise maintainability or evolution. The identification of code smells in spreadsheets, which can be viewed as software development environments for non-professional programmers, has already been the subject of confluent researches by different groups. While these research groups have focused on detecting smells on concrete spreadsheets, or spreadsheet instances, in this paper we propose a comprehensive set of smells for abstract representations of spreadsheets, or spreadsheet models. We also propose a set of refactorings suggesting how spreadsheet models can become simpler to understand, manipulate and evolve. Finally we present the integration of both smells and refactorings under the MDSheet framework.Part funded by ERDF - European Regional Development Fund through the COMPETE Programme (operational programme for competitiveness) and by National Funds through the FCT - Fundação para a Ciência e a Tecnologia within projects FCOMP-01-0124-FEDER-022701 and Network Sensing for Critical Systems Monitoring (NORTE-01-0124-FEDER-000058), ref. BIM-2013 BestCase RL3.2 UMINHO

Model inference for spreadsheets

Many errors in spreadsheet formulas can be avoided if spreadsheets are built automati- cally from higher-level models that can encode and enforce consistency constraints in the generated spreadsheets. Employing this strategy for legacy spreadsheets is dificult, because the model has to be reverse engineered from an existing spreadsheet and existing data must be transferred into the new model-generated spreadsheet. We have developed and implemented a technique that automatically infers relational schemas from spreadsheets. This technique uses particularities from the spreadsheet realm to create better schemas. We have evaluated this technique in two ways: First, we have demonstrated its appli- cability by using it on a set of real-world spreadsheets. Second, we have run an empirical study with users. The study has shown that the results produced by our technique are comparable to the ones developed by experts starting from the same (legacy) spreadsheet data. Although relational schemas are very useful to model data, they do not t well spreadsheets as they do not allow to express layout. Thus, we have also introduced a mapping between relational schemas and ClassSheets. A ClassSheet controls further changes to the spreadsheet and safeguards it against a large class of formula errors. The developed tool is a contribution to spreadsheet (reverse) engineering, because it lls an important gap and allows a promising design method (ClassSheets) to be applied to a huge collection of legacy spreadsheets with minimal effort.We would like to thank Orlando Belo for his help on running and analyzing the empirical study. We would also like to thank Paulo Azevedo for his help in conducting the statistical analysis of our empirical study. We would also like to thank the anonymous reviewers for their suggestions which helped us to improve the paper. This work is funded by ERDF - European Regional Development Fund through the COMPETE Programme (operational programme for competitiveness) and by National Funds through the FCT - Fundacao para a Ciencia e a Tecnologia (Portuguese Foundation for Science and Technology) within project FCOMP-01-0124-FEDER-010048. The first author was also supported by FCT grant SFRH/BPD/73358/2010

Directed evolution of model-driven spreadsheets

Dissertação de mestrado em Engenharia InformáticaSpreadsheets are among the most used programming languages today. The easy to use and the intuitive nature of the visual interface makes them a preferred programming tool for any kind of individual or organization. The flexibility they provide to organize data as users need to is one of the reasons that makes them so popular. However, this flexibility also makes them very error-prone. In order to improve spreadsheet quality and reduce the number of errors, software engineering practices were introduced, namely object oriented and model-driven techniques. These techniques enabled the specification of the spreadsheet business logic, which offers the possibility to better structure data, while at the same time narrowing the range of types of errors made by user input. While these developments had a huge impact, spreadsheet evolution is still an inherently human process, which is in itself error-prone. In many real world applications of spreadsheets, they are used to store and disseminate data between different systems. Different systems can use different data formats, this leads to the need to change and adapt the data produced by a source system so that it complies to the data format consumed by a target system. Usually in these cases, both the initial and final data models are known in advance. The objective of this thesis is to present techniques that enable data evolution to be made automatically, using model-driven spreadsheets.Folhas de cálculo são um dos paradigmas de programação mais utilizados actualmente. A sua facilidade de utilização e reduzida curva de aprendizagem torna-as numa das ferramentas de programação mais utilizadas diariamente por milhões de indivíduos e organizações. A flexibilidade concedida pelas folhas de cálculo para organizar dados consoante a preferência dos utilizadores é uma das razões que as torna tão populares. Esta flexibilidade tem, contudo, uma grande desvantagem: torna-as muito propícias a erros. De forma a elevar a qualidade, e reduzir o número de erros em folhas de cálculo, foram introduzidas práticas já estabelecidas em engenharia de software, nomeadamente técnicas de desenvolvimento orientado a objectos e desenvolvimento dirigido por modelos. Com estas técnicas passou a ser possível especificar a lógica de negócio de folhas de cálculo, o que proporciona a estruturação dos dados nelas contidos e, ao mesmo tempo, limita o tipo de erros passíveis de serem cometidos pelos utilizadores. Embora estes desenvolvimentos tenham tido um grande impacto, a evolução de folhas de cálculo continua a ser um processo inerentemente humano, o que pode, ainda assim, originar erros. Em muitos casos reais de utilização de folhas de cálculo, elas são utilizadas para armazenar e disseminar informação entre diferentes sistemas. Diferentes sistemas podem utilizar diferentes formatos de dados, isto leva à necessidade de adaptar os dados produzidos por um sistema para que sejam compatíveis com um determinado sistema de destino. Normalmente nestes casos, ambos os modelos de dados são conhecidos à partida. O objectivo desta tese é apresentar um conjunto de técnicas que permitam fazer esta evolução de forma totalmente automática, utilizando para isso folhas de cálculo dirgidas por modelos.Fundação para a Ciência e a Tecnologia (FCT) - FCOMP-01-0124- FEDER-010048 and FCOMP-01-0124-FEDER-020532 projects.FEDER Funds through the Programa Operacional Factores de Competitvidade - COMPETE