Building our PowerSchool SIS connector
The Data Viz Starter Pack extracts data from your key data systems to seed a series of data sets that you can use to create reports in Google Data Studio. We support a number of edTech applications and student information systems, one of them being PowerSchool. This article is a “behind the scenes” look at how we engineered this integration so you can understand how the Starter Pack does what it does.
PowerSchool maintains a website, PowerSource, that serves as their customer support portal providing knowledge base articles, a support ticketing system, and customer forums. If you are the technical contact of your organization, you also have access to their developer focused portal. This developer portal is dense and filled with helpful information.
The diagram below shows how we’ve architected our PowerSchool integration. There are two main components to this integration: our PowerSchool plugin and our Python scripting library.
We provide a plugin that is installed in your PowerSchool instance. Plugins are detailed on PowerSource here. Plugins come in the form of a ZIP file and will typically contain a few different files. At the root is a plugin.xml file that has some metadata defined for the plugin such as who made it and a brief description of its intended use. This file also has a <access_request> section that details all database fields the plugin has access to and whether that access is read only or full read and write. The Starter Pack has read only access to the fields needed to seed the data sets. The plugin also has a folder queries_root/ that contains our SQL queries.
Python Scripting Library
The second piece to our integration is our Python scripting library. The scripting library runs on a Linux virtual machine in your Google Cloud environment. The library handles authenticating (via oAuth) with the PowerSchool APIs created by the plugin, extracting the data into a Pandas dataframe, and then loading it into Google Sheets and/or Google Cloud SQL.
Writing your own SQL
If your instance is hosted by PowerSchool, you can request VPN and ODBC credentials from the support desk. This will allow you to connect to PowerSchool’s network and run SQL queries against the PowerSchool database. In an upcoming blog post we will detail out which tables and views we found most helpful when writing SQL queries against the PowerSchool database.