Architecting our Aspen SIS Integration
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 already support many of the common student information systems: Illuminate, PowerSchool, Skyward, and eSchool Plus. We’re excited to expand that to support Aspen, a student information system by Follett.
Aspen allows you define data exports using both Java and XML. We’ve found Java to be more powerful, but XML to be easier to work with. We’ve built out a data export for each of our base data sets. The first district to request Aspen data was Foster-Glocester and their data analyst, Ann Mariano, jumped in and helped build out the XML with us!
Our ETL (extract, transform, load) process for extracting data from Aspen and loading it into a Data Studio compatible data source (Google Sheets and Cloud SQL) takes advantage of a few solutions provided by Google.
As mentioned above, our Aspen exports are built using XML. The exports can be run manually in Aspen, but Aspen also provides a jobs feature that runs the exports on a schedule and saves the files to an SFTP server. We use Google Apps Script heavily in our work. For us the one missing feature of Apps Script is the ability to fetch files from an SFTP server. That’s where Google Cloud Functions comes in. Our schools have Google Apps Script scripts that run nightly to pickup the file from the SFTP server via Google Cloud Functions, do some light processing, and then sends the data to both Google Sheets and their Cloud SQL data warehouse.