GISdevelopment.net ---> GITA 1997 ---> Lessons Learned

The database design: The key to success

Michael A. Nicolls
Analytical Surveys, Inc.
1935 Jamboree Dr., Suite 100, Colorado Springs, CO, 80920


Abstract
As the end-users of geographic information systems become more sophisticated and end-user sofhvare becomes more powerful, the data required to allow those systems to function becomes more difllcult, and therefore more expensive, to produce. Unfortunately, the amount of time and effort being put into producing the database design document, or data conversion specification, is not increasing in a similar fashion. This paper will address the production of this critical document in some detail, with its goal the minimizing of uncertainties and ambiguities on the part of both the end-user of the data and in particular on the part of the data conversion vendors chosen to receive the Request for Proposal (RFP), thus allowing for more accurate and meaningful responses to the RFP.

At a minimum, a quality database design document should accomplish the following three tasks:
  • Every data item needed by all end-user parties to the GIS should be accounted for in the document.
  • Detailed footnotes should be included, spelling out exactly what each item is to contain, and what the source / selection criteria will be for each feature.
  • The document should be complete and final before the RFP for data conversion is released to vendors.
Introduction
A thorough survey of the proceedings from each of the major geographic information system (GIS) user conferences (URISA, AM/FM, GIS / LIS, and ACSM / ASPRS) over the past five years reveals a number of papers and presentations on the related subjects of database designs, data conversion issues, requests for proposals, and data conversion contractor selection. It is interesting that virtually all of these papers are written by people who work for data conversion companies. This paper is no exception. All of these papers have pointed to the need for more thorough up front work by the end users (governments, utility companies, etc.) and the consultants hired by them in the analysis and design of the needs of the GIS users. Despite this and despite the fact that most GIS industry insiders recognize that the data conversion is the single most expensive and important part of the GIS implementation effort, with few exceptions, the Requests for Proposals coming into the offices of this writer’s company do not reflect nearly as much forethought and prior work as there should have been before the release of the RFP.

Trent Meyers (Meyers, 1994) has pointed out how many people in the GIS industry have experienced situations where bids have been solicited from qualified vendors, only to find that the bids will vary by several tens if not hundreds of thousands of dollars, leaving the entity seeking the data conversion services extremely frustrated and confused. There is a good reason why these situations occur. Usually, critically needed information is lacking in the documentation provided with the RFP, leaving the conversion vendors to take educated guesses about many of the undefined and unknown (to them) variables. In order to protect themselves against these unknowns and the almost inevitable increase in the scope of work that occurs in these kinds of projects - for which the RFP issuing entity rarely wants to pay - data conversion service companies are forced to build extra cost into their bid price responses to these RFP’s. If the RFP is pai-titularly bad, the data conversion company may even elect not to respond at all. As William Reid has pointed out (Reid, 1992), responding to an RFP costs a significant amount of time and money, often several thousand dollars, and these costs must also be passed on to the data conversion companies’ clients in the form of higher prices for services.

The Solution
Just what exactly is a “database design document” or “data conversion specification”? Nancy Lerner and Dave DiSera (Lerner & DiSera, 1996) define the document as “A combination of a physical database design, database quality and accuracy requirements, source document information, and requirements for the data capture or conversion process”. While most that are issued in the industry do one or two of the above mentioned tasks well, very few address all four issues to the extent necessary. What can be done about this situation in order to ultimately reduce both the data conversion companies, and therefore the requesters’ for services, costs? The following suggestions provide a starting point for discussion.
  1. Develop a source data matrix. Both Anthony Thorpe (Thorpe, 1995) and Andrew Hawkes (Hawkes, 1994) have described and discussed the value of a source matrix to the conversion requester. Briefly, creating a source matrix involves a thorough review of all sources to be used. These sources include aerial photography, paper maps and other documents, digital files of all types (e.g. text, database and graphic) or data types generated in software from other types. Every feature type and attribute required is entered into a column with both primary and, if appropriate, secondary sources defined. Usually just the process of doing this forces the end-user to make many significant decisions concerning the final product. The alternative is to have the selected data conversion company discover all of the anomalies and begin a continual bombardment to the customer of questions, requests and complaints about data items not covered in the database design, conflicts between differing data sources, etc.

  2. Give careful thought to the data required. In particular, several issues in data conversion can cost quite a bit more than the end-user might realize. Meyers (Meyers, 1995) has pointed out how exact replication of source documents, unreasonable accuracy requirements (especially given the source documents to be used), and additional attributes can all drive up the cost of a data conversion project significantly. It is critical for the consultant contracted by the data end-user to point out these issues to their client and steer them away from spending more than their applications will ultimately require. Not only is quality and the extra work required to ensure it not free, it’s not particularly cheap either.

  3. Discourage the release of preliminary /incomplete database design documents in RFP’s. If a database design is not complete, an RFP should not be issued. There is no more sure way to drive up the cost of a conversion effort than to continue to revise the database as the bidder selection process, contract negotiations and conversion process proceeds. Most responses to RFP’s by data conversion companies contain a lot of padding to account for the inevitable “scope creep” engendered by poor database design documents. The only way to avoid this extra cost is to convince those companies that respond that this will not occur by proving to them that all of these data issues have been thoroughly thought out.

  4. “Freeze” the database design except at critical milestones. Recognizing that even the best of efforts by the end-user and GIS consultant will miss items, and that as the project proceeds from prototype to pilot to fidl-scale conversion (Thorpe, 1995) changes will occur, allowance must be made for changes to the document. However, these must occur only at significant junctures during the data conversion process or much expensive rework will have to occur for which the conversion provider will want to charge. In other words, the database design should be unchangeable from the time the RFP is released until a conversion company is selected, again from when contract negotiations are completed until the prototype file is converted, a third time from beginning to end of the pilot phase of a project, and finally from the time the pilot is accepted until the project is complete. Any changes to the database during these “open” periods can be reasonable cause for additional contract amendments on the part of either party, and should be written into the original contract as such. The point is that with sufficient up front work on the part of everyone, changes will be kept to a minimum and should not be a basis for additional charges. It is even possible that the contract amount could go down should unneeded items be eliminated by the end-users.

  5. Allow the conversion companies sufficient time to respond to an RFP. As the end of a calendar year approaches, conversion companies anticipate with dread the flood of RFP’s that suddenly appear (often requiring an almost instant response) for projects that may often cost hundreds of thousands of dollars to produce. Realizing that our clients are usually constrained by budgets which are often not released until close to year-end, and that optimal flying times for many areas of the country occur within a matter of months after year-end, the solution to this dilemma can be difficult. However, since the development of a quality data conversion specification should require at a minimum three months time (Lerner and DiSera, 1996), it seems reasonable to allow at least half that time for responses to be received by data conversion vendors. Six to eight weeks will allow plenty of time for the next two points listed below to be fully realized.

  6. A11owtime for questions from conversion providers to formally become part of an amended RFP Each interested company will make different assumptions about exactly what the requester wants, and these written questions will prove to be a very usefi.d learning exercise to the end-user party. In addition, these questions and the accompanying answers keep all parties using the same knowledge base in discussions. As with the Request for Information (RFI) advocated by Reid (Reid, 1992), these questions will ultimately allow for more accurate price quotes once the project is better understood.

  7. Allow bidders access and time to inspect all source documents. As with the general questions about the RFP and accompanying database design document, this exercise helps everyone understand the assumptions being made by the other parties to the GIS implementation. By this time, of course, the end-users should be thoroughly familiar with their source documents. The conversion companies, however, are not, and can usually provide valuable insight into numerous issues via their source inspections. Samples of these sources can even be included in the RFP if not cost-prohibitive, and are ~ways appreciated. It is particularly important to see all digital sources that need to be integrated into the final database in order to check for completeness, accuracy and formatting.

  8. Retain the GIS consultant until a data provider is selected and all conversion issues are resolved. Ideally, the GIS consultants’ and data conversion companies’ periods of time spent working on a GIS implementation should overlap by several months. Often, however, this is not the case as the consultant is paid in full once the RFP is issued or the conversion company is selected. This leaves the end-user to deal with all the issues detailed above alone, usually when they are already overworked. By retaining the consultant until the pilot is complete and full production is underway, the end-user has someone to turn to not only when complicated issues arise, but in case it becomes obvious that the consultant has not performed their job properly in some way in the education of their client.

  9. Include detailed footnotes in the database design specifying sources and selection criteria. Table 1 spells out many of the issues faced by data conversion vendors for just one theme commonly found in most planimetric mapping GIS implementations. One would think hydrography would be one of the easier themes to understand. However, as the table shows, numerous issues arise concerning definitions, digitizing procedures, minimum and maximum dimensions for data to be captured, etc. This information is extremely important not only once data capture is set to begin, but at the very beginning stages of a GIS project when both end-users and data conversion providers are trying to figure out exactly what is needed. The ultimate conversion cost can be affected dramatically by the precise definitions given in the database design. Take as an example the way in which the hydrography theme could be affected in a state such as Florida. If the end-user only needs to know where canals of greater than 20 feet in width or swamps larger than 200 acres are located, this detail should be present in the RFP. Otherwise the data conversion companies will assume all of these features are required and the bid price will be adjusted upward accordingly.

Conclusions
The process of designing and writing a database design documen~ or data conversion specification, is a time consuming but extremely important procedure in the overall scheme of a GIS implementation. Any corners cut during this process will undoubtedly make themselves felt later as the issues ignored early on come back to haunt the end-user of the GIS. Working together with both the GIS consultant and the data conversion company selected, most of the pitfalls known through years of experience can be avoided. Since most end-user clients are new to the GIS community, the primary responsibility for their education and for the avoidance of major problems lies with the selected consultant. Once a conversion company is selected, they too must share an equal role in taking responsibility for the overall success of a project. However, there must be a solid commitment on the part of the end-user to put in all of the work necessary in order to omit as much of the uncertainty and ambiguity about the stated needs and goals of the GIS. Data conversion companies and GIS consultants do an excellent job when they know exactly what is required of them. With all parties working together, the end result will always be a positive one.

References
  1. Hawkes, A.G.D., 1994, “The Conversion Specification: Foundation for Success”, JYocee-Conf_ e XVII, pp. 662-667.
  2. Lerner, N. and DiSera, D., 1996, “Cost Effective Database Design: What Every Manager Should Know”, GIS/J .1S ’96: Annual conference and Exposition Proceedings . . , pp. 1034-1049.
  3. Meyers, T.F.., 1994, “Managing Data Conversion Costs”, mceedings: AM/FM , pp. 726-735.
  4. Reid, W.B., 1992, “How to Select the Best Conversion Contractor for Your Project”, m Proceeclings. VOhundI , pp.129-138.
  5. Thorpe, A., 1995, “Keys to Successful Water Conversion”, lJRISA Proceeding. Vol . d, pp.322-
© GISdevelopment.net. All rights reserved.