|
|
|
|
|
COUNTY OF SAN MATEOInter-Departmental Correspondence |
Human Services Agency |
| |
DATE: |
November 8, 2002 |
| |
BOARD MEETING DATE: |
December 03, 2002 |
| |
TO: |
Honorable Board of Supervisors |
FROM: |
Maureen D. Borland, Director, Human Services Agency |
| |
Kathryn Ciamarro, Director of Automation |
| |
|
SUBJECT: |
Presentation of the SHARP Data Warehouse |
| |
Background |
The San Mateo Human Services Analytical Reporting Project (SHARP) has been structured so that the work can be performed iteratively. Phase I of the project focused on establishing the project infrastructure and providing a "quick win" for the Agency to be able to answer a series of basic business questions developed by their Outcome Based Management Group. In later phases new source systems and additional data were added to the Data Warehouse. |
| |
While Phase I integrated data from the Case Data System (CDS) and the Child Welfare System (CWS), Phase II expanded on Phase I by adding additional data from CDS and CWS and integrated another system in to the data warehouse, the Job Training and Automation System (JTA). Phase III of the data warehouse expanded Phase II by answering additional business questions and integrating additional data for the existing systems as well as adding two additional source systems - the Gain Information System (GIS) and Service Management & Access Resource Tracking (SMART) System. At this point, the County staff fully supports the environment. |
| |
Phase IV will extend the Data Warehouse Project by adding Geographical Information Systems (GIS), which will help the Human Services Agency's staff display, explore, and understand the information collected on a daily basis. This tool will guide us into making better-informed decisions, increase our ability to analyze and use information, and ultimately improve program effectiveness. |
| |
Discussion: |
Typically, the development of a data warehouse of the size and scope of SHARP, takes several years to complete. |
| |
As the following table illustrates, there has been a significant decrease in the cost of development over the duration of the project. This is due primarily to the fact that County staff has assumed increased responsibility for all aspects of the project as it has progressed. There will be additional phases of the project as data from additional systems is introduced to the warehouse; however, it is anticipated that this development will take place using internal resources only. |
| |
The Geographic Information System (GIS) is a new technology that is being used in conjunction with the data warehouse to further enhance data reporting. While we in HSA hope to become fully trained and self-reliant in regard to further development of this tool, it is not yet known if consulting services will be required after this initial phase is in production. |
| |
Project Phases: Phase 1 Phase 2 Phase 3 GIS Project Dates: 02/01 to 06/01 07/01 to 12/01 01/02 to 08/02 10/02 to 06/03 Accomplishments: 2 systems - CDS and CWS 1 system - JTA 2 systems - SMART and GIS Project underway to plot client information in geographical maps. 13 OBM reports Add'l data from CDS and CWS Add'l data from CDS, CWS, JTA 11 OBM reports 15 OBM reports 25 add'l reports 27 add'l reports Cost: $1,087,308 $685,000 $430,090 $192,640 (includes $600,00 for Consulting Svcs and $487,308 for hardware and software) Total Cost To Date: $2,395,038 |
| |
The SHARP project has fulfilled the following requirement for the County: |
| |
Business: |
|
Compiled data to provide answers to critical OBM measures from multiple agency source systems |
|
Increased flexibility and efficiency of web access to critical agency data via the department's intranet, including data that aids in staffing and regular reporting to the State |
|
The ability to view a client across multiple source systems through a matching logic that compares clients across the agency's source systems |
|
The ability to have a single and trusted version of data across the agency |
| |
Technology: |
|
Provided a design for a scalable, flexible database environment for current use and enterprise expansion |
|
Facilitated development of ETL processes that supported both the initial load of data and future data loads |
|
Facilitated development of warehouse testing and validation processes that promoted confidence in warehouse query results |
|
Provide a knowledge transfer of data warehousing design and development techniques to HSA and ISD staff that now fully support the environment |
| |
|
Key Deliverables: |
|
Monthly Status Reports |
|
Business Requirements Definition Document |
|
Data Architecture |
|
Data Access and Transformation Strategy |
|
Completed ETL Processes & Test Data Load |
|
Business Objects Universes |
|
Data Validation Results |
|
System & User Acceptance Test Results |
|
Completed Data Load |
|
Training Strategy Document |
|
WebI 101 Training Materials |
|
WebI 201 Training Materials |
|
Online Training Modules |
| |
|
Vision Alignment |
The SHARP Data Warehouse project keep the commitment of: Responsive, effective, collaborative government and goal number 21: County employees understand, support and integrate the County vision and goals into the delivery of services. This Agreement contributes to this commitment and goal since Deloitte Consulting will assist the Human Services Agency (HSA) in developing systems to collect and report data for OBM outcome measures. Outcome measure data demonstrates HSA's success in contributing to the County vision and goals, and will be published and distributed to HSA staff and the Agency's community partners for use in programs and budget planning. |