Database Development for the
U.S. Agency for International Development
in Almaty, Kazakhstan

Background

“MEASURE Evaluation is committed to coordinated, cost-effective and efficient approaches in data collection, monitoring and evaluation of population, health and nutrition services worldwide. It is one of five separate projects funded by the U.S. Agency for International Development (USAID) to examine the impact of a wide range of activities to improve human health and well-being, with a focus on family planning, maternal and child health and nutrition, and the prevention of HIV/STDs.”

 

MEASURE stands for Monitoring and Evaluation to ASsess and Use Results

 

The Carolina Population Center is a UNC department committed to “to promot[ing] population research and research training.”

 

“USAID is an independent federal government agency that receives overall foreign policy guidance from the Secretary of State. The agency works to support long-term and equitable economic growth and advancing U.S. foreign policy objectives by supporting: economic growth, agricultural and trade; global health; democracy, conflict prevention and humanitarian assistance.”

 

The CPC applied for the Population, Health and Nutrition Office of USAID’s 5 year MEASURE Evalution Project which they put up for bid in 1997. The award totaled some $30 million, making it the largest project CPC administers.

 

In addition to MEASURE Evaluation, there are four other projects that fall under USAID’s MEASURE program—“dedicated to providing accurate and timely information on population, health, and nutrition in developing countries. The projects offer technical services in data collection, analysis, dissemination, and use.” They are:

 

  • MEASURE DHS+ - Collecting and analyzing survey data
  • MEASURE Communication - Disseminating and using data for policy
  • U.S. Bureau of the Census (SCILS) - Supporting censuses and add-on surveys
  • Centers for Disease Control and Prevention (Division of Reproductive Health) - Providing technical assistance for reproductive health and young adult surveys

Introduction

MEASURE Evaluation administers more than 130 activities. Some of these involve direct consulting with USAID’s various missions throughout the world. Other activities are devised and implemented by Measure’s staff, for the purpose of research, training, or the creation of a product (a survey, a publication, a statistical method, etc) which other activities can use. Of course, in order to help coordinate all of these activities, MEASURE Evaluation has a substantial administrative staff. I am employed by the financial program manager, Phill Lyons, in order to develop and maintain MEASURE Evaluation’s financial and programmatic databases.

 

In early October of 2001, it was brought to my attention that one of MEASURE Evaluation’s technical assistance activities to the USAID Mission in Almaty, Kazakhstan was thinking that a database might help them with some formidable data collecting and reporting tasks. However, email communication with the Mission was spotty and the proposal presented to me by one of  MEASURE Evaluation’s researchers was tenuous at best. I did a little design work but nothing much through the end of the year. This researcher entreated me to being working on it again in January—at least enough to create a working prototype. I was pretty wary of taking on an additional and nebulous project at the time, so I sent an email to the INLS 258 list detailing my situation--to see if it would elicit comments from anyone else:

 

“For amusement and conversation. Feel free to reply.

 

This is a scenario in which I need to make a database.

 

My employer needs reporting information from one of its subcontractors. The subcontractor does good work (in Kazakhstan), but they are behind in their use of technology to help manage their information gathering tasks.

 

A database has been proposed as a solution to aid their management and retrieval of information. The info extracted from the database will be used for the subcontractor's in-house administration, for reporting back to my employer, and for reporting back to the US Agency for International Development (USAID), the overarching federal organization that funds all of us.

 

My employer has a rough idea of the information this subcontractor might want to track, as well as the type of reporting we'd like to receive---however, these are merely guesses. We know little about what their in-house needs are or what USAID might ask from them. Since they currently use a system of word documents to store data and temporary excel spreadsheets to analyze the data upon request, they can't seem to articulate what they want this database to do--even on a general functional/conceptual level. Communication is limited to email, and their reponses to our design/feature questions have essentially been "I don't know/I'm not sure."

 

Yet still, my employer wants me to create this database, based on the organization of the reports we'd like them to send to us, complete with data-entry forms and reports, user and developer-level documentation, and without any actual data. The hope is, that once they see something in front of them and try to use it (imperfect as it may be) they will be able to provide us some meaningful feedback. Microsoft Access97 has been chosen as the DBMS, however, the eventual users of this database have little to no experience with Access. Thus developer level info must include concepts like "how to preform a query" or "how to create a report."

 

Being a good database designer/administrator, I can:

 - create a structurally general/nimble database which I will be able to flexibly change later

 - create a database that anticipates the type of data that will be tracked to prevent massive/expensive changes later

 - use fake data to ease form/report creation

 

After taking system's analysis, you might suggest ways to improve communication  with the subcontrator (contextual inquiry!), or you might say this project is just bunk. Unfortunately, I can do little to improve communication or observe their day to day work environment, and I cannot abandon the project.

 

Any additional thoughts, comments, suggestions? :)”

 

Nonetheless, I took a new look at my original design and saw ways I could simplify it with self-joins and super/sub-class notation. I created a working prototype that more than met this researcher’s expectations—which she was planning on taking with her to Kazakhstan some time in March.

 

Come late February, I received an email from her proposing a slight change in plans: that I accompany her to Kazakhstan to further customize the system, to install it, and to train the people there in its use.

The Database Design

The purpose of the database is to simplify the management and reporting of the USAID Central Asian Republics (CAR) mission’s Health and Population Office’s Performance Monitoring Plan (PMP). The plan in essence is a framework of results used both to justify funding to Washington and used to track the progress of various projects enacted by the mission. At the top of the framework are the strategic objectives, one for each of the Central Asian Republics: Kazakhstan, Kyrgyzstan, Tajikistan, Turkmenistan, and Uzbekistan. Each of the strategic objectives has one or more related intermediate results, intended as more specific objectives which can help achieve the strategic objective. For the purpose of progress tracking and benchmarking, each strategic objective and each intermediate result can have one or more indicators, with each indicator possessing many relevant data points. In addition to data points, each indicator has a page worth of descriptive attributes, such as the precise definition, the justification, the data collection method, etc. USAID and Congress in Washington are really only interested in the indicators belonging to the strategic objectives and intermediate results. The mission, however, goes one step further by developing lower-level results for each intermediate result, each of which also has one or many indicators.

 

The current method of storing this information is via word document for each indicator, with a folder for each strategic objective/intermediate result/lower-level result. The limitations of this data storage approach should be immediately obvious—data entry and retrieval are a nightmare, as there are over 100 indicators and a multitude of folders.

 

The goal then of the database is to facilitate much simpler data access. This should then create new ways in which the data can be reported/extracted painlessly.

 

It would have been one thing if only the lower level results had indicators, but rather the results at all levels had indicators, and the only difference the type of result was how it related to other results. So I devised a very simple ER schema, where a result (SO, IR, LLR) can have one or many indicators and an indicator can have one or many data points. The results are then broken into three disjoint subclasses: strategic objectives, intermediate results, and lower-level results, with one to many relationships between the three. Finally the SO has a country attribute which the IR’s and LLR’s inherit from it.

 

 

Conclusion

Creating a database that users with no experience with Microsoft Access would have to construct QBE queries and reports was out of the question. Therefore, from a user interface perspective, I tried to create forms and reports that were maximally flexible and encompassing.

 

One of the largest changes made to the database on site was adding the ability to link the results and indicators of different countries. It turns out that Kazakhstan, Kyrgyzstan, and Uzbekistan have almost identical results frameworks and almost identical indicators for each result. The implications of this meant that either the database would have to be restructured, or some linking solution would have to be employed so that changes to the fields in one country’s results or indicators would be applied to the fields in a linked country’s results or indicators. Since several hundred pages of word documents would have to be entered into the database, linking the results and indicators of KZ, KG, and UZ would reduce data entry by 2/3.

References