K12 school districts need local data warehouses

In Utah, the state office maintains a data warehouse that enables record exchange between districts and smaller systems, as well as populating a longitudinal database for analysis. Both efforts still feel like they are in the early days, especially the longitudinal database. As far data warehousing for K12 education, the state is the only game in town and their DW efforts are almost entirely shaped by legal and logistical necessity.

This leaves a gap that districts must fill. How can districts structure and store data for their own analyses? Currently, district personnel make do with pulling reports from their SIS (student information system) or exporting tables from the SERF disk, an ACCESS database which is USOE’s method of returning accountability datasets to the district level. These sources are clunky, often redundant, and slow.

In an era of real-time analyses, machine learning, and Big Data, Utah K12 districts are still struggling to compile data that is months old. It’s 2015, shouldn’t we be able to predict which students will likely not graduate?  Which students need multi-year reading interventions as they transition from elementary to secondary? We have all of the data, but we don’t have a way to put all the pieces together consistently and automatically. That’s where a data warehouse comes in.

Three reasons K12 districts need local data warehouses:

Reason #1: We are data rich, but information poor.

Knowing what works in your district shouldn’t be as much of a guessing game as it is. For example, our CTE department asked me to compile a list of students who had completed multiple CTE credits and then compare that to other populations in terms of other academic achievements, such as AP scores and pass rates.  The query to gather the CTE credits per students had to be broken up into school-by-school, year-by-year pieces and even then each query took nearly seven hours to run. We have all of the data needed to explore many questions about program effectiveness, but the data is stored in such a hard-to-get way that most people ignore it.


Reason #2: The SIS system is not built for analysis

As I said previously, the SIS is slooowww. Queries across the district take upwards of a day to run. It has to do with the way the data is stored. The tables in the SIS database aren’t set up to allow the data to be easily analyzed. They’re set up to allow the data to be easily entered. That’s a key difference.  An SIS is an operational system: the focus is updating and recording the data. In a data warehouse, the focus would be on compiling and analyzing the data, which would involve a different data structure. Another way to think of the difference? An SIS deals with one transaction at a time (student marked absent, one set of scores added, a class dropped). A data warehouse would almost never deal with one student at a time. Instead the focus would always be counting absences, grades, or changes for large groups across time.

As Ralph Kimball and Margy Ross (gurus of data warehousing) stated, “Simply speaking, the operational systems are where you put the data in, and the [Data Warehouse] system is where you get the data out.”

K12 districts need a system that allows them to easily get the data out.

#3 There’s no pre-packaged solution.

New start-ups like Mastery Connect, Amplify, and Imagine Learning all promise high-level, fast reporting. To be sure, these applications are better than anything that we’ve had before. Nevertheless, the reports you can pull from these vendor systems are narrow, incomplete, and inflexible. And it’s not their fault. How could they build a system that anticipated the needs of every school district? They simply can’t. They can build least-common-denominator reports and that’s all.

To keep up with evolving reporting and analysis needs, K12 districts need their own data warehouses, built from the ground up, using tools like SQL Server, Cognos, or Pentaho.


What’s an SGP? An introduction for Utah educators

One of the best recent developments in public education is the ability to measure, consistently and on a large scale, student growth. In Utah, all students who take the SAGE summative assessments get scored on their content and skill mastery. More than that, a growth score is also calculated for each student. These growth scores, combined with proficiency scores, give us the most useful indication yet of how students are progressing year-to-year across the state.

What are the advantages of using SGPs?

  • The focus is not on categorizing or labeling, but on tracking growth
  • It is a meaningful measure for all students, from least to most advanced
  • It is straightforward and comparable across all classrooms

What are the disadvantages?

  • Not a diagnostic measure, i.e. there is no indication as to the “why” behind student growth.
  • There’s still a ceiling for the highest-performing students

Questions for thinking through your students’ SGPs

  • How do growth metrics compare to what you already know about students?
  • Which students are not proficient, but are showing growth?
  • Which students are proficient, but not showing growth?
  • How does growth compare across subject areas for each student? Is growth in Math, ELA, and Science similar or different?
  • Are any student groups (by demographic or classroom) showing patterns or trends?

This overview is brief, and there is much more potential use for SGPs in the classroom. How good is SGP as metric of school quality? That I can’t say.  Making causal claims to categorize classrooms, teachers or schools is really not the point of an SGP. The best use for this metric is to monitor our own instruction as teachers and our own leadership as administrators, and see where we can take steps forward.  Data like this is always meant to start conversation, not bypass it.

Further information on SGPs available from

Legislative Letter: Support continued investment in SAGE and SGP

Utah policymakers and stakeholders should support for the SAGE (Student Assessment of Growth and Excellence) and the resulting SGP (student growth percentile) for the following reasons:

  • Student Growth Percentile is the best metric we have in Utah public education. An SGP, and resulting MGP (median growth percentile) is a simple and informative metric. It helps teachers monitor the effectiveness of their own instruction, helps districts monitor effectiveness of schools, teachers, and programs, and helps make comparisons across the state. (Student Growth Percentiles 101 from RAND http://www.rand.org/education/projects/measuring-teacher-effectiveness/student-growth-percentiles.html)
  • We have avoided the pitfalls of PARCC and value-added models. Instead of a nationalized test such as PARCC from Pearson, Utah uses an adaptive assessment built to Utah standards. That allows Utah to work closely with American Institutes for Research to continue tailoring the assessment, which is ideal.
  • Effective implementation takes time and patience. The SGP and the adaptive version of SAGE have not been around long enough to be consistently integrated into public education culture and decision-making. Education administrators need time to integrate this data into our information systems, educate teachers and stakeholders, and develop effective analysis and reporting protocols.
Ideally, we should use SAGE and student growth metrics through an entire 3rd-12th grade cohort (from 2013-2014 to 2021-2022). Consistent data over a long time is necessary for optimizing our public education system and such longitudinal data would be a first.
There are additional ways to support SAGE and SGP beyond the issue of funding.
  • Avoid inconsistent or contradictory policies. I am especially concerned with any legislative attempts to exempt certain public schools or classrooms from evaluation models. We should monitor student growth across the board.
  • Be cautious in attaching incentives to this assessment and metric. Educators have internal incentive to help students succeed, and rewarding or punishing based on metrics may undermine that. SGP is an informative metric, but not a decisive one.
  • Find out the assessment scores and growth metrics for the schools in your area (USOE’s Data Gateway can help). Compare percent proficient on SAGE to the Median Growth Percentile. It’s especially helpful to view the schools in four quadrants:
    • High growth, high proficiency. We’d all like to be here.
    • High growth, low proficiency. The students started and ended with lower proficiency scores, but showed above-average improvement. In these schools, low proficiency scores may be masking high growth.
    • Low growth, high proficiency. The students started and ended with high proficiency scores, but showed below-average growth. In these schools, high proficiency scores may be masking low growth.
    • Low growth, low proficiency. No one wants to be in this quadrant, but this result should spark thoughtful conversation and decision-marking about how to improve.
We have a successful assessment and metric. The SAGE test and SGP metric are meeting our initial expectations. Let’s stick with them. Utah public education has made strides in increasing graduation rates, decreasing achievement gaps, improving early literacy, etc. We have the methods and data to do more given time.
Legislative Letter 1: SB 204 and grading policy
Legislative Letters: be sure to communicate with policymakers about what you know.

Legislative Letter: Using SAGE assessments as part of grading, promotion

In 2014, the Parental Rights in Education bill was passed and was updated in 2015.

The bill covers many areas, but one change between 2014-2015 is problematic for Utah teachers and administrators.

Utah Code Section 53A-1-603.4.f was altered as follows

90     (f) providing that scores on the tests and assessments required under Subsection (2)(a)
91     and Subsection (3) [shallmay not be considered in determining:
92     (i) a student’s academic grade for the appropriate course [and]; or

93     (ii) whether a student [shallmay advance to the next grade level.

The practical translation for educators is this: you can’t use SAGE Summative as part of students’ term 4/final grades.

Legislating that the results of SAGE Summative may not be considered in a student’s grade is an unnecessary intrusion into school and classroom management. In practice, this results in the SAGE Summative not being used as a the summative assessment for the class when it is appropriate to do so. For example, a Biology class could use SAGE Summative as a comprehensive final, as it covers the standards taught. Instead, students may be required to take a separate final exam within 1-2 weeks of taking the SAGE final exam.

As far as grade promotion being determined by standardized assessments, are there any Utah LEAs using them for that purpose? I am not aware of any. It would be more appropriate to say that students’ promotion may not solely be determined by a test, rather than mandating it not be used at all. As student non-promotion decisions are currently handled on a case-by-case basis, the legislature should not seek to limit the information parents and educators can use in reaching a decision.

Especially when there is concern about over-testing, we should be cautious in amending our education policy in ways that may require teachers to develop redundant assessments.

I’ve already emailed these thoughts to my legislators. I encourage you to communicate what you know and think as well.

Legislative Letter 1: SB 204 and grading policy

Looking at a new assessment? Ask these questions first.

Questions to ask before adopting a computer-based test system
Assessments, like all things, are moving online. While this saves paper and creates possibilities for data and reporting, you are changing one set of problems for another. Before saying yes, make sure your vendor can work with you to answer the following questions.
  • How well does this test fit with our district strategy?
  • How well will this help us realize our strategy?
  • How well will this fit with our existing IT infrastructure?
Accounts management
  • How will users be added and deleted?
  • Is it possible to extract user data from the SIS to set up accounts automatically?
  • What is the unique identifier for students within the system?
  • What is the unique identifier for teachers within the system?
  • How will the district manage user accounts?
  • How will teachers manage student accounts? Will they be able to print, access, and reset student passwords?
  • How hard is it to switch students to a different teacher the day of the test?
  • Can teacher/administrative users be associated with multiple schools within the assessment system?
  • What is the process for managing accommodations? Who can enter and manage students’ test accommodations? How many users per school will be able to do this?
IT Infrastructure
  • Is a secure browser available?
  • If no secure browser is available, which browser versions are supported?
  • When and how are regular system updates scheduled?
  • How are users notified of system downtime?
  • How do students begin a test session? Will they be able to switch computers mid-test?
  • For a recording component, how will teachers be trained to troubleshoot microphones?
  • Will schools need to purchase more computers to accommodate this test?
  • Does the teacher interface display the same way in all major browsers?
  • How will students be accommodated? Is there a built-in screen reader? Adjustable contrast and font size? Volume control?
  • If there is an audio component, will students be able to adjust volume during the test?
  • If there is a recording component, is there a scrubber so students can replay and re-record?
  • What tools will students be able to use within the interface? Highlighting? Flagging? Note-taking? Reference sheets? Calculators?
  • What is the objective for this data?
  • Get we get large-scale data extracts or dumps? In what formats?
  • How will teachers access test results? District administrators? Students? Parents?
  • How soon the test results be ready?
  • Once the test results are ready, what is the plan or strategy for reporting on them or making use of them?
  • What user roles are available and what access to data does each role have?
Implementation and Training
  • How will employees be trained on this system? How many training opportunities before a live session?
  • What opportunities will students have to interact with this system before a live testing session?
  • How accessible is the Test Administration Manual to teachers?
  • Are user tutorials available within the system?
  • What is the cost per student? Per administration window?
  • What is the cost of related equipment: headphones, microphones, etc?
  • What is the cost of training and professional development?