Roadblocks on the Information Highway

By Charles E. Gardner

SECTION THREE:
Understanding the tools users need

The users pushing IM departments to buy spreadsheet software in the early 1980s were the micro-computer programers of the late 1970s who immediately saw spreadsheets as the perfect problem solving tool for the end-user. It probably would have taken IM another five years to come to the same conclusion by themselves because even today they resist the concept of using the computer as a tool to independently empower the end-user.

The Spreadsheet Revolution

What's software?

Although spreadsheets such as VisiCalc had been in use on Apple IIs and Radio Shack microcomputers for several years, the introduction and mass marketing of Lotus 1-2-3 in the early 1980s sparked a sudden revolt in the workplace that IM departments were totally unprepared for. At that time most people were unfamiliar with the term "software" because they did not make a distinction between the programing and computer that ran it. If they used a computer at work at all it was most likely an OIS word processor or a turn-key accounting package on a mainframe run by the IM department.

Until the introduction of Lotus 1-2-3 and IBM PC the entire history of corporate computing had consisted of IM installing turn-key systems and saying, "Here use this, it's exactly what you need.". Most of the time it wasn't, but when the users asked for changes IM couldn't or wouldn't make them.

Lotus 1-2-3 was the first mass-marketed end-user software. Its TV ads made many computer users realize for the first time they had a choice of what they ran on their terminals and they asked for it. IM managers responded by circling the wagons.

If it wasn't for Lotus 1-2-3 you'd still be using the mainframe

Spreadsheets exposed the first crack in the IM armor. It proved impossible to run spreadsheet programs on a mini-computer, so PCs slowly started to appear in offices, usually next to the mini-computer terminals. In the early days of PCs it would not be uncommon to find a financial manager modeling a budget on a PC using Lotus 1-2-3, then re-keying the totals on the mainframe terminal next to it to produce the "official" report.

Two classes of Lotus 1-2-3 spreadsheet users evolved. The first never got beyond basic arithmetic, and they would continue to be led like sheep by IM bureaucracies who had learned that controlling PC software purchases was almost as effective as keeping users on a copper leash. IM would decide on a package, usually the least expensive, define it as the corporate standard and tell the users, "Here use this, it's exactly what you need."

The second class of spreadsheet users soon earned the appellation "power user" for their ever increasing appetite for more powerful PCs to run the sophisticated spreadsheet applications they created for their offices. Many of these power users were people who had learned to program in BASIC or other microcomputer languages as a hobby. They quickly learned to use the programming tools that were part of the spreadsheet programs. The men and women of the computer Renaissance who had languished for several years in the OIS Dark Ages finally had, in the PC and Lotus 1-2-3, the tools need to address the redundancy and inefficiency they had first seen and had started to attack with their microcomputers back in the 1970s.

Why the spreadsheet is an effective problem solving tool for managers

Eliminating Redundancy with Look-ups

By the time spreadsheets came into common usage PCs were equipped with enough memory to allow the power-users to create large, complex budget and inventory spreadsheets with hundreds of line items. They quickly learned the utility of the @LOOKUP function, which uses a value entered into a cell, such as an inventory number, to copy the description, price, and other information from a table in the spreadsheet. This simple device saved many hours of redundant typing and improved accuracy.

Finding Information With Sorts

The creators of Lotus 1-2-3 had not lost sight of the fact that people needed to process data. Spreadsheet users exploring the DATA command menu soon discovered they could sort the information on spreadsheets and change its order to suit different needs. Before conducting a physical inventory they could sort the list by storage location, then resort it by inventory for the report. This ability to sort might seem trivial today, but one must consider that up to that point the processing of information--that is to say how people used it to get work done--was largely dictated by the reports produced by the mainframe computers. In nearly every case a report would be sorted only one way; usually in the order least convenient for person who used it the most. In 1980, getting a mainframe-produced report in a new sort order was nearly impossible because it was likely produced using a turn-key program developed by an IM contractor. Even today getting something changed on a mini-computer or mainframe application to suit an end-user's needs is still a considerable bureaucratic undertaking.

The birth of expert systems and management by exception

The ability to sort information gave most PC users their first real understanding of the database concept. By using sorts they could bring different information to the top of a spreadsheet, depending on what they happened to be looking for at that particular moment. Sorting allowed them to focus on the information which needed their attention. This practice of "management by exception" was enhanced by incorporating status codes and "flags" into spreadsheets with simple logical formulas such as: @if(BALANCE ²MINIMUM,"REORDER,"-OK- "). This formula would display the word "REORDER" whenever the balance reached the minimum inventory level. A series of embedded "@if(...@if(...))" statements could be combined to produce an in-depth analysis of the data on the spreadsheet or perform a complex decision making process.

Other "DATA" commands allow users to extract the specific information they needed rather than just sorting it to the top of the page. Using the "FIND" function it is possible to extract a listing of records meeting a specific critera.

Lotus has a programming feature with commands similar to BASIC. By using the programing feature of Lotus, power-users were able to create automated routines to extract the information needed to make decisions--such as what to inventory items to order-- with a single keystroke. Macros allowed power users to create complicated spreadsheets which could be used by others. Any user could then open the spreadsheet, enter data, punch a couple of keys and a produce reports and immediately extract information to solve problems.

Managers who understood Lotus could create spreadsheets with logical formulas which automated many of their judgment and decision making process creating some of the first "expert" systems used in government and business. Now, instead of waiting for a manager to pour over an inventory printout line by line to determine what, when, and how much to order that decision making logic could be imbedded in the spreadsheet. Now a clerk or line worker could be delegated the task of taking action whenever a flag such as "REORDER" appeared on the spreadsheet when the inventory was adjusted. A cleverly designed inventory spreadsheet could adjust the order quantity based on shelf life, order cycle, and storage cost and round the quantity to be ordered by the number of items per package and compute the total cost of the order.

Incorporating their decision making process into expert spreadsheets allowed them to delegate it to lower levels. More significantly it totally eliminated the biggest roadblock in the traditional automation / application development process. Typically any corporate / government automation project would start with an end-user who had no idea of what a computer was capable of trying to explain to a computer specialist who knew nothing about the process what the application should do.

How do spreadsheets differ from databases?

A database stores its records on disk or other storage media and brings them into memory only as needed, while a spreadsheet hold the entire file all the records (rows of cells) into the computer's volitile memory chips.

Databases pre-date spreadsheets.In the first computer databases the data was stored on punched cards and the "computer" was simply a device which collate them in bins according to a programmed criteria. But this database model has remained valid as databases hove grown to include millions and billions of records stored on rolls of magnetic tape and disks.

The size of the spreadsheet is limited by the amount of memory in the computer. Back in 1960 a mainframe computer with 16,000 characters of memory was considered a super computer. Today PCs usually have between 2 to 20 million characters of active memory and can handle spreadsheets with thousands of records.

On a PC there is little functional difference between a database and a spreadsheet. Both can be used to calculate, sort, and extract data according to user-defined criteria. Both can be designed with look-up functions which use key fields (e.g., employee number) to retrieve other data (name, address, etc.) Both use mathematical and logicalformulas and recalculate automatically when a data value is changed.

In the PC LAN or other network environment the difference between a spreadsheets and a databases is very significant. Because the entire data set of a spreadsheet resides in the memory of the computer, only one person can work on it still resides on disk. Saving the file overwrites the old data. If the file is stored on a fileserver and two people opened it into memory, change it, then save it, the changes made by the last person to save will overwrite the changes made by the first. Many PC users accustomed to using spreadsheet on stand-alone PCs learned about this characteristic the hard way when their offices switch to LANs and filesrvers to improve efficiency. In the early days of LANs it was common to hear things like, "Hey Fred, will you close the inventory spreadsheet on the fileserver so I can open it to enter my transactions?"

Databases have a protocol which locks a record on disk when it is being modified. Only the single record a person is using, instead of the entire file is in memory at any point so there are fewer "ownership" contention problems. Many people can view the same record at the same time. If two people try to change the same record at the same time the second person will be notifed that the someone else is modifying it.

Which is better?

The tool of choice depends on the task at hand. If you have enough memory and your reporting requirements are simple, a spreadsheet application is easier to create than a database; you simply type the column names, enter the data and formulas, then print. If the application involves crunching numbers spreadsheets do the job much faster than databases because all the data is already in the computer's memory. The biggest drawback with spreadsheets is that a user can easily enter a number over a mathematical or logical formula, erasing it forever. Erasing a formula will result in an erroneous totals and massive embarrassment when the boss checks your complicated spreadsheet on his calculator and spots an simple addition mistake. This problem is aggravated when several people use the same spreadsheet. Spreadsheets can be formatted to lock cells and protect them, but if several different people will use an application to share information it usually pays in the long run to use a database instead of a spreadsheet. A database automatically protects the layout formatting and the calculation fields.

Users discover the database and rub the magic lamp

A database is any collection of "records." It can be on a computer, in a file cabinet, or in the box of 4 x 5 cards in the kitchen that holds your recipes. What makes it a database is that all of the records are similar in nature. Once PC users finally realize this and start developing their own effective databases problem solving Genie will be out of the bottle.

Some people have been using databases for years on mainframe computers but never have realized it because the process isn't interactive. They enter data into a terminal and receive a report days later. Most decision makers just get a report and don't have a clue how or where the data is stored. Every decision maker (i.e., someone who reads a report, makes changes on it in pencil, and then gives it to someone else to change on the computer) should have on-line interactive access to the database which created the report. Even if they don't make the corrections themselves, it is easier and faster to find the information that needs correcting by using various search criteria. Often, a simple change in procedure brought about by improved access to information can result in a large gains in efficiency and productivity; people spot problems sooner and can correct them immediately when they are found.

If a database is on a mainframe the complexity of its database query language probably prevents most users from utilizing it effectively. The most powerful feature of a PC database is scripting, which allows a user to record a complex series of finds and sorts and reuse it again and again. With Mac and Windows databases it is possible to link the saved script to a button on the screen. A user who knows little about computers and less about databases can now use them effectively. It is now possible for such a person to find all the accounts with balances less than $5,000 with a click a button. People who use well designed shared databases are more productive and make more decisions themselves than ever before.

How do you create a database?

A computer database consists of records. Each record will contains a number of data "fields" (e.g. description, unit cost, etcÉ) to store numbers; text; calculations; and nowadays even audio, photos and video clips. The creator of a database first defines the attributes of each field, then arranges the fields into one or more "layouts." While creating the layouts the creator formats of the fields (e.g., currency signs, decimal points, font, size, justification, pull-down menu or check boxes, etc.). A database may have one, or as many as 50 different layouts depending on user needs. Each layout can display only those fields that are need to accomplish the task it was designed to facilitate (e.g. specific tasks in various departments, detail and summary reports). By tailoring a layouts to the task the designer can make the application easier to use. If a shipping clerk only sees the fields need a input shipping data there is less chance he will put in in the wrong place or erase someone else's entry by mistake. Some databases only contain text (e.g., a database created to do faxes) but most are a combination of text and calculations. Calculations may be mathematical (2*3) or logical (if(BALANCE < MINIMUM,"REORDER,"-OK- ").

The possibilities for designing databases are limited only by the creativity of the designer and their understand of the problem they wish to solve. End-users can solve a multitude of information bottlenecks in a very short time when given an easy to use Windows / Mac GUI database development tool. As the user gains a better understanding of how the various commands and features work, the applications they create quickly become increasingly sophisticated. Just learning one new command can sometimes unlock a whole new way of looking at a problem which can result in an effective new solution. Even a relatively simple Windows / Mac database is more powerful than one a mainframe or mini. The power is not in its speed, but its flexibility and responsiveness to the changing needs of its users.

How does a database function?

Database records are stored on disk and called into the computers' memory only when the users asks to see them. Two or more users may view a record at the same time without risk of conflicting alterations. Multi-user database programs use a record or field locking protocol which prevents the second and subsequent viewers of a record from making changes while the first user (the temporary "owner" ) has it open. Database size is usually limited only by the capacity of the disk drives to hold it. Totaling a database requires reading every record from disk to memory; a process that can take from several hours with a large database with many calculations.

A complex database is usually many smaller databases and lists, linked together to share information. If information will be reused often (e.g. data about an employee) a database designer will either tag it with a code (e.g. entering an employee number will retrieve all the other information about an employee) or put it in a short list the user can pick from when entering data (e.g. department names). Allowing a user to pick from a list instead of typing the entry reduces redundancy and entry errors. Forcing the user to pick from a list limits choices and can control their action (e.g. a person can only order items approved for their department). Formatting can also ensure that data is entered in the correct sequence for sorts and searches. For instance, if some people enter "country, city," others "city, country," or just "city," or just "country" it will be impossible to process (search and sort) the data later. To avoid this problem the database designer will either make "country"and "city" separate fields.

A well designed automation system allows people in different departments to use the keystrokes entered by others instead of retyping the same information redundantly. Instead of typing vendor name and address it is possible to make a separate database of vendor information which is "imported" or looked-up when the vendor's code is entered. But, what if the vendor's address changes? Relational and non-relational databases

A relational database allows automatic updating of imported information. If the vendor address on an order is altered after being imported from the vendor file, a relational database will ask the user if the vendor information should also be changed in the vendor file also. If so, it will be updated automatically by the computer. A non-relational database would require the vendor file to be changed manually as a separate operation. Automatic relational updating is an important feature on mainframe and mini-computer databases which can only run one database at a time, but with today's Windows and Mac databases both the order and vendor files could be opened simultaneously on the user's screen in separate windows, making it relatively easy to correct the vendor file by typing or copying and pasting the change.

Designing a relational database requires careful planning and design. Its complicated structure takes longer to build and requires a higher skill level. The person creating it likely will not work in your office and understand the problem first hand, and they won't be around when you need a change made six months later. The result of an agency-wide, three-year-long, mainframe relational database project, designed by an outside contractor and administered by IM, is often a masterpiece of efficient and foolproof technology that does not meet the needs of the users, or respond as those needs change. Power-users--people now working in the offices--can build database applications separately (often by converting existing spreadsheets) and then link them together later. User development makes "mid-course" simple.

Non-relational database programs are about as simple as a spreadsheet to learn and develop applications with. It is so simple to use that soon most people in the office will soon be doing their own databases to balance their checkbooks, inventory their belongings, or organized their CD's and recipes (like they did with spreadsheets 10 years ago). That should be encouraged, since it is much easier to learn technology when you first see a tangible problem that you can solve with it. By the time people run out of "personal" things to do with a personal computer (the above list, plus writing a letter to Mom covers just about everything you need a "personal" computer for) they will turn their attention to problems in the office. Because they have learned a "new" solution, the shared database, they will see "new" problems they never saw before and attack them with vigor and enthusiasm.

The extra cost and work involved in using and developing a complex relational database may not be necessary. But, that is often the only kind IM departments are are familiar with. Which is best? It depends on the size and complexity of the application and the need for flexibility in the future. The author's advice is try to solve a problem yourself first with a simple inexpensive PC database before running to the IM department for help. Even if the result only meets 80% of your needs you will have worked 100% of the problems by the time you are finished. The biggest commitment of time is figuring out how to solve the problem. There will always be blind alleys and wrong turns turning development and implementation. It is much better to have the flexibility do make mid-course corrections yourself with a $300 PC database package than paying a contractor $25,000 for modifications on an application that was originally budgeted to cost $20,000. Having a small-scale working model is valuable for soliciting input from the users. More than once the author has designed a complete system and towards the end of the process discovered a much better way to collect or distribute data based on a user's suggestion. You can't afford that if you use contract solutions. If you do it yourself the worst you lose is a couple of days of your time. At best you can save the $55,000 it would have cost for the contract solution. Of course this approach won't work if the system in an air traffic control system or the like, but its great for inventory, accounting, ordering office supplies, and 101 other administrative tasks that an organization must do to keep its wheels turning. For example, the author created a shared database to for leave slips because he could never find the blank forms. Now all the managers can view the leave record of any employee in seconds.

What to do if you've never created a spreadsheet or a database

Go see a power-user; and bring the IM manager with you. Bring a problem you need solved and the all the current reports and forms used in the process. Its easier to show someone how a database works than it is to explain it, and its about time the IM manager learned how to design databases to solve problems on a small scale.
[- next section - ] [- table of contents -]