Case Study 14

Indiana State Board of Health

Frank Loogootee, director of the Bureau of Administrative Services for the Indiana State Board of Health, walked across the hall to the office of Darrell Fisher, his MIS director. “Darrell, what’s this about a data architecture project I hear you want to do? I thought that new system, IDMS, we bought was going to solve our backlog of requests.”

“Frank, from a database standpoint, we are kind of at square one and I want to try to grow an integrated database correctly. My goal is to maximize each system development effort as it comes along, rather than have individual systems and databases exist, isolated by themselves. I need to obtain some efficiencies in systems development. With my limited head count, I can’t keep up with the tremendous load of requests for new systems from the division and bureau chiefs.

“As you will recall, Frank, we have been designated as a data capture site for all health information for the state. Because of this and other recent changes, I think that every senior manager at the board sees processing and disseminating information as our primary function. We are in the information business. IDMS is the engine to run our information business. But I need some way to understand what all this means, so that when I get a new system request, I don’t have to reinvent a totally new system. In fact, I think that maybe once we get organized, most new system requests might be able to be met simply by writing a few report programs. Now our data is so spread across seven or eight major systems and 30 minor ones that I’m better off going ahead and building a new system each time. I can’t continue to do this much longer.

This case was prepared by Professor Jeffrey A. Hoffer as the basis for class discussion rather than to illustrate either effective or ineffective handling of an administrative situation.

Copyright © 1989, 1990, 1993 by Jeffrey A. Hoffer

“We continue to use the same data over and over again, but I’m convinced we just give it a different name each time. So, we go ahead and build what we think is a unique system for a unique need. Our new vital records system is a great example. The database for this system, our first major one under IDMS, has data in it from many of our other systems, but we couldn’t get a handle on this overlap. So we just went ahead and created a whole new database without a road map.”

Background

The mission of the Indiana State Board of Health (ISBH) is as follows:

To promote health and wellness activities, to encourage the prevention of disease, and to administer public health programs that promote the availability, accessibility, and quality of health-care services throughout the state of Indiana.

The ISBH is committed to interagency communication, cooperation, and coordination in pursuit of the health and well-being of the citizens of the state.

As early as 1855, the Indiana State Medical Society, a public interest group, was working to secure a public health and vital statistics law for Indiana. After several failed attempts in the legislature, the ISBH was established in 1875. In 1886 the first conference of Indiana state health officers was held in Indianapolis. This conference outlined the needs for statistics and vital records (for example, birth and death) and for ISBH involvement in many health matters in the state (for example, vaccination, and control of diseases, such as cholera and typhoid fever).

By statute the ISBH operates as the superior health board of the state, to which all other boards (such as county departments of health) are subordinate, but do not necessarily report to the ISBH. The ISBH holds all the powers necessary to bring legal action for the enforcement of health laws and rules. The ISBH keeps vital statistics and cooperates with other state agencies in the sharing of such statistics. It licenses and certifies medical personnel and many different types of health, agriculture, and food services for the purpose of controlling contamination and the spread of disease. The ISBH is a proactive agency involved not only in the control of health-related activities, but also in the prevention of health problems (see Exhibit 1 for a list of the 1987 ISBH strategic initiatives).

The ISBH organization chart appears in Exhibit 2. The state health commissioner, appointed by the governor, oversees the ISBH and serves as chief public spokesperson for health issues in the state. Three commissions, each composed of several bureaus, which in turn are composed of divisions, manage and operate the various programs, laboratories, planning, certification and licensing, surveying and monitoring, public information and publishing, administration, and vital record activities.

The Bureau of Administrative Services includes the Management Information Services Division (MISD), which provides computing and data processing services to all other divisions. This division provides systems analysis and design, programming, data administration, operations and user support, and network management services. The MISD is separate from the central state data processing department and MIS departments in other state agencies. As of July 1, 1987, the division employed twenty-three staff members (four management, eight technical, two operations, and nine clerical or data entry). The director of the MISD is Darrell Fisher.

Traditionally, ISs at the ISBH have been nonintegrated (that is, separate systems and files for each user division and application), using conventional file management technologies, which has meant excessive data storage and processing, as well as incompatible data. The result has made it difficult and costly to answer questions and deal with management issues that cross divisional boundaries. For example, being able to link activities of the Dairy Division with those in the Nutrition Division is almost impossible. Recently, the ISBH purchased IDMS/R from Cullinet Systems. This database management system with integrated dictionary provides the technological basis to support common data stores on keyboard data entities, such as facilities, programs and individuals.

The MISD also:

• supports the IBM Professional Office System (PROFS) office automation software.

• is testing the development of a statewide local health network.”

• is establishing an information center to support personal computer usage throughout the board.

• has developed a disaster recovery p an for all computer systems in the ISBH

The Data Architecture Project

As Fisher explained the goal of his data architecture project idea to Loogootee:

To me, the goal of the project is to build a tool that will help us in two ways in systems development—to identify the overlap in data utilization and to help us be more productive. I can also use the results of this project as a planning tool to identify gaps for new systems as part of our biannual planning and budgeting process. As part of this planning, we do a general survey, division by division, of their information needs for the next two years. A data architecture will help us relate their requests. This could lead to raising the priorities on individual requests when we can see the synergy of multiple needs, which can be supported from a common set of data.

The data architecture project was undertaken during May-August 1988, at the initiation of Fisher with the full support of Loogootee. The purpose of the project was to develop an inventory of data requirements (not processing), or a logical data architecture, for the board. This architecture was to be independent of how data was or would be managed by specific technology. The general motivation was to provide the logical view of data needed to deploy a more efficient and functional set of systems for ISBH. The premise was that ISBH, from a data viewpoint, needed three types of information systems.:

1. Those that capture and maintain data from the various sources of data to the board (such as certification applications, site surveys of regulated facilities, and population and other socioeconomic data from other state agencies).

2. Those that transfer data between separate databases or download data to personal computers for manipulation and display.

3. Those that analyze and report/present summaries and projections for decision-making and policy-making, as well as operational activities.

The goal was to develop an overall design for managing the ISBH data that would make systems of these different types as independent from one another and as efficient as possible. When a new request for a report would be submitted to the MISD, Fisher wanted to be able to identify if and where the data existed to satisfy this request. When new data had to be captured and maintained, the data architecture would be used to help determine logically where this would fit in the overall set of data maintained by ISBH. When standards or the definition of certain data would be changed, the architecture could be used to evaluate the impact of these changes. Subsequently, when a data architecture would be linked with systems and network architectures, a complete management system would exist for planning and control of all ISs in the ISBH.

Organizing the Project It was decided that current MISD employees could not be freed from other responsibilities to conduct this project. The State Office of Systems Technologies (an advisory group, part of the Data Processing Oversight Commission) had initiated a summer internship program to bring students at Indiana universities into state data processing departments. The long-range goal was to attract more highly trained systems professionals into state government. One such student, Jackie Hamon, a computer science masters candidate at Indiana University—Purdue University at Indianapolis, was chosen from this program to be the principal analyst for this project. Neither the ISBH nor any other state agency had ever conducted such a project. Fisher was uncertain what methodology and documentation notation to use. Jerry Douglass, an IS professor from the Indiana University School of Business with a specialty in data management, was hired to help structure the project, set a work plan, and assist in choosing the methods to be used and the form for presenting the results.

It was also decided early that within the summer internship time period, a data architecture that would cover all divisions could not be developed. Therefore, seventeen high-priority divisions were selected by Fisher and Loogootee for analysis. Hamon interviewed and reinterviewed division heads and other employees, studied operating plans and descriptions of the divisions, and analyzed the data defined in current systems for these divisions
From this work, information was gathered about the persons, places, and things (data entities) about which information is and needed to be stored. The relationships among these entities were also noted. (Relationships are associations, such as a bureau has many employees or a facility has many inspections.) Five products documenting the data architecture were produced from this analysis:

1. A model or view of data, using the generally accepted extended entity-relationship (EER) notation for each of the seventeen divisions (see Exhibit 3 for a sample of three of these seventeen charts).

2. A consolidated data model using EER notation, showing a unified and consistent view across the whole board (see Exhibit 4).

3. Definitions for each of the data entities, along with the primary key (unique identifying data) for each (see Exhibit 5 for an excerpt).

4. Definitions of each relationship, which describe how the board operates and policies on the data (see Exhibit 6 for an excerpt).

5. A cross-reference chart that shows which divisions create, use (retrieve), update/modify, or delete/archive (a so-called CRUD analysis) the data entities (see Exhibit 7).

The Extended Entity-Relationship Notation

Early in the project Douglass, Hamon, and Fisher settled on using the extended entity-relationship notation for pictorially describing the data architecture Because Fisher planned to acquire one of these CASE tools soon (possibly one supplied by Cullinet Systems that would work with the IDMS/R product), EER diagraming seemed to be an appropriate choice.

Relationships can exist between a pair of entities (for example, between Grant and Budget) or among three or more entities (for example, Budget, Vehicle, and Employee). For each relationship, besides what entities are connected together, the so-called cardinality must be specified. Relationship can be of cardinality one-to-one, one-to-many, or many-to-many.

For the ISBH, an example of a one-to-one relationship is License-to-Facility (named “Legalizes’, in the Bureau of Consumer Protection EER diagram in Exhibit 3), for which there must be a one-to-one match of one license for each facility and vice versa.

Numerous one-to-many relationships exist One is Division-to-Employee (named “Supervises” in the Bureau of Consumer Protection EER diagram in Exhibit 3), a relationship that indicates that an employee must work in one and only one division, but a division can have many employees.

Also, a large percentage of the ISBH data relationships are many-to-many. An example is Budget to-Contract/Purchase Order (named “Paid From’ in the Bureau of Administrative Services EER diagram in Exhibit 3). This relationship is many-to-many because a budget can fund many contracts and the total support for a contract can come from several budgets.

The cardinality of each relationship is designated by shading corners of the relationship symbol. The three-way relationship, named “Monitors,” between Budget, Vehicle, and Employee (see the EER diagram for the Bureau of Consumer Protection in Exhibit 3) indicates that when an employee uses a particular vehicle, it must be funded from only one budget, but over time a particular budget can fund the use of a certain vehicle by many employees, and one employee can be funded to use many vehicles from the same budget.

One additional notation is the circle placed on a line connecting relationship diamonds and entity rectangles. The circle indicates that there may actually be no entities from time to time associated with the relationship. Consider the EER diagram for the Bureau of Administrative Services in Exhibit 3. This diagram shows that a Grant can supply funds for one or more budgets, and that zero or one grants may support any particular budget.

The EER diagram for the Bureau of Consumer Protection in Exhibit 3 also shows that there are four different types of facilities: Weights and Measures, Dairy Farm, Retail and Manufacturing, and Processing Plant. All facilities have a license, are located in some geographical area, and can be surveyed. Only the processing plant type of entity, however, is associated with animal slaughter event (which is itself a type of the event entity).

To avoid crossing lines and a possibly unreadable chart, a cross-hatch is drawn in the corner of an entity symbol that is repeated on that chart.

Conducting the Project To gain a broader commitment to the project, to provide a periodic check on project progress, and to begin sharing ideas on data management across the board, Douglass recommended forming a project advisory panel, composed of influential department heads. A panel was formed, including:

Darrell Fisher

Dan Gurney (data administrator)

Joe Fox (director of the Bureau of Policy Development, a bureau which helps each division in its planning and direction setting)

Dave Smith (a consultant working on a new vital records system for one of the divisions)

Frank Loogootee

The panel met every two to four weeks and reviewed interim charts, suggested who should be interviewed from the seventeen priority divisions, suggested ways to present the results for the final report, and accepted responsibility to move forward with the final report-findings.

The process that Hamon followed to gather the information needed to produce the data architecture utilized both interviews and the analysis of critical business documents. She started by interviewing various bureau directors to get an idea of the functions of each bureau and who within the bureaus would have the best understanding of the data handled in each group. Then she set up meetings with these people and some division directors.

She described her procedure as follows:

First I explained what I was doing and that I wasn’t particularly interested in things that should be processed by a computer, but just the data handled in their unit. I had to use a lot of examples, since they had trouble just talking about data. I also encouraged them to show me lots of examples, like of a survey they took, a report they produced for distribution to the public or other state agencies, or standards on the work in their area. Even the actual laws that define a division’s responsibilities were helpful. If I could get my hands on these things before the interview, I’d review them to be better prepared.

They used so many local terms that frequently I had to give examples of the kind of entities I had been formulating, like facility and budget. I wanted to know how they used this data, which was fairly easy for them to talk about. It usually took several interviews, since being new to the organization it took me a long time to understand what I was hearing and to know what questions to ask.

Sometimes I’d hear a term and think that it meant the same as what someone in another division had said, but when I’d go back to my desk and try to draw this into an EER diagram, I’d find inconsistencies and I’d have to ask follow-up questions. Frequently this is when I’d realize that an entity, like facility, had many subclasses or types, and what I was hearing was different because the different types of the same entity were handled in different ways in various divisions.

I also found that the people in the divisions had never really taken the time to conceive of the kind of picture of data I was developing, so a lot of my questions in the first interview didn’t generate much. In general, I had to just let them talk about what they do and I had to figure out, and then check with them, what data or relationships they were utilizing.

When I’d go back for a second interview I’d take the preliminary diagrams with me and I would key my questions off the diagram. I was surprised that many of the managers seemed to be able to figure out the diagrams with just a little explanation, and then they could give me the answers I needed. Other times, I’d just refer to the charts myself to pace me through my questions. In either case, the charts were a big help in focusing in on just the information I needed from them. For example, the diagram allowed me to find out if one survey helped a facility to qualify for just one license or if several licenses were being considered. This was very straightforward to them.

The biggest problem I had was to get them to think about all the data they used, not just that computerized. I had to be careful not to say much about our goal of a computer data architecture, since that would really limit what they would talk about.

Another thing was that they often gave me too much detail, much more than I needed. Many people would go on and on and bring up all the problems they ever had with MISD. Yet others were very protective of their data. They felt that nobody else would ever want to use this data, so why should they tell me about it. This was especially true in the divisions that had confidential data on individuals.

Another problem was that I had never done a project like this before and it took me three to four weeks to get comfortable with what I was expected to do. Being new to the board and new to this project made it doubly difficult for me. On the other hand, I was not biased by history or details, so maybe being an outsider had some benefits, too. I was able to see things at a higher level than many of the MISD staff, who have lived through the development of the existing systems, could have.

Also, except for Darrell and one or two other people, nobody had much of an idea of what I was doing. They didn’t know if I was actually developing a database for them or what. Maybe if we had a sample of a report and findings from another project to show people quickly, they would have been more on target in what they said to me. One or two people became hostile from some of my questions. I guess that they felt that I was asking some of the same questions that had been asked before and nothing very significant came out of those interviews. These people are very busy and many of them want action, not just planning.

One of the side benefits of this project was that I was able to discover areas that were doing the same thing with data and not realizing it. And I found areas that had systems with so much data that other divisions could use, if they only knew it existed. I was really surprised how little these people knew about what data others had in their systems. People bring in a tape and have it loaded on the MISD computer or on their PC and nobody else ever knows about it. There is so much data here that seems so valuable, but it lies dormant because most people have no way to know it exists or where. They just keep asking for a lot of the same data over and over again.

The other thing that I discovered is that there are a few people who have been around here long enough and are in some rather central positions, like Joe Fox in policy development, who do have a pretty good idea on the total information usage at the board. But these people are swamped with their own jobs and just don’t have the time to sort this out for MISD. And when these people leave or retire, their understanding is lost.

Another problem I had was maintaining all the interview data I collected and drawing and redrawing all the EER diagrams and charts. I used dBASE to keep some of the data and a Macintosh to draw the figures, but all of this was brute force. I could have really used better computer tools for organizing the data and maybe even automatically redrawing the EER diagrams when I discovered cardinality changes, entity subclasses, or even new entities. I looked at the data dictionary from IDMS to help with this, but it was more geared to describing an operating database and it couldn’t draw any of the diagrams we wanted. It wasn’t very flexible and I didn’t have the time to learn how to use it. So, I just did it all my own way.

Results of the Project Besides producing the five data architecture products outlined, Hamon and the consulting faculty member were able to use these charts and tables as well as the qualitative information collected from the interviews and analyses to develop many general observations and recommendations. These can be summarized as follows:

Even though only selected data elements were documented (because of the limited time of the project and the desire to keep the project at a high data entity and relationship level), the data about each entity and relationship used may be quite different for each division. Thus, the physical design of databases and systems may need to split apart storage of data about a given entity into multiple computer files to handle the large volume and diverse data processing requirements. This emphasizes that this data architecture is strictly a logical prescription, not recommendations on the physical design and organization of data.

The time dimensions of data is not precisely captured in these charts. Many data elements exist and change over time (for example, the address of an employee). Further analysis would be necessary to determine proper archiving and historical record-keeping requirements.

Some apparent flaws in board procedures exist. For example, it is common that several divisions believe that they are responsible for creating instances of a given data entity (such as facility). In some cases, this may be legitimate, since each division is concerned with different types of the entity or different subsets of facility data. In other cases, the historical development of separate systems has encouraged multiple divisions to capture the same data redundantly. Board data policy will need to be established to coordinate this data capture under the new data architecture.

To provide such coordination, Hamon and Douglass recommended establishing a Data Coordinating Committee. This group would be composed of bureau representatives knowledgeable about information needed in their bureaus, along with Dan Gurney, recently appointed to be in charge of the data dictionary of IDMS/R. The duties of this committee are outlined in the recommendations. Briefly, this committee would establish who is responsible for (in a sense, owns) different data, decide how and when data definitions may change, support MISD in acquiring data management stan and software to better manage data as a board resource, and resolve conflicts on data and system plans as it relates to data formats and representations (for example, how to encode the unique identification of a facility, which is now done five different ways across the various systems in place in the ISBH).

Hamon recommended follow-up work to complete the analysis for all thirty-three divisions and to produce an inventory of data that resides in current systems, including a cross-reference of existing locations of data to the elements of the data architecture.

At the final presentation, the advisory panel enthusiastically accepted the products of the project and decided to move forward with the recommendations. Fisher took responsibility for implementing the recommendations. In looking to the next steps, Fisher said:

We are going to have to set up procedures that will help us standardize how we name and identify data elements so that we can recognize that two elements that are today called by different names are actually the same data. The data dictionary will be our tool to accomplish this. There will be a lot of work to populate the data dictionary, but this must be done. We will need also to put more clout behind our data administration function as the way to operationalize the policies and plans of the Data Coordinating Committee. I want to give a copy of this report to every one of my managers so that they better appreciate where I want to go and how each of them fits in. I want them to stop thinking just about the needs of the individual client they serve but to think also in terms of managing the total data resource of the board. This will be a difficult attitudinal change, but a necessary one.

We have a very limited staff. Every time we throw the switch for a new system we better be getting the most for our effort or we will be working against ourselves. We are just not going to get many opportunities to do it right. We need to do a serious inventory of our mainframe systems to see where all this data outlined in the EER diagrams and other charts now reside. I don’t see us purposely going out and starting to consolidate these systems, but when they do come up for changes, we can take advantage of the opportunity and roll them into the data architecture. Personal computer systems are more of an issue for such an inventory since people take data in those systems more personally. But if we don’t manage all data, we are not really managing any data.

Hamon added:

The board has so many divisions using so much of the same information. When I started I thought EER diagrams would be quite distinct from each other, but just look at them! Entities like facility, division, budget, survey, individual, program, employee, and many others are everywhere. There is so much potential here. There is so much more you could do once you realize all the data you have and you can organize it so that everyone can have access to it.