Ravi Mohan's Blog
Sunday, September 17, 2006
Spreadsheets and Self Sabotage A Case Study
This is a true story. Names have been changed to protect the innocent (and the guilty). Once upon a time,long long ago, in a faraway land, there was this company, a typical enterprisey company we'll call BankCo (in reality the company doesn't work in banking, the domain is similar but different, and I am just obfuscating things as explained above). BankCo was running all its "enterprise" operations off a set of excel sheets and at some point decided to redo it "properly". The folks at BankCo were essentially businessmen, not programmers so they decided to get some consultants at the cutting edge of technology. Enter AgileCo. AgileCo decided to rewrite the business logic as a 3 tired OO app using all the latest blub technologies. The result? the BHS project (in AgileCo most projects have three letter acronyms) has frustrated developers, grumbling clients and absolutely yucky code. Now there were a lot of things the team did wrong. Fundamentally the project was doomed the moment the team was "volunteered" for such an aggressive schedule (committed to the client by the sales manager, "agile planning" notwithstanding) but the thinking might have been that "hey we just have a bunch of spreadsheets, how hard can it be to convert them into a 3 tiered Blub app" . The (non intuitive) correct answer is "potentially very very hard". Spreadsheets are very subtle beasts. They just look very simple. There are these rows and columns of cells and even non programmers can quickly create useful "programs" so it should be fairly easy to translate a set of spreadsheets into OO programs right? Wrong. For one, a spreadsheet is essentially a functional program.[warning - pdf] [Excerpt] "It may seem odd to describe a spreadsheet as a programming language. Indeed, one of the great merits of spreadsheets is that users need not think of themselves as doing “programming”, let alone functional programming — rather, they simply “write formulae” or “build a model”. However, one can imagine printing the cells of a spreadsheet in textual form, like this: A1 = 3 A2 = A1-32 A3 = A2 * 5/9 and then it plainly is a (functional) program. Thought of as a programming language, though, a spreadsheet is a very strange one. In particular, it is completely ﬂat: there are no functions apart from the built-in ones(*). Instead, the entire program is a single ﬂat collection of equations of the form “variable = formula”. Amazingly, end users nevertheless use spreadsheets to build extremely elaborate models, with many thousands of cells and formulae." Simon goes on to state how he and his colleagues worked on removing the "procedural abstraction barrier" in the functional language embodied by a typical spreadsheet. (There are folks working on integrating more powerful functional languages and other paradigms into spread sheets. The key point is that a spreadsheet is essentially functional. Besides, a spreadsheet propogates changes in values and formulae automatically, unlike imperative and oo languages (e.g if the cell A1 is set to 5 instead of 3 in the above sample, A2 automatically becomes -27). Besides value propagation , the 2 dimensional spatial relationships between spreadsheet cells makes it unlike most programming interfaces in existence today. " One of the most successful end-user programming techniques ever invented, the spreadsheet, uses what is essentially a functional model of programming. Each cell in a spreadsheet contains a functional expression that specifies the value for the cell, based on values in other cells. There are no imperative instructions, at least in the basic, original spreadsheet model. In a sense each spreadsheet cell pulls in the outside values it needs to compute its own value, as opposed to imperative systems where a central agent pushes values into cells. In some sense each cell may be thought of as an agent that monitors its depended-upon cells and updates itself when it needs to. As Nardi (1993)(Nardi 1993) points out, the control constructs of imperative languages are one of the most difficult things for users to grasp. Spreadsheets eliminate this barrier to end-user programming by dispensing with the need for control constructs, replacing them with functional constructs. " [ Link Thus, when you try to convert the functionality of a complex set of spreadsheets to a blub-dot-net program, you are, in essence translating from a functional language with value propagation to a very different paradigm (OO in this case) and also adding things like persistence and multi user interaction which a normal spreadsheet does not have to deal with. This project needs competent developers who have enough resources (including time) to do a proper job,and are expert in both functional and object oriented programming with a keen awareness of the chasm between the two paradigms and a knowledge of how to bridge it. You wouldn't have much use for a German to English translator who didn't know any German, would you? Besides this technical misstep there are a host of others that make AgileCo's BHS project an interesting case study. The first is the premature time commitment to clients, bypassing the essence of AgileCo's favorite planning methodology. This is the killer mistake. The second is an arbitrary team structure with unclear roles and shaky team buy in. The third factor worth discussing is how the dominant methodology at AgileCo discourages deep thought by developers and encourages them to grab a "User Story" and hit the keyboard as soon as possible. This eliminates the chances of success for finding a technical approach that attacks the core of the problem. The fourth is how a totally unnecessary onshore/offshore team split adds dysfunctional management, unnecessary paperwork and communication overhead. The fifth and most crucial insight is about how dumb management can totally screw up a project's chances of success beyond repair. Postscript:- The team is now on deathmarch mode and works on Saturdays and some Sundays. Of course the managers don't work on weekends. The brighter developers are looking for other jobs. (*) Most spreadsheets have some kind of extension mechanism. In Excel, for e.g. you can use VB script to write your own extensions. But this means (a) and end user has to learn a programming language and (b) subtle errors can creep in when you mix imperative and functional code. Debugging spreadsheets is a nightmare at the best of times.