Using Data Warehouses to Support TANF Program Objectives
Click here to download the MS Word (124.0 KB)
PDF (195.0 KB)
June 2004
David Ivy, Ruth Seale
Prepared for:
State Information Technology Consortium
U.S. Department of Health and Human Services
Administration for Children and Families
Contract: GS35F5365H 03Y00345601D
2214 Rock Hill Road
Herndon , Virginia 20170-4227
Acknowledgments
The Project Team would like to recognize the many individuals in State Human Services organizations, Information Technology Divisions, and other Government organizations that provided information for this report. The team is grateful to participants representing Florida , Minnesota , North Carolina , and Wisconsin . This report was made possible through their active participation and generous sharing of knowledge and experiences.
In addition to the participants, other individuals also provided assistance:
Sponsors within the U.S. Department of Health and Human Services, Administration for Children and Families for providing overall aid and direction throughout this project.
Extended Project Team members who helped organize and conduct the data collection sessions and made many contributions to this report.
Members of the Temporary Assistance for Needy Families (TANF) Technical Advisory Group who provided direction and assistance to identify organizations to include in the report quick look and provided insight on issues to explore.
The Project Team also is grateful for the readers and users of this report. You are encouraged to contact statesystems@acf.hhs.gov with any comments or suggestions.
Executive Summary
The creation of automated computer systems, which provided eligibility determination, tracking, and case management for Aid to Families with Dependent Children (AFDC) in the mid-1980s was the initial step to fully automate the critical business functions. Since that time, the addition of work programs, child support enforcement systems, and State Automated Child Welfare Information Systems (SACWIS), have intensified the need to retrieve data across systems. The manner in which data was collected and reported in the older systems leads States toward utilization of data warehousing technologies.
Many of the eligibility systems rely on technologies that are several decades old and difficult to adapt to Agency needs for reporting, trending, and forecasting. Data warehousing technology offers States a potential solution to address these needs. This technology automates efficiencies by capturing data, which exists across programs; therefore, enabling all staff from management to case managers the ability to obtain significant insight into cases that contain information on clients that span across the programs within the State Human Services Agency.
The use of data warehouse technology to support (TANF) and other Human Services Programs is increasing. Four States currently utilizing this technology were interviewed for this project. This document is a consolidation and summarization of their experiences. It is through this documentation that insight is gained through shared experiences and lessons learned.
The overall purpose of the report is to assist States considering data warehousing technologies in understanding the high-level objectives and outcomes of States that have already implemented the technologies.
Discussions were held during the spring of 2004 with program and technical representatives from Florida , Michigan , North Carolina , and Wisconsin . The report discusses the factors and influences of data warehousing in the State Human Services Agency in the following categories:
- Overall Lessons Learned. A summary of frequently cited lessons learned essential to the success of a data warehouse approach.
- Objectives. Primary reasons States undertook the data warehouse effort.
- Benefits. Most frequently noted benefits from using a data warehouse.
- Business Perspective and Challenges. Key experiences and challenges relating to the business use of the data warehouse.
- Technical Perspective and Challenges. Key experiences and challenges facing technical staff in development and support of a data warehouse.
- Timeframes. Changes relating to the time to define, build, test, deploy, and evolve the data warehouse.
- Cost. General observations relating to factors affecting cost.
2.4 Business Perspective and Challenges
2.5 Technical Perspective and Challenges
APPENDIX B: OBJECTIVES AND BENEFITS TABLE
Introduction
Background
Traditionally, a State’s TANF (e.g., eligibility) online transaction processing systems (OLTP) can collect and manage vast amounts of data. Each system maintains and enforces the complex relationships between demographic, benefit, service, financial, and management data for millions of current and closed cases, encompassing a period of many years. A significant number of these systems rely on technologies several decades old and are not easily adapted to satisfying new information needs such as trending and forecasting.
Prior to the advent of data warehousing approximately ten years ago, there was limited sharing or coordination of information between online systems. Within a State’s Human Services Agency a patchwork of technology platforms can exist that may each serve a different Human Services Program; these are commonly referred to as “silos” of information technology or data. Data warehouse technology automates efficiencies by capturing data that exists across programs, enabling case managers to start with a base of information and continue to build upon that knowledge. In addition, having access to this information provides the case manager with the data required to target specific areas that better meets the needs of the family. Delivering comprehensive, customer-focused service and financial assistance requires insight within and across programs. The needed information is not easily extracted from OLTP systems that are optimized to support day-to-day program workflow and operational management. Crafting reports that answer strategic or cross-program management questions to run on these systems is often technically complex, adds significant processing burden to already limited computer resources, and takes significantly more effort and time to implement than is available.
To provide a capability to make timely and objective (data-driven) decisions about their programs, many States are turning to the use of data warehousing technologies. These technologies, whether implemented as a data warehouse or a specialized data mart , provide the organizations the ability to extract, organize, and analyze the massive amounts of program data their online systems generate. The potential benefits from the application of data warehouse technologies are large, helping to generate actionable data when needed. Realizing these benefits however, requires a commitment to invest in the technology and organize around its use, hence introducing additional risk factors that must be managed.
Purpose and Scope
The use of data warehouse technology to support TANF and other Human Services Programs is increasing. This report previews the utilization of data warehouse technology in four States. This document consolidates and summarizes their experiences, providing insight into lessons learned. This document helps State IT and program staff leverage their experiences and incorporates their knowledge into their efforts - whether planning or developing a new data warehouse or improving existing ones.
The Administration for Children and Families, an agency of the U.S. Department of Health and Human Services, is sponsoring this project. Although data warehouses by their very nature tend to cut across organizational and program boundaries, the federal TANF program is the primary focus.
Data Collection Approach
Discussions were held in the Spring 2004 timeframe with program and technical representatives from States that have implemented a data warehouse. Florida , Michigan , North Carolina , and Wisconsin participated in our discussions. Information from those discussions is consolidated and summarized in this report. The discussion focused on key motivations, benefits, and program and technical lessons learned. Individuals listed in Appendix A may be contacted to gain further insight.
Results
This section describes key lessons learned and experiences regarding the planning, development, and use of data warehouse technologies to support TANF programs. For purposes of illustration, the information is categorized into objectives, benefits, business perspective and challenges, and technical perspective and challenges .
Overall Lessons Learned
The following list describes some frequently cited essential lessons learned for the success of a data warehousing approach:
Cross-program application. The participating States typically used data warehouses to collect and report data across various Human Services programs (e.g., TANF, Child Support Enforcement, Child Welfare, Food Stamps, and Medicaid). The need to satisfy cross-program queries was one of the main drivers to building the data warehouse.
Coordinating across programs . Sometimes, the States used data-sharing agreements or memorandums of understanding (MOUs) to benefit all users of the data warehouse and enable collaboration and cooperation across participating programs. These agreements served as the principal directive or governance document to coordinate the providing and sharing of data in order to meet the requirements and expectations of all participating parties.
Agreements spelled out retention and maintenance of confidential data, often health-related, and clarified use and access of that data (e.g., security access levels and permissions). Agreements will be frequently updated as the data warehouse functionality and scope changes, such as adding programs or new subject areas.
Start small and increment. Most States started their data warehousing effort by focusing on one or two programs and added additional programs in an incremental fashion. Keeping the solution generic and looking for simplicity, help avoid becoming overwhelmed by too much detail. Initial increments were scoped to provide access to end users within a short timeframe (6 months to 1 year).
Security/privacy/confidentiality/access. Consolidating data from several source systems or programs naturally raises privacy and confidentiality concerns. To protect sensitive data, the States implemented security processes and mechanisms in the data warehouses to ensure only valid users had access to the data. User accounts established for the data warehouse system (separate from the feeder systems) were the basis of authenticating and controlling user access. Generally, aggregate data was available Statewide, while detailed confidential information was available only to individuals associated with the source program cases. The user’s role or function in the source program was a consideration for determining which information they are able to view in the warehouse.
Growing user population and needs . The ability of the data warehouse to provide data in a timely manner and cross-program views of information led to an increased demand for access to the data warehouse and to user requests for more data and reports. The data warehouse user population includes individuals within the Agency (decision makers, County managers, case managers) and outside the Agency (contractor staff). Expanding access to different types of users can lead to additional requests for sensitive data; States need to define appropriate ways that users access the data.
Measuring program performance . States with County-administered programs use the data warehouse to measure performance across Counties, providing reports to State and County managers. In one instance, the data warehouse was able to help apply proper credit for client participation when other methods were unable to track the data adequately. This resulted in improved County participation measures.
Ensuring understanding of reported data . The data warehouse supports heterogeneous users with different backgrounds and knowledge; as a result, training may be needed for users to understand the data fully so they can define and execute meaningful reports and interpret them correctly. Subject matter experts and skilled technical staff with business understanding are necessary to help form and filter queries. Metadata to describe the data element is necessary. Misunderstanding the data/reports is one of the most common problems with new data warehouse implementations.
Importance of training. When transitioning to a data warehouse approach, new users may be reluctant to embrace the new tools and reports because they are more comfortable with the old software tools and reports. Orientation, training, and assistance are necessary to help users apply the new tools and understand the potential of the data and reports.
Data warehouse team. Staff supporting the design and operation of the data warehouse should include stakeholders across State and County organizations. These stakeholders should become involved early in the definition of the data warehouse as well as throughout development, testing, implementation, and ongoing maintenance.
Understand the real requirements. Often, organizations dictate an elaborate data warehouse solution when a simple data mart is really what is needed. States must take into account the additional costs of a data warehouse as compared to a data mart. These additional costs translate across the information technology (IT) spectrum ¾ hardware, software, services and programmatic/IT staff.
Objectives
States we talked to pursuing a data warehouse have various reasons for undertaking the effort. A few of the principle reasons are:
Facilitate reporting . Information from the data warehouse can provide States the ability to meet both State and Federal reporting requirements by consolidating reporting and tracking across otherwise separate systems. The inherent speed and ease of reporting over that of the existing systems are significant contributing factors that motivated States to pursue a data warehousing approach. Users can generate reports that span multiple programs from predefined queries on a systematic basis without having to generate the data separately from stovepipe systems and then combine it into one report.
Reduce programmer support . The tools to query and analyze data from the data warehouse can help to reduce the need for programmers to write programs and run jobs to extract and prepare reports directly from the source systems. Technical personnel may not have a strong understanding of the data outcomes envisioned in the report requested; additional coordination may be needed between a non technical person needing the report and the person executing the report.
The data warehouse’s enhanced query tools and simplified user interfaces can provide power users direct access to custom or ad hoc reports without the need for programmer support. Staff can write predefined reports once and make them available to a broad audience without having to re-implement them on each source system.
Improve turnaround time . Simplifying report definition and generation can reduce the time between the need for a report and its availability. The ability to generate on-demand reports by non-technical users allows staff to resolve legislative or management inquiries quickly. States reported that the quick turnaround time is a significant benefit.
Support operational performance and quality goals . The data warehouse can provide insight for case managers and supervisors into information regarding customer case status, work requirements, and activities. If the data in the warehouse is maintained and synchronized with the data from the different source systems (e.g., the system that contains the eligibility information and the system that contains the work data), the resulting information and tools that the data warehouse provides can be invaluable to case managers by assisting them in meeting their program outcomes. Since TANF applications must be processed in a timely manner, both supervisors and case managers can use the data warehouse to monitor their own activities and performance. This monitoring activity helps ensure that applications are processed and that customers receive their benefits on time. For example, supervisors can use the data warehouse tools to identify cases where applications are still pending. In addition, supervisors also can use the information to identify how many applications were approved or denied, or areas where case managers may need additional training.
Data warehousing provides the case manager and supervisor the ability to view reports in the manner most valuable to their perspectives. Case managers and supervisors can define reports that would otherwise require consolidating or combining data from numerous reports of the online systems.
Track customer cases. Many States supporting TANF have one or more computer systems to deliver and track services. Often, these systems are composed of a separate TANF eligibility system and a system that tracks work activities. Data warehousing allows for tracking customer cases across divergent systems. Types of information include months of benefits used, work participation rates, reasons clients leave TANF, and job placement data.
Provide County access to data . Typical State Human Services Agency provides periodic, predefined reports generated at the State level, which are then physically or electronically distributed to remote counties. Depending on the State, the process to distribute paper reports throughout the Counties can take up to one week. The lag time may cause reports to be obsolete when received. The data warehouse, if remotely accessible, allows case managers or supervisors to generate their own predefined and ad hoc reports when needed. Access to this data also allows decision makers the ability to compare performance between different Counties or offices located within a County. Information such as caseload count by case managers helps the decision makers determine when they should redistribute resources across Counties.
Increase data access. Agencies can make the data contained in the warehouse available to others outside the Agency or program. Permissions and accesses can be granted and managed outside the operational system. Current generation, web-based user platforms can be used to access the data warehouse, rather than relying on legacy devices and protocol. Use of the data warehouse can be extended to policy researchers and service partners as applicable, without providing direct access to the operational systems.
Benefits
States reported the following major benefits from using their data warehouse:
Increased return on investment (ROI). Quantitative ROI data is sensitive and not generally available. The State noted many qualitative returns and examples of financial gain attributed to the data warehouse. Sample savings from using a data warehouse include the following:
- Better tracking and reporting of TANF information resulted in a Federal TANF bonus payment of approximately $13,000,000.
- Using the data warehouse to identify individuals that received State-funded medical assistance and determining that they were eligible for Federally funded medical assistance, resulted in a savings of $6,000,000.
- Isolating Food Stamp error-prone cases and training case managers resulted in a savings of $800,000 in quality assurance penalties.
- Reduction in the amount of postage to mail reports resulted in funds that could be available to the data warehouse operations.
Identification and understanding of trends . The software query, analysis, and reporting tools available from the data warehouse allowed States to perform trend analysis across many years of data with increased speed and accuracy. Several participating States used their data warehouse to develop synthetic policy changes and produce impact data. The ability to submit what if scenarios to policy makers enabled a more accurate analysis of the policy changes as if they were actually enacted. The ability to predict the impact and outcome of policy decisions was a valuable enhancement for example, identifying how many clients would be impacted if the Agency changed or modified requirements for a program.
Monitoring quality performance. Workers across all levels of the organization were able to use the data warehouse capabilities for reporting and use data that was previously unavailable or difficult to obtain. This capability provided statistical analysis to management in directing and monitoring individual case managers, which may result in performance improvements.
Cost savings . Electronic reports viewable by end users has allowed some States to save significantly by reducing the costs to produce and distribute paper-based reports (e.g., mailing costs). Because business users have access to the reporting tools, they can generate their own reports, avoiding the cost of using programmers to generate the reports.
Better technical resource management. Business users, empowered with user-friendly query and reporting tools, were better able to satisfy their own reporting needs. This allows the skilled technical resources to devote more time to detailed technical projects instead of generating reports.
Integration of service delivery and identification of services gaps . In trying to improve the overall well being of the family, the data warehouse allows decision makers to identify service gaps. Bridging gaps can ultimately help the family achieve self sufficiency; for example, identifying cases with small children, correlating the availability of child care, and providing child care services can help the custodial parent focus their efforts on becoming gainfully employed instead of worrying about child care. In addition, case managers can use the data warehouse to look across the programs for which the family is currently eligible and identify any additional services or programs that the family might need. These additional services that the family may be eligible for but is not receiving can be an important stepping-stone to help the families achieve self-sufficiency.
Redistribution of case loads . Some participating States indicated they used the data warehouse to help redistribute caseloads across workers in a more equitable manner.
Performance measures . The ability of the data warehouse to synchronize customer activities and work activities across eligibility and work systems allowed States to better quantify participation rates and evaluate performance against TANF block grant goals.
Work process performance . Some participating States indicated that the data warehouse provides a means to monitor performance of workers and aids in assessing performance of work functions at State and County field offices.
Improvement in data quality. The overall quality of data in both the data warehouse and source system often improved as a result of using the data warehouse. Data anomalies in the source systems became apparent when data was extracted and transformed into a consistent view in the data warehouse, for example, differences in birthdates, social security numbers, and addresses across multiple systems. Data quality in the source systems can be improved by implementing additional edits when inputting data that is prone to mistakes. As a side benefit, a focus on data quality made case managers more aware of its importance, and they tended to exercise additional care when entering data into the source systems.
Federal and State reporting . The data warehouse provided States the ability to produce timely and accurate reports to meet compliance requirements. Some participating States noted that significant Federal penalties have been avoided as a result of their ability to report required data timely and accurately. The data warehouse reduced the time and complexity needed to file reports based on the older legacy systems.
Business Perspective and Challenges
Key experiences and challenges relating to the business use of the data warehouse include:
Privacy. Satisfying the requirement for privacy and the need for decision makers to access relevant data in sufficient detail are ongoing concerns. To balance these competing objectives, States used the techniques of aggregating information and restricting access based on user roles and program affiliation. Sometimes, the States used governance documents such as MOUs to ensure procedural control over the release and dissemination of the data from the source systems.
Understanding the data. The data warehouse tools provide power users the ability to easily formulate and execute complex ad hoc queries that can result in grouping or summarizing sets of data not previously available to staff. Power users may need additional training in order to interpret the meaning behind the data and the results correctly. Several participating States provided access to business analysts, review boards, committees, or specialized reviewers to validate predefined or ad hoc reports on behalf of users. States used metadata or help text associated with the data elements or query to assist users in understanding the data and query results.
Comprehending the data requires an in-depth understanding of the source programs and the source system’s data elements; finding technical support staff that understands the programs is difficult. One approach that works well is to train technically inclined program staff on the data warehouse structure and tools so they can act as subject matter experts and assist other users in developing queries and subsequent reports.
Realistic data quality . The data in the warehouse is only as good as the data entered into the source system; however, not all data is of equal importance, nor does it have to be perfect to be of value to decision making. One approach is to complete a risk-based assessment to determine the impact of cleaning the data versus the cost and time to do so. Staff can make tradeoffs during the design phase and as well as when new data is added. During data extraction, preparation, and loading, processes can use predefined indicators and thresholds to determine whether to include the data or use remedial actions. An example is setting a 1% failure threshold during the extraction transformation and loading (ETL) processing. If this threshold is exceeded, then staff must take action to correct it or provide a description to help users understand deficiencies or anomalies. Some participating States used metadata to facilitate data element interpretation in the data warehouse.
Maintenance. Maintenance of the data warehouse includes scheduled updates for incoming data from source systems and purging of data. Because the timeframes and frequency of updating the data have a direct impact on users of the data warehouse, States should plan them carefully.
Technical Perspective and Challenges
States implemented different technical approaches to providing a data warehousing capability. Some key experiences and challenges include:
Managing processing resource loads. To satisfy most user needs, keep processing loads manageable and predictable across a diverse set of users, pre-defined queries, and a list of reports that are available. Limited groups of power users can have access to ad hoc queries.
Tools. Usability of analysis and reporting tools is a significant factor for end-user productivity. Participating States noted that graphical tools to build queries were useful. The ability to export data to user office tools and spreadsheets is also a benefit. A non-technical, end-user-friendly interface is important to minimize business user training and orientation requirements.
Diverse user population . Users of the data warehouse can include program administrators, supervisors, case managers, and research and evaluation staff (university studies). They may be on site or remote. Some State Human Services Agencies implemented thick client solutions supporting a rich user interface (analytical aids and ad hoc queries) while also supporting thin client solutions (web-browser-based) for predefined reports.
Blended teams. Technical staff may have limited functional background to understand the nuances of the programs to define meaningful and efficient queries (e.g., determining what type of information is based on a case versus an individual). Combining business experts with technical experts to produce accurate and timely reports seemed to work best. User involvement from all levels was valuable in data mapping, metadata definition, redefinition of security rules, user acceptance testing, and end-user training.
Evolutionary process. The typical data warehouse started with an initial deployment of data from a single-source system. As operations matured and staff began to understand how to use the data warehouse and its value, participating States added additional source systems. The need to start simple and iterate (often referred to as a phased implementation approach) was a common approach across the participating States.
Data extraction process. Depending on the scope and numbers of source systems providing information to the data warehouse, the actions and processes for extracting, cleansing, and transforming the data may result in significant efforts. Consideration should be given to the initial process of loading the data warehouse as well as ongoing cycles that provide updates from the source systems. Frequency of data extraction and data warehouse updates often were driven by the time requirements and tools to extract, cleanse, and load the data. Several States we talked to adapted the completereload approach to updating the data warehouse. For example, one Agency extracts all the data warehouse information from the operational sources and loads the data warehouse from scratch, on a weekly basis. A caveat of this approach, depending on the amount of data being loaded, could develop into significant staff and computer time to complete.
Development and implementation team. States generally relied on a contractor development staff augmented with State expertise to plan and develop the initial data warehouse installation. Once the data warehouse is operational, contractor support staff were usually reduced or phased out, and State staff supported daily operations.
Change management. From the technical perspective, coordination of changes in the operations of the data warehouse and modifications resulting from data input from the source systems required detailed and manageable processes. It is important not to underestimate the clarity and enforceability of the change management process. Some of the participating States used steering/oversight committees to provide governance for this effort.
Communications. From a technical perspective, communications are critical to the success of the data warehouse operation. Three major areas have to be managed with respect to communications; the data warehouse technical operations, data changes from the source systems, and user awareness of the data changes. The participating States recommended enforced processes for communicating to all impacted parties to ensure that data changes in the source systems were anticipated and handled in the data warehouse and that any significant changes were communicated in a timely manner to all users.
Timeframes
The following items relate to the time to define, build, test, deploy, and evolve a data warehouse:
Start-up. The typical time to get a data warehouse designed, loaded, and in operation was generally less than 1 year. Most States needed approximately 6 months for the definition and planning of the project, including the advanced planning document or other State and Federal authorizations, and about 6 months to build, and fully deploy the initial release.
Iterative approach. Most States pursued an iterative approach where the first release would focus on a specific program such as Medicaid, and subsequent releases would include additional functional program areas as the processes matured and they gained operational experience.
Time drivers. The participating States reflected that the time drivers for the initial development and implementation were from the need to perform some specific task, whether reporting to meet a particular business need or to perform some specific task. Typically, the schedule and time expectations were driven by the initial need to solve a problem.
Costs
Cost data is sensitive and must be interpreted within the unique environment of each State. Some general observations about factors affecting cost include:
Database engines. The cost of the database engines is one of the most significant cost factors. To manage this cost necessitates sizing the infrastructure to meet user performance demands and growth projections while minimizing licensing costs (e.g., multiple CPUs). In one instance, to reduce licensing costs, a State considered changing database products and using more servers with less CPUs as opposed to multiple CPU servers.
Funding. Since most data warehouses support programs in separate funding streams, the reimbursement and cost allocation requirements are a significant factor in planning, developing, operating, and improving the data warehouse. States discussed using an innovation approach as a springboard for the design of the data warehouse; one participating State defined and implemented a data warehouse to support one program’s study project and used that instance as the first generation for other programs to build on.
States can apply the cost savings from reduction in technical staff to support queries, reduction in paper or mailing costs, or other cost avoidance measures to operations. States can recover operational costs from programs based on usage, infrastructure costs or size, and participation level in the data warehouse. Additionally, States can charge development costs back to specific programs when reports or data changes are made for their program data (such as initially loading data into the warehouse).
Initial infrastructure. The cost for the hardware, software, and personnel (development) support were the main cost considerations. States considered the data warehouse a capital investment for new areas as opposed to ongoing maintenance. The State’s procurement processes and any applicable Federal approvals must be followed.
Contact the individuals below for additional insight into their data warehouse efforts.
|
Florida |
|
|
|
Kim Brock |
|
|
Bill Hudgens |
|
Minnesota |
|
|
|
Nina A. Terhaar |
|
North Carolina |
|
|
|
Hank Bowers |
|
|
Mike Hoenes |
|
|
Robin Register |
|
Wisconsin |
|
|
|
Ken Funck |
Objectives |
Benefits |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
APPENDIX B: OBJECTIVES AND BENEFITS TABLE
Some of the benefits reported by States satisfied several objectives.
http://www.hhs.gov/ The home page for the United States Department of Health and Human Services
|
http://www.acf.hhs.gov/ - The home page for the Administration for Children and Families
|
http://www.acf.hhs.gov/nhsitrc/ - The home page for the National Human Services Information Technology Resource Center
|
The term data warehouse generally implies the storage of data to cover the full range of program intelligence available to a State Human Services Agency to meet Agency-wide information needs. A term data mart generally implies a lower level capability, where a Program can use specific data from the warehouse to suite its own program specific analysis. Within this report, the term data warehouse is used to encompass both concepts with no specific distinction made between the two. Data warehouse pertains to any capability where data warehouse technology is used to extract, store, query, or otherwise access program related data independent of the OLTP.