Workforce Board P-20w Projects

1. Workforce Board P-20w Projects Karen Pyle, IT and Research Specialist ERDC ARRA SLDS Grant Conference May 21, 2014

2. Workforce Board Projects • Workforce Board Data Reporting System • Workforce Board Data Warehouse • Employment Data Handbook Total SLDS grant funding for all 3 projects = $179,506

3. Workforce Board Data Reporting System • Purpose: Replace outdated, costly, inefficient system for collecting individual student records from the nearly 500 postsecondary schools required to report data directly to the Workforce Board. • How: Contracted with vendor for design and development of web-based system. • When: July 2012 – June 2013 • Contractor cost: $95,000

4. Student Data Collected • 320 private vocational schools licensed by the Workforce Board. • 125 post-secondary schools on the state’s Eligible Training Provider List (www.careerbridge.wa.gov) required to report data directly to Workforce Board. • Name, address, DOB, SSN, demographics, enrollment info. • Annual report is due each Fall. • Reporting since 2001.

5. Old Process • School fills in Excel file. • School password-protects file and uploads to FTP site. OR • School mails hard copy of filled-in Excel file. • Workforce Board enters data into Access database. • Workforce Board staff conducts multi-stage data checking, cleaning and processing lasting several months. • Final files appended to an Access database table for final processing and preparation of analysis files.

6. New Process • Authorized user logs in to the online Workforce Board Data Reporting System. • User either uploads a student data file or enters data directly into the System data entry form. • System checks for errors before data can be saved. User makes corrections. • School certifies and submits report for review by Workforce Board. • Workforce Board staff does further checks and either accepts the data or ‘returns’ it to the school for edits. • Final data stored in Workforce Board data warehouse for use in research datasets.

7. Data Processing Workflow Before • Initial check in: • Add school ID to each row. • Check into the data report database—enter date received, format, reporting method, data contact person. • Do initial validity check for all programs reported, correct year, all required fields filled in. • Check for low number of students, no students reported or all SSNs missing. • Contact school if problems. Re-check corrected file. • Data entry of hard copy forms. • Second level review: • Check data formats and codes. • Check for errors (start after exit, missing exit dates, missing start dates). • Check for invalid SSNs, or SSNs all missing. • Date of birth is out of valid range. • Duplicate records. • Check if continuing students from previous year are included. • Contact school if problems. • Append file to master database and assign CIP codes and program IDs.

8. Data Processing Workflow After • Initial check in: • Add school ID to each row. • Check into the data report database—enter date received, format, reporting method, data contact person. • Do initial validity check for all programs reported, correct year, all required fields filled in. • Check for low number of students, no students reported or all SSNs missing. • Contact school if problems. Re-check corrected file. • Data entry of hard copy forms. • Second level review: • Check data formats and codes. • Check for errors (start after exit, missing exit dates, missing start dates). • SSN field-check for invalid SSNs, or SSNs all missing. • Date of birth is out of valid range. • Duplicate records. • Check if continuing students from previous year are included. • Contact school if problems. • Append file to master database and assign CIP codes and program IDs

9. Benefits of the Data Reporting System • Improved data security • Real time error checking • Data Quality • Cost and time savings • Extensible application and database model

10. Challenges • Keeping to the budget by repeatedly prioritizing features. • Allowing time for user testing. • Database design was time-consuming. • Designing for all levels of technological capability. • Data conversion – loading data from multiple systems.

11. Workforce Board Data Warehouse • Upgrade current system for management of research and analytic data, using SQL Server. • standardization • efficiency of processing • security • Goals: • Increase the timeliness of annual and biennial reports and free up capacity for other research. • Improve data quality of data transferred between the Workforce Board and other agencies (P-20).

12. Scope of Work • Automate data transformations and analysis dataset creation currently done using many steps with Excel, Access and SAS. • Develop demonstrations of automated reports and data cubes. • Teach research team how to access datasets in SQL Server using familiar tools such as SAS and Excel.

13. Data Warehouse Project Details • Contractor cost: • $28,985 from SLDS plus $15,800 from another grant • Conducted spring 2013 through September 2013 • SQL Server 2012

14. Project Results • ETL processes for 11 datasets. • Demonstration of SQL Server Reporting Services for two different types of reports. • Demonstration of Analysis Services - data cube for PVS participant data. • Training sessions for Workforce Board staff. • Detailed instruction manual.

15. Employment Data Handbook • Purpose: A guide for using employment data from a state unemployment insurance system with P-20 longitudinal data. • Covers: possible uses, data sources and fields, how to get the data, data sharing agreements, examples of research using UI wage data. • Published by ERDC in July 2012 and available here: http://www.erdc.wa.gov/briefs/pdf/EmploymentDataHandbook_v1.pdf

16. Employment Data Handbook:Workforce Board Role • The 1991 statute establishing the Workforce Board mandated the creation of standardized methods and measures for assessing the outcomes of workforce program participants. • Standards developed over two years by a workgroup. • Collaborative inter-agency system of file transfers, using UI wage record matching, in use since 1998. • SLDS project goal: Document the standard methods and measures for using wage records for outcomes assessment used by the Workforce Board and its partners, to supplement the Employment Data Handbook published by ERDC.

17. Questions? Contact me: Karen Pyle Workforce Training & Education Coordinating Board Karen.pyle@wtb.wa.gov 360-709-4634