This article is also available in PDF File.

In an increasingly computerized business environment, it has become much easier for organizations to collect and store large amounts of information. Government agencies in particular have amassed great volumes of data on their clients and programs. Decision-makers are often interested in finding more expedient, more meaningful ways to save and analyze these large collections of data. A technology called data warehousing answers precisely this need. Data warehouses can organize vast amounts of data from different sources, helping analysts answer timely questions and identify relevant patterns. These discoveries can be the key to better decisions and wise use of scarce resources.

What are Data Warehouses?
The amount of data available in a large, complex organization may be overwhelming. Some decision-makers may not even be fully aware of all the data that are actually collected. Furthermore, data are often collected on the same population by multiple agencies but stored in different locations. Data warehouses are one way to make data more manageable and more accessible for the researchers and decision-makers that need it most.

Data warehouses serve as a central repository for all or most of the data collected by the various operating units and systems of an organization. A data warehouse organizes and stores data with a long historical perspective. The data are presented on a common platform from which information processing and analysis can take place. Data can then be analyzed from the perspective of the organization as a whole, rather than by individual units of organizations. The analysis can take the form of queries constructed to answer a specific question, but it may also take the form of sophisticated data mining. Data mining, as the name implies, examines the data as a whole in an effort to identify ("mine") key trends or associations that may otherwise have been overlooked.

To harness the true power of a data warehouse, it is important to recognize how it differs from traditional databases. As an analytical tool, the design of a data warehouse generally starts not with a review of specific user requirements, but with an analysis of the data itself. Planners need to first consider what data sources already exist and how the information they contain can best be centrally collected. If planned on a solid foundation, a data warehouse can begin on a small scale and continue to grow to meet the changing needs of the organization it serves. Related to data warehousing is the concept of the data mart. It, too, is a repository of data gathered from various sources, but it is created specifically to serve a particular group of users in an organization. The content, interface, and functions of a data mart reflect the unique needs of a segment of an organization, whereas a data warehouse is less specialized. For example, a corporation may have a data warehouse that every department can access, but the sales department may have its own data mart. Together, data warehouses and data marts can work to transform data from a latent resource into a powerful, dynamic information tool.

Data Warehousing: A State Case
Texas is one of a growing number of States implementing data warehouses to facilitate child welfare practice. The CAPS (Texas SACWIS) data warehouse is still a work in progress, but has already been well received.

The idea to use data warehousing started when Texas officials encountered difficulties generating ad hoc management reports from their new SACWIS system. The new system was proving too complex for users to generate ad hoc reports. The new system contains about three hundred tables and data elements that changed from what existed in the legacy system. Most users were not skilled enough to make effective use of the ad hoc reporting tool that Texas had chosen during the planning and development process. Indeed, even expert users were challenged by the task of creating queries based on the new data elements and complex table structures in the new system.

CAPS staff came up with the concept of a warehouse to facilitate reporting. The CAPS data warehouse is an Oracle 8 database. The reports are generated using the Oracle Reports software contained in the Oracle Enterprise Developer's Suite. It is made up of a number of data marts; each focused on a specific facet of the child welfare process. Currently, modules for Intake, Investigations, Permanency Planning, Substitute Care, Family Stages which includes Substitute Care and Family Reunification, and Adult Protective Services Service Delivery have been completed with several more in development.

The data marts are designed in a star configuration, which places key data in a central table or set of tables and places additional details in secondary, linked locations. The single, biggest task in designing the data marts has been getting user buy in. The first step was to identify the stakeholders such as staff reporting to the legislature, regional and program managers, and supervisors. Users were first queried as to what types of questions they would like answered. They were then asked to agree on definitions for the data that would make up the answers to those questions (e.g., what constitutes an intake?). Once those questions were answered, CAPS staff could begin designing the data mart. The final big issue was the decision on how often the data would be updated. The decision was made to populate the data warehouse on a monthly basis, with updates at three months on a rolling basis with a 12 month fiscal year ending update to coincide with the timetable of annual reports to the legislature.

CAPS staff is not resting on their accomplishment with the data warehouse. They have implemented a web-enabled front end that allows users to access the 300 or so canned reports and ad hoc capability of the system via the State Intranet. Such an interface would place the power of the warehouse at user's fingertips in an easy to understand, user-friendly format.

The CAPS data warehouse effort is being headed by CAPS Project Manager Donna Marler and CAPS Data Administrator Christie Mitschke. For additional information on the CAPS data warehouse project contact Donna Marler at (512) 834-3758.

This document was prepared as a supplement to the material presented in add-on sessions at the May 2000 ACF Users Group Meeting in Louisville, KY. It is the result of collaboration between the Office of State Systems and the National Resource Center for Child Welfare Data and Technology. The purpose of this document is to provide a brief overview of an issue relevant to the development or implementation of SACWIS systems and to the delivery of child welfare services. It is written with the goal of being accessible and informative to a wide audience including program and systems staff with varying levels of comfort with technology and policy. We hope that it will serve to stimulate an exchange of ideas and information among States and between systems and program staff. Your feedback is important to us. If you have any additional information on the topic presented in this sheet, or if you have any comments or suggestions regarding its presentation or content, please contact Tom Wetterhan of Xtria at (703) 821-3090 x250 or tomw@xtria.com.