Strategic Planning for Data Warehousing in the Public Sector

Proceedings of the 29th Annual Hawaii International Conference on System Sciences - 1996

Strategic Planning for Data Warehousing in the Public Sector Ashok Subramanian, L. Douglas Smith, Anthony C. Nelson, James F. Campbell, and David A. Bird School of Business Administration Universify of Missow, St Louis St. Louis, Missouri, U.SA.

advantage of changes in technology. Even more imposing, however, is the challenge of maintaining and sharing data when managerial responsibilities are, by City charter, extremely diffuse. Early in the course of the strategic planning exercise, the concept of a data warehouse emerged as a potentially useful strategy for consolidating and sharing information among the many City departments with different informational needs and various computing platforms. Data warehousing has been proposed as an consolidating corporate effective method of information and sharing it among organizational entities for analytical purposes and decision support [l, 2, 3, 4, 51. Much of the published literature on this topic, however, has focused on the technology required to establish and support data warehouses[6, 7, 87. Less attention has been given to the analytical processes used for determining when a data warehouse would be effective, for gauging potential demand in the user community, for deciding on the software and data structures to use, and for identifying the data elements that should be maintained in various forms 12, 9, 10, 111. In this paper, we describe planning processesand analytical methods that proved useful for shaping a data warehousing strategy and for determining the contents of the data warehouse.

Abstract The City of St Louis, Missouri is a large municipality with an extremely fragmented organizational structure. The City recently undertook a major strategic planning study for development of its municipal information systems. In the course of the study, data warehousing emerged as a strategyfor consolidating and sharing information among many City departments with different informational needs and a variety of computing platforms. In this paper we describe processes and analytical methods that proved use&l for shaping a data warehousing strategy and for determining the contents of the data warehouse. We also present some cautionary conclusions about the developmentof monolithic data warehousesfor decision support.

Introduction The municipal information systems of the City of St. Louis serve over 150 departments or subdivisions of City government, several municipal utilities, and numerous external constituencieswith widely varying needs. The City’s organization is fragmented, with managerial responsibilities distributed among elected officials and senior civil servants. Some of the departmentsgeneraterevenue from their operations or receive grants from external agencies that provide discretionary funds for MIS services and computer technology; others are service departments that rely upon financial allocations from the Board of Estimate and Apportionment. Some departments engage in their own systems development and support; others are reliant on central MIS services. Strategic planning for MIS in such an environment is an extremely difficult exercise, requiring the voluntary cooperation of elected officials, departmental managers, technical staff, systems analysts and endusers with very heterogeneous interests and perspectives. Like many organizations, the City faces the classical problem of economically converting “legacy systems”that are several decadesold to take

1060-3425/96 $5.00 0 1996 IEEE

The Data Warehouse Data exist in organizations in two forms: (1) current operational data about individual entities or transactions, and (2) frozen extracts that show the status of selected entities at chosen points in time, or that consolidate and summarize information about changes in states through time. The former we shall call “current”; the latter we shall call “frozen”. Other authors [l, 51, characterizing dam on a related dimension, use the term “operational” for data that support business transactions and “summarized”for data that are intended for end-user analysis and decision support. Operational databasesare often of such a size and structure that they are inefficient for 54

Proceedings of the 1996 Hawaii International Conference on System Sciences (HICSS-29) 1060-3425/96 $10.00 © 1996 IEEE


analytical functions. Data warehouses have been describedas subject-oriented,integrated,time variant, non-volatile sets of data in support of management’s decision making process [5]. They are now widely regardedas useful &vices for maintaining information from multiple sources and delivering it to managers and analysts who may use a variety of computer platforms or software with special features and capabilities [4]. Data warehousesmay, for example, be composed of hundreds of tables in a relational databaseconstructed to support typical queries [ll]. They contain data about high-level business entities of an organization. In the case of the city government, they would include data describing parcels of land, personal property, voters, permits, licenses, etc. The creation of a data warehouse,particularly on a corporate-wide scale, is a major undertaking. It involves the development or acquisition of tools for user access,databasemaintenance, and data transfer and scrubbing @adding [4]). Tools may be especially designed for that purpose, or may be crafted from general-purpose software such as statistical packages, relational database systems, or spreadsheetsoftware Becausethe development of a full-scale data warehousewith supporting middleware and analytical tools can cost upward of one million dollars, care must be taken to determine the likely nature of its ultimate use. Before dwelling on the detailed aspects of the warehouse design, it is necessaryto analyze the types of information required for effective managementof operations,organizational control, and strategic planning. It is also necessaryto assessthe talents and mindsets of potential users. In other words, only after a comprehensive strategic analysis, should attention be focused on the supporting technology and the mechanics of maintaining the warehouse. In the sections to follow, we describe such a strategic analysis and the resulting implications for data warehousing.

information gathering process. We askedmembers of the oversight committee to identify major problems to be addressed, opportunities to be considered, and obstacles to be overcome in the implementation of a comprehensive plan for municipal information systems. As a result of this exercise, fifty-one problems, twenty-one opportunities, and seven obstacles were identified. Grouping the issues into thematic categories,we produced seven categoriesof problems, five categories of opportunities, and six categories of obstacles. Median rank values were computed for each thematic category as a means of representing the group’s consensus. According to the consolidated rankings, the primary problem facing the city government was considered to be the existence of islands of incompatible (or unconnected) technologies and computer systems. As a result, city departments are unable to consolidate data, and to share data in a timely manner. Logically, the integration of these incompatible individual information systems was ranked highest in the lit of opportunities. The corresponding remedies seemed to be in the development of a city-wide policy with respect to the sharing of information and in providing a “seamless” connection among the various repositories of data. To overcome obstacles to the successful implementation of a comprehensivestrategic plan for municipal information systems, members of the oversight committee stressed the importance of encouraging a philosophy that treats information as a city-wide resource, the sharing of which should be restricted only by legislated mandate or generally acceptedneeds for confidentiality. Thus, at the end of our nominal group exercise, it was evident that there was a widely recognized need for integrating diverse systems with the primary objective of sharing cross-functional data. Data warehousing seemedto be a natural fit to the organizational setting and its related problems. Although the planning committee pointed to the need for interdepartmental sharing of data, it was not clear how this should be accomplished For example, it could be accomplished by (1) sharing files on an interdepartmental file server, (2) providing accessto a large organizational data base, or (3) providing access to data warehouses or “data marts”. To address this and other strategic issues, a structured open-ended MIS survey was designed and administeredwithin eachmajor departmentand within each major departmental subdivision (143 surveys in all). Background information was requestedregarding the size of the department,historical expendituresfor

Preliminary Requirements Analysis A Strategic Planning Oversight Committee was formed with representation from thirty-five major (umbrella) departmentsof the City. The first activity in the project was a nominal group exercise involving members of the Strategic Planning Oversight Committee. The purpose of the exercise was to achieve consensus on the critical issues for the development of an effective MIS plan, to obtain a city-wide perspective on MIS needs and to set the stage for cooperation of the various parties in the

55



The Information

computing technology, the department’s mission and goals, departmental functions and the computing technology used to support the departmentalfunctions. Most important, for each of the department’s functions the general types of information generated by or used by the department were identified. Then, for each of the information types, the flows between the department and external entities were described, and the currency of the information was designated. A set of general questions were then addressed regarding special requirements for privacy or security of data, current problems with information processing, opportunities for improving the ways in which information is acquired or processed,possible changes in the organization of support for management information systems activities and delivery of computing services, and possible changes in the City’s processes for planning and budgeting improvements in municipal information systems and supporting technology. Each department was asked to recommended a set of MIS initiatives or projects that would best improve its productivity. A complementary technology usage survey was administered to a representative of each employee category in each department of the City (158 surveys representing 2700 employees). In this survey, we inquired about the hardware and software available and the uses to which they are put. We probed further to determine the level of end-user computing expertise and the ability to develop and maintain applications within the department. Functional profiles for each major department were prepared from the responsesto the MIS surveys. The profiles include the following: 1. the mission and functions of the department and its subdivisions. 2. the types of information used or generatedby the department. 3. the associatedcomputing platform or storage medium and the functions supported by the information. 4. the transmission of information to or from other departments or external organizations. 5. the currency of the various types of information (e.g., daily or weekly extract or perhaps a summary for a given time interval), 6. the list of recommended MIS projects or initiatives. A sample extract showing the mission and functions of a division of the personnel department is provided in Table 1. In Table 2, we provide an example of the type of information used in that division.

Usage Matrix

From the information-usage sections of the departmental profiles, we created an information usagematrix to depict how various departments share general types of information in which they have a common interest. In preparing the matrix, we created broader categoriesfor the information types to enable a manageableconsolidation of data for the thirty-five major departments. We also ignored information that was mentioned by only one department or division, as it would not be included in the warehouse. An illustrative excerpt from the information usagematrix is provided in Table 3. Entries in the matrix contain three groupings of information. The fast group refers to the type of system used to furnish the information. The second group pertams to the manner in which the information is used by the department. The third group distinguishes whether absolutely current data (as in an on-line master file or hard-copy archive) am used or whether “frozen” extracts or summaries are used. The following codes are used for the respective groups. Delivery

System

H = M=

hard copy main frame computer (IBM 438) IBM mid-frame computer (e.g., AS/400) local server (e.g., a file server) stand-alone personal computer on-line service from an external source (e.g., Internet)

m

=

S P 0

= = =

Use of the Information creating elements of c =

U

=

R

=

data in the information category that are added to a database updating elements of data in the information category reading information for inquiries, reports or analysis

Currency Status = current

C

f = frozen. Multiple designations are possible in each category. For example, MS:UR:cf would apply if a type of information were maintained (updated) by a department on the mainframe, and downloaded periodically to a local server for analytical purposes. In examining the information usage in aggregate, we observed a considerable need for sharing both

56


Proceedings of the 29th Annual Hawaii International Conference on System Sciences -

1996

Table 1 - Sample Functional Profile

Department Name: Department of Personnel Area: Compensation

and Employee Relations

622-3565

Ronald Marshall, Manager Mission: to administer the following city-wide programs: Safety Program Appeal Program Drug and Alcohol Testing Program. Functions:

1) administer safety program, report injuries, gather safety data and accident/injury data 2) handle appeals and compile grievance reports 3) administer drug and alcohol program and compile related reports.

Table 2 - Sample Information Information

Types Functional

Entities Used

Narrative

The safety program information is used b administer safety programs, prepare accident-injury reports and lost - time salary expense reports.

Safety Program

Location: Minicomputer, PC Update needs: Monthly From: All departments To: Concerned department

The appeal program information is used to track grievances and prepare grievance reports, monthly examination reports and service rating reports.

Appeal Program

Location: PC Update needs: Monthly From: All departments To: Internal

The dmg and alcohol report is used to track positive and negative drug alcohol tests and prepare related reports.

Drug and Alcohol Report

Location: PC Update needs: Monthly From: Healthline Corporate Health services To: Not specified

57


Proceedings of the 29th Annual Hawaii International Conference on System Sciences -

% b w

8

3-o 3$

O8-3. nr

58


1996

Table 4 - Format for Summarizing

Characteristics

Project Description and anticipatedbenefits

Technology Investment f&main frame PC= PC or workstation Sserver &communications

(El = estimatedimpact) (l=low, 5=high)

MF

Developa new dispatchingsystem that displays availableequipment,recommendsequipmentto dispatch,and links to hazardousmater’tls databaseand incident reportingsystem. This will allow better equipmentmanagement(most beneficialduring major incidents). A link to hazardousmaterialsdatabasecan improve safety.A link to incident reportingsystem improvesaccuracyand speed of incident reporting. (DPS, Fire Dept., EI=4) introducethe automationof docket informationfc udgesto eliminatea manual process (City Courts,Docket& Preparation,EN) k Providefor automatedroutingfor pickupsto be accessedby foreman. (Street - Refuse Dtvrsron,


of Proposed Projects

X

C

Systems Development MF=mainframe PC= PC or workstation Sserver &communications s S C

MF

PC z 7=

Software f&main frame PC= PC or workstation Sserver &communications

MF

Other Effort

Proceedings of the 29th Annual Hawaii International

frozen form. On further investigation, these projects were concentrated in twenty-three departments. Within that group, there was further concentration. Two thirds of the projects expected to be supported by the data warehouse emanated from six departments. Four of those departments (streets, water department, public safety, and community development) were involved in the construction of geographic information systems for their individual analytical purposes. In the course of their analysis, they would produce information from their sphere of operations that would be relevant to some other department and which would therefore be a logical addition to the data warehouse. The maintenance of the data would therefore have to be modeled along network lines, rather than in a strict hierarchy. This has both organizational and technical ramifications in the maintenance of a data warehouse. We suspect that similarly nonuniform patterns of expected usage and nonhierarchical generation and maintenance of information would occur in most organizations.

current data and frozen extracts among departments. Further analysis of the functional profiles for the individual departments revealed that the needs for current data (other than for direct data entry or transaction processing) are primarily for routine crosschecking of information in connection with some transaction taking place. Analysis and periodic reporting relied more on frozen data. The need for complex integration of data from multiple sourceswas much more prevalent for frozen data than current data. Entities with the frozen (f) designation were therefore obvious candidates for subject-oriented extracts in the “data warehouse”.

Characteristics

Conference on System Sciences - 1996

of Proposed M IS Projects

There were 363 recommendations from departments or subdivisions for MIS projects or initiatives ranging from major computer upgrades and applications development efforts to minor connections Clustering the between existing systems. recommendations in a platform hierarchy from mainframe to server to stand-alone PC, we observed: 75 recommendations for projects requiring investments in technology, software, or systems development on the mainframe 111 recommendations for projects requiring investments in technology, software, or systems development on network servers 171 recommendations for projects requiring investments in technology, software, or systems development on desktop computers. In addition, there were 71 recommendations that dealt with the electronic communication of data or “other effort” such as data cleansing. This rough metric gives a strong indication of the fact that a considerable number of the City’s MIS initiatives will involve the mainframe computer; a greater number will involve network servers; an even greater number will involve stand-alone PCs. Table 4 illustrates how we summarized the projects for this phase of the analysis. For each of the project recommendations, we estimated (as low, medium, or high and indicated by one, two, or three check marks respectively) the incremental investment in technology, the systems development effort, and software investment required on each type of platform. We also indicated whether the corresponding data would be obtained: (1) in current form from a single database, (2) in current form from multiple databases, (3) in frozen form from a single database, or (4) in frozen form from multiple databases (including hard copy). The items in multiple databases in frozen form are prime candidates for the data warehouse. Eighty-nine of the 363 projects used data from multiple databasesin

Conclusion The development of analytical models and decision support systems in organizations is often hindered by the lack of data in a convenient and relevant form. Data warehousing is advocated as a remedy to this problem. We have presented an analytical framework for the determination of potential users, applications, and information entities for a data warehouse and described the insight we derived from applying it to municipal information systems in a large city. In the case of St. Louis City, we undertook a comprehensive strategic planning exercise and encouraged managers throughout the project to consider information in a value-added context and as a potential resource for analysis and decision support. The data warehouse was seen as a natural device to aid in the transition from a mainframe-centric to a network-centric computing topology. We would, however, caution against the development of a monolithic data warehouse that is supported by highly standardized desk-top tools throughout the organization. Much of the shared use of “current” data seemed to be for highly structured and well defined integmtion of information in conjunction with routine business decisions or service delivery. This use seemedable to be supported by a gateway to the mainframe or mid-frame computers from desktops through terminal emulation. These systems would be enhanced and maintained through centralized MIS services in the traditional manner. A surprising number of recommended MIS projects pertained to routine businessoperations and transaction processing, 60


Proceedings of the 29th Annual Hawaii International

rather than to the analytical work for which the warehouse is most suited. Integration of “faed” data from a variety of internal and external sources and sharing it among departments for analysis and decision-support is the primary goal of data warehousing. With the help of functional profiles and data usage matrix from a comprehensive strategic planning effort, we were able to determine that usage of the data warehouse will be far from uniform. We also determined the need for informational loops from departmentsback to the data warehouse as errors in the data are corrected and as complementary information is added by end-users during the course of their unstructured analysis. The potential use of data warehousing to facilitate the development and maintenance of decision support systems is reinforced by our study. We believe that the key to a successful data warehousing effort is to perform a strategic analysis along the lines presented in this paper. As users’information needs change, the information usage matrix and corresponding data model of the organization, must be updated and modified. Considering the high cost of data warehousing technology and the inherent risks of failure, we concur with recommendations that the implementation begin with the creation of small warehouses or data marts for prime users with considerable end-user computing experience [ 111. Much of the potential benefit from data warehousing may be realized from using the concept on a selective basis and modest scale.

Conference on System Sciences - 1996

6. Appleton, E., “The Right Server for Your Data Warehouse,” Datamation, Vol. 41, Number 5, March 15 1995, pp. 56-58. 7. Orr, K., “Data Warehouse Technology,” An

Information Builders White Paper, 1995, pp. 1-21. 8. ADS Data Warehousing Survey 1995, The Meta Group Inc., pp.l-12. 9. White, C., “The Key to a Data Warehouse,” DatabaseProgramming & Design, February 1995, pp. 23-25. 10. Parsaye,K., “The Sandwich Paradigm,” Database Programming & Design, April 1995, pp. 50-55. 11. Bischoff, J., “Achieving Warehouse Success,” Database Programming & Design, July 1994, pp. 27-33.

Acknowledgements Mimi Duncan, Dolly Matthew, Lenis Boswell and Nancy Tabor provided valuable assistance in the definition of information entities and creation of the information usage matrix. We are grateful to these individuals, to others on our twenty-member project team, to City departmental managers, MIS technical staff, members of the Strategic Planning Oversight Committee, MIS Subcommittee, Technology Subcommittee, and GIS Subcommittee who participated in the study. Without the cooperation and involvement of all these parties, the successful completion of the study would have been impossible.

References 1. Kimball, R. and Strehlo, K., “Why Decision Support Fails and How to Fix It,” Datamation, Vol. 40, Number 11, June 1994, pp.4043 2. Inmon, W., “Building the Data Warehouse”, QED Technical Publishing Group, 1992. 3. Hackathom, R., “Data Warehousing Energizes Your Enterprise,” Da&nation, Vol. 41, Number 2, February 1 1995, pp. 38-45. 4. Radding, A., “Support Decision Makers with a Data Warehouse,” Datamation, Vol. 41, Number 5, March 15 1995, pp. 53-56. 5. Software AG Technical Literature, “The Decision Maker’s Goldmine: The Data Warehouse,” Datamation. Vol. 41, Number 5, March 15 1995, pp. s6-~13. 61
