Office Infotech Symbiosis

Category: BrainStorm

Programming for businesses has always involved BusinessProfessionals who communicate with IT staff. TgpMethodology makes BusinessProfessionals part of the development teams, which is a close relationship.

Perhaps the BusinessProfessionals are able to do more with tools.

Many BusinessProfessionals make complicated spreadsheets. Although, spreadsheets are a corporate resource, they are not as well tested and controlled as they could be, see:

Spreadsheets are classified as cell-oriented languages, see:

I believe the state of spreadsheets is a business opportunity for tools that let BusinessProfessionals develop software, and IT professionals manage, test, and help maintain that software. There are procedural and technical challenges, both short and long term, to fully exploit this opportunity.

IT can, for example, do simple things, such as making versioned backups of spreadsheets. IT may also loan programmers to help test spreadsheets. But, the greatest opportunities lie with additional tools.

The following isn't about a traditional spreadsheet.

My vision of helpful tools actually involves two symbiotic languages, one used by BusinessProfessionals and the other used by IT programmers. The one to be used by BusinessProfessionals is a cell-oriented programming language that is, among other things, a SpreadsheetMetaphor. For familiarity, cells can be initially arranged as a table with rows and columns, and the typical spreadsheet functions and features will work as they do with any spreadsheet.

However, cells can be placed anywhere on a worksheet, not only among rows and columns. The appearance of a spreadsheet is a facade or skin caused by a grid on the worksheet. Thus, a worksheet can be a form or menu, with cells used as fields on the form or menu. And, with this tool BusinessProfessionals may design systems with data entry forms, report forms, menus, and spreadsheet tables, using familiar cells.

Cells will be named, instead of numbered by row and column. Consequently, cells must have properties to display cells in the proper size and location, specifically worksheet name, x, y, height and width.

Since cells are named, there should be a display of cells in alphabetical order similar to a dictionary, showing name and value or name, formula and value.

A data entry form will be able to fill rows and columns with data. Thus, it will sometimes be necessary to iterate calling a data entry worksheet. At least two different kinds of call are necessary, one that allows an unlimited number of call iterations, and one that limits the number of call iterations.

The unlimited call requires a form name that is called, a worksheet name where data is to be stored, and a grid location on the worksheet where data is to be stored. The first call to a data entry form would store the entered data in the worksheet row at the grid location. The next call would store a row of data below the first, the next below the second, and so on. The process stops when the person entering data clicks a stop button, which is a cell that works as a button.

The limited call requires one additional call argument, the maximum number of times the form will be called. Otherwise, it is the same as an unlimited call.

I believe that too many features for the cell-oriented language can make it intimidating for a BusinessProfessional?, and advise against loading it with many more features than already exist in a spreadsheet. Even the idea iteratively calling a data entry program seems a bit risky, but believe most BusinessProfessionals can understand and use it.

A menu worksheet contains links that show other worksheets, similar to links on a web page that load other web pages.

IMO the MetaLanguage used by IT professionals to operate on the cell-oriented language should be the language used to implement the cell-oriented language. Then, the two languages would be SymbioticLanguages. Moreover, the metalanguage would know the spreadsheet file format, which is the dictionary structure used by the cell-oriented language. To the MetaLanguage, a spreadsheet file would be a symbol table and parse tree.

IT Programmers and meta programmers can do many things to the spreadsheet programs, for example ReFactoring, DataMining, optimizing, and translating cell programs into another language with a faster runtime. Translated code may become independent programs or additional functions for the cell-oriented language. In addition, meta programmers may process to cell-oriented language feature enhancement requests from BusinessProfessionals.

That's enough brainstorm, by -- EdwinEarlRoss Hope it makes sense, I'm dyslexic, which adversely affects my writing and proofreading.

It's an interesting idea, one that seems to pop up repeatedly. When I was at university in the early 1980s, I came up with a similar idea and called it the "datasheet". I thought it was very clever and that I must be brilliant and creative. Unfortunately, "my" idea never made it much beyond a few rough prototypes, but it led to discovering that numerous other developers had very similar notions, to the point that almost every business developer with an ounce of creativity seems to "invent" it at some point. It seems almost obvious, doesn't it? Lotus Corp had the idea too and called it Improv. It was a commercial flop. Subsequent efforts, such as Spreadsheet 2000, have similarly tanked. -- AnonymousDonor

I read a review of Improv and I don't think that the idea brainstormed above is insufficiently innovative. I don't buy the argument that they are bad because they were not successful. Evolution doesn't choose the best.

The problem with Improv (and other innovations) is not that they are not up to the task or difficult to learn. Rather they are TooPowerfulForTheirOwnGood?. This takes different forms: And in the face of such difficulties and without any marketing the tool can at best hope for a niche despite all its innovation.

The trick is to provide users with a migration path instead of instantly dumping them into a no-return situation.

Thus I think - to get back to the spreadsheet - that you cannot immediately let go of the rows and columns. They are needed at least for import and export. What you might do is to use a customizable naming and layout scheme. Thus the normal grid sheet would be a two-dimensional view onto a data set. Or onto more than one data set: All too often one sheet contains more than one 'table' as well as some extra 'parameter' cells. And be it only for presentational reasons. To accommodate this you'd need to view the traditional grid only as a presentation area.

Most spreadsheet apps already abstract a bit from the cell references: Just insert rows or columns or move a part of the sheet and the formulas will adapt. So why not -- GunnarZarncke

Support for named ranges has been a standard feature of spreadsheet software since (at least) the DOS versions of Lotus 1-2-3. It's rarely used. Whilst IT professionals, and probably software developers in particular, regard naming ranges to be intuitive, non-technical users usually find it the opposite.

Spreadsheet users often put a label (e.g., Total Sales) in a cell nearby another cell or group of cells. Just need to design an intuitive, easy user interface to connect labels and cells they name. Unlabeled cells can default a name as either the cell formula or location. -- EdwinEarlRoss

Making software do a specific job is within the realm of some non-IT professionals. But, making it maintainable is not, in my observation. I compare it to a home-made car. While one may be able to make one that runs after taking some auto-repair and shop class, making it serviceable by other mechanics is a step up in skill level. "Why is this paper-clip covering this bobby pin?" Maintenance is often the most expensive aspect of software, not getting version 1.0 to run. Maintainability of design takes programming experience, pure and simple. Related: ComputerProgrammingForEverybody, especially "Case Study: Lotus 1-2-3".

I've experimented, failed, and learned about what works and what doesn't over the years as far as maintainability. Being burned by both my designs and others' has left behind mental pathways that dutifully warn me when a design is going down an overly-risky path. Sometimes I cannot even find a fix (cleanup) of a bad design within a reasonable amount of time. But at least I know to document (comment) the hell out of that section so that I can figure out the mess 5 years down the road. -top
Most jobs I've had were doing SW maintenance. In my experience programmers write more unmaintainable programs than otherwise, probably because they are happy to get their program to work. In my experience programmers do not RefactorMercilessly, and management doesn't want them to take the time. Moreover, I've seen the quality of programs degrade after a series of "minor" enhancements and/or bug fixes; eventually someone decides the degraded piece of trash needs to be rewritten. IT shops need to clean up their act, too.

However, IT is in the business of maintaining software. And, a business need has been identified regarding spreadsheet errors and maintenance. See: I started this page hoping some creative people would have creative ideas toward solving that need. I posed an idea, and got a few comments. However, it has not sparked a brainstorm. In fact, the opposite seems to have happened. People came up with "reasons" why my idea cannot help, and some things that have been tried by others did not work either. Such experience is not evidence there is no solution, only that no one has solved the problem to date.

Unmaintainable code is a big expense, whether spreadsheet or otherwise. I believe we can do better as an industry, but it will not be easy.


The two biggest problems with maintainability are incentive and measurement. There is currently only limited incentive to make code maintainable in most orgs. And if there was an incentive system, it would require metrics. A person designated "maintenance czar" would likely inject personable preferences and controversial procedures that may not go over well with staff, creating a cat-and-mouse battle. And it's hard to prove that your efforts are really paying off. The average staff turn-over in IT is about 3 to 4 yours, which is not enough to really know. -t

Spreadsheets are deductive dataflow systems, which program proof type systems can process. Instead of proof, would it be possible to process a set of spreadsheets and rank them as more and less maintainable, and more or less likely to contain bugs? And, might these metrics, or others, increase incentive to examine and perhaps improve the lowest rated spreadsheets?

{The users most inclined to produce erroneous spreadsheets are typically the least inclined to believe they can make errors, and most likely to resist initiatives like the above, which they would see as yet more interference from the IT department. Such users are often driven to work around those "£$%&!! IT nerds" and want to "get some work done at last, ferchrissakes, how hard can it be? I just want something simple," etc.}

Many end user program tools of this type exist and have been tried for years. First it was 4GL 's and declarative languages (even SQL was supposed to be an end user tool), SPSS, SAS, RAMIS, Mathcad god knows how many decision support tools, BI tools etc. End User programing is still programing. This will not change. If you let them do throw away programs little harm will be done. If you let them write 'programs' and then the toss support to IT, I, for one will leave IT. I will just become an end user and toss my garbage over the wall like everyone else. This is not a technical problem and thus has no technical solutions. Users will need to be trained as programmers -- nothing else will do.


{The push toward end user computing, spreadsheets in particular, is because from the user's point of view all the IT department ever does is find various ways to say "no". Part of this is due to a common attitude amongst IT departments that they exist in opposition to users ("bloody lusers", etc.) rather than to serve them. Part of this is due to resistance among many developers to extending and improving the state-of-the-art. That includes extending and improving their own education. In short, if the IT department could produce useful and validated results as quickly as a user with his or her spreadsheet tools, there would be no issue with end-user computing, because there would be no need for end-user computing.}

I used to be able to bang out smallish apps pretty quickly in ExBase, embarrassing the BigIron shops. However, what often happens is that unanticipated needs start to pop up as the app and information become used more. Some higher-up comes along and says, "can you add this and that?" and one is obligated because of their rank. It turns into a bigger project and there is only one of you to go around. Although production spreadsheets often have a lot of duplication and fudgey stuff, spreadsheets are easier to change on a dime. Add a new column? okay: bam! If it causes side-effects, the user will work it out on their own because it's their baby. They don't have to schedule a fix with IT.

Spreadsheets also allow more experimentation. If they go to IT for a semi-formal project, they have to get the specs about 85% right the first time, or else the project is delayed. Users often don't really know what they want, and spreadsheets allow them to test ideas in an organic way. IT can then go in a turn the successful and road-tested spreadsheet ideas into something more formal as the volume and users increase.

However, I agree there is a need for something that is half spreadsheet and half database (SpreadsheetDatabase). I've yet to see such a contraption. It would be something akin to DynamicRelational.


I have had experiences where I was asked to explain why the results from a user spreadsheet did not match the results from our system. What was interesting was the presumption that the system was wrong, but the spreadsheet was right. It wasn't-- But could you prove it? And If you could how? I do not object to end-user programing. I object to being held responsible for it. Many users now make their own web sites and blogs and publish thereon. I have seen IT asked to prevent the release of sensitive information via these channels. Do you suppose that users will take kindly to this? Do you think that IT is just making up rules about this or is it perhaps corporate council that may have an issue with this kind of thing? I have no objection if they really want to retire the IT staff and hire a Webmaster from the local high school, I just do not want to be holding the bad when they do. I am also curious as to why IT is the only 'profession' which is presumed to be incompetent, and yet is not replaced. I suggest that it is better to be replaced than scapegoated.


{IT is presumed to be incompetent because its doings are mysterious and largely conducted by unattractive people whose habits and interests are nerdy and unpleasant, and whose priorities are clearly all wrong.}
"Inventions have long since reached their limit, and I see no hope for further development." -- Julius Frontinus (Highly respected engineer in Rome, 1st century A.D.)

Brainstorming sometimes just leads to storms. --EdwinEarlRoss

Or we see the problem as having a different origin. It is not the lack of tools that impede our progress. It is a lack of clarity of thought-- I should think that the task of getting a clear statement of business need has proven difficult enough. I am not prepared to assume that new ways to express poorly conceived ideas will help advance the state of the art. Of course, your experiences may certainly differ, and perhaps your users would benefit from new tools -- perhaps they are already able to clearly render business need in written form. If that is that case you are miles and miles ahead of any place I have worked, or even heard of. If so great, I am not so fortunate.

Category: ProtoPattern

View edit of August 27, 2010 or FindPage with title or text search