Lecture Notes in Computer Science Volume 6654, 2011Spreadsheets are widely used and studies show that most of the existing ones contain non-trivial errors. To improve end-users productivity, recent research proposes the use of a model-driven engineering approach to spreadsheets. In this paper we conduct the first empirical study to assess the effectiveness and efficiency of this approach. A set of spreadsheet end users worked with two different model-based spreadsheets. We present and analyze here the results achieved.(undefined

B.A. Nardi

C. Scaffidi

E.F. Codd

G. Engels

J. Cunha

R. Abraham

Beckwith, Laura

Cunha, Jácome Miguel Costa

Fernandes, João Paulo

Saraiva, João Alexandre

Universidade do Minho: RepositoriUM

End-users productivity in model-based spreadsheets: an empirical study

End-users Productivity in Model-based Spreadsheets:An Empirical Study?Laura Beckwith1, Ja´come Cunha2,3, Joa˜o Paulo Fernandes2,4, and Joa˜o Saraiva21 HCIResearcher, Denmark, beckwith@hciResearcher.com2 Universidade do Minho, Portugal, {jacome,jpaulo,jas}@di.uminho.pt3 ESTGF, Instituto Polite´cnico do Porto, Portugal4 Universidade do Porto, PortugalAbstract. Spreadsheets are widely used and studies show that most of the exist-ing ones contain non-trivial errors. To improve end-users productivity, recent re-search proposes the use of a model-driven engineering approach to spreadsheets.In this paper we conduct the first empirical study to assess the effectiveness andefficiency of this approach. A set of spreadsheet end users worked with two differ-ent model-based spreadsheets. We present and analyze here the results achieved.1 IntroductionSpreadsheets (SS) can be viewed as programming environments for non-professionalprogrammers, the so-called “end users” [7]. End-user programmers vastly outnumberprofessional ones and create hundreds of millions of spreadsheets every year [10], espe-cially for developing business applications. As numerous studies have shown, this highproduction rate is accompanied by an alarming high rate of errors, with some reportsclaiming that 90% of real-world spreadsheets contain errors [8, 9].In order to improve the robustness of SS, a considerable amount of research hasbeen done [1, 3–6]. One of the promising solutions advocates the use of a Model-DrivenEngineering (MDE) approach, in which a business model of the spreadsheet data is de-fined; end users are then guided to introduce data that conforms to the model [4]. Sev-eral models have been proposed namely, templates [1], ClassSheets [3, 6] and relationalmodels [5] and also techniques to infer models from (legacy) spreadsheet data [1, 3].Although all these works claim that a MDE approach improves end-users produc-tivity, the reality is that there is no detailed evaluation study to support this idea. In thispaper, we present an empirical study that we have conducted with the aim of analyz-ing the practical influence that models have on productivity. We consider two differentmodel-based SS, as proposed in [4, 5]. We assess the productivity in introducing, up-dating and querying data in those two model-based SS and in a traditional one.Our study is necessary and useful: it is based on a sound experimental setting whichallows us to draw sound conclusions and directions for further studies on the same topic.With it, we wish to answer the following research questions:RQ1 Do end users introduce fewer errors when they use one of the model-basedspreadsheet versus the original unmodified spreadsheet?RQ2 Are end users more efficient using the model-based ones?? Supported by Fundac¸a˜o para a Cieˆncia e a Tecnologia, grants no. SFRH/BD/30231/2006,SFRH/BPD/46987/2008 and PTDC/EIA-CCO/108613/2008.IIRQ3 Do particular models lead to fewer errors in particular tasks?We used a within subjects design, where each participant received a task list for eachof three spreadsheet environments. Participants were asked to do various tasks in eachspreadsheet, for example: data entry, modifications to existing data, and calculations ofthe data in the spreadsheet. They were encouraged to work as quickly as possible, butwere not given time limits for any specific spreadsheet.2 Model-based SpreadsheetsTwo different techniques to tackle the problem of preventing errors in SS have beenproposed [4, 5]. In order to introduce them we will rely on the spreadsheet shown inFig. 1, which represents a movie renting system (labels should be self-explicative).Fig. 1: Part of a spreadsheet representing a movie renting system.The Refactored Model: The spreadsheet in Fig. 1 shows an instance of a renting sys-tem containing redundant information: for example, client Paul’s information appearstwice! This kind of redundancy makes maintenance complex and error-prone. A mis-take is easily made (for example, by mistyping a name) and thus corrupting the data.The same information can be stored without redundancy: in the database realm, tech-niques for data normalization, based on the exploitation of functional dependencies(FDs) inherent in the data, are commonly used to minimize duplication of informationand improve data integrity [2]. We have adapted these techniques to work with spread-sheets: from the spreadsheet data we infer a set of normalized FDs, and from them, wecompute a relational model [5]. A spreadsheet respecting such model is shown in Fig. 2.Fig. 2: Part of a refactored spreadsheet representing a movie renting system.The obtained data organization solves two well-known database problems, namelyupdate anomalies and deletion anomalies [2]. The former occurs when we change in-formation in one tuple but leave the same information unchanged in others, e.g., if a userchanges the rent per day of mv23 from 0.5 to 0.6. This value occurs only once in themodular spreadsheet, where it must be updated. The latter happens when we delete atuple and lose other information as a side effect, e.g., if a user deletes the rent in row 3in the original spreadsheet all the information concerning movie mv1 is eliminated.Since we know the data relations and relationships, we can generate SS that re-spect them, and thus, help end users. For example, in the renter table, the generatedspreadsheet should not allow the user to introduce two renters with the same number.The new spreadsheet improves modularity and detects the introduction of incorrectdata, and also eliminates redundancy; this should help end users commit less errors.IIIThe Visual Model: In [4], a technique to enhance a system with mechanisms to guideend users to introduce correct data was proposed. Using the relational database schemainduced by the data we construct an environment that respects that schema. For ex-ample, for the movie spreadsheet, the system must not allow the introduction of twodifferent movies with the same number. Instead, it offers to the user a list of possiblemovies, such that the value to fill in the cell can be chosen. This new spreadsheet, thatwe show in Fig. 3, also includes advanced features which provide information to theend user about correct data that can be introduced.Fig. 3: Part of a visual spreadsheet representing a movie renting system.We consider three types of advanced features: (bidirectional) auto-completion ofcolumn values, non-editable columns and safe deletion of rows.3 Analyzing End-users PerformanceFig. 4: Global effectiveness results.Effectiveness Each participant was handed3 different lists of tasks to perform on3 different spreadsheets, each of whichconstructed under a different model. Wehave graded their performance under eachmodel, obtaining the results in Fig. 4. Wenotice that no model is neither the best northe worst for all spreadsheets. Neverthe-less, the results seem to indicate that modelsfrom [3] and [5] are not effective in reduc-ing the number of errors, since one of themis always getting the lowest scores. This intuition deserves further development.One may argue that original is the model that users are more accustomed to. Never-theless, we remark that the more complex models refactored and visual where given noexplanation; a part of our study was also to learn whether they could live on their own.Our next step was to investigate whether the (apparent) poor results obtained bycomplex models are due to their own nature or if they result from participants not havingunderstood them. In order to realize this, we studied the participations that did notachieve a score of at least 50%: 0% in original, 25% in refactored and 21% in visual.While in original no participation was graded under 50%, this was not the case forrefactored and visual; this may have degraded their overall average results. For theseparticipations, we analyzed the questionnaire that participants were asked to fill in afterthe session. The classifications for the post session questionnaires, for participations inthe study that were graded under 50% is 24% for refactored and 31% for visual.These results show that participants obtaining poor gradings on their effectiveness,also got extremely poor gradings for their answers to the questions assessing how theyunderstood (or not) the models. Indeed, we can see that they were not, in average, ableIVto answer correctly to (at least) two thirds of the questions raised in the post sessionquestionnaire. From such results we can read that (roughly) a quarter of participantswas not able to understand the more complex models used in the study, which mighthave caused a degradation of the global effectiveness results for these models. This alsosuggests that if these models are to be used within an organization, it is necessary to takesome time to introduce them to end users in order to achieve maximum effectiveness.Effectiveness by Task Type: Next, we wanted to realize how effective models are toperform the different types of tasks that we proposed: insertion, edition and statistics.Fig. 5: Effectiveness results for insertion.i) Data insertion: the original modelwas the most effective, for all 3 spread-sheets, being closely followed by refac-tored and visual for DISHES, and by visualfor PROJECTS. The refactored model, forPROJECTS, and refactored and visual, forPROPERTIES, proved not to be competitivefor data insertion. Again, we believe thatthis in part due to the lack of introductionto these models: the insertion of new data isthe task that most likely benefits from un-derstanding them, and also the one that can be otherwise most affected. This is con-firmed by the effectiveness results observed for other task types, that we present next.Fig. 6: Effectiveness results for edition.ii) Data edition once a spreadsheet ispopulated, we can effectively use modelsto edit it. This is the case of refactoredfor PROJECTS and for PROPERTIES. orig-inal is the most effective in editing forDISHES. visual is comparable to refactoredfor DISHES, but for other spreadsheets, it al-ways achieves the lowest scores.iii) Statistics: we can see that visual ob-tained the best results for DISHES, and thatrefactored obtained the best results for bothspreadsheets PROJECTS and PROPERTIES.We can also see that all models obtained the worst results for exactly one spreadsheet.Fig. 7: Effectiveness results for statistics.Results from i), ii) and iii) confirm thatthe models are competitive against the orig-inal model. On the other hand, these re-sults allow us to draw some new conclu-sions: if the models are going to be usedwithin an organization, it may not alwaysbe necessary to introduce them prior to theiruse. Indeed, if an organization mostly ed-its spreadsheet data or computes new val-ues from such data, and does not insert newdata, then the models, and specially refac-Vtored, may deliver good results even when they are not explicitly explained (as it wasthe case in our study). These results also show that it is in the data insertion tasks thatthe models need to be better understood by end users in order to increase effectiveness.Fig. 8: Global efficiency results.Efficiency We started by measuring, foreach participant, and for each spreadsheet,the time elapsed from the moment partici-pants started reading the list of tasks to un-dertake until the moment they completedthe tasks proposed and moved on to a dif-ferent spreadsheet or concluded the study.We are able of calculating these times bylooking at the individual screen activity thatwas recorded during the study, for each par-ticipant: the participant stopping interactingwith the computer signals the end of his/her work on a spreadsheet. The measured pe-riod therefore includes the time that participants took trying to understand the modelsthey received each spreadsheet in. Fig. 8 presents the average of the overall times, foreach spreadsheet and for each model.We can see that refactored and visual are competitive against original. Indeed, par-ticipants performed fastest for DISHES in visual, and fastest for PROPERTIES in refac-tored. The original model got the best result for PROJECTS. Again, note that no intro-duction to refactored or visual preceded the study. Therefore, it is reasonable to assumethat, for these models, the results observed include some time overhead. In an attemptto measure this overhead we extracted some more information out of the results of ourstudy: we measured the time elapsed from the moment participants started reading, foreach spreadsheet, the list of tasks to perform, until the moment they actually began edit-ing the spreadsheet. We assume that this period corresponds exactly to the overhead ofunderstanding each model. The average results obtained are presented in Table 1.original refactored visualDISHES 2′ 6′ 1′PROJECTS 2′ 4′ 2′PROPERTIES 2′ 2′ 2′Table 1: Average overhead results.There is a constant average overhead of 2minutes for almost all models and all spread-sheets, with the most significant exceptionsoccurring for refactored, for both DISHESand PROJECTS. In these cases, we can clearlynotice an important time gap, which pro-vides some evidence that refactored is mostlikely the hardest model to understand. Thisalso comes in line with previous indications that the merits of the models can be max-imized if we take the time to explain them to end users. For the particular case ofefficiency, this means that the results shown in Fig. 8 could be further improved for themore complex models, and particularly for refactored.4 ConclusionsWe have presented the results of an empirical study that we conducted in order to assessthe practical interest of models for spreadsheets. From the preparation of the study, fromVIrunning it and from its results, we can summarize our main contributions as follows:i) we have shown that MDE techniques can be adapted for end-users software; ii) weproved empirically that models can bring benefits for spreadsheet end users; iii) weproposed a methodology that can be reused in studies similar to ours.Now, we seek to answer our initial research questions.RQ1 Our observations indicate that model-based spreadsheets can improve end-usereffectiveness. Even if this is not always the case, our results also indicate that deeperinsight on the spreadsheet models is required to maximize effectiveness.RQ2 We observed that, frequently, the more elaborate spreadsheet models allowedusers to perform faster. Nevertheless, we were not fully able of isolating the time thatparticipants took trying to understand the models they were working with. So, we be-lieve that the observed efficiency results could also be better for refactored and visualif they had been previously introduced.RQ3 Although this was not observed for inserting tasks, for editing and querying datathe models did help end users. Furthermore, the results seem to indicate that the insert-ing data task is the one that benefits the most from better understanding the models.With this study we have shown that there is potential in MDE techniques for helpingspreadsheet end users. The study of these techniques for professional users of spread-sheets seems a promising research topic. Moreover, the use of MDE techniques in othernon-professional softwares should also be investigated.References1. Abraham, R., Erwig, M.: Inferring templates from spreadsheets. In: Proc. of the 28th Int.Conference on Software Engineering. pp. 182–191. ACM, New York, NY, USA (2006)2. Codd, E.F.: A relational model of data for large shared data banks. Communications of theACM 13, 377–387 (June 1970)3. Cunha, J., Erwig, M., Saraiva, J.: Automatically inferring classsheet models from spread-sheets. In: Proc. of the 2010 IEEE Symposium on Visual Languages and Human-CentricComputing. pp. 93–100. IEEE Computer Society, Washington, DC, USA (2010)4. Cunha, J., Saraiva, J., Visser, J.: Discovery-based edit assistance for spreadsheets. In: Proc.of the 2009 IEEE Symposium on Visual Languages and Human-Centric Computing. pp.233–237. IEEE Computer Society, Washington, DC, USA (2009)5. Cunha, J., Saraiva, J., Visser, J.: From spreadsheets to relational databases and back. In:PEPM ’09: Proceedings of the 2009 ACM SIGPLAN Workshop on Partial Evaluation andProgram Manipulation. pp. 179–188. ACM, New York, NY, USA (2009)6. Engels, G., Erwig, M.: ClassSheets: Automatic generation of spreadsheet applications fromobject-oriented specifications. In: Proceedings of the 20th IEEE/ACM International Confer-ence on Automated Software Engineering. pp. 124–133. ACM, New York, NY, USA (2005)7. Nardi, B.A.: A Small Matter of Programming: Perspectives on End User Computing. MITPress, Cambridge, MA, USA (1993)8. Panko, R.R.: Spreadsheet errors: What we know. What we think we can do. Proceedings ofthe Spreadsheet Risk Symposium, European Spreadsheet Risks Interest Group (July 2000)9. Rajalingham, K., Chadwick, D., Knight, B.: Classification of spreadsheet errors. EuropeanSpreadsheet Risks Interest Group (EuSpRIG) (2001)10. Scaffidi, C., Shaw, M., Myers, B.: Estimating the numbers of end users and end user pro-grammers. In: Proceedings of the 2005 IEEE Symposium on Visual Languages and Human-Centric Computing. pp. 207–214. IEEE Computer Society, Washington, DC, USA (2005)

Crossref

End-Users Productivity in Model-Based Spreadsheets: An Empirical Study

http://alfa.di.uminho.pt/~jacome/down/is-eud11.pdf

End-users productivity in model-based spreadsheets: an empirical study

Abstract

Similar works

Full text

Available Versions

Universidade do Minho: RepositoriUM

Universidade do Minho: RepositoriUM

Crossref