The Computerworld Honors Program
Honoring those who use Information Technology to benefit society
Pittsburgh, PA, US





Allegheny County Department of Human Services

ACDHS Data Warehouse

Short Summary
The DHS Data Warehouse went into production in April 2001. It is a result of a 1996 proposal to merge four separate Allegheny County human services entities that would, at the beginning of 1997, become the Allegheny County Department of Human Services (DHS). The merger occurred as a result of the understanding that many individuals receive services from more than one of the previously disconnected entities and that incorporating these separate entities would allow more coordinated service delivery to the vulnerable population of Allegheny County. It was recognized early on that integration posed significant challenges for the new Department’s information technology systems since the formerly independent entities stored client, provider, and service information in more than 80 disparate databases and systems. There was a significant need to track information in a common way for the clients served by DHS. The result of this need is the DHS Data Warehouse. The DHS Data Warehouse is a central repository of human services data that allows DHS to track client demographic and service data across its program offices. The goal of the DHS Data Warehouse is to enable data-driven decision-making among DHS staff and therefore lead to better outcomes for the individuals served by the Department.

Introductory Overview
Prior to 1996, Allegheny County (Pittsburgh), Pennsylvania delivered human services to its vulnerable citizens through independent county departments. There was no coordination of services and no tracking of who was receiving services from multiple departments. In 1996, a special blue-ribbon panel recommended that the individual human service departments be integrated into a single department. The Allegheny County Commissioners responded by combining several separate human services entities and creating DHS.

In 1997, a coalition of 12 local foundations created the Human Services Integration Fund (HSIF) to assist with the development of the newly created DHS and to fund DHS projects that fell outside rigid government funding streams. In 1999, with the backing of HSIF, DHS announced a Request For Proposals to develop a computing architecture to support the business process of an integrated DHS that included the DHS Data Warehouse.

In April 2001, the DHS Data Warehouse went into production with client demographic and service data from two information systems internal to DHS. As of 2007, the DHS Data Warehouse contains more than 15 million client records and receives data from 17 independent operating applications. Further breakdown of these operating applications shows 11 are internal to DHS and six are from sources external to DHS. Work is currently being done to include two other external data sources. The internal sources include child welfare, behavioral health, mental retardation, aging services, employment and training, homeless services and several low-income services such as energy assistance and medical assistance transportation. The external sources consist of county and state corrections agencies, city and county housing authorities and the Pennsylvania Department of Public Welfare (including TANF, general assistance, food stamps and Medical Assistance). Juvenile Probation information and data from Allegheny County’s Medical Examiner will be bought into the DHS Data Warehouse within the next three months. Historical data from all operating applications are included, allowing for data analysis to go back further than the DHS Data Warehouse’s inception.

All operating systems included in the DHS Data Warehouse supply extracts on a regular basis to update the Data Warehouse. A complex algorithm is run on all client information to “unduplicate” clients common to two or more operating systems. By incorporating historical data from sources both internal and external to DHS, a multi-dimensional picture of DHS clients and their cultural surroundings is created. Further, DHS realizes that the data collected and stored in the DHS Data Warehouse is not only valuable to DHS staff but can also be used as a resource tool for research, strategic planning, needs assessment and program evaluation to the broader community.

Has your project helped those it was designed to help?   Yes

What new advantage or opportunity does your project provide to people?
There are three core reasons why the DHS Data Warehouse is beneficial to the human services arena:
1 The DHS Data Warehouse’s uniqueness in combining historical data from sources both internal and external to DHS is the first reason. The breadth and depth of the data collected and housed in the DHS Data Warehouse allows many stakeholders to access valuable information that will support issues such as placement of service delivery, needs assessments and policy making that has and will result in positive outcomes for clients receiving human services.

2 The vast amount of data stored in the DHS Data Warehouse allows stakeholders to look at individuals as a whole, not just the part for which DHS provides services. Additionally, the DHS Data Warehouse is working towards tracking individuals in related groups such as families or households. This type of tracking will enable stakeholders to look at and assist clients from yet another perspective.

3 The DHS Data Warehouse allows information to be tracked electronically. Previously, data were tracked manually on paper or on disconnected spreadsheets and, in some cases, data were not tracked or available at all. The DHS Data Warehouse provides DHS staff with immediate client and service information to help a client in need.

Has your project fundamentally changed how tasks are performed?   Yes

How do you see your project's innovation benefiting other applications, organizations, or global communities?
The DHS Data Warehouse continues to provide positive benefits and changes for many stakeholders regarding human service delivery in Allegheny County.
1 Provider and service data from the DHS Data Warehouse is exposed to the public through an application developed by DHS called HumanServices.Net. Work is currently under way to present aggregate client data in HumanServices.Net. DHS clients and families use HumanServices.Net to make critical decisions regarding services for themselves and/or their family members.

2 The DHS Data Warehouse has enabled people to make data-driven decisions. This has lead to a movement away from the “this is the way it has always been done” thinking and has resulted in innovative, long-term solutions to the County’s human services issues. Assisting in this culture change is the constant effort put into making the data ever more reliable. For instance, improvements on the client matching algorithm have improved the identification of clients in multiple information systems. In calendar year 2005, the DHS Data Warehouse reported 250,000 clients served, with 26% of teenaged children served in child welfare also served by the mental health system. In calendar year 2006, the DHS Data Warehouse more reliably reported serving 181,000 clients, with 44% of teenaged children in child welfare also receiving mental health services.

3 Educational and research institutes have relied on the DHS Data Warehouse to assist in studies and research projects as a single point of data collection rather than going to many sources and piecing data together. These institutes include: RAND Corporation, the Center for Disease Control, Carnegie Mellon University, the University of Pittsburgh, Funders in Criminal Justice and the U.S. Department of Justice.

The Importance of Technology
How did the technology you used contribute to this project and why was it important?
The construction of a Data Warehouse was fundamental to this project. It helped DHS to realize and surpass most, if not all, of its goals in creating a shared ground for reporting across DHS. In addition to the original objective of building a Data Warehouse, the establishment of multiple reporting tools has allowed both technical and non-technical DHS users immediate access to the data, resulting in increased outlets for analysis and education. With the addition of the Data Warehouse and its reporting tools, DHS has been able to:
1 “Unduplicate” the client population across DHS
A complex matching algorithm was developed to “unduplicate” clients across data sources so that the population can be accurately analyzed. This has led to better understanding of the number of clients served, as well as detailed data on clients receiving services from multiple offices.

2 Create a common language for reporting
A platform was created where all data could be stored and analyzed. The new platform standardized the varied demographic data elements across data sources so that numbers could be reported using common descriptions.

3 Develop a method for trend analysis
Historical data now allows the means for trend analysis, resulting in improved, data-driven business decisions.

4 Client population education
“Unduplication” of clients, storage of historical data and use of reporting tools have facilitated the education of DHS staff on their client population, leading to more efficient use of limited funding. Analysis is conducted that shows which programs need expansion, the severe areas of need within the county, and the distribution of clients across and beyond DHS.

5 Intuitive reporting tools
Various reporting tools are used, which allow non-technical users the opportunity to see and manipulate the data without needing to know and fully understand the highly complex backbone of the DHS Data Warehouse. Authorized stakeholders make use of these tools to display the data in several formats, including aggregate unduplicated counts; client-specific, provider-specific and/or program-specific reports and displays; and data by geographic location. The tools used to access the data are:
- “Cognos cubes” display aggregate client and service data in cross tabs, tables or graphs. This format allows users to slice and dice the data for quick answers and also provides trend analysis in monthly, quarterly or yearly increments.

- “Cognos ReportNet” reports put client information in the hands of those who need it in two ways: through standardized reports available at the push of a button and through frameworks that allow users to easily drag and drop various data elements to build reports based on what they need.

- Complex ad hoc reports are generated using SQL queries straight from the DHS Data Warehouse.

- GIS maps developed with ArcMap technology allow users to view the data visually by geographic locations. GIS Maps have been used to match census data (such as poverty) to DHS service locations.

- DataVue is a newly developed application that allows users to search for known clients and view related current and historical data across all sources stored in the DHS Data Warehouse. The nature of the human services field is sometimes depicted through local news stories such as child endangerment or suicidal citizens. When these stories hit the news, DHS staff can immediately find a wealth of information about those involved through the use of DataVue.

What are the exceptional aspects of your project?
The DHS Data Warehouse is one of the few, if not the only one, of its kind at the local government level. Allegheny County has become a powerhouse for reporting due to great successes with its:
1 Method of “unduplicating” clients

2 Import of client data both internal and external to DHS

3 Import and storage of all historical client and service data

(see below)

How is it original?
The client matching algorithm, which is DHS' method of unduplicating clients, was originally designed, constructed and put into production with the launch of the Data Warehouse. Constant efforts have been made to continue to improve the process, but already, it has been extremely successful in giving DHS a true look at their client population. The logic has been reviewed and discussed with various people and organizations both within and outside the county and is now being built into a new, county wide application.

The unique ability of the DHS Data Warehouse to incorporate data from sources external to DHS creates a multi-dimensional picture of its clients and their cultural surroundings. Further, because the DHS Data Warehouse stores data internal and external to the Department, a more complete picture of all the social services DHS clients receive can be captured. This information facilitates coordinated service delivery among and beyond the DHS offices.

The storage of historical data and the ability to establish trends has become an important and distinguishing aspect of the DHS Data Warehouse. New data sources are asked to send to DHS all historical, as well as current data, to be imported so that trend analysis over time can be conducted using complete data sets. In order for this to happen, DHS takes great effort to integrate data from new data sources with already existing data. Data integration is achieved by updating the DHS Data Warehouse history with the new data in an ad hoc, month-by-month, one-source “data refresh.” The success of this data integration has been demonstrated by universities and research organizations now making special efforts to work with DHS because of the large amount of historical internal and external data available for examination.

Is it the first, the only, the best or the most effective application of its kind?   Only

Has your project achieved or exceeded its goals?   Exceeded

Is it fully operational?   Yes

How many people benefit from it?   200,000+

If possible, include an example of how the project has benefited a specific individual, enterprise or organization. Please include personal quotes from individuals who have directly benefited from your work.
Examples of success for the stakeholders listed in the benefits section include:
1 DHS officials in the Office of Children, Youth and Families (CYF) and the DHS Executive Office are able to identify and closely watch the growing numbers of youth transitioning out of the child welfare system and the possible ensuing serious issues that these youth face. Programs are currently being developed to address the issues facing these youth once they leave CYF. These issues are uncovered and tracked through the DHS Data Warehouse in the following ways:
- While out-of-home placement for younger children has decreased, the number of teenagers in out-of-home placement continues to increase. More critical, the number of children whose first out-of-home placement occurs when they are teenagers continues to increase.
- The youth aging out of CYF are increasingly receiving multi-system services such as mental health and drug and alcohol treatment.
- Almost 40% of the 18 to 25 year olds in the Allegheny County Jail were previously involved in the child welfare system.

2 Further research was done to look at the persons in the Allegheny County Jail in relation to DHS service utilization. Reports were run to determine:
- How many persons in the jail were receiving DHS services prior to being in jail and upon release.
- What types of services were being received by these groups of people.
- If persons unknown to DHS prior to being in jail began to receive DHS services upon release.

3 Research and educational institutions successfully conduct studies to assist in human service delivery nationwide using the DHS Data Warehouse:
- Carnegie Mellon University uses the Data Warehouse to support their research.

4 Consumers and families find needed services such as family support, day and residential services and prevention services, including after-school programs through HumanServices.Net.

How quickly has your targeted audience of users embraced your innovation? Or, how rapidly do you predict they will?
The target audience for the DHS Data Warehouse is DHS staff, clients and family members who receive DHS services, and employees and students of educational and research institutes. Convincing DHS staff of the value of the DHS Data Warehouse continues to be a work in progress. In the beginning, developing a culture of data-driven decision-making was a struggle. As the amount and quality of the data in the Warehouse has increased and tools such as Cognos ReportNet have been implemented to assist with easier access to reports, DHS staff have begun to appreciate how the DHS Data Warehouse is of value to them.

Clients and their families have long wanted data access that would help them to make better decisions for themselves and their relatives. This was apparent by high traffic on the public facing website,, as soon as services data was available to them.

Educational and research institutes quickly embraced the DHS Data Warehouse. The primary function of these agencies is to work with and analyze data and were excited that the Data Warehouse could so quickly and easily meet their requests. These entities continue to use the DHS Data Warehouse to further their work.

What were the most important obstacles that had to be overcome in order for your work to be successful? Technical problems? Resources? Expertise? Organizational problems?
Allegheny County has overcome, yet still faces, many challenges in developing and implementing widespread use of the DHS Data Warehouse. For many years, DHS staff did not have the advantage of using data to guide the decision-making process. Much time and effort has been put into shifting the culture away from “decision-making by what the gut tells you” to decision-making driven by reliable data. Further, much time and energy has been put into making the data collected and stored in the DHS Data Warehouse more reliable. In particular, the matching process that recognizes the same individual in separate information systems has been continuously analyzed and improved to provide more accurate client counts. The continuous improvements to the DHS Data Warehouse assist in the culture shift to data-driven decision-making.

Another difficulty facing the development of the DHS Data Warehouse is the reluctance of entities external to DHS to share their data. To offset this issue, data-sharing agreements are always developed and amended, and the external entities are offered non-confidential DHS data in exchange for their data.

Finally the DHS Data Warehouse is a victim of its own success. Data requests, requests for maps, and a desire for trend analysis and program evaluation continue to increase in frequency. Finding resources and funding to keep up with these requests and tasks continues to be a challenge.

Often the most innovative projects encounter the greatest resistance when they are originally proposed. If you had to fight for approval or funding, please provide a summary of the objections you faced and how you overcame them.
The biggest challenge to building and implementing the DHS Data Warehouse was securing the funding. Human Services dollars are generated from taxes and consequently are closely monitored and come with many restrictions. In 1997, a coalition of 12 local foundations created the Human Services Integration Fund (HSIF) to assist with the funding of DHS projects that fell outside of rigid government funding streams. In 1999, with the backing of HSIF, DHS announced an RFP to develop a computing architecture to support the business process of an integrated DHS. The key to this architecture was the DHS Data Warehouse. As mentioned in previous sections, another obstacle to overcome was to educate the primary users, DHS staff, about the value of the Data Warehouse. This resistance was addressed through many meetings, demonstrations and informal discussions that eventually enabled the users to feel comfortable with how to access and use the data.
Digital/Visual Materials
The Program welcomes nominees to submit digital and visual images with their Case Study. We are currently only accepting .gif, .jpg and .xls files that are 1MB or smaller. The submission of these materials is not required; however, please note that a maximum of three files will be accepted per nominee. These files will be added to the end of your Case Study and will be labeled as "Appendix 1", "Appendix 2" or "Appendix 3." Finally, feel free to reference these images in the text of your Case Study by specifically referring to them as "Appendix 1", "Appendix 2" or "Appendix 3."

Currently Uploaded Appendices:
No appendices currently uploaded.