SQL and PowerSchool

As a “back-end” PowerSchool guy or gal, you may occasionally be asked to pull out data for which there is no pre-built report. In most cases this is possible, but it’s also usually really complicated. The purpose of this post is to (hopefully) provide you with some direction when you are “voluntold” to do undertake such a task.

Data Dictionaries

Chances are you’re somewhat familiar with PowerSchool’s Data Dictionary. The Data Dictionary is a massive PDF reference that defines each table and field in PowerSchool – except for the ones in Gradebook. After spending an hour and a half on the line with support one time, we discovered together that their exists a completely separate Data Dictionary just for the Gradebook. It’s called the PowerSchool Gradebook Data Dictionary. So, if you’re tasked with pulling out all of the standards based grades for a single assignment that can be parsed by school, standard, and teacher like I was, you’re going to want to look there to figure out how all of the tables tie together

High Level Concept

What you’ll find is that there exist many tables, and each table is connected to another by a common column. For example, the WhoCreated column in the psm_assignmentstandardscore table contains the same values as the id column in the psm_teacher table. By using the tool SQL Developer (by Oracle), we can connect directly to our database and write custom queries in a language called SQL to output the data in a manner that is useful. When we query data from multiple tables, we have to choose one table FROM where we are going to conduct our search and then “JOIN” the other tables to the first to eventually generate a new spreadsheet that contains the data that we actually want. Sometimes we need to join one table, to another, to another, TO ANOTHER! to get what we are looking for.

Basically, you are going to find that all of the data you want in your output is divided among many tables, and you are going to have to reference the Data Dictionary to figure out how all of the tables connect.

Filters

Once you’ve figured out how to get at the data you seek, you’re going to realize that there is WAY MORE of it than you realized. For example, there existed over 1,800,000 individual standards-based grades at the time I ran the query below, and our district is only in its second year with the system! Fortunately, you can use WHERE and LIKE statements to only return values that meet certain conditions – like if the standard identifier contains the characters ELA, or if the assignment abbreviation = EALU2.

My Query

The query below returned all of the standards-based scores that were attached to a common assessment that every student in our district completed. In order for this query to work, teachers had to use the same Abbreviation, when they entered the assisgnment into their gradebooks. The code below is heavily commented. Everything in between /* and */ is a comment. The syntax highlighter plugin I’m using isn’t displaying SQL code well, so I set it to plain text, but if you copy and paste it into a text editor like Notepad++ and set the language to SQL it should become much easier to read.

After you watch a couple of SQL tutorials on YouTube, the code below should provide you with a pretty good understanding of how every comes together when you’re faced with such a challenge.

Two Last Things

Once you extract the data, you’ll probably have to save it as a .CSV, open it in Excel or a Google Spread Sheets, insert a pivot table, and manipulate the data as you see fit. If you don’t know what a pivot table is, and you have to deal with large volumes of data, you need to spend more time watching tutorials on YouTube.

It can be very helpful to sketch out what the tables look like. I wouldn’t have written this query successfully without staring at this sketch for WAY TOO LONG.
table_diagram

The Code

/*
Title: End of Unit Exam Query
Author: Mike Macfadden
Version: 0.1
Date Published: 11/14/13

About:
The query below pulls out standards-based scoring data by teacher and school for an individual assignment (like the ELA end of unit exams). When exported to excel, this data can be inserted into a pivot table and used to identify trends and to set goals.

Usage Notes: 
0. !!!CRITICAL!!! TEACHERS MUST USE THE SAME ABBREVIATION IN GRADEBOOK FOR THIS TO WORK. !!!CRITICAL!!!
1. This query should be run using SQL Developer by Oracle. 
2. Once run, export the output as a CSV file.
3. Open the CSV file in excel
4. Insert a pivot table:  Report Filer(s):	IDENTIFIER, NAME_2
						  Column Labels:		N/A
						  Row Labels:			NAME
						  Values:				ACTUALSCOREENTERED (Value Field Settings: Average)
5. Use the IDENTIFIER filter to parse out grade level scores
*/



/* The SELECT statement queries the specific field values that you would like in you spreadsheet */
SELECT sch.name, tea.firstname, tea.lastname, assn.name, st.identifier, st.name, ass.actualscoreentered

/* The individual assignment standard-based scores are saved in the table: psm_assignmentstandardscore (AKA: 'ass') */
FROM psm_assignmentstandardscore ass 

/* In order to see the standard identifier and name in our output, we need to JOIN these two tables to 'ass' */
  JOIN psm_assignmentstandard ast
    ON ass.assignmentstandardid = ast.id 
      JOIN psm_standard st
        ON ast.standardid = st.id
  
/* In order to see which assignment is attached to the standards and scores, we need to JOIN these three tables to 'ass' */
  JOIN psm_assignmentstandard ast
    ON ass.assignmentstandardid = ast.id 
      JOIN psm_sectionassignment sa
        ON ast.sectionassignmentid = sa.id
          JOIN psm_assignment assn
            ON sa.assignmentid = assn.id
/* In order to parse out the data amongst the different schools in the district, we need to JOIN these three tables to 'ass' */
  JOIN psm_sectionenrollment se
    ON ass.sectionenrollmentid = se.id
      JOIN psm_section sct
        ON se.sectionid = sct.id
          JOIN psm_school sch
            ON sct.schoolid = sch.id
/* In order to see which teacher belongs to the scoring data we need to JOIN this table to 'ass' */
  JOIN psm_teacher tea
    ON ass.whocreated = tea.id

/* Change the value in single quotes after like to match the assignment abbreviation in Gradebook */
WHERE assn.abbreviation LIKE 'ELAU2'

/* Although unlikely, this filters out any non-ELA assignments that might have the same abbreviation */
AND st.identifier LIKE '%ELA%'

/* Change the date to filter out assignments from previous years with the same Abbreviation */
AND ass.whencreated > to_date('8/21/2013','mm/dd/yyyy')

/* This orders the date by school */
ORDER BY sch.name

/*Good Luck*/

One thought on “SQL and PowerSchool

  1. I am a data analyst with Cabarrus County Schools and need help figuring out how to find students without a full schedule using oracle or access.

    My colleague and I have only gotten so far and keep getting hung up on the membership default table…any suggestions?

Comments are closed.