Ad Hoc Reports on the Fly - Dare to Dream It!

1. Ad Hoc Reports on the Fly- Dare to Dream It! Virginia Department of Education Office of Educational Information Management 2012 MIS Conference

2. Introductions • Susan Williams • Manager, Data Management and Accountability Systems • Scott Walden • Team Lead, State and Federal Reporting

3. Public Reporting • Static Excel Reports • Dynamic Report Cards • Customized Student Achievement Reports • Dynamic Excel Reports • Ad Hoc and FOIA Requests

4. Static Excel Reports + Easy to replicate + Trend data – Personal attention needed – Pre-defined layout – Multiple ad hoc requests for similar data www.doe.virginia.gov» Statistics & Reports » Enrollment & Demographics » Fall Membership » Archive Data

5. Dynamic School Report Cards + 3 years of data + PDF or Excel format + Updated by triggers in the database – Pre-defined layout – Only one school at a time – Multiple ad hoc requests www.doe.virginia.gov» School Report Card

6. Customized Student Achievement + Multiple years of data + Excel format + Auto update + User selected records/fields – No aggregation/limited fields – Multiple ad hoc requests www.doe.virginia.gov» School Report Card » Customized Student Achievement Reports

7. Dynamic Excel Reports + Trend data + Auto update + Subgroup data + All schools in one file – Pre-defined layout – Multiple ad hoc requests www.doe.virginia.gov» Statistics & Reports » Graduation, Completion & Dropout Data »Virginia Cohort Reports

8. Ad Hoc and FOIA requests

9. A New Direction • Defined outcome of SLDS grant • High expectations • Customized tables  • Ready made charts  • Data on a map  • Reduce ad hoc reports   

10. A Tool for Every Job • Commonwealth resources • Sought technical expertise within state agencies • COTS • Product that was designed to be customized • Easy to learn • Develop expertise within DOE/state agencies

11. Access to the Data • Protect the Production Database • Security • Performance • Created a “Reporting Database” • Long term solution for all public reporting • Database tools keep both databases in sync

12. Timeline • Phase 1 included • Fall Membership • Special Education Enrollment • Annual Graduates and Completers • Annual Dropouts • Phase 1  6 months to complete

13. Development Decisions Program the business rules Create tables of final data + Maximum control + Programmable update/refresh schedule + Improved system response time – Program Automation – Creating a new view of existing data + No new work – Requirements gathering – Extensive testing – Rules about releasing data – Rules about aggregation – Annual ongoing maintenance – SLOW processing time

14. Table Structure • De-normalized (“somewhat”) • Subgroup indicator (race, special education, LEP, etc.) • Additional fields for Gender and Race • Records for State-level, District-level and Sch-level data • Possible: white males in Special Education • Not Possible: white males in both Special Education & LEP programs

15. Data Considerations • What data is best displayed on a map? • How should multi-year, multi-school, multi-anything be displayed in a chart? • Is that different than multi-grade, single year? • How can a user pick one division and all the schools within? • How do we represent suppressed data on a chart? – a map? What data is suppressed?

16. Customized Reports • Fall Membership • Dec 1 Special Education Child Count • Annual Graduates and Completers • Annual Dropouts

17. The Next Step – Phase 2 Reports • Assessment Reports • Cohort Graduation and Dropout Reports • Virginia On-Time Graduation Rate • Federal Graduation Indicator (ESEA) • Graduation/Completion Index

18. Assessment Reports • Analyzed 4 years’ worth of Ad Hoc requests to determine what the BI tool had to address • Aggregate groups of data • Include more variables • AND logic – a student is in both subgroups • OR logic – a student is in one, the other or both • Build-A-Table capability needed

19. A New Table Structure • De-normalized (“more or less”) • fields for Gender, Race, Grade, every program and demographic for which there is data • Records for State-level, District-level and Sch-level data • Added more assessment-specific fields • Test type, scaled score, proficiency level, retesting identifiers (to name a few)

20. Same Table Maintenance Plan • Availability based on publicly released data • Scheduled updates that use last update triggers in the database • Limited human intervention required which greatly reduces the chances of errors

21. Landing Page for Build-A-Table Define inclusive or exclusive groups here

22. Landing Page for Build-A-Table

23. Other Phase 2 Reports • Available in the Build-A-Table tool • Also includes pre-defined reports similar to Phase 1 reports • Existing Phase 1 reports will be available in Build-A-Table tool • Will require new table structures

24. Phase 3 and Beyond • School Nutrition Program Data • AYP and other Accountability ratings • Advanced Placement classes • Career and Technical Education Data • Finance Data • Teacher Data

25. Questions

26. Contact Information • E-Mail: • Susan.Williams@doe.virginia.gov • Scott.Walden@doe.virginia.gov • Phone: • (804) 225-2099