T3 - Application Domain 1: Spreadsheet Transformation

Spreadsheets are widely used by non-professional programmers, the so-called end users, to develop business applications. These programmers vastly outnumber professional ones creating millions of spreadsheets per year. Spreadsheets are quite flexible, but also very error-prone.

We have shown that the use of business logic models for spreadsheets can prevent errors and improve users productivity [4]. Using such MDE approach, the user is responsible to synchronize the (spreadsheet) models and instances. This is a complex and error-prone task since users may introduce data that violates the model. To overcome these limitations, we will define techniques to keep spreadsheet data and models synchronized. Moreover, to reflect data changes in the model is also an objective. Spreadsheets can be seen as a non-hierarquical structure where nodes are cells and edges references between them. Thus, techniques from task 2 can be used to specify synchronization of spreadsheets. We will take under consideration spreadsheet specific properties, such as layout, that we know from experience [4], can be very useful. This synchronization can be extended to synchronize a spreadsheet and a database (DB). One can imagine a situation where a DB is accessed by several kinds of users: professional programmers access it using SQL and end users using a spreadsheet as a view. In previous work we defined a formal transformation of a spreadsheet into a DB and vice versa, but without synchronization [32]. In this task we will define BTs between spreadsheets and DBs. Both the spreadsheet and the database can be the source and the view, and thus, transformations must be studied in both directions. Changes performed by each kind of users should be reflected in all the views of the DB. This problem is know as the view-update problem and has been studied in the context of DBs. To create a solution for DBs and spreadsheets is challenging for several reasons: in contrast to DBs, spreadsheets have spacial constraints and, moreover, spreadsheets are usually not normalized as DBs. Tools such as Excel can import data from DBs, but synchronization is not possible.

BTs are an important advance for spreadsheets because end users have two possible views: one view contemplates the formulas and another their results. A big source of errors is the replacement of a formula by a value, involuntarily or to have the correct value in such cell. In fact, this can be useful: if this change was back-propagated to the rest of the spreadsheet the user could understand why that result is wrong. We will use BTs to achieve this goal. The forward function is the usual spreadsheet mechanism. The backward one will receive the spreadsheet and the results the user wants and will produce a new updated spreadsheet with the correct values in the corresponding cells. The backward function may be ambiguous, which is a challenge and deserves further study.

We will run empirical studies to assess the impact of our tehcniques in error committing and speed of users doing their tasks. The experience of the team can help to define studies that produce significant validation.

Jácome Cunha has a PhD? on spreadsheets and is now a post-doc fellow working on the same subject. This makes him a good candidate to lead this task. Jácome Cunha was supervised in his PhD? by João Saraiva, which makes him a very experienced person on spreadsheets. João Fernandes' experience working with Jácome Cunha and João Saraiva, also on spreadsheets, is a fundamental for this task. Finally, a BI grant holder will be of great help implementing an Excel tool.

The expected deliverables are:

• An extension for Excel.
• Two international conference papers and a journal paper describing the techniques developed and their application to the aforementioned scenarios.
• A master thesis describing the implementation of the Excel extension.

Team

• Jácome Cunha
• João Fernandes
• João Saraiva
• BI2