Until now, EDFacts has required a state-by-state response. A proven opportunity is available for all state education agencies (SEAs) now. The EDFacts Shared State Solution (ES3) maximizes shared components to reduce duplicate effort, yet still accommodates unique SEA configurations and adaptability.
ES3 includes:
- Updated SQL Server submission tables formatted in the EDFacts submission file specification,
- Updated SQL Server Integration Services (SSIS) extract-transform-load (ETL) processes to create submission files from the EDFacts submission tables,
- Standardized SQL Server staging tables for either unit records or aggregate records, aligned with the Common Education Data Standards (CEDS),
- SSIS ETL processes to convert from local codes and formats to the federal standards and load the submission tables from the staging data,
- SQL Server Reporting Services (SSRS) validation reports against both the staging and submission tables so SEAs can review their data prior to submission,
- All ETL processes with full audit logging and email notification,
- A centralized web front-end for triggering the ETL and accessing validation and management reports, and
- Customized ETL to load the staging tables from the SEA’s existing data sources.
The content for the top seven bullets is common for all SEAs. ES3 means this common content does not need to be developed 59 times for the states and extra-state jurisdictions. The last bullet, customized ETL from SEA data sources to ES3 staging databases, is unique to each agency. For an SEA, the ETL will be built from your existing authoritative data sources to the up-to-date cycle of EDFacts specifications.
The first SEAs to contribute code to the components of the EDFacts Shared State Solution were Idaho and Missouri. The South Dakota Department of Education, Tennessee Department of Education, Maine Department of Education, and U.S. Virgin Islands Department of Education have also begun their contracts. Others for whom ESP has prepared data for submission have added concepts to the architecture (e.g., Delaware, North Carolina, Louisiana, New Hampshire, District of Columbia, and Georgia). As other SEA partners use ES3, their contributions will enhance the solution, especially in the area of reports.
From this core, ESP has committed to be managing partner for all states joining an SEA Partner Association to share the maintenance of requirements, business rules, and the data model. ESP will manage documentation and sharing of enhancements such as reports.
Because the solution is founded on these multiple SEAs’ ideas and processes, ES3 is portable across agencies; and is provided with a no-fee license. This proposal provides for the initial documentation of data sources, ETL into ES3, and production of an annual cycle of submission files. In their second cycle, an SEA may choose to maintain the ETL or contract for services for assistance. An SEA may choose to maintain the data model and data mart tables to be up-to-date with USED’s EDFacts specifications. Alternatively, the SEA may either contract for those services, or join the Partnership Association to receive those updates.
ES3 Newsletter July 2015 Insert